iamberry-common-parent/watero-rst-web/src/main/java/com/iamberry/rst/utils/ExcelUtil.java

package com.iamberry.rst.utils;

import com.iamberry.rst.core.order.EfastOrder;
import com.iamberry.rst.core.order.ProductColor;
import com.iamberry.rst.core.pts.PtsBomComponents;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel工具类
 *
 * @author 献
 * @company 深圳爱贝源科技有限公司
 * @website www.iamberry.com
 * @tel 18271840547
 * @date 2017/5/25
 */
public class ExcelUtil {

    private static final org.slf4j.Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * 读取Excel文件头
     *
     * @param filePath
     * @return
     */
    public static List<String> readExcelHead(String filePath) throws IOException {
        List<String> heads = new ArrayList<String>();
        // 判断文件是否存在
        File file = new File(filePath);
        if (!file.exists()) {
            return null;
        }
        // 获取Workbook
        InputStream inputStream = new BufferedInputStream(new FileInputStream(file));
        Workbook wb = null;
        if (filePath.endsWith("xls")) {
            wb = new HSSFWorkbook(inputStream);
        } else {
            wb = new XSSFWorkbook(inputStream);
        }
        inputStream.close();

        // 获取Sheet
        Sheet sheet = wb.getSheetAt(0);
        // 获取第一行数据
        Row row = sheet.rowIterator().next();

        Iterator<Cell> cells = row.cellIterator();
        // 获得第一行的迭代器,并遍历cell
        while (cells.hasNext()) {
            Cell cell = cells.next();
            heads.add(cell.getStringCellValue());
        }

        return heads;
    }

    /**
     * 读取Excel内容
     * 去除重复
     *
     * @param filePath
     * @return
     */
    public static List<String> readCell(String filePath, String cellName) throws IOException {
        // 判断文件是否存在
        File file = new File(filePath);
        if (!file.exists()) {
            return null;
        }
        // 获取Workbook
        InputStream inputStream = new BufferedInputStream(new FileInputStream(file));
        Workbook wb = null;
        if (filePath.endsWith("xls")) {
            wb = new HSSFWorkbook(inputStream);
        } else {
            wb = new XSSFWorkbook(inputStream);
        }
        inputStream.close();

        // 获取Sheet
        Sheet sheet = wb.getSheetAt(0);
        Iterator<Row> rows = sheet.rowIterator();

        // 获取第一行的数据，匹配到表头
        int cellNum = -1;
        Row row = rows.next();
        Iterator<Cell> cells = row.cellIterator();
        while (cells.hasNext()) {
            Cell cell = cells.next();
            if (cellName.trim().equals(cell.getStringCellValue().trim())) {
                cellNum = cell.getColumnIndex();
                break;
            }
        }
        if (cellNum == -1) return null;

        // 读取数据
        Map<String, String> map = new HashMap<>();
        while (rows.hasNext()) {
            Row temp = rows.next();
            Cell cell = temp.getCell(cellNum);
            // 非空的列需要使用
            if (cell == null) {
                continue;
            }
            String name = getValue(cell);
            if (name != null && !"".equals(name.trim())) {
                String cellValue = getValue(cell);
                if (map.get(cellValue) == null) {
                    map.put(cellValue, cellValue);
                }
            }
        }

        // Map to List
        Collection<String> valueCollection = map.values();
        return new ArrayList<String>(valueCollection);
    }

