解决easyExcel按模板导出xlsx文件打开提示“发现xxx.xlsx中部分内容有问题，是否让我们尽量尝试恢复？”的问题

最近项目一个需求要求将订单按照excel模板导出，其中商品有多行，需要动态插入行并且存在合并单元格的情况，使用easyExcel官网提供的demo的填充和合并单元格：
官网填充demo
官网合并单元格demo

按模板导出主要代码：

public class DataToExcel {public void exportFile() {File filePath = new File("D:\\test\\testMerge.xlsx");OutputStream os= Files.newOutputStream(filePath.toPath());int firstRow = 18;//从第18行开始合并int lastRow = 18;int beginRow = 18;//单元格合并List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();if (CollectionUtil.isNotEmpty(excelVoList)) {if (excelVoList.size() > 1) {for (int i = 0; i < excelVoList.size() - 1; i++) {cellRangeAddressList.add(new CellRangeAddress(firstRow, lastRow, 1, 4));cellRangeAddressList.add(new CellRangeAddress(firstRow, lastRow, 7, 8));firstRow++;lastRow++;}}}FillMergeStrategy fillMergeStrategy = new FillMergeStrategy(cellRangeAddressList, beginRow, excelVoList.size() - 1);//获取excel模板File file = new File("D:\\template\\template01.xlsx");InputStream inputStream = Files.newInputStream(file.toPath());//InputStream inputStream = new URL(filePath).openStream();ExcelWriter excelWriter = EasyExcel.write(os).withTemplate(inputStream).registerWriteHandler(fillMergeStrategy).build();WriteSheet writeSheet = EasyExcel.writerSheet().build();FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();//参数集合，直接写入到Excel数据excelWriter.fill(paramsMap, writeSheet);//列表数据excelWriter.fill(excelVoList, fillConfig, writeSheet);excelWriter.finish();}}

合并单元格的策略为：

public class PiFillMergeStrategy implements RowWriteHandler {//合并坐标集合private List<CellRangeAddress> cellRangeAddress;//从哪行开始private int beginRow;//合并行数private int mergeRows;public PiFillMergeStrategy(List<CellRangeAddress> cellRangeAddress, int beginRow, int mergeRows) {this.cellRangeAddress = cellRangeAddress;this.beginRow = beginRow;this.mergeRows = mergeRows;}@Overridepublic void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {if (CollectionUtil.isNotEmpty(cellRangeAddress)) {if (row.getRowNum() >= beginRow && row.getRowNum() <= beginRow + mergeRows) {for (CellRangeAddress item : cellRangeAddress) {writeSheetHolder.getSheet().addMergedRegionUnsafe(item);}}}}}

当有多行商品导出的excel文件打开时会提示：

点击“是”是可以打开的，但用户体验很不好，认为导出文件有问题！
调试了下easyExcel代码，发现合并单元格的方法主要有两个：

/*** 添加单元格的合并区域（因此这些单元格形成一个）* 参数：region – (rowfrom/colfrom-rowto/colto) 合并* 返回：该地区的指数 */int addMergedRegion(CellRangeAddress region);/** * 添加单元格的合并区域（因此这些单元格形成一个）。跳过验证。可以创建重叠的合并区域或创建与多单元格 * 数组公式与此公式相交的合并区域，这可能会导致工作簿损坏。要在调用 addMergedRegionUnsafe 后检 * 查合并区域重叠数组公式或其他合并区域，请调用validateMergedRegions() ，它在 O(n^2) 时间内 * 运行。 * 参数：region ——合并 * 返回：该地区的指数 * 抛出：IllegalArgumentException – 如果区域包含的单元格少于 2 个 */int addMergedRegionUnsafe(CellRangeAddress region);

可以看出使用addMergedRegionUnsafe方法合并单元格可能会导致工作簿损坏，而使用addMergedRegion会进行单元格是否重复合并的校验：

private int addMergedRegion(CellRangeAddress region, boolean validate) {if (region.getNumberOfCells() < 2) {throw new IllegalArgumentException("Merged region " + region.formatAsString() + " must contain 2 or more cells");}region.validate(SpreadsheetVersion.EXCEL2007);if (validate) {// throw IllegalStateException if the argument CellRangeAddress intersects with// a multi-cell array formula defined in this sheetvalidateArrayFormulas(region);// Throw IllegalStateException if the argument CellRangeAddress intersects with// a merged region already in this sheetvalidateMergedRegions(region);}CTMergeCells ctMergeCells = worksheet.isSetMergeCells() " />.getMergeCells() : worksheet.addNewMergeCells();CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();ctMergeCell.setRef(region.formatAsString());final int numMergeRegions=ctMergeCells.sizeOfMergeCellArray();// also adjust the number of merged regions overallctMergeCells.setCount(numMergeRegions);return numMergeRegions-1;}

校验合并单元格的方法validateMergedRegions(region)，如果候选区域不与此工作表中的现有合并区域相交就会报错：

private void validateMergedRegions(CellRangeAddress candidateRegion) {for (final CellRangeAddress existingRegion : getMergedRegions()) {if (existingRegion.intersects(candidateRegion)) {throw new IllegalStateException("Cannot add merged region " + candidateRegion.formatAsString() +" to sheet because it overlaps with an existing merged region (" + existingRegion.formatAsString() + ").");}}}

可以看出addMergedRegionUnsafe会跳过单元格合并的校验，但会导致文件被损坏，所以导出的文件打开后会提示文件有问题，如果使用addMergedRegion方法，easyExcel在列表动态添加行excelWriter.fill(excelVoList, fillConfig, writeSheet);时就会直接报上述错误，导致程序中断。

我采用的方法是用easyExcel不使用合并策略导出xlsx文件到临时文件中，然后使用poi的XSSFWorkbook读取该临时文件，然后用这个新的临时文件进行单元格合并，这样单元格检查时就不会报错了，顺利导出，打开后也不会有错误提示！

public class DataToExcel {public void exportFile() {File filePath = new File("D:\\test\\testMerge.xlsx");OutputStream os= Files.newOutputStream(filePath.toPath());int firstRow = 18;//从第18行开始合并int lastRow = 18;int beginRow = 18;//单元格合并List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();if (CollectionUtil.isNotEmpty(excelVoList)) {if (excelVoList.size() > 1) {for (int i = 0; i < excelVoList.size() - 1; i++) {cellRangeAddressList.add(new CellRangeAddress(firstRow, lastRow, 1, 4));cellRangeAddressList.add(new CellRangeAddress(firstRow, lastRow, 7, 8));firstRow++;lastRow++;}}}//将easyExcel生成的文件保存在临时文件中待poi进一步做合并单元格File tmpFile = new File("D:\\tmp\\tmpFile.xlsx");OutputStream tmpOutputStream = Files.newOutputStream(tmpFile.toPath());//获取excel模板File file = new File("D:\\template\\template01.xlsx");InputStream inputStream = Files.newInputStream(file.toPath());//将easyExcel生成的文件保存在临时文件中待poi进一步做合并单元格//File tmpFile = new File("/tmp/" + "tmp_file.xlsx");//OutputStream tmpOutputStream = Files.newOutputStream(tmpFile.toPath());//获取excel模板//InputStream inputStream = new URL(filePath).openStream();ExcelWriter excelWriter = EasyExcel.write(tmpOutputStream).withTemplate(inputStream)//.registerWriteHandler(fillMergeStrategy)//不采用合并策略.build();WriteSheet writeSheet = EasyExcel.writerSheet().build();FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();//参数集合，直接写入到Excel数据excelWriter.fill(paramsMap, writeSheet);//列表数据excelWriter.fill(excelVoList, fillConfig, writeSheet);excelWriter.finish();//使用poi合并单元格，使用registerWriteHandler合并单元格会与fill方法中创建单元格后校验合并单元格冲突而引发报错InputStream in = Files.newInputStream(tmpFile.toPath());XSSFWorkbook workbook = new XSSFWorkbook(in);XSSFSheet sheet = workbook.getSheetAt(0);if (CollectionUtils.isNotEmpty(cellRangeAddressList)) {for (CellRangeAddress cellAddresses : cellRangeAddressList) {//合并单元格sheet.addMergedRegion(cellAddresses);//设置单元格样式，解决合并单元格后边框缺失问题setRegionStyle(sheet, cellAddresses, setDefaultStyle(workbook));}}workbook.write(os);os.flush();os.close();}//使用poi设置合并单元格后的样式public void setRegionStyle(XSSFSheet sheet, CellRangeAddress region, XSSFCellStyle xssfCellStyle) {for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {XSSFRow row = sheet.getRow(i);if (null == row) row = sheet.createRow(i);for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {XSSFCell cell = row.getCell(j);if (null == cell) cell = row.createCell(j);cell.setCellStyle(xssfCellStyle);}}}public XSSFCellStyle setDefaultStyle(XSSFWorkbook workbook) {XSSFCellStyle cellStyle = workbook.createCellStyle();// 边框cellStyle.setBorderBottom(BorderStyle.THIN);cellStyle.setBorderLeft(BorderStyle.THIN);cellStyle.setBorderRight(BorderStyle.THIN);cellStyle.setBorderTop(BorderStyle.THIN);// 居中cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 字体XSSFFont font = workbook.createFont();font.setFontName("Calibri");font.setFontHeightInPoints((short) 10);cellStyle.setFont(font);return cellStyle;}}

如果有更好的解决方式，欢迎再评论区留言哦！

参考