Java poi填充Excel（包含公式）

淡淡的烟草味﹌ 2023-10-09 22:43 1阅读 0赞

## **依赖** ##

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.1.2</version>
    </dependency>

## **Excel模板** ##

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

![fbb09fcb0dc34cda879a1ee79a8f5c41.png][]

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

![5472ef36f0ef4f17ad11675225e9168a.png][]

## **实现** ##

@Autowired
    private ResourceLoader resourceLoader;

public void uploadRecordExcelFill() {
            FileOutputStream out = null;
            try {
                out = new FileOutputStream("E:/数据上传统计HochladenStatistiks.xlsx");
                //spring自带的读取文件（文件放在resource下）
                Resource resource = resourceLoader.getResource("classpath:excel/upload_record.xlsx");
                InputStream is = resource.getInputStream();
                //这里使用流的方式。如果使用File的方式，打包后是找不到模板文件路径的
                Workbook workbook = HSSFWorkbookFactory.create(is);
    
                //第一个sheet页  上传记录
                Sheet uploadRecordSheet = workbook.getSheetAt(0);
    
                //第一个sheet页所用数据
                List<UploadRecordVo> recordList = getUploadRecordExcelData();
                for (int i = 0; i < recordList.size(); i++) {
                    Row row = uploadRecordSheet.createRow(i + 1);
    
                    row.createCell(0).setCellValue(recordList.get(i).getNum());
                    row.createCell(1).setCellValue(recordList.get(i).getMachineName());
                    row.createCell(2).setCellValue(recordList.get(i).getExperimentName());
                    row.createCell(3).setCellValue(recordList.get(i).getFileTime());
                    row.createCell(4).setCellValue(recordList.get(i).getBeginTime());
                    row.createCell(5).setCellValue(recordList.get(i).getEndTime());
                    row.createCell(6).setCellValue(recordList.get(i).getFileSize());
                    row.createCell(7).setCellFormula("IF(G" + (i + 2) + "=\"\",\"\",IF(TEXT(F" + (i + 2) + "-E" + (i + 2) + ",\"[s]\")=\"0\", \"上传时间<1s\",G" + (i + 2) + "/TEXT(F" + (i + 2) + "-E" + (i + 2) + ",\"[s]\")))");
                    row.createCell(8).setCellValue(recordList.get(i).getYears());
                    row.createCell(9).setCellValue(recordList.get(i).getMonths());
                    row.createCell(10).setCellValue(recordList.get(i).getWeeks());
                }
                //执行公式
                uploadRecordSheet.setForceFormulaRecalculation(true);
    
                //第二个sheet页  统计数据
                Sheet statisticalDataSheet = workbook.getSheetAt(1);
                //第二页左侧数据
                List<WeekDataStatisticsVo> yearWeekList = selectYearAndWeek();
                for (int i = 0; i < yearWeekList.size(); i++) {
                    Row row = statisticalDataSheet.createRow(i + 2);
    
                    row.createCell(0).setCellValue(yearWeekList.get(i).getYearsWeek());
                    row.createCell(1).setCellValue(yearWeekList.get(i).getWeeks());
                    row.createCell(2).setCellFormula("IF(COUNTIFS(上传记录!B:B,\"*\"&\"Changchun\",上传记录!I:I,统计数据!A" + (i + 3) + ",上传记录!K:K,统计数据!B" + (i + 3) + ")=0,\"\",COUNTIFS(上传记录!B:B,\"*\"&\"Changchun\",上传记录!I:I,统计数据!A" + (i + 3) + ",上传记录!K:K,统计数据!B" + (i + 3) + "))");
                    row.createCell(3).setCellFormula("SUMIFS(上传记录!G:G,上传记录!B:B,\"*\"&\"Changchun\",上传记录!I:I,统计数据!A" + (i + 3) + ",上传记录!K:K,统计数据!B" + (i + 3) + ")/1000000");
                    row.createCell(4).setCellFormula("IF(COUNTIFS(上传记录!B:B,\"*\"&\"PG\",上传记录!I:I,统计数据!A" + (i + 3) + ",上传记录!K:K,统计数据!B" + (i + 3) + ")=0,\"\",COUNTIFS(上传记录!B:B,\"*\"&\"PG\",上传记录!I:I,统计数据!A" + (i + 3) + ",上传记录!K:K,统计数据!B" + (i + 3) + "))");
                    row.createCell(5).setCellFormula("SUMIFS(上传记录!G:G,上传记录!B:B,\"*\"&\"PG\",上传记录!I:I,统计数据!A" + (i + 3) + ",上传记录!K:K,统计数据!B" + (i + 3) + ")/1000000");
                    row.createCell(6).setCellFormula("IF(AND(C" + (i + 3) + "=\"\",E" + (i + 3) + "=\"\"),\"\",IF(C" + (i + 3) + "=\"\",0,C" + (i + 3) + ") + IF(E" + (i + 3) + " =\"\",0,E" + (i + 3) + "))");
                    row.createCell(7).setCellFormula("D" + (i + 3) + "+F" + (i + 3));
                }
                //第二页右侧数据
                List<MonthDataStatisticsVo> yearMonthList = selectYearAndMonth();
                for (int i = 0; i < yearMonthList.size(); i++) {
                    Row row = statisticalDataSheet.getRow(i + 2);
    
                    row.createCell(9).setCellValue(yearMonthList.get(i).getYearsMonth());
                    row.createCell(10).setCellValue(yearMonthList.get(i).getMonths());
                    row.createCell(11).setCellFormula("IF(COUNTIFS(上传记录!B:B,\"*\"&\"Changchun\",上传记录!I:I,统计数据!J" + (i + 3) + ",上传记录!J:J,统计数据!K" + (i + 3) + ")=0,\"\",COUNTIFS(上传记录!B:B,\"*\"&\"Changchun\",上传记录!I:I,统计数据!J" + (i + 3) + ",上传记录!J:J,统计数据!K" + (i + 3) + "))");
                    row.createCell(12).setCellFormula("SUMIFS(上传记录!G:G,上传记录!B:B,\"*\"&\"Changchun\",上传记录!I:I,统计数据!J" + (i + 3) + ",上传记录!J:J,统计数据!K" + (i + 3) + ")/1000000");
                    row.createCell(13).setCellFormula("IF(COUNTIFS(上传记录!B:B,\"*\"&\"PG\",上传记录!I:I,统计数据!J" + (i + 3) + ",上传记录!J:J,统计数据!K" + (i + 3) + ")=0,\"\",COUNTIFS(上传记录!B:B,\"*\"&\"PG\",上传记录!I:I,统计数据!J" + (i + 3) + ",上传记录!J:J,统计数据!K" + (i + 3) + "))");
                    row.createCell(14).setCellFormula("SUMIFS(上传记录!G:G,上传记录!B:B,\"*\"&\"PG\",上传记录!I:I,统计数据!J" + (i + 3) + ",上传记录!J:J,统计数据!K" + (i + 3) + ")/1000000");
                    row.createCell(15).setCellFormula("IF(AND(L" + (i + 3) + "=\"\",N" + (i + 3) + "=\"\"),\"\",IF(L" + (i + 3) + "=\"\",0,L" + (i + 3) + ")+IF(N" + (i + 3) + "=\"\",0,N" + (i + 3) + "))");
                    row.createCell(16).setCellFormula("M" + (i + 3) + "+O" + (i + 3));
                }
    
                statisticalDataSheet.setForceFormulaRecalculation(true);
    
                workbook.write(out);
    
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (out != null) {
                    try {
                        out.flush();
                        out.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
    
                }
            }
        }

## 结果 ##

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

![a9d7a838e70f416aa5488a85fb3f742c.png][]

**第二个sheet页**

![4dc4cdfb1e8147368a07c41fc2442633.png][]

[fbb09fcb0dc34cda879a1ee79a8f5c41.png]: https://img-blog.csdnimg.cn/fbb09fcb0dc34cda879a1ee79a8f5c41.png
[5472ef36f0ef4f17ad11675225e9168a.png]: https://img-blog.csdnimg.cn/5472ef36f0ef4f17ad11675225e9168a.png
[a9d7a838e70f416aa5488a85fb3f742c.png]: https://img-blog.csdnimg.cn/a9d7a838e70f416aa5488a85fb3f742c.png
[4dc4cdfb1e8147368a07c41fc2442633.png]: https://img-blog.csdnimg.cn/4dc4cdfb1e8147368a07c41fc2442633.png