    /**
     * 读取Excel内容
     * 读取bom单excel的内容
     *
     * @param filePath
     * @return
     */
    public static Map<String, Object> readExcelBom(String filePath) throws IOException {
        // 判断文件是否存在
        File file = new File(filePath);
        if (!file.exists()) {
            return null;
        }
        // 获取Workbook
        InputStream inputStream = new BufferedInputStream(new FileInputStream(file));
        Workbook wb = null;
        if (filePath.endsWith("xls")) {
            wb = new HSSFWorkbook(inputStream);
        } else {
            wb = new XSSFWorkbook(inputStream);
        }
        inputStream.close();

        // 获取Sheet
        Sheet sheet = wb.getSheetAt(0);

        //获取bom单名称
        Row rows = sheet.getRow(1);// 获取行
        Cell cell = null;
        cell = rows.getCell(1);//列
        String bomName = "";
        if(cell != null){
            bomName = cell.getStringCellValue();
        }

        //获取产品名称
        rows = sheet.getRow(2);// 获取行
        cell = rows.getCell(1);//列
        String produceName = "";
        if(cell != null){
            produceName = cell.getStringCellValue();
        }

        //硬件版本号
        rows = sheet.getRow(3);// 获取行
        cell = rows.getCell(1);//列
        String bomVersion = "";
        if(cell != null){
            bomVersion = cell.getStringCellValue();
        }

        //软件版本号
        rows = sheet.getRow(4);// 获取行
        cell = rows.getCell(1);//列
        String machineVersionNo = "";
        if(cell != null){
            machineVersionNo = cell.getStringCellValue();
        }

        //备注
        rows = sheet.getRow(5);// 获取行
        cell = rows.getCell(1);//列
        String bomRemarks = "";
        if(cell != null){
            bomRemarks = cell.getStringCellValue();
        }

        List<PtsBomComponents> componentsList = new ArrayList<PtsBomComponents>();
        Iterator<Row> rowss = sheet.rowIterator();
        int i = 0;
        while (rowss.hasNext()) {
            Row ro = rowss.next();
            if(i>7){
                Iterator<Cell> cells = ro.cellIterator();
                if(ro == null){
                    break;
                }

                int j=0;
                PtsBomComponents bomComponents = new PtsBomComponents();
                String componentsNo = "";
                String bomComponentsQuantity = "";
                while (cells.hasNext()) {
                    Cell ce = cells.next();
                    if (ce == null){
                        break;
                    }
                    if(j == 0){
                        componentsNo = ce.getStringCellValue();
                    }else if(j == 1){
                        bomComponentsQuantity = ce.getStringCellValue();
                    }
                    j++;
                }
                bomComponents.setComponentsNo(componentsNo);
                bomComponents.setBomComponentsQuantity(Integer.valueOf(bomComponentsQuantity) * 100);
                componentsList.add(bomComponents);
            }
            i++;
        }

        Map<String,Object> map = new HashMap<String, Object>();
        map.put("bomName",bomName);
        map.put("produceName",produceName);
        map.put("bomVersion",bomVersion);
        map.put("machineVersionNo",machineVersionNo);
        map.put("bomRemarks",bomRemarks);
        map.put("componentsList",componentsList);
        return map;
    }





    /**
     * 读取Excel内容
     * 不去除重复
     *
     * @param filePath
     * @return
     */
    public static List<String> readCell2(String filePath, String cellName) throws IOException {
        // 判断文件是否存在
        File file = new File(filePath);
        if (!file.exists()) {
            return null;
        }
        // 获取Workbook
        InputStream inputStream = new BufferedInputStream(new FileInputStream(file));
        Workbook wb = null;
        if (filePath.endsWith("xls")) {
            wb = new HSSFWorkbook(inputStream);
        } else {
            wb = new XSSFWorkbook(inputStream);
        }
        inputStream.close();

        // 获取Sheet
        Sheet sheet = wb.getSheetAt(0);
        Iterator<Row> rows = sheet.rowIterator();

        // 获取第一行的数据，匹配到表头
        int cellNum = -1;
        Row row = rows.next();
        Iterator<Cell> cells = row.cellIterator();
        while (cells.hasNext()) {
            Cell cell = cells.next();
            if (cellName.trim().equals(cell.getStringCellValue().trim())) {
                cellNum = cell.getColumnIndex();
                break;
            }
        }
        if (cellNum == -1) return null;

        // 读取数据
        ArrayList<String> arreyMap = new ArrayList<String>();
        while (rows.hasNext()) {
            Row temp = rows.next();
            Cell cell = temp.getCell(cellNum);
            // 非空的列需要使用
            if (cell == null) {
                continue;
            }
            String name = getValue(cell);
            if (name != null && !"".equals(name.trim())) {
                String cellValue = getValue(cell);
                arreyMap.add(cellValue);
            }
        }

        // Map to List
        /*Collection<String> valueCollection = map.values();*/
        return arreyMap;
    }


