需求概述测试代码测试代码运行结果
需求概述项目中需要导出的Excel如下所示：
目前的列表是查询出所有行的数据，前端再去按主键进行合并的。所以，导出时只要Excel下一行的主键相同，就合并上一行框红的列。
目前网上找到的EasyExcel自定义合并单元格都是ExcelFillCellMergeStrategy，这个工具类只要下一行的cell和上一行的cell内容相同就会合并，不符合目前的需求。本例也是在此基础上进行逻辑修改。 参考链接。
测试代码引入相关依赖：
com.alibaba easyexcel 2.2.6 org.projectlombok lombok 1.18.20 provided
ExcelModel类：
@Getter
@Setter
@ContentRowHeight(15) //内容行高
@HeadRowHeight(20)//表头行高
public class ExcelModel {
public static final String RESEXCELNAME = "document.xlsx";
public static final String TEMPLATEEXCELNAME = "文章管理";
public static final String SUFFIX = ".xlsx";
@ColumnWidth(10)//单元格长度
@ExcelProperty(value = "序号", index = 0)
private String order;
@ColumnWidth(20)//单元格长度
@ExcelProperty(value = "文章标题", index = 1)
private String title;
@ColumnWidth(15)//单元格长度
@ExcelProperty(value = "单位", index = 2)
private String company;
@ColumnWidth(15)//单元格长度
@ExcelProperty(value = "编号", index = 3)
private String documentCode;
@ColumnWidth(12)//单元格长度
@ExcelProperty(value = "发文日期", index = 4)
private String publishDate;
@ColumnWidth(25)//单元格长度
@ExcelProperty(value = "意见", index = 5)
private String idea;
}
ExcelFillCellMergeStrategy类(核心)：
public class ExcelFillCellMergeStrategy implements CellWriteHandler {
// 需要从第几行开始合并，0表示第1行
private int mergeRowIndex = 1;
// 合并的哪些列，比如为4时，当前行id和上一行id相同则合并前五列
private int mergeColumnRegion = 4;
public ExcelFillCellMergeStrategy() {
}
public ExcelFillCellMergeStrategy(int mergeRowIndex, int mergeColumnRegion) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnRegion = mergeColumnRegion;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 隐藏id列
writeSheetHolder.getSheet().setColumnHidden(0, true);
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
//当前行
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColumnRegion; i++) {
if (curColIndex <= mergeColumnRegion) {
mergeWithPreviousRow(writeSheetHolder, cell, curRowIndex, curColIndex);
break;
}
}
}
}
private void mergeWithPreviousRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
// 当前行的第一个Cell
Cell curFirstCell = cell.getSheet().getRow(curRowIndex).getCell(0);
Object curFirstData = curFirstCell.getCellTypeEnum() == CellType.STRING ? curFirstCell.getStringCellValue() : curFirstCell.getNumericCellValue();
// 上一行的第一个Cell
Cell preFirstCell = cell.getSheet().getRow(curRowIndex - 1).getCell(0);
Object preFirstData = preFirstCell.getCellTypeEnum() == CellType.STRING ? preFirstCell.getStringCellValue() : preFirstCell.getNumericCellValue();
// 当前行的id和上一行的id相同则合并前面（mergeColumnRegion+1）列
if (curFirstData.equals(preFirstData)) {
Sheet sheet = writeSheetHolder.getSheet();
List 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);
}
}
}
}
ExportTest测试类：
public class ExportTest {
public static void main(String[] args) throws IOException {
List datas = getData();
String filename="E:\Downloads\测试导出合并单元格Excel.xlsx";
File file1 = new File(filename);
if(!file1.exists()){
file1.createNewFile();
}
//打印单个sheel页
EasyExcel.write(filename, ExcelModel.class )
.autoCloseStream(Boolean.TRUE)
.registerWriteHandler(new ExcelFillCellMergeStrategy())
.sheet("测试导出合并单元格Excel").doWrite(datas);
}
private static List getData() {
List list = new ArrayList<>();
ExcelModel model1 = new ExcelModel();
model1.setOrder("1");
model1.setTitle("标题111");
model1.setCompany("单位111");
model1.setdocumentCode("编号111");
model1.setIdea("意见111");
model1.setPublishDate("2022-01-21");
ExcelModel model2 = new ExcelModel();
model2.setOrder("1");
model2.setTitle("标题111");
model2.setCompany("单位222");
model2.setdocumentCode("编号222");
model2.setIdea("意见111");
model2.setPublishDate("2022-01-21");
ExcelModel model3 = new ExcelModel();
model3.setOrder("1");
model3.setTitle("标题333");
model3.setCompany("单位222");
model3.setdocumentCode("编号222");
model3.setIdea("意见333");
model3.setPublishDate("2022-01-21");
ExcelModel model4 = new ExcelModel();
model4.setOrder("4");
model4.setTitle("标题444");
model4.setCompany("单位444");
model4.setdocumentCode("编号444");
model4.setIdea("意见444");
model4.setPublishDate("2022-01-21");
ExcelModel model5 = new ExcelModel();
model5.setOrder("5");
model5.setTitle("标题555");
model5.setCompany("单位555");
model5.setdocumentCode("编号555");
model5.setIdea("意见555");
model5.setPublishDate("2022-01-21");
list.add(model1);
list.add(model2);
list.add(model3);
list.add(model4);
list.add(model5);
return list;
}
}
测试代码运行结果
其中序号列是隐藏的。另外，CellWriteHandler接口方法的执行顺序为beforeCellCreate()->afterCellCreate()->afterCellDataConverted()->afterCellDispose()。
