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 com.iamberry.rst.faces.cm.SalesOrderService;
import com.iamberry.wechat.tools.AddrUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

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
 */
@Component
public class ExcelUtil {

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

    @Autowired
    private SalesOrderService salesOrderService;

    /**
     * 读取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内容 -- 行数是否超过100，超过false  不超过true；
     * @param filePath
     * @return
     */
    public static boolean readExcelNumber(String filePath,Integer maxNuber) throws IOException {
        // 判断文件是否存在
        File file = new File(filePath);
        if (!file.exists()) {
            return false;
        }
        // 获取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);

        Integer number = 0;     //控制有效行数在maxNumber之内
        Integer i = 0;         //控制总行数在MaxNumber之内，因为会出现无效行数的问题，如果一直循环，会循环65535行

        Iterator<Row> rows = sheet.rowIterator();
        while (rows.hasNext()) {
            Row row = rows.next();
            Cell cell = row.getCell(0);
            // 非空的列需要使用
            if (cell == null) {
                continue;
            }
            String name = getValue(cell);
            String cellValue = "";
            if (name != null && !"".equals(name.trim())) {
                cellValue = getValue(cell);
            }

            if (cellValue != null && !"".equals(cellValue)) {
                number ++;
            }
            i++;

            if(number > maxNuber+1){
                return false;
            }

            if(i > maxNuber+1){
                break;
            }
        }
        return true;
    }

