解决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;    }    @Override    public 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代码，发现合并单元格的方法主要有两个：

        int addMergedRegion(CellRangeAddress region);        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 sheet            validateArrayFormulas(region);            // Throw IllegalStateException if the argument CellRangeAddress intersects with            // a merged region already in this sheet            validateMergedRegions(region);        }        CTMergeCells ctMergeCells = worksheet.isSetMergeCells() ? worksheet.getMergeCells() : worksheet.addNewMergeCells();        CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell();        ctMergeCell.setRef(region.formatAsString());        final int numMergeRegions=ctMergeCells.sizeOfMergeCellArray();        // also adjust the number of merged regions overall        ctMergeCells.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;}}

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

参考

来源地址：https://blog.csdn.net/Matthew_99/article/details/130030262