JAVA POI Excel 使用数组公式 FREQUENCY

平台及依赖

1. JAVA 17
2. POI版本

<dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>5.2.5</version>
 </dependency>
 <dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi-ooxml</artifactId>
     <version>5.2.5</version>
 </dependency>
1
2
3
4
5
6
7
8
9
10

正文

说明

有时候我们希望导出的excel能够包含一些基本公式，普通的单个公式如 SUM, COUNT等直接在cell单元格设置即可，如下代码

XSSFCell cell3 = row.createCell(0);
cell3.setCellFormula("SUM($A$2:$A$1000)");
1
2

但对于一些特殊公式如FREQUENCY这样需要一组公式共同完成结果集的，需要额外设置才能获取对应结果。
看下面的例子:

源码

package org.lionzhou.example.excel.formula.array;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.util.Arrays;
import java.util.List;

/**
 * @author liangcheng.zhou@microtechmd.com
 * @version 1.0
 * @date 2024/4/8
 */
public class EvaluateFormulaFrequency {

    // test data
    static final List<String> TEST_DATA = Arrays.asList("6.364", "6.44", "6.693", "6.508", "6.895", "6.41", "6.758", "6.364", "6.39", "6.663", "6.538", "6.431", "6.658", "6.51", "6.164", "6.613", "6.475", "6.121", "0", "5.884", "6.721", "6.345", "6.346", "6.504", "6.406", "6.993", "6.909", "7.071", "6.255", "6.938", "6.831", "7.058", "6.708", "0", "6.17", "6.716", "6.624", "7.113", "6.913", "6.769", "6.297", "6.554", "6.756", "7.073", "6.518", "7.725", "6.599", "6.795", "7.208", "6.349", "6.069", "6.391", "7.108", "6.888", "6.589", "6.7", "6.964", "7.061", "6.408", "6.864", "6.01", "6.441", "6.633", "6.18", "6.245", "6.589", "6.266", "6.865", "6.481", "7.06", "7.118", "6.516", "7.136", "6.672", "6.635", "6.796", "6.355", "7.356", "6.385", "6.693", "6.354", "6.515", "6.581", "7.248", "8.03", "6.836", "6.926", "6.311", "6.155", "6.656", "6.318", "6.938", "6.07", "6.331", "6.348", "6.538", "6.773", "6.203", "6.21", "6.344");

    public void export(File file) throws Exception {
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();

        XSSFSheet sheet = workbook.createSheet("sheet-1");
        // column title start
        XSSFRow headerRow = sheet.createRow(0);
        XSSFCell cell = headerRow.createCell(0);
        cell.setCellValue("data");
        // cell width, 一个字符默认情况下是256个单位
        sheet.setColumnWidth(0, 256 * 8);

        XSSFCell cell1 = headerRow.createCell(2);
        cell1.setCellValue("scale");
        sheet.setColumnWidth(2, 256 * 6);

        XSSFCell cell2 = headerRow.createCell(3);
        cell2.setCellValue("数量");
        sheet.setColumnWidth(3, 256 * 6);
        // column title end

        // set test data
        for (int i = 0; i < TEST_DATA.size(); i++) {
            XSSFRow row = sheet.createRow(i + 1);
            XSSFCell rowCell = row.createCell(0);
            // warning 类型会影响数据公式结果
            rowCell.setCellValue(Double.parseDouble(TEST_DATA.get(i)));
        }

        // set scale
        for (int i = 0; i < 11; i++) {
            XSSFRow row = sheet.getRow(i + 1);
            XSSFCell rowCell = row.createCell(2);
            rowCell.setCellValue(i);
        }

		// 关键 start
        // a array formula  前面是数据公式，后面是参与公式的一组单元格范围
        sheet.setArrayFormula("FREQUENCY($A$2:$A$10000,$C$2:$C$12)", new CellRangeAddress(1, 11, 3, 3));

        // execute formula
        workbook.setForceFormulaRecalculation(true);
        workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();
        // 关键 end

        workbook.write(new FileOutputStream(file));
    }

    public static void main(String[] args) throws Exception {
        EvaluateFormulaFrequency evaluateFormula = new EvaluateFormulaFrequency();
        File file = new File(String.format("%s/data/%s.xls", System.getProperty("user.dir"), evaluateFormula.getClass().getSimpleName()));
        if (file.exists()) {
            file.delete();
        } else {
            file.getParentFile().mkdirs();
        }
        evaluateFormula.export(file);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

上面的例子可以直接下载
源码链接

效果