1、网上大多是通过CellWriteHandler接口实现,CellWriteHandler按单元格读写,这种效率会降低,实现代码可以自行搜索;
2、通过实现RowWriteHandler接口,按行读取,比较当前行与前一行的数据合并单元格,下面贴上代码及效果图(根据两个字段值确定列是否需要合并)
public class PurchaseReportMergeHandler implements RowWriteHandler {
// 需要合并的列索引
private int[] mergeColumnIndex;
// 从那行开始合并
private int mergeRowIndex;
public PurchaseReportMergeHandler() {
}
public PurchaseReportMergeHandler(int mergeRowIndex, int[] mergeColumnIndex) {
this.mergeRowIndex = mergeRowIndex;
this.mergeColumnIndex = mergeColumnIndex;
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
if (isHead) {
return;
}
int curRowIndex = row.getRowNum();
if (curRowIndex > mergeRowIndex) {
for (int columnIndex : mergeColumnIndex) {
mergeWithPrevRow(writeSheetHolder, row, curRowIndex, columnIndex);
}
}
}
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Row row, int curRowIndex, int columnIndex) {
Row preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1);
// 注意这里是自己的合并规则:以项目编号及前向合同编号为合并标识
String currUniqueMerge = row.getCell(0).getStringCellValue() + "_" + row.getCell(3).getStringCellValue();
String preUniqueMerge = preRow.getCell(0).getStringCellValue() + "_" + preRow.getCell(3).getStringCellValue();
boolean dataBool = StringUtils.isNotEmpty(currUniqueMerge) && StringUtils.isNotEmpty(preUniqueMerge) && currUniqueMerge.equals(preUniqueMerge);
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, columnIndex)) {
sheet.removeMergedRegion(i);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
}
// 若上一个单元格未被合并,则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, columnIndex, columnIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
List<PurchaseReportExportResp> result = purchaseReportService.getPurchaseReportList(req);
// sheet中需要合并的列的索引
final int[] mergeColumnIndex = {0, 1, 2, 3,4,5};
EasyExcel.write(response.getOutputStream(),PurchaseReportExportResp.class)
.autoCloseStream(false)
.sheet("采购报表")
.registerWriteHandler(new PurchaseReportMergeHandler(0,mergeColumnIndex))
.doWrite(result);
效果