    /**
     * 读取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 List<EfastOrder> readCell(String filePath, String productName, String[] maps,
                                            String[] infos, String tel, String name,String platformOrder, String num,String remark,
                                            Map<String, ProductColor> price, String postType, String orderStoreInfoSt, Integer isOtherStore,String[] storeArray)
            throws IOException {
        Map<String, String> map = new HashMap<>();
        for (String t : maps) {
            String[] temp = StringUtils.split(t, "_");
            map.put((temp[1].trim()), (temp[0].trim()));
        }

        // 判断文件是否存在
        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 remarkColumnIndex = -1;
        int orderStoreInfoStColumnIndex = -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.trim().equals(cell.getStringCellValue().trim())) {
                // 匹配姓名列
                nameColumnIndex = cell.getColumnIndex();
            }
            if (platformOrder.trim().equals(cell.getStringCellValue().trim())) {
                // 匹配订单编号
                platformOrderColumnIndex = cell.getColumnIndex();
            }
            if (tel.trim().equals(cell.getStringCellValue().trim())) {
                // 匹配电话
                telColumnIndex = cell.getColumnIndex();
            }
            if (productName.trim().equals(cell.getStringCellValue().trim())) {
                // 匹配产品列
                productColumnIndex = cell.getColumnIndex();
            }
            if (num.trim().equals(cell.getStringCellValue().trim())) {
                // 匹配购买数量
                numColumnIndex = cell.getColumnIndex();
            }
            if (remark.trim().equals(cell.getStringCellValue().trim())) {
                // 匹配备注
                remarkColumnIndex = cell.getColumnIndex();
            }
            if (orderStoreInfoSt.trim().equals(cell.getStringCellValue().trim())) {
                // 匹配店铺名称
                orderStoreInfoStColumnIndex = cell.getColumnIndex();
            }
            // 匹配地址（可能是多个，需要组合连接）
            for (int i = 0; i < infos.length; i++) {
                if (infos[i].equals(cell.getStringCellValue())) {
                    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;
        int errorNum = 0;
        while (rows.hasNext()) {
            Row temp = rows.next();
            try {
                if(errorNum > 5){
                    break;
                }
                EfastOrder order = new EfastOrder();
                // 读取name
                String nameValue = getValue(temp.getCell(nameColumnIndex));
                // 读取平台id
                String platformOrderIdValue = getValue(temp.getCell(platformOrderColumnIndex));
                //获取手机号码
                //String userTel = getValue(temp.getCell(telColumnIndex)).trim();   // 读取tel
                Cell telCell = temp.getCell(telColumnIndex);
                String userTel = String.valueOf(getExcelCell(telCell));
                // 读取对应的产品id
                String OrderProductBarCodeValue = map.get(getValue(temp.getCell(productColumnIndex)).trim());
                if(StringUtils.isEmpty(nameValue)){
                    errorNum ++;
                    order.setReturnStatus("error");
                    order.setReturnMsg("姓名为空-交易号:"+platformOrderIdValue);
                    order.setPlatformOrderId("1");
                }
                if(StringUtils.isEmpty(userTel) ){
                    errorNum ++;
                    order.setReturnStatus("error");
                    order.setReturnMsg("手机号码为空-交易号:"+platformOrderIdValue);
                    order.setPlatformOrderId("1");
                }
                if(StringUtils.isEmpty(OrderProductBarCodeValue)){
                    errorNum ++;
                    order.setReturnStatus("error");
                    order.setReturnMsg("交易号出错-交易号:"+platformOrderIdValue);
                    order.setPlatformOrderId("1");
                }

                order.setOrderAddressName(nameValue);
                if(!StringUtils.isEmpty(platformOrderIdValue)){
                    if (!ValidateUtil.checkString(platformOrderIdValue.trim())) {
                        order.setReturnStatus("error");
                        order.setReturnMsg("交易号出错-交易号:"+platformOrderIdValue);
                        order.setPlatformOrderId("1");
                    }else{
                        Integer flag = salesOrderService.getOrderBySalesDealCodeNum(platformOrderIdValue.trim());
                        if(flag > 0){
                            order.setReturnStatus("error");
                            order.setReturnMsg("交易号重复");
                        }
                        order.setPlatformOrderId(platformOrderIdValue.trim());
                    }
                }else{
                    order.setReturnStatus("error");
                    order.setReturnMsg("订单交易号不正确");
                    order.setPlatformOrderId("1");
                }

                // 如果手机号码校验不通过，则应该不录入
                if (userTel.trim().length() < 11 || userTel.trim().length() > 15) {
                    order.setReturnStatus("error");
                    order.setReturnMsg("手机号码不正确");
                    order.setPlatformOrderId("1");
                }
                order.setOrderAddressTel(userTel.trim());

                order.setOrderProductBarCode(OrderProductBarCodeValue);
                String orderNum = getValue(temp.getCell(numColumnIndex)).trim();
                String orderRemark = getValue(temp.getCell(remarkColumnIndex));
                if(orderNum != null && !"".equals(orderNum)){
                    orderNum = orderNum.replaceAll("\\r|\\r|\\n","");
                }
                if(orderNum == null || "".equals(orderNum) || Integer.valueOf(orderNum) == 0){
                    order.setReturnStatus("error");
                    order.setReturnMsg("数量不正确");
                    // 读取购买数量
                    order.setOrderNum(0);
                }else{
                    // 读取购买数量
                    order.setOrderNum(Integer.valueOf(orderNum));
                }
                order.setOrderRemark(orderRemark);
                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;
                    }

                    try{
                        order.setOrderProvince(addrs[0].trim());
                        order.setOrderCity(addrs[1].trim());
                        order.setOrderArea(addrs[2].trim());
                        order.setOrderAddress(addrInfo.trim());
                    }catch (Exception e){
                        order.setReturnStatus("error");
                        order.setReturnMsg("地址错误:"+ addrInfo);
                        order.setPlatformOrderId("1");
                    }
                } 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))));

                if(isOtherStore == 2){
                    // 读取店铺
                    String storeValue = getValue(temp.getCell(orderStoreInfoStColumnIndex)).trim();
                    for (int k=0;k<storeArray.length;k++){
                       String[] stores = storeArray[k].split("_");
                       if(stores[0].trim().equals(storeValue.trim())){
                           //店铺
                           order.setStoreId(Integer.valueOf(stores[1]));
                       }
                    }
                }else{
                    //店铺
                    order.setStoreId(Integer.valueOf(storeArray[0]));
                }
                order.setOid(oid);
                try{
                    if (postType.equals("ems")) {
                        if (order.getOrderProvince().contains("广东")) {
                            order.setOrderPostType("ems");
                        } else {
                            order.setOrderPostType("eyb");
                        }
                    } else {
                        order.setOrderPostType(postType);
                    }
                }catch (Exception e){
                    order.setOrderPostType("ems");
                    e.printStackTrace();
                    logger.info("匹配快递公司失败");
                }
                // 保存
                orderEfasts.add(order);
            } catch (Exception e) {
                row.getCell(0).setCellStyle(cellStyle);
                errorNum ++;
                logger.error(e.getMessage(), e);
            }
            // 迭代
            oid++;
        }

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


    /**
     * 读取Excel内容(上传excel文档)
     * 不去除重复
     *
     * @param filePath
     * @return
     */
    public static List<String> readCell3(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);
        Iterator<Row> rows = sheet.rowIterator();