    public static List<EfastOrder> readCell(String filePath, String productName, String[] maps,
                                            String[] infos, String tel, String name,String platformOrder, String num,
                                            Map<String, ProductColor> price, String postType, String shopId)
            throws IOException {
        Map<String, String> map = new HashMap<>();
        for (String t : maps) {
            String[] temp = StringUtils.split(t, "_");
            map.put(temp[1], (temp[0]));
        }

        // 判断文件是否存在
        File file = new File(filePath);
        if (!file.exists()) {
            return null;
        }
        // 获取Workbook
        InputStream inputStream = new BufferedInputStream(new FileInputStream(file));
        Workbook wb = null;
        if (filePath.endsWith("xls")) {
            wb = new HSSFWorkbook(inputStream);
        } else {
            wb = new XSSFWorkbook(inputStream);
        }
        inputStream.close();

        // 获取Sheet
        Sheet sheet = wb.getSheetAt(0);
        Iterator<Row> rows = sheet.rowIterator();

        // 获取第一行的数据，匹配到表头
        int nameColumnIndex = -1;
        int platformOrderColumnIndex = -1;
        int telColumnIndex = -1;
        int productColumnIndex = -1;
        int numColumnIndex = -1;
        int[] infoColumnIndex = new int[infos.length];
        int infoCount = 0;

        // 读取数据
        Row row = rows.next();
        Iterator<Cell> cells = row.cellIterator();
        while (cells.hasNext()) {
            Cell cell = cells.next();
            if (name.equals(cell.getStringCellValue().trim())) {
                // 匹配姓名列
                nameColumnIndex = cell.getColumnIndex();
            } else if (platformOrder.equals(cell.getStringCellValue().trim())) {
                // 匹配订单编号
                platformOrderColumnIndex = cell.getColumnIndex();
            } else if (tel.equals(cell.getStringCellValue().trim())) {
                // 匹配电话
                telColumnIndex = cell.getColumnIndex();
            } else if (productName.equals(cell.getStringCellValue().trim())) {
                // 匹配产品列
                productColumnIndex = cell.getColumnIndex();
            } else if (num.equals(cell.getStringCellValue().trim())) {
                // 匹配购买数量
                numColumnIndex = cell.getColumnIndex();
            } else {
                // 匹配地址（可能是多个，需要组合连接）
                for (int i = 0; i < infos.length; i++) {
                    if (infos[i].equals(cell.getStringCellValue().trim())) {
                        infoColumnIndex[i] = cell.getColumnIndex();
                    }
                }
            }
        }

        List<EfastOrder> orderEfasts = new ArrayList<>();

        // 样式
        HSSFCellStyle cellStyle = (HSSFCellStyle) wb.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

        // 读取数据
        int oid = 0;
        while (rows.hasNext()) {
            Row temp = rows.next();
            try {

                String nameValue = getValue(temp.getCell(nameColumnIndex));  // 读取name
                String platformOrderIdValue = getValue(temp.getCell(platformOrderColumnIndex));  // 读取平台id
                //获取手机号码
                //String userTel = getValue(temp.getCell(telColumnIndex)).trim();   // 读取tel
                Cell telCell = temp.getCell(telColumnIndex);
                String userTel = String.valueOf(getExcelCell(telCell));
                String OrderProductBarCodeValue = map.get(getValue(temp.getCell(productColumnIndex))); // 读取对应的产品id

                if(StringUtils.isEmpty(nameValue) && StringUtils.isEmpty(userTel) && StringUtils.isEmpty(OrderProductBarCodeValue)){
                    break;
                }

                EfastOrder order = new EfastOrder();
                order.setOrderAddressName(nameValue);
                if(!StringUtils.isEmpty(platformOrderIdValue)){
                    order.setPlatformOrderId(platformOrderIdValue.trim());
                }else{
                    order.setPlatformOrderId("1");
                }

                // 如果手机号码校验不通过，则应该不录入
                if (!ValidateUtil.checkMobile(userTel.trim())) {
                    //temp.getCell(telColumnIndex).setCellStyle(cellStyle);
                    order.setReturnStatus("error");
                    order.setReturnMsg("手机号码不正确");
                    //continue;
                }
                order.setOrderAddressTel(userTel.trim());

                order.setOrderProductBarCode(OrderProductBarCodeValue);
                String orderNum = getValue(temp.getCell(numColumnIndex));
                if(orderNum == null || "".equals(orderNum)){
                    order.setReturnStatus("error");
                    order.setReturnMsg("数量不正确");
                    order.setOrderNum(0);               // 读取购买数量
                }else{
                    order.setOrderNum(Integer.valueOf(orderNum));               // 读取购买数量
                }

                if (infoColumnIndex.length == 1){
                    // 如果infoColumnIndex = 1, 则表示需要截取（可能是-、“ ”）
                    Cell cell = temp.getCell(infoColumnIndex[0]);
                    String addrInfo = getValue(cell);
                    if (addrInfo == null || "".equals(addrInfo)) {
                        // 地址为空
                        cell.setCellStyle(cellStyle);
                        continue;
                    }

                    // 处理地址信息
                    String[] addrs = splitAddress(addrInfo);
                    if (addrs == null && addrs.length < 4) {
                        // 地址不合法
                        cell.setCellStyle(cellStyle);
                        continue;
                    }

                    order.setOrderProvince(addrs[0].trim());
                    order.setOrderCity(addrs[1].trim());
                    order.setOrderArea(addrs[2].trim());
                    order.setOrderAddress(addrInfo.trim());
                } else if (infoColumnIndex.length == 4) {
                    order.setOrderProvince(getValue(temp.getCell(infoColumnIndex[0])).trim());
                    order.setOrderCity(getValue(temp.getCell(infoColumnIndex[1])).trim());
                    order.setOrderArea(getValue(temp.getCell(infoColumnIndex[2])).trim());
                    order.setOrderAddress(getValue(temp.getCell(infoColumnIndex[3])).trim());
                } else if (infoColumnIndex.length == 3) {
                    order.setOrderProvince(getValue(temp.getCell(infoColumnIndex[0])).trim());
                    order.setOrderCity(getValue(temp.getCell(infoColumnIndex[1])).trim());
                    order.setOrderArea(getValue(temp.getCell(infoColumnIndex[1])).trim());
                    order.setOrderAddress(getValue(temp.getCell(infoColumnIndex[2])).trim());
                } else {
                    for (int i : infoColumnIndex) {
                        temp.getCell(infoColumnIndex[i]).setCellStyle(cellStyle);
                    }
                    continue;
                }
                // 支付方式
                order.setOrderPayType(1);               // 微信支付
                order.setOrderPayMoney(price.get(order.getOrderProductBarCode()).getColorDiscount() * order.getOrderNum());
                order.setOrderAmount(price.get(order.getOrderProductBarCode()).getColorDiscount() * order.getOrderNum());
                order.setOrderOpenId(order.getOrderAddressTel());
                order.setOrderProductName(price.get(order.getOrderProductBarCode()).getColorName());
                order.setOrderId(OrderNoUtil.createOrderCode(Math.abs(new Random().nextInt(1000))));
                order.setOrderRemark("");
                order.setOrderStoreId(shopId);           // 微商/代理商仓库
                order.setOid(oid);                       // oid
                if (postType.equals("ems")) {
                    if (order.getOrderProvince().contains("广东")) {
                        order.setOrderPostType("ems");
                    } else {
                        order.setOrderPostType("eyb");
                    }
                } else {
                    order.setOrderPostType(postType);
                }

                // 保存
                orderEfasts.add(order);
            } catch (Exception e) {
                row.getCell(0).setCellStyle(cellStyle);
                logger.error(e.getMessage(), e);
            }
            // 迭代
            oid++;
        }

        // 写入
        OutputStream output = new BufferedOutputStream(new FileOutputStream(filePath));
        wb.write(output);
        output.close();
        return orderEfasts;
    }


