关于java:利用-Apache-POI-实现动态公式计算

问题追踪

1. 实现简单的数学公式计算，例如方差、阶乘、排列组合等。
2. 自定义公式进行计算。
3. 单值后果。

需要剖析

利用 Apache POI 实现动静写入 Excel 公式和公式参数，读取计算后的数值。

Maven

<!-- Apache POI start -->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>5.0.0</version>
  <scope>test</scope>
</dependency>
<!-- xss 模式 -->
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>5.0.0</version>
  <scope>test</scope>
</dependency>
<!-- Apache POI end -->

JAVA

import org.apache.poi.hssf.usermodel.HSSFCell;
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.CellValue;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 利用 Apache Poi 实现动静公式计算
 *
 * @author BBF
 */
public class PoiTest {private static final CellValue DEFAULT_VALUE = new CellValue("");

  /**
   * 获取单元格
   * <p> 如果单元格没有值，须要构建单元格 </p>
   *
   * @param row 行对象
   * @param idx 单元格的列索引号，从 0 开始
   * @return 单元格对象
   */
  private static Cell getCell(Row row, int idx) {Cell cell = row.getCell(idx);
    if (cell == null) {cell = row.createCell(idx);
    }
    return cell;
  }

  /**
   * 从新计算单元格的值
   * <p style="color:red"> 特地留神：当动静扭转 excel 的值的时候，必须用本办法从新用公式计算单元格的值 </p>
   *
   * @param cell 单元格
   * @return {@link org.apache.poi.ss.usermodel.CellValue}
   */
  private static CellValue calcCellValue(Cell cell) {Workbook workbook = cell.getSheet().getWorkbook();
    FormulaEvaluator formulaEvaluator = null;
    if (cell instanceof HSSFCell) {
      // Excel 2003
      formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
    } else if (cell instanceof XSSFCell) {
      // Excel 2007+
      formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
    }
    if (formulaEvaluator != null) {
      // 进行计算并拿到值
      return formulaEvaluator.evaluate(cell);
    }
    return DEFAULT_VALUE;
  }
  
  /**
   * 从新设置公式和值，并读取公式计算后的值
   *
   * @param args Main 函数默认入参
   */
  public static void main(String[] args) {
    // 创立空对象（xssf - excel2007+；hssf - excel2003）Workbook hw = new XSSFWorkbook();
    // 创立 sheet
    Sheet sheet = hw.createSheet();
    // 定位 excel 的行
    Row r1 = sheet.createRow(0);
    // 调用公式取值
    Cell c1 = getCell(r1, 0);
    // 从新设置单元格的公式，不容许等号结尾！！此公式间接从 excel 文件中复制进去。c1.setCellFormula("SQRT(POWER(B1+C1,2)/D1)");
    // 传入值，顺次是 B1、C1、D1
    getCell(r1, 1).setCellValue(9);
    getCell(r1, 2).setCellValue(8);
    getCell(r1, 3).setCellValue(4);
    // 获取 c1 的值，也就是公式计算后的值
    CellValue v1 = calcCellValue(c1);
    System.out.println("单元格的值：" + v1.formatAsString());
    c1.setCellFormula("FACT(B1)");
    System.out.println("阶乘值：" + calcCellValue(c1).getNumberValue());
  }
}

执行后果

单元格的值：8.5
阶乘值：362880.0

备注

• 应用空的 XSSFWorkbook 对象，实用于 Excel2007+，同时也向下兼容Excel2003 的公式。
• 公式的语法能够参考 Excel 相干文档，倡议先在 Excel 中编辑调试一下。