EasyExcel导出合并单元格并合计

写这篇文章只是为了自己以后参考,

进入正文：

poi版本：4.1.2

easyExcel版本：2.2.3

1.导出的实体类，也就是表头

@Data@NoArgsConstructor@JsonInclude@ApiModel(value = "省级行业党委信息季度报送-导出")public class CasPartyReportInfoExportVO implements Serializable {private static final long serialVersionUID = 1L;@ExcelProperty(value = { "数据1" }, index = 0)@ApiModelProperty("所在区域")private String data1;@ExcelProperty(value = { "基本情况", "数据2" }, index = 1)@ApiModelProperty("数据2")private Integer data2;@ExcelProperty(value = { "基本情况", "数据3" }, index = 2)@ApiModelProperty("数据3")private Integer data3;@ExcelProperty(value = { "基本情况", "数据4" }, index = 3)@ApiModelProperty("数据4")private Integer data4;@ExcelProperty(value = { "数据5" }, index = 3)@ApiModelProperty("数据5")private Integer data5;}

2、行合并工具类

import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;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;public class ExcelFillCellMergeStrategyUtils implements CellWriteHandler {/*** 需要合并列的下标，从0开始*/private int[] mergeColumnIndex;/*** 从第几行开始合并，表头下标为0*/private int mergeRowIndex;public ExcelFillCellMergeStrategyUtils(int mergeRowIndex, int[] mergeColumnIndex) {this.mergeRowIndex = mergeRowIndex;this.mergeColumnIndex = mergeColumnIndex;}@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> 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 < mergeColumnIndex.length; i++) {if (curColIndex == mergeColumnIndex[i]) {mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);break;}}}}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();// 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行if (curData.equals(preData)) {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);}}}}

3、列合并的工具类

@Data@AllArgsConstructorpublic class CellLineRange {/*** 起始列*/private int firstCol;/*** 结束列*/private int lastCol;}

4、调用工具类，开始合并：

List<CasPartyReportInfoExportVO> list = casPartyReportInfoDao.partyInfoSummary(casPartyReportInfoBO);try {String fileName = "信息汇总表";response.setContentType("application/octet-stream");response.setCharacterEncoding("utf-8");response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName+"_"+casPartyReportInfoBO.getReportQuarter()+".xlsx", "UTF-8"));CasPartyReportInfoExportVO capacityPo=new CasPartyReportInfoExportVO();capacityPo.setAddress("合计");//遍历列表，求各数据汇总capacityPo.setDate1(list.stream().filter(Po-> Po.getDate1()!=null).mapToInt(CasPartyReportInfoExportVO::getDate1).sum());capacityPo.setDate2(list.stream().filter(Po-> Po.getDate2()!=null).mapToInt(CasPartyReportInfoExportVO::getDate2).sum());capacityPo.setDate3(list.stream().filter(Po-> Po.getDate3()!=null).mapToInt(CasPartyReportInfoExportVO::getDate3).sum());capacityPo.setDate4(list.stream().filter(Po-> Po.getDate4()!=null).mapToInt(CasPartyReportInfoExportVO::getDate4).sum());capacityPo.setDate5(list.stream().filter(Po-> Po.getDate5()!=null).mapToInt(CasPartyReportInfoExportVO::getDate5).sum());list.add(capacityPo);ArrayList<CellLineRange> cellLineRanges=new ArrayList<>();//设置第几列开始合并int[] mergeColumnIndex = {0, 0};//设置第几行开始合并int mergeRowIndex = list.size();cellLineRanges.add(new CellLineRange(0,2));EasyExcel.write(response.getOutputStream())//设置行合并单元格.registerWriteHandler(new ExcelFillCellMergeStrategyUtils(mergeRowIndex,mergeColumnIndex))//设置行合并单元格.registerWriteHandler(new ExcelFillCelMergeStrategyUtils(cellLineRanges,list.size()-1)).head(CasPartyReportInfoExportVO.class).sheet("sheet1").doWrite(list);} catch (Exception e) {e.printStackTrace();}

成果：

本文参考了EasyExcel导出合并单元格_easyexcel合并单元格_是一个菜鸟程序员啊的博客-CSDN博客EasyExcel模板导出(行和列自动合并)_easyexcel 模板导出_Lzfnemo的博客-CSDN博客