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

叁歲伎倆 2022-04-03 17:35 99阅读 0赞

/** * 导入商家类目信息工具类 */
    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;
            }
        }
    
    }