        // 获取第一行的数据，匹配到表头
        int cellNum = -1;
        Row row = rows.next();
        Iterator<Cell> cells = row.cellIterator();
        while (cells.hasNext()) {
            Cell cell = cells.next();
                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;
    }

    /**
     *获取值
     * @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;
                // Boolean
                case HSSFCell.CELL_TYPE_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;
        String splitCharTemp = null;
        // 首先通过整体切割符合切割
        for (String charSplit : ENTIRETY_SPLIT) {
            addrs = StringUtils.split(addrInfo, charSplit);
            splitCharTemp = 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("小区")) {
                String address = AddrUtil.dealAddress(addrs[0].trim(), addrs[1].trim(), addrs[2].trim(), addrInfo, splitCharTemp, false);
                return new String[]{addrs[0].trim(), addrs[1].trim(), addrs[2].trim(), address};
            }
        } else if (addrs != null && addrs.length == 3) {
            // 防止区域数据出现小区 扰乱数据
            if (!addrs[1].contains("小区")) {
                String address = AddrUtil.dealAddress(addrs[0].trim(), addrs[0].trim(), addrs[1].trim(), addrInfo, splitCharTemp, true);
                return new String[]{addrs[0].trim(), addrs[0].trim(), addrs[1].trim(), address};
            }
        }

        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;
                    }
                    // 只有两级地址
                    String address = AddrUtil.dealAddress(addrs[0].trim(), addrs[0].trim(), addrs[1].trim(), addrInfo, null, true);
                    return new String[]{addrs[0].trim(), addrs[0].trim(), addrs[1].trim(), address};
                } else if ((addrs.length - 1) == 3) {
                    // 防止区域数据出现小区 扰乱数据
                    if (addrs[2].contains("小区")) {
                        continue;
                    }
                    // 三级地址
                    String address = AddrUtil.dealAddress(addrs[0].trim(), addrs[1].trim(), addrs[2].trim(), addrInfo, null, false);
                    return new String[]{addrs[0].trim(), addrs[1].trim(), addrs[2].trim(), address};
                }
            }
        }

        return addrs;
    }

    public static void main(String[] args) throws IOException {
        String[] add = splitAddress("北京 北京市 昌平区 回龙观街道回龙观 冠庭园5号楼2单元502");
        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()); //日期型
                }
                DecimalFormat df = new DecimalFormat("#");
                return df.format(new Double(cell.getNumericCellValue())); //数字
            case Cell.CELL_TYPE_BOOLEAN: //布尔型
                return String.valueOf(cell.getBooleanCellValue());
            case Cell.CELL_TYPE_BLANK: //空白
                return cell.getStringCellValue();
            default: //错误
                return "ERROR";
        }
    }

    /**
     * excel 强制获取text文本
     * @param cell
     * @return
     */
    public static String getTextValue(Cell cell) {
        if (cell == null) return null;
        return cell.getStringCellValue();
    }

}