EasyExcel按模板导出（动态合并单元格问题处理）

EasyExcel按模板导出（动态合并单元格问题处理）参考⽹上的链接：
EasyExcel按模板导出与下载（⾃定义合并单元格）
https:///weixin_44511845/article/details/120290264
EasyExcel（根据条件动态合并单元格的重复数据））
https:///Violet_201903027/article/details/105724907
编写模板导出时，某⼀列单元格合并功能
上⾯的资料使⽤的EasyExcel版本是：2.1.7
我使⽤的版本是：2.2.0-beta2
使⽤资料代码进⾏模板导出同时，动态单元格合并
github官⽅提供了 LoopMergeStrategy 合并策略在模板导出不⽣效。

因为LoopMergeStrategy extends AbstractRowWriteHandler ，使⽤ afterRowDispose（）⾏操作完成后执⾏合并操作。

但是在模板导出过程中afterRowDispose（）⽅法没有被触发。

合并策略类：ExcelFillCellMergeStrategy
1public class ExcelFillCellMergeStrategy implements CellWriteHandler {
2
3/** 需要进⾏单元格合并的列数组 **/
4private int[] mergeColumnIndex;
5/** 单元格合并从第⼏⾏开始 **/
6private int mergeRowIndex;
7
8public ExcelFillCellMergeStrategy() {}
9
10public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
11this.mergeRowIndex = mergeRowIndex;
12this.mergeColumnIndex = mergeColumnIndex;
13 }
14。

15。

16
17 @Override
18public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
19 List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
20int curRowIndex = cell.getRowIndex();
21int curColIndex = cell.getColumnIndex();
22if (curRowIndex > mergeRowIndex) {
23for (int i = 0; i < mergeColumnIndex.length; i++) {
24if (curColIndex == mergeColumnIndex[i]) {
25 mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
26break;
27 }
28 }
29 }
30 }
31
32/**
33 * 当前单元格向上合并
34 *
35 * @param writeSheetHolder
36 * @param cell
37 * 当前单元格
38 * @param curRowIndex
39 * 当前⾏
40 * @param curColIndex
41 * 当前列
42*/
43private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
44 Object curData =
45 cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
46 Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
47 Object preData =
48 preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
49// 将当前单元格数据与上⼀个单元格数据⽐较
50 Boolean dataBool = preData.equals(curData);
51if (dataBool) {
52 Sheet sheet = writeSheetHolder.getSheet();
53 List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
54boolean isMerged = false;
55for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
56 CellRangeAddress cellRangeAddr = mergeRegions.get(i);
57// 若上⼀个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
58if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
59 sheet.removeMergedRegion(i);
60 cellRangeAddr.setLastRow(curRowIndex);
61 sheet.addMergedRegion(cellRangeAddr);
62 isMerged = true;
63 }
64 }
65// 若上⼀个单元格未被合并，则新增合并单元
66if (!isMerged) {
67 CellRangeAddress cellRangeAddress =
68new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
69 sheet.addMergedRegion(cellRangeAddress);
70 }
71 }
72 }
73 }
测试代码
@Test
public void fillTest1() throws IOException {
// 模板注意⽤{} 来表⽰你要⽤的变量如果本来就有"{","}" 特殊字符⽤"\{","\}"代替
// 填充list 的时候还要注意模板中{.} 多了个点表⽰list
String templatePath = EasyexcelAnnotationFillTest.class.getResource("/").getPath();
templatePath = templatePath + "fillTemplate.xlsx";
OutputStream outputStream = new FileOutputStream("E:/测试填充数据.xlsx");
// 填充列表数据
List<FillWithAnnotationData> listData = getFillListData();
// 第⼀列进⾏单元格合并
int[] mergeColumeIndex = {0};
// 从第4⾏开始合并
int mergeRowIndex = 3;
ExcelWriter excelWriter = EasyExcelFactory.write(outputStream)
.withTemplate(templatePath)
//设置合并单元格策略
.registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex))
.build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
excelWriter.fill(listData, writeSheet);
excelWriter.finish();
outputStream.close();
}
public List<FillWithAnnotationData> getFillListData() {
List<FillWithAnnotationData> listData = new ArrayList<>();
for (int i=0; i < 10; i++) {
FillWithAnnotationData data = new FillWithAnnotationData();
data.setName("名称");
data.setMoney(new BigDecimal(1002.35+i).toPlainString());
data.setNumber(1002.35+i);
listData.add(data);
}
return listData;
}
public class FillWithAnnotationData {
private String name;
@NumberFormat(",##0.00")
@ExcelFillProperty(converter = StringNumberConverter.class)
private String money;
@NumberFormat(",##0.00")
@ExcelFillProperty(converter = DoubleStringConverter.class)
private Double number;
}
测试模板如下图
报错位置：ExcelFillCellMergeStrategy合并策略类的 mergeWithPrevRow()⽅法中
第46⾏位置
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
这⼀⾏代码会报空指针异常 ng.NullPointerException
原因:
debug发现，cell.getSheet() ⾏的下标第0到3的数据⾏，获取的是同⼀个 sheet 实例
当下标为4时，执⾏cell.getSheet()获取到的 sheet 实例不⼀样，⽽且⾥⾯的sheet存在的row数据，只有下标为4以后的
⽽下标0到3的⾏数据被存储到存储sheet中。

writeSheetHolder.getCachedSheet()
所以改进合并策略类的合并⽅法：
当获取不到前⼀⾏数据时，查找缓存sheet中的⾏数据
/**
* 当前单元格向上合并
*
* @param writeSheetHolder
* @param cell
* 当前单元格
* @param curRowIndex
* 当前⾏
* @param curColIndex
* 当前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) { Object curData =
cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
Row preRow = cell.getSheet().getRow(curRowIndex - 1);
if (preRow == null) {
// 当获取不到上⼀⾏数据时，使⽤缓存sheet中数据
preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
}
Cell preCell = preRow.getCell(curColIndex);
Object preData =
preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 将当前单元格数据与上⼀个单元格数据⽐较
Boolean dataBool = preData.equals(curData);
if (dataBool) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上⼀个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上⼀个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress =
new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}。