Java POI excel单元格计算的三种方式

Maven环境

  <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
  <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.2</version>
  </dependency>
  <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
  <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.2</version>
  </dependency>

1、方式一：填写数字计算

/**
 * excel单元格公式计算
 * <pre>
 *     workbook三种文件格式的导出对比
 *     https://blog.csdn.net/sufu1065/article/details/115301974
 * </pre>
 */
public static void setCellFormulaCalc1() throws IOException {
    /*
     * POI关于 工作簿的三个对象
     *      1. new HSSFWorkbook(): 格式为office 2003专用格式，即.xls，优点是导出数据速度快，但是最多65536行数据，行数多了就会报错
     *      2. new XSSFWorkbook(): 导出的文件格式为office 2007专用格式，即.xlsx，优点是导出的数据不受行数限制，缺点导出速度慢
     *      3. new SXSSFWorkbook(): SXSSF 是 XSSF API的兼容流式扩展，主要解决当使用 XSSF 方式导出大数据量时，内存溢出的问题，支持导出大批量的excel数据
     */
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    SXSSFSheet sheet = workbook.createSheet("excel单元格公式计算");

    SXSSFRow row1 = sheet.createRow(0);

    // 第一列
    SXSSFCell cell1 = row1.createCell(0);
    // 不推荐使用指定单元格的类型
    // cell1.setCellType(CellType.FORMULA); // 公式类型：在直接写计算方式的时候不能指定CellType.FORMULA类型，否则会报类型非法的错误
    cell1.setCellFormula("2+3*4"); // 计算公式 结果14

    FileOutputStream outputStream = new FileOutputStream("D:/temp/Excel单元格公式计算.xlsx");
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}

直接由数字计算来最终结果

2、方式二：excel函数计算（示例SUM求和函数）

/**
 * excel单元格公式计算
 * <pre>
 *     workbook三种文件格式的导出对比
 *     https://blog.csdn.net/sufu1065/article/details/115301974
 * </pre>
 */
public static void setCellFormulaCalc1() throws IOException {
    /*
     * POI关于 工作簿的三个对象
     *      1. new HSSFWorkbook(): 格式为office 2003专用格式，即.xls，优点是导出数据速度快，但是最多65536行数据，行数多了就会报错
     *      2. new XSSFWorkbook(): 导出的文件格式为office 2007专用格式，即.xlsx，优点是导出的数据不受行数限制，缺点导出速度慢
     *      3. new SXSSFWorkbook(): SXSSF 是 XSSF API的兼容流式扩展，主要解决当使用 XSSF 方式导出大数据量时，内存溢出的问题，支持导出大批量的excel数据
     */
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    SXSSFSheet sheet = workbook.createSheet("excel单元格公式计算");

    SXSSFRow row1 = sheet.createRow(0);

    // 第一列
    SXSSFCell cell1 = row1.createCell(0);
    // 不推荐使用指定单元格的类型
    // cell1.setCellType(CellType.FORMULA); // 公式类型：在直接写计算方式的时候不能指定CellType.FORMULA类型，否则会报类型非法的错误
    cell1.setCellFormula("2+3*4"); // 计算公式 结果14

    // 第二列
    SXSSFCell cell2 = row1.createCell(1);
    cell2.setCellValue(10);

    // 第三列
    SXSSFCell cell3 = row1.createCell(2);
//        cell3.setCellType(CellType.FORMULA);
    // 计算A列1行+B列1行的和
    cell3.setCellFormula("SUM(A1,B1)");

    // 第四列
    SXSSFCell cell4 = row1.createCell(3);
//        cell3.setCellType(CellType.FORMULA);
    // 计算A列1行+B列1行的和+C列1行的和
    cell4.setCellFormula("SUM(A1,B1,C1)");

    FileOutputStream outputStream = new FileOutputStream("D:/temp/Excel单元格公式计算.xlsx");
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}

3、方式三：按指定单元格计算

/**
 * 直接指定列名的计算方式
 */
