Java easyexcel填充Excel（包含公式）

太过爱你忘了你带给我的痛 2023-10-09 22:43 2阅读 0赞

## **依赖** ##

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

## Excel模板 ##

#### 第一个sheet页 ####

![c3a7a94e7dd546dcbf2cc3a7f00195ad.png][]

#### 第二个sheet页 ####

![9b453d5c332148488fa3ded697766cde.png][]

## **监听器** ##

@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();
        }

## **结果** ##

#### 第一个sheet页 ####

![686fdc6f11db4ea081ac0a246283aabc.png][]

#### 第二个sheet页 ####

![88c82e36ab0e4f609ed3d7e3c746a7e1.png][]

[c3a7a94e7dd546dcbf2cc3a7f00195ad.png]: https://img-blog.csdnimg.cn/c3a7a94e7dd546dcbf2cc3a7f00195ad.png
[9b453d5c332148488fa3ded697766cde.png]: https://img-blog.csdnimg.cn/9b453d5c332148488fa3ded697766cde.png
[686fdc6f11db4ea081ac0a246283aabc.png]: https://img-blog.csdnimg.cn/686fdc6f11db4ea081ac0a246283aabc.png
[88c82e36ab0e4f609ed3d7e3c746a7e1.png]: https://img-blog.csdnimg.cn/88c82e36ab0e4f609ed3d7e3c746a7e1.png