easyexcel实现相同内容的上下行合并

需求：同一列上下行内容相同的情况下进行合并，有一个前提要求是某一列的值相同情况下，再去判断要合并的列是否值相同。

比如下面的核心代码中前提是第19列的值相同的前提下，再去判断要合并的上下行是否相同，如果相同就合并，不相同就不合并。这里有个坑，可以继续往下看。

实现：

import com.alibaba.excel.metadata.Head;import com.alibaba.excel.metadata.data.WriteCellData;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import lombok.Data;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;import java.util.Objects;@Datapublic class ExcelFillCellMergeStrategy implements CellWriteHandler {//需要合并的列private int[] mergeColumnIndex;//需要从第二行开始，列头第二行private int mergeRowIndex;public ExcelFillCellMergeStrategy() {}public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {this.mergeRowIndex = mergeRowIndex;this.mergeColumnIndex = mergeColumnIndex;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {//当前行int curRowIndex = cell.getRowIndex();//当前列int curColIndex = cell.getColumnIndex();if (curRowIndex > mergeRowIndex) {for (int i = 0; i < mergeColumnIndex.length; i++) {if (curColIndex == mergeColumnIndex[i] && curColIndex == 19) {mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);break;} else if (curColIndex == mergeColumnIndex[i] && curColIndex != 19) {mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);break;}}}}/** * 当前单元格向上合并 * * @param writeSheetHolder * @param cell 当前单元格 * @param curRowIndex当前行 * @param curColIndex当前列 */private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell,int curRowIndex, int curColIndex) {//获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();//当前行第19列的值Cell businessCodeCell = cell.getSheet().getRow(curRowIndex).getCell(19);//上一行第19列的值Cell prebusinessCodeCell = cell.getSheet().getRow(curRowIndex - 1).getCell(19);String businessCodeData = businessCodeCell.getStringCellValue();String preBusinessCodeData = prebusinessCodeCell.getStringCellValue();// 我是因为有一些数据是空串，不进行合并，所以做了特殊处理的。可以根据自己的需求进行修改// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行if (Objects.equals(cell.getCellType(), CellType.STRING) && Objects.equals(preCell.getCellType(), CellType.STRING)&& curData.equals(preData)) {if (curColIndex != 19 && Objects.equals(businessCodeData, preBusinessCodeData)) {addmergeRegion(writeSheetHolder, curRowIndex, curColIndex);} else if (curColIndex == 19) {addmergeRegion(writeSheetHolder, curRowIndex, curColIndex);}} else if (Objects.equals(cell.getCellType(), CellType.NUMERIC) && Objects.equals(preCell.getCellType(), CellType.NUMERIC)&& curData.equals(preData)&& Objects.nonNull(businessCodeData) && Objects.nonNull(preBusinessCodeData)&& Objects.equals(businessCodeData, preBusinessCodeData)) {addmergeRegion(writeSheetHolder, curRowIndex, curColIndex);}}private void addmergeRegion(WriteSheetHolder writeSheetHolder, int curRowIndex, int curColIndex) {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);}}}

public void export(HttpServletResponse response, InvestmentAndIncomeInfoDTO investmentAndIncomeInfoDTO) throws Exception {//需要合并的列int[] mergeColumeIndex = {19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40};//需要从第二行开始，列头第二行int mergeRowIndex = 2;String date = DateTimeUtil.date();List exportData = getExportData();String fileName = URLEncoder.encode("测试合并" + date + ".xlsx", "UTF-8");ExcelDataUtil.setResponseHeader(response, fileName);EasyExcel.write(response.getOutputStream(), InvestmentAndIncomeInfoDTO.class).excelType(ExcelTypeEnum.XLSX)//内容居中.registerWriteHandler(horizontalCellStyleStrategyBuilder()).registerWriteHandler(new ExcelFillCellMergeStrategy(mergeRowIndex, mergeColumeIndex)).sheet("测试合并").doWrite((exportData));}

让合并的内容居中并且加上框线显示的代码。

 public HorizontalCellStyleStrategy horizontalCellStyleStrategyBuilder() {WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 单元格边框类型headWriteCellStyle.setBorderBottom(BorderStyle.THIN);headWriteCellStyle.setBorderLeft(BorderStyle.THIN);headWriteCellStyle.setBorderRight(BorderStyle.THIN);headWriteCellStyle.setBorderTop(BorderStyle.THIN);// 单元格边框颜色headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.index);headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.index);headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.index);headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.index);//内容策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();//设置 水平居中contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//垂直居中contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 单元格边框类型contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);contentWriteCellStyle.setBorderRight(BorderStyle.THIN);contentWriteCellStyle.setBorderTop(BorderStyle.THIN);// 单元格边框颜色contentWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.index);contentWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.index);contentWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.index);contentWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.index);return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);}

上述代码自己可以添加一些样例数据。进行测试。很完美的实现了。如果数据量多的情况下，执行效率是不高的。有什么好的办法欢迎评论留言，一起学习。

说一下这次的坑，此次的需求是在第19列（第19列的值不会为空）值相同的情况下再进行合并，这个时候我又想合并前18列，于是我修改了要合并的列集合。

int[] mergeColumeIndex = {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40};

运行，报错，空指针！！！非常好，有问题才有进步嘛。

跟代码看看，发现在获取某行第19列值的情况下获取不到。

一路看下来，发现只有在进行合并前18列的时候才会发生这个问题，有点眉目了哈，在合并前18列时，是获取不到当前合并行合并列后的值的！！！

这应该是easyexcel 的特殊机制吧。原生的poi 我没试。有小伙伴试的话欢迎评论留言。

好了，前面的不合并了。就这样吧！！！