导入开放平台类目表的工具类

/**
 * 导入商家类目信息工具类
 */
public class ImportMerchantCategoryUtil {

    private static final Logger logger = LoggerFactory.getLogger(ImportMerchantCategoryUtil.class);

    public static List<MerchantCategory> importFromExcel() {
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(new File("G:\\import.xlsx"));
            int numberOfSheets = workbook.getNumberOfSheets();
            List<MerchantCategory> merchantCategoryList = new ArrayList<>();
            logger.debug("该excel共有{}张sheet", numberOfSheets);
            int count = 0;
            for (int i = 0; i < numberOfSheets; i++) {
                XSSFSheet sheet = workbook.getSheetAt(i);
                int lastRowNum = sheet.getLastRowNum();
                logger.debug("第{}张sheet有{}行数据", i + 1, lastRowNum + 1);
                int numMergedRegions = sheet.getNumMergedRegions();
                for (int j = 0; j < numMergedRegions; j++) {
                    CellRangeAddress mergedRegion = sheet.getMergedRegion(j);
                    //一级类目
                    int startRow = -1, endRow = -1;
                    if (mergedRegion.getFirstColumn() == 0) {
                        startRow = mergedRegion.getFirstRow();
                        endRow = mergedRegion.getLastRow();
                        XSSFRow row = sheet.getRow(mergedRegion.getFirstRow());
                        XSSFCell cell = row.getCell(mergedRegion.getFirstColumn());
                        count++;
                        String id = "1000" + count;
                        String name = cell.getStringCellValue();
//                        logger.debug("类目<{}>行号 {}->{}", name, startRow+1, endRow+1);
                        merchantCategoryList.add(new MerchantCategory(id, 1, name, null, null));
                        //该一级类目对应的开始行结束行，处理二级类目
                        int sCount = 0;
                        for (int k = startRow; k <= endRow; k++) {
                            XSSFRow xssfRow = sheet.getRow(k);
                            XSSFCell xssfCell = xssfRow.getCell(1);
                            XSSFCell nextCell = xssfRow.getCell(2);
                            String sId = null,licenseRequire = null;
                            sCount++;
                            if (nextCell == null || nextCell.getStringCellValue().equals("")) {
                                sId = xssfRow.getCell(4).getStringCellValue();
                                licenseRequire = xssfRow.getCell(3).getStringCellValue();
                            } else {
                                sId = id + new DecimalFormat("00").format(sCount);
                            }
                            //三级类目参数
                            String tId = xssfRow.getCell(4).getStringCellValue();
                            String tName = xssfRow.getCell(2).getStringCellValue();
                            String desc = xssfRow.getCell(3).getStringCellValue();
                            //合并单元格非第一行的情况
                            if (xssfCell == null || xssfCell.getStringCellValue().equals("")) {
                                sCount--;
                                if (!xssfRow.getCell(2).getStringCellValue().equals("")) {
                                    sId = id + new DecimalFormat("00").format(sCount);
                                    merchantCategoryList.add(new MerchantCategory(tId, 3, tName, desc, sId));
                                }
                                continue;
                            }
                            if (xssfCell != null && !nextCell.getStringCellValue().equals("")) {
                                //合并单元格首格
                                merchantCategoryList.add(new MerchantCategory(tId, 3, tName, desc, sId));
                            }
                            String sName = xssfCell.getStringCellValue();
                            merchantCategoryList.add(new MerchantCategory(sId, 2, sName, licenseRequire, id));
                        }
                    }
                }
            }
            return merchantCategoryList;
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            return null;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }

}