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;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @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<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
@AllArgsConstructor
public class CellLineRange {

    /**
     * 起始列
     */
    private int firstCol;

    /**
     * 结束列
     */
    private int lastCol;
}

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 ExcelFillCelMergeStrategyUtils implements CellWriteHandler {

    //自定义合并单元格的列 如果想合并   第4列和第5例 、第6列和第7例： [CellLineRange(firstCol=3, lastCol=4), CellLineRange(firstCol=5, lastCol=6)]
    private List<CellLineRange> cellLineRangeList;

    //自定义合并单元格的开始的行  一般来说填表头行高0 表示从表头下每列开始合并 ：如表头行高位为3则 int mergeRowIndex = 2  ;
    private int mergeRowIndex;

    public ExcelFillCelMergeStrategy(List<CellLineRange> cellLineRangeList, int mergeRowIndex) {
        this.cellLineRangeList=cellLineRangeList;
        this.mergeRowIndex=mergeRowIndex;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @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<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {

        //当前单元格的行数
        int curRowIndex = cell.getRowIndex();
        // 当前单元格的列数
        int curColIndex = cell.getColumnIndex();
        if (curRowIndex > mergeRowIndex) {
            if (curRowIndex > mergeRowIndex) {
                for (int i = 0; i < cellLineRangeList.size(); i++) {
                    if (curColIndex > cellLineRangeList.get(i).getFirstCol()&&curColIndex<=cellLineRangeList.get(i).getLastCol()) {
                        //单元格数据处理
                        mergeWithLeftLine(writeSheetHolder, cell, curRowIndex, curColIndex);
                        break;
                    }
                }
            }
        }
    }

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 模板导出_Lzfnemo2009的博客-CSDN博客