解决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代码，发现合并单元格的方法主要有两个：
/**
* 添加单元格的合并区域（因此这些单元格形成一个）
* 参数：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 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;
}
}
如果有更好的解决方式，欢迎再评论区留言哦！
旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。
更多推荐
1
0- 0
已为社区贡献3条内容
所有评论(0)