    /**
     *获取值
     * @param cell
     * @return
     */
    private static Object getExcelCell(Cell cell){
        Object obj;
        if (null != cell) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: // 数字
                    double cellValue = cell.getNumericCellValue();
                    obj = new DecimalFormat("#").format(cellValue);
                    break;
                case HSSFCell.CELL_TYPE_STRING: // 字符串
                    obj = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    obj = cell.getBooleanCellValue();
                    break;
                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                    obj = cell.getCellFormula();
                    break;
                case HSSFCell.CELL_TYPE_BLANK: // 空值
                    obj = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR: // 故障
                    obj = "";
                    break;
                default:
                    obj = "";
                    break;
            }
        } else {
            obj = "";
        }
        return obj;
    }

    /**
     * 地址 - 整体切割符
     */
    private static String[] ENTIRETY_SPLIT;

    /**
     * 地址 - 省份数据切割符
     */
    private static String[][] PROVINCE_CITY_AERA_SPLIT;

    static {
        ENTIRETY_SPLIT = new String[]{" ", "-", "_", "*"};
        PROVINCE_CITY_AERA_SPLIT = new String[][]{
                {"省", "市", "区"},
                {"省", "市", "县"},
                {"省", "市", "乡"},
                {"省", "市", "镇"},
                {"省", "市", "市"},

                {"自治区", "市", "区"},
                {"自治区", "市", "县"},
                {"自治区", "市", "乡"},
                {"自治区", "市", "镇"},
                {"自治区", "市", "市"},

                {"省", "州", "区"},
                {"省", "州", "县"},
                {"省", "州", "乡"},
                {"省", "州", "市"},
                {"省", "州", "镇"},

                {"市", "市", "区"},
                {"市", "市", "县"},
                {"市", "市", "镇"},
                {"市", "市", "乡"},

                {"市", "区"},
                {"市", "乡"},
                {"市", "镇"},
                {"市", "县"}
        };
    }

    /**
     * 切割地址
     */
    private static String[] splitAddress(String addrInfo) {
        String[] addrs = null;

        // 首先通过整体切割符合切割
        for (String charSplit : ENTIRETY_SPLIT) {
            addrs = StringUtils.split(addrInfo, charSplit);
            if (addrs != null && addrs.length >= 4) {
                break;
            } else if (addrs != null && addrs.length == 3 && !addrs[0].contains("省")) {
                break;
            }
        }

        // 详细地址不能直接切割
        if (addrs != null && addrs.length >= 4) {
            // 防止区域数据出现小区 扰乱数据
            if (!addrs[2].contains("小区")) {
                return new String[]{addrs[0], addrs[1], addrs[2], addrInfo};
            }

        } else if (addrs != null && addrs.length == 3) {
            // 防止区域数据出现小区 扰乱数据
            if (!addrs[1].contains("小区")) {
                return new String[]{addrs[0], addrs[0], addrs[1], addrInfo};
            }
        }

        for (String[] splits : PROVINCE_CITY_AERA_SPLIT) {

            // 每组独立切割字符单独循环
            String tempInfo = addrInfo;
            addrs = new String[splits.length + 1];
            boolean isNext = false;
            for (int i = 0; i < splits.length; i++) {
                String splitChar = splits[i];
                String[] temps = StringUtils.split(tempInfo, splitChar);
                if (temps.length >= 2) {
                    // 获取到第一个地址
                    addrs[i] = temps[0] + splitChar;
                    // 下一次迭代内容
                    tempInfo = StringUtils.substring(tempInfo, temps[0].length() + splitChar.length());
                } else {
                    // 如果首次匹配失败，则停止匹配
                    isNext = true;
                    break;
                }
            }
            if (!isNext) {
                if ((addrs.length - 1) == 2) {
                    // 防止区域数据出现小区 扰乱数据
                    if (addrs[1].contains("小区")) {
                        continue;
                    }
                    // 只有两级地址
                    return new String[]{addrs[0], addrs[0], addrs[1], addrInfo};
                } else if ((addrs.length - 1) == 3) {
                    // 防止区域数据出现小区 扰乱数据
                    if (addrs[2].contains("小区")) {
                        continue;
                    }
                    // 三级地址
                    return new String[]{addrs[0], addrs[1], addrs[2], addrInfo};
                }
            }
        }

        return addrs;
    }

    public static void main(String[] args) throws IOException {
        String[] add = splitAddress("新疆维吾尔自治区乌鲁木齐市沙依巴克镇农大东路311号新疆农业大学外国语学院(830002).");
        if (add == null) {
            System.out.println("切割失败...");
        }
        for (String i : add) {
            System.out.println(i);
        }
    }

    private static SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");

    public static String getValue(Cell cell) {
        if (cell == null) return null;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING: //文本
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_NUMERIC: //数字、日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    return fmt.format(cell.getDateCellValue()); //日期型
                }
                return String.valueOf((new Double(cell.getNumericCellValue())).intValue()); //数字
            case Cell.CELL_TYPE_BOOLEAN: //布尔型
                return String.valueOf(cell.getBooleanCellValue());
            case Cell.CELL_TYPE_BLANK: //空白
                return cell.getStringCellValue();
            default: //错误
                return "ERROR";
        }

    }
}