public static void calcCellByCellName() throws IOException {
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    SXSSFSheet sheet = workbook.createSheet("excel指定列计算公式");

    int rowIndex = 0, rowNum = 1;
    int cellIndex1 = 0, cellIndex2 = 1, cellIndex3 = 2, cellIndex4 = 3; // 1-4列, 第四列为前三列计算结果 cellIndex1 - cellIndex2 + cellIndex3

    // 创建行列并设置值和计算公式
    SXSSFRow row = sheet.createRow(rowIndex);
    SXSSFCell cell1 = row.createCell(cellIndex1);
    cell1.setCellValue(10);

    SXSSFCell cell2 = row.createCell(cellIndex2);
    cell2.setCellValue(2);

    SXSSFCell cell3 = row.createCell(cellIndex3);
    cell3.setCellValue(4);

    SXSSFCell cell4 = row.createCell(cellIndex4);
    // ====> A1 - B1 + C1
    //cell4.setCellFormula((excelIndexToStr(cellIndex1) + rowNum) + "-" + (excelIndexToStr(cellIndex2) + rowNum) + "+" + (excelIndexToStr(cellIndex3) + rowNum));
    cell4.setCellFormula("A1-B1+C1");

    FileOutputStream outputStream = new FileOutputStream("D:/temp/Excel指定列计算公式.xlsx");
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}

4、求和函数升级计算：求指定行一行数据的和，结果填到最后

/**
 * 单元格列尾求和
 */
public static void calcCellEndSum() throws IOException {
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    SXSSFSheet sheet = workbook.createSheet("excel单元格列尾求和计算");

    // 定义：定义列数据
    int[] dataList = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10};

    // 第一行定义为列名
    SXSSFRow row1 = sheet.createRow(0);
    for (int cellIndex = 0; cellIndex <= dataList.length; cellIndex++) {
        SXSSFCell cell = row1.createCell(cellIndex);
        if (cellIndex == dataList.length) {
            cell.setCellValue("合计");
        } else {
            cell.setCellValue("数据" + (cellIndex + 1));
        }
    }

    SXSSFRow row2 = sheet.createRow(1);
    // 第二行为数据
    for (int cellIndex = 0; cellIndex < dataList.length; cellIndex++) {
        SXSSFCell cell = row2.createCell(cellIndex);
        cell.setCellValue(dataList[cellIndex]);
    }
    // 计算指定行的数据，结果填到最后
    lastCellSumByRow(row2);


    FileOutputStream outputStream = new FileOutputStream("D:/temp/Excel单元格列尾求和计算.xlsx");
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}

/**
 * 指定行的末列求和: 填写计算结果
 */
public static void lastCellSumByRow(SXSSFRow row) {
    short lastCellNum = row.getLastCellNum();
    // 统计一行数据的求和结果
    List<String> sumCell = new ArrayList<>();
    for (int index = 0; index < lastCellNum; index++) {
        String str = excelIndexToStr(index);
        // A+行号
        int rowNum = row.getRowNum() + 1;
        System.err.println(str + rowNum);
        sumCell.add(str + rowNum);
    }

    // 最后一列求和
    SXSSFCell lastCell = row.getCell(lastCellNum);
    if (lastCell == null) {
        lastCell = row.createCell(lastCellNum);
    }
    lastCell.setCellFormula("SUM(" + StringUtils.join(sumCell, ",") + ")");
}

/**
 * https://blog.csdn.net/leeo_may/article/details/123680350
 * 列索引转换为A，B，C...
 * <pre>
 *     如：cellIndex=0 --> A
 *     如：cellIndex=1 --> B
 *     如：cellIndex=25 --> Z
 *     如：cellIndex=26 --> AB
 * </pre>
 *
 * @param cellIndex Excel列索引
 */
private static String excelIndexToStr(int cellIndex) {
    // 转26进制 0-25
    // int calculateValue = value - 1; (value = cellIndex + 1)
    int calculateValue = cellIndex; // 本身传进来的就是列索引0-25，的26进制
    // 取高位
    int high = calculateValue / 26;
    // 取低位
    int low = calculateValue % 26;
    // 低位可直接取出对应的字母
    String transStr = String.valueOf((char) (low + 65));
    if (high > 0) {
        // 高位递归取出字母
        transStr = excelIndexToStr(high) + transStr;
    }
    return transStr;
}

参考链接

Java实现Excel列号转字母_幼稚园保安的博客-CSDN博客
Java中操作Excel的3种方法，太好用了！_sufu1065的博客-CSDN博客