背景

在项目中遇到需要大量公式计算的逻辑每次根据不同值和不同的公式进行计算需要每个公式都解析成代码会消耗大量时间

实现思路

在使用Excel时,它里面提供了大量的数据计算表达式那么我们可以通过使用Excel设置好公式再用Java的POI对Excel进行填写, 最后读取指定公式位置的值这样就实现了公式计算

开始实现

这里我们用到的工具是 Apache 的 POI 工具那么先来导入依赖

<!-- 支持 2003 版本的 office 文件 -->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>5.2.2</version>
</dependency>

<!-- 支持 2007 版本以上的 office 文件 -->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>5.2.2</version>
</dependency>

<!-- poi 部分日志输出,需要依赖log4j, 不添加也可以 -->
<dependency>
  <groupId>org.apache.logging.log4j</groupId>
  <artifactId>log4j-core</artifactId>
  <version>2.17.2</version>
</dependency>

然后开始读取文件

拿到指定的 Workbook, Sheet, Row, Cell 即可以进行操作了 Workbook: 工作簿 Sheet: 工作表 Row: 数据行 Cell: 数据列,也可以理解为单元格

String filePath = "D:\\tmp\\demo_temp\\demo_calc.xls";

// 获取文件流
FileInputStream fileInputStream = new FileInputStream(filePath);

// 读取工作簿
Workbook workbook = new HSSFWorkbook(fileInputStream);

// 读取第一个工作表
Sheet sheet = workbook.getSheetAt(0);

// 读取第一行数据
Row row = sheet.getRow(0);

// 读取第一行第一列的单元格 也就是 A1
Cell cell = row.getCell(0);

操作单元格

在拿到 Cell 之后,就可以对单元格进行操作了,设置值和读取值

Cell cell = row.getCell(0);
// 设置单元格数值,也支持String其他的类型
cell.setCellValue(10.0D);
// 读取单元格字符串类型数据, 当然也有其他类型的
cell.getStringCellValue();
// 获取单元格数据类型,类型有多种,可以去看 CellType 枚举的详情
CellType type = cell.getCellType();
// 获取计算的值,注意获取计算值时,要在类型为 CellType.FORMULA 才可以
cell.getNumericCellValue();

基本的读取和单元格操作就上面这些下面开始直接show code

Demo代码

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Objects;

/**
 * @author Nick
 * @since 2022/7/1/001
 */
public class GetExcelCalcDemo {

    public static void main(String[] args) throws IOException {

        // 需要读取的文件
        String filePath = "D:\\tmp\\demo_temp\\demo_calc.xls";

        GetExcelCalcDemo getExcelCalcDemo = new GetExcelCalcDemo();
        getExcelCalcDemo.getCalc(filePath);
    }

    public String getCalc(String filePath) throws IOException {
        // 这里的计算模板内容为
        // 计算公式:  A1+B1-C1+0.5
        // A4 为公式计算的单元格

        // 准备用于计算的值
        Double[] args = {10D,20D,50D};
        // 结果应该是 10 + 20 - 50 + 0.5 = -19.5

        // 读取工作簿
        FileInputStream fileInputStream = new FileInputStream(filePath);
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        if (Objects.nonNull(workbook)) {
            // 读取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            if (Objects.nonNull(sheet)) {
                // 读取第一行
                Row row = sheet.getRow(0);
                if (Objects.nonNull(row)) {

                    // 设置输入值
                    for (int i = 0; i < args.length; i++) {
                        Double arg = args[i];
                        Cell cell = row.getCell(i);
                        if (Objects.isNull(cell)) {
                            cell = row.createCell(i);
                        }
                        cell.setCellValue(arg);
                    }

                    // 更新公式计算值
                    FormulaEvaluator eval=null;
                    if(workbook instanceof HSSFWorkbook) {
                        eval = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
                    }
                    else if(workbook instanceof XSSFWorkbook) {
                        eval = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                    }

                    // 读取第四列
                    Cell cell = row.getCell(3);
                    // 获取计算公式
                    String cellFormula = cell.getCellFormula();
                    // 计算公式
                    eval.evaluateFormulaCell(cell);

                    double numericCellValue = cell.getNumericCellValue();
                    System.out.println("计算公式:  " + cellFormula);
                    System.out.println("计算值:  " + numericCellValue);
                    return String.valueOf(numericCellValue);

                }

            }
        }

        return "";
    }

}

注意点

想要计算的结果马上更新,需要手动调用evaluateFormulaCell()
Excel2003 和 Excel2007的FormulaEvaluator不一样,需要分别创建
在设置输入的单元格数据时,注意要判断单元格是否为null,需要进行创建单元格
获取计算值时,要在类型为 CellType.FORMULA 才可以进行计算
因为只是Demo,输入的参数不够优雅,正式使用需要考虑更好的方式

利用Excel实现模板化公式计算

背景

实现思路

开始实现

然后开始读取文件

操作单元格

Demo代码