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

2022-07-011166

背景

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

实现思路

在使用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,输入的参数不够优雅,正式使用需要考虑更好的方式
分享
点赞0
打赏
上一篇:Docker常用命令笔记(一)
下一篇:自动化测试重运行怎么用?