java实现excel的多sheet页合并成一个sheet，完美保证格式。可以实现合并时去掉sheet页的表头，同时设置各个sheet之间的间隔，以及递归合并
1. 需求：将一个源excel的多个sheet页，合并到目标excel的单个sheet页中， 源excel可能需要内部自身的子sheet页合并，合并的时候做到将源excel中的单元格是公式的值赋值到目标单元格中，同时完整的合并源excel中出现的合并单元格。
2. 代码实现
2.1 业务table设计
主表：源excel中需要合并到目标excel的sheet信息
CREATE TABLE `sheet_message` ( `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '主键', `model_name` varchar(255) NOT NULL COMMENT 'excel名称', `sheet_name` varchar(255) NOT NULL COMMENT 'sheet页名称', `need_merge` varchar(255) NOT NULL COMMENT '是否需要合并，需要合并为yes', `order_no` int(11) DEFAULT NULL COMMENT '合并的时候排序，决定哪个sheet页需要排在最前面', `last_sheet_interval` int(8) DEFAULT NULL COMMENT '当前合并的sheet，与上一个sheet页之间的间隔', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
子表：源excel中sheet需要内部合并的sheet信息
CREATE TABLE `child_sheet_message` ( `id` bigint(8) NOT NULL AUTO_INCREMENT COMMENT '主键', `parent_sheet_name` varchar(255) NOT NULL COMMENT '父sheet页名称，即需要将子sheet页合并到的位置', `sheet_name` varchar(255) NOT NULL COMMENT '子sheet页名称', `order_no` int(8) NOT NULL COMMENT '子sheet页合并到父sheet页的顺序', `title_length` int(8) DEFAULT NULL COMMENT '父子合并的表头处理，如果合并不需要去掉表头，填0，需要去掉填写sheet表头长度', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
2.2 代码service层
2.2.1 主方法mergeExcelBySheetMessage， 中间用到的mapper层省略， 用到的sql如下
2.2.1.1 取出所有需要合并sheet页 select * from sheet_message s where s.model_name = #{fileName} and s.need_merge = 'yes' order by s.order_no
/** * 传递 源workbook， 和 源文件名fileName * * @param sourceWorkBook 源workbook 用来获取excel数据 * @param fileName 源文件名称，用来获取数据库中的sheet页信息 * @return * @throws IllegalAccessException * @throws InstantiationException */ @Override public Workbook mergeExcelBySheetMessage(Workbook sourceWorkBook, String fileName) throws IllegalAccessException, InstantiationException { // 创建和目标workbook相同类型的workbook Workbook targetWorkBook = sourceWorkBook.getClass().newInstance(); // 创建目标sheet Sheet targetSheet = targetWorkBook.createSheet(); // 取出所有的需要合并的sheet页合集 List<SheetMessage> needMergeSheetList = sheetMessageMapper.selectByFileName(fileName); // 当不需要合并的时候，直接返回源workbook if (needMergeSheetList == null || needMergeSheetList.isEmpty()) { return sourceWorkBook; } // 记录已经合并过的sheet页，防止重复合并 List<String> sheetNameList = new ArrayList<>(); // 将合并完之后的sourceWorkbook合并到targetWorkbook中 for (SheetMessage sheetMessage : needMergeSheetList) { String sheetName = sheetMessage.getSheetName(); // 与上一个合并的sheet之间的间隔 Integer lastSheetInterval = sheetMessage.getLastSheetInterval(); if (lastSheetInterval > 0) { targetSheet.createRow(targetSheet.getLastRowNum() + lastSheetInterval); } // 源sheet合并需要合并的子项 mergeChildSheetMessage(sheetName, sourceWorkBook, sheetNameList); Sheet sourceSheet = sourceWorkBook.getSheet(sheetName); moveSourceSheetIntoTargetSheet(targetWorkBook, sourceSheet, targetSheet, 0); } return targetWorkBook; }
2.2.2 源sheet需要自身合并的子项（源excel中有的sheet页是需要同过自身的其他sheet页合并而来）
中间用到的mapper层，省略，用到的sql语句如下
2.2.2.1 select * from child_sheet_message c where c.parent_sheet_name = #{parentSheetName} order by c.order_no
/** * 源excel中sheet页合并需要合并的子sheet * * @param parentSheetName 需要合并的sheet页名称 * @param sourceWorkBook 源excel * @param sheetNameList 已经合并过的sheet名称的list集合，用来防止重复合并 */ private void mergeChildSheetMessage(String parentSheetName, Workbook sourceWorkBook, List<String> sheetNameList) { if (sheetNameList.contains(parentSheetName)) { return; } List<ChildSheetMessage> childSheetList = childSheetMessageMapper.selectByParentSheetMessage(parentSheetName); if (childSheetList == null || childSheetList.isEmpty()) { sheetNameList.add(parentSheetName); return; } // 合并 Sheet targetSheet = sourceWorkBook.getSheet(parentSheetName); for (ChildSheetMessage childSheetMessage : childSheetList) { String sheetName = childSheetMessage.getSheetName(); Integer titleLength = childSheetMessage.getTitleLength(); // 递归合并子项的子项sheet mergeChildSheetMessage(sheetName, sourceWorkBook, sheetNameList); Sheet sourceSheet = sourceWorkBook.getSheet(sheetName); // 合并sheet页 moveSourceSheetIntoTargetSheet(sourceWorkBook, sourceSheet, targetSheet, titleLength); } // 记录已经合并了的sheet页名称 sheetNameList.add(parentSheetName); }
2.2.3 moveSourceSheetIntoTargetSheet 合并源sheet到目标sheet中
/** * sheet页合并 * * @param targetWorkBook 目标workbook，该对象主要用来创建单元格格式 * @param sourceSheet 源sheet * @param targetSheet 目标sheet * @param removeTitleLength 源sheet页合并到目标sheet中需要去掉的表头长度，如果不去掉传递0 */ private static void moveSourceSheetIntoTargetSheet(Workbook targetWorkBook, Sheet sourceSheet, Sheet targetSheet, int removeTitleLength) { if (sourceSheet == null) { return; } // 获取目标sheet最后一行的下一行 int targetRowNums = targetSheet.getLastRowNum(); int physicalNumberOfRows = targetSheet.getPhysicalNumberOfRows(); targetRowNums = physicalNumberOfRows == 0 ? 0 : targetRowNums + 1; // 移动 源sheet页中的 合并单元格区域 到目标sheet页中 moveSourceSheetAllMergedRegionToTargetSheet(sourceSheet, targetSheet, targetRowNums, removeTitleLength); int sourceRowNums = sourceSheet.getLastRowNum(); for (int i = removeTitleLength; i <= sourceRowNums; i++) { Row targetRow = targetSheet.createRow(targetRowNums++); Row sourceRow = sourceSheet.getRow(i); // 复制行 copySourceRowToTargetRow(targetWorkBook, sourceRow, targetRow); } }
2.2.4 合并sheet的时候，先处理sheet页中的合并单元格区域问题
/** * 合并sheet页中，处理源sheet页中可能存在的 合并单元格部分； * 当源sheet页在合并单元格的时候可能去掉表头，所以也需去掉表头的合并单元格部分 * * @param sourceSheet 源sheet * @param targetSheet 目标sheet * @param targetRowNums 目标sheet的最后一行（源合并单元格的位置，需要变化到目标单元格区域，需要提供一个位置角标） * @param removeTitleLength 需要移除的表头长度 */ private static void moveSourceSheetAllMergedRegionToTargetSheet(Sheet sourceSheet, Sheet targetSheet, int targetRowNums, int removeTitleLength) { int numMergedRegions = sourceSheet.getNumMergedRegions(); for (int i = 0; i < numMergedRegions; i++) { CellRangeAddress mergedRegion = sourceSheet.getMergedRegion(i); int firstRow = mergedRegion.getFirstRow(); // 去掉表头的 单元格合并 if (firstRow < removeTitleLength) { continue; } int lastRow = mergedRegion.getLastRow(); int firstColumn = mergedRegion.getFirstColumn(); int lastColumn = mergedRegion.getLastColumn(); // 合并单元格的行需要跟随当前单元格的行数下移 firstRow = firstRow + targetRowNums; lastRow = lastRow + targetRowNums; CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn); targetSheet.addMergedRegion(cellRangeAddress); } }
2.2.5 移动行copySourceRowToTargetRow
/** * 将源行复制到目标行 * @param targetWorkBook 目标workbook，主要用来创建单元格样式 * @param sourceRow 源行 * @param targetRow 目标行 */ private static void copySourceRowToTargetRow(Workbook targetWorkBook, Row sourceRow, Row targetRow) { if (sourceRow == null) { return; } // 行高 targetRow.setHeight(sourceRow.getHeight()); int sourceCellNums = sourceRow.getLastCellNum(); for (int i = 0; i < sourceCellNums; i++) { Cell targetCell = targetRow.createCell(i); Cell sourceCell = sourceRow.getCell(i); // 复制单元格 copySourceCellToTargetCell(targetWorkBook, targetCell, sourceCell); } }
2.2.5 移动单元格
/** * 移动单元格 * * @param targetWorkBook 目标workbook，用来在本方法中创建单元格样式 * @param targetCell 目标单元格 * @param sourceCell 源单元格 */ private static void copySourceCellToTargetCell(Workbook targetWorkBook, Cell targetCell, Cell sourceCell) { if (sourceCell == null) { return; } // 将源单元格的格式 赋值到 目标单元格中 CellStyle sourceCellStyle = sourceCell.getCellStyle(); /* 此处由于是新建了workbook对象，只能新建 CellStyle对象，然后clone，再赋值； 直接赋值 源CellStyle对象 会报不是同源异常 */ CellStyle targetCellStyle = targetWorkBook.createCellStyle(); targetCellStyle.cloneStyleFrom(sourceCellStyle); targetCell.setCellStyle(targetCellStyle); CellType cellTypeEnum = sourceCell.getCellTypeEnum(); switch (cellTypeEnum) { case STRING: targetCell.setCellValue(sourceCell.getStringCellValue()); break; case NUMERIC: if (DateUtil.isCellDateFormatted(sourceCell)) { // 日期格式的值 targetCell.setCellValue(sourceCell.getDateCellValue()); } else { targetCell.setCellValue(sourceCell.getNumericCellValue()); } break; case BOOLEAN: targetCell.setCellValue(sourceCell.getBooleanCellValue()); break; case FORMULA: // ***为公式的情况下获取的是单元格的数值 targetCell.setCellValue(sourceCell.getNumericCellValue()); break; case BLANK: break; case ERROR: targetCell.setCellValue(sourceCell.getErrorCellValue()); break; case _NONE: break; default: } }
如果有意见可以留言，谢谢~
