Java easyexcel填充Excel（包含公式）

乐得屁颠儿。

已于 2023-05-11 14:05:30 修改

阅读量3.6k

点赞数 3

文章标签： excel java

于 2023-05-09 17:25:28 首次发布

本文链接：https://blog.csdn.net/weixin_50272584/article/details/130583923

版权

依赖

<!-- easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>
<!-- poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>

Excel模板

第一个sheet页

第二个sheet页

监听器

@Slf4j
public class EasyExcelCellWriteHandler implements CellWriteHandler {

    @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) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

        //sheet页码
        Integer sheetNo = writeSheetHolder.getSheetNo();
        //第一页
        if (sheetNo == 0) {
            if (cell.getColumnIndex() == 7) {
                int actualCellRowNum = cell.getRowIndex() + 1;
                cell.setCellFormula("IF(G" + actualCellRowNum + "=\"\",\"\",IF(TEXT(F" + actualCellRowNum + "-E" + actualCellRowNum + ",\"[s]\")=\"0\", \"上传时间<1s\",G" + actualCellRowNum + "/TEXT(F" + actualCellRowNum + "-E" + actualCellRowNum + ",\"[s]\")))");
            }
        }
        //第二页
        if (sheetNo == 1) {
            //行
            int rowNum = cell.getRowIndex() + 1;
            //列
            int columnNum = cell.getColumnIndex();
            if (columnNum == 2) {
                cell.setCellFormula("IF(COUNTIFS(上传记录!B:B,\"*\"&\"Changchun\",上传记录!I:I,统计数据!A" + rowNum + ",上传记录!K:K,统计数据!B" + rowNum + ")=0,\"\",COUNTIFS(上传记录!B:B,\"*\"&\"Changchun\",上传记录!I:I,统计数据!A" + rowNum + ",上传记录!K:K,统计数据!B" + rowNum + "))");
            } else if (columnNum == 3) {
                cell.setCellFormula("SUMIFS(上传记录!G:G,上传记录!B:B,\"*\"&\"Changchun\",上传记录!I:I,统计数据!A" + rowNum + ",上传记录!K:K,统计数据!B" + rowNum + ")/1000000");
            } else if (columnNum == 4) {
                cell.setCellFormula("IF(COUNTIFS(上传记录!B:B,\"*\"&\"PG\",上传记录!I:I,统计数据!A" + rowNum + ",上传记录!K:K,统计数据!B" + rowNum + ")=0,\"\",COUNTIFS(上传记录!B:B,\"*\"&\"PG\",上传记录!I:I,统计数据!A" + rowNum + ",上传记录!K:K,统计数据!B" + rowNum + "))");
            } else if (columnNum == 5) {
                cell.setCellFormula("SUMIFS(上传记录!G:G,上传记录!B:B,\"*\"&\"PG\",上传记录!I:I,统计数据!A" + rowNum + ",上传记录!K:K,统计数据!B" + rowNum + ")/1000000");
            } else if (columnNum == 6) {
                cell.setCellFormula("IF(AND(C" + rowNum + "=\"\",E" + rowNum + "=\"\"),\"\",IF(C" + rowNum + "=\"\",0,C" + rowNum + ") + IF(E" + rowNum + " =\"\",0,E" + rowNum + "))");
            } else if (columnNum == 7) {
                cell.setCellFormula("D" + rowNum + "+F" + rowNum);
            } else if (columnNum == 11) {
                cell.setCellFormula("IF(COUNTIFS(上传记录!B:B,\"*\"&\"Changchun\",上传记录!I:I,统计数据!J" + rowNum + ",上传记录!J:J,统计数据!K" + rowNum + ")=0,\"\",COUNTIFS(上传记录!B:B,\"*\"&\"Changchun\",上传记录!I:I,统计数据!J" + rowNum + ",上传记录!J:J,统计数据!K" + rowNum + "))");
            } else if (columnNum == 12) {
                cell.setCellFormula("SUMIFS(上传记录!G:G,上传记录!B:B,\"*\"&\"Changchun\",上传记录!I:I,统计数据!J" + rowNum + ",上传记录!J:J,统计数据!K" + rowNum + ")/1000000");
            } else if (columnNum == 13) {
                cell.setCellFormula("IF(COUNTIFS(上传记录!B:B,\"*\"&\"PG\",上传记录!I:I,统计数据!J" + rowNum + ",上传记录!J:J,统计数据!K" + rowNum + ")=0,\"\",COUNTIFS(上传记录!B:B,\"*\"&\"PG\",上传记录!I:I,统计数据!J" + rowNum + ",上传记录!J:J,统计数据!K" + rowNum + "))");
            } else if (columnNum == 14) {
                cell.setCellFormula("SUMIFS(上传记录!G:G,上传记录!B:B,\"*\"&\"PG\",上传记录!I:I,统计数据!J" + rowNum + ",上传记录!J:J,统计数据!K" + rowNum + ")/1000000");
            } else if (columnNum == 15) {
                cell.setCellFormula("IF(AND(L" + rowNum + "=\"\",N" + rowNum + "=\"\"),\"\",IF(L" + rowNum + "=\"\",0,L" + rowNum + ")+IF(N" + rowNum + "=\"\",0,N" + rowNum + "))");
            } else if (columnNum == 16) {
                cell.setCellFormula("M" + rowNum + "+O" + rowNum);
            }

        }
    }
}

实现

public void fillInData() {

        // 输入模板路径
        String templatePath = "E:/upload_record.xlsx";
        // 结果输出路径
        String outputPath = "E:/数据上传统计HochladenStatistiks.xlsx";

        //第一个sheet页所用数据
        List<UploadRecordVo> recordList = getUploadRecordExcelData();

        //第二个sheet页左侧所用数据
        List<WeekDataStatisticsVo> yearWeekList = selectYearAndWeek();
        //第二个sheet页右侧所用数据
        List<MonthDataStatisticsVo> yearMonthList = selectYearAndMonth();

        //数据写出
        ExcelWriter writer = EasyExcel.write(new File(outputPath)).withTemplate(new File(templatePath)).build();
        //操作第一个sheet(记得注册自定义的CellWriteHandler)
        WriteSheet sheet1 = EasyExcel.writerSheet(0).registerWriteHandler(new EasyExcelCellWriteHandler()).build();

        //操作第二个sheet
        WriteSheet sheet2 = EasyExcel.writerSheet(1).registerWriteHandler(new EasyExcelCellWriteHandler()).build();


        // 填充第一个sheet页列表数据
        writer.fill(recordList, FillConfig.builder().forceNewRow(Boolean.TRUE).build(), sheet1);

        // 填充第二个sheet页左侧列表数据
        writer.fill(yearWeekList, FillConfig.builder().forceNewRow(Boolean.TRUE).build(), sheet2);

        // 填充第二个sheet页右侧列表数据
        writer.fill(yearMonthList, FillConfig.builder().forceNewRow(Boolean.TRUE).build(), sheet2);

        //设置强制计算公式：不然公式会以字符串的形式显示在excel中
        Workbook workbook = writer.writeContext().writeWorkbookHolder().getWorkbook();
        workbook.setForceFormulaRecalculation(true);

        writer.close();
    }