解决easyExcel按模板导出xlsx文件打开提示“发现xxx.xlsx中部分内容有问题，是否让我们尽量尝试恢复？”的问题
短信预约 -IT技能 免费直播动态提醒
最近项目一个需求要求将订单按照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
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
