evaluation/ims-common/src/main/java/com/ims/common/utils/FormulaUtils.java

package com.ims.common.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FormulaEvaluator;

import java.math.BigDecimal;

public class FormulaUtils {




	/**
	 * Sheet 中的每一行
	 */
	private static HSSFRow row = null;

	private static FormulaEvaluator formulaEvaluator = null;

	/**
	 * 计算值
	 *
	 * @param formula Excel 中的公式，例如：MAX(56-FLOOR(20/6,1),2)
	 * @return
	 */
	public static double caculateFormula(String formula) {
		if(formula.startsWith("=")){
			formula =formula.replace("=","");
		}
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();
		row = sheet.createRow(0);
		formulaEvaluator = new HSSFFormulaEvaluator(workbook);

		// 这里必须新建一个对象，否则只有第一个 formula 才有效。查看 formulaEvaluator.evaluate 的源码。
		HSSFCell cell = row.createCell(0);
		cell.setCellType(CellType.FORMULA);
		try {
			cell.setCellFormula(formula);
			double value = formulaEvaluator.evaluate(cell).getNumberValue();
			return new BigDecimal(value).setScale(4, BigDecimal.ROUND_CEILING).doubleValue();
		} catch (FormulaParseException e) {
			System.out.print(e.getMessage());
			return 0;
		}
	}
}