2 回答

TA貢獻1829條經驗 獲得超7個贊
apache poi
需要工作簿是正確的WorkbookEvaluator
。并且由于您正在談論評估“類似 excel 公式的表達式”,這是必要的,因為此類公式中的所有變量都必須是該工作簿中的單元格引用或名稱。您給出的示例CONCATENATE(a,b,c)
只能Excel
在a
,b
和c
是Excel
names 時用作公式。否則會#Name?
導致Excel
.?順便說一句:Excel
函數是CONCATENATE
而不是CONCAT
。
但是這個工作簿不一定存儲在某個地方。它也只能在隨機存取存儲器中。
公式本身不需要在工作表中的某個地方。該公式也可以作為字符串給出,因為有WorkbookEvaluator.evaluate(java.lang.String formula, CellReference ref)。
例子:
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.formula.BaseFormulaEvaluator;
import org.apache.poi.ss.formula.WorkbookEvaluator;
import org.apache.poi.ss.formula.eval.*;
import org.apache.poi.ss.util.CellReference;
public class EvaluateExcelFunctions {
?static Object evaluateExcelFormula(String formula, Workbook workbookWithVariables) {
? if (workbookWithVariables.getNumberOfSheets() < 1) workbookWithVariables.createSheet();
? CellReference reference = new CellReference(workbookWithVariables.getSheetName(0), 0 , 0, false, false);
? CreationHelper helper = workbookWithVariables.getCreationHelper();
? FormulaEvaluator formulaevaluator = helper.createFormulaEvaluator();
? WorkbookEvaluator workbookevaluator = ((BaseFormulaEvaluator)formulaevaluator)._getWorkbookEvaluator();
? ValueEval valueeval = null;
? try {
? ?valueeval = workbookevaluator.evaluate(formula, reference);
? } catch (Exception ex) {
? ?return ex.toString();
? }
? if (valueeval instanceof StringValueEval) {
? ?String result = ((StringValueEval)valueeval).getStringValue();
? ?return result;
? } else if (valueeval instanceof NumericValueEval) {
? ?double result = ((NumericValueEval)valueeval).getNumberValue();
? ?return result;
? } else if (valueeval instanceof ErrorEval) {
? ?String result = ((ErrorEval)valueeval).getErrorString();
? ?return result;
? }
? return null;??
?}
?public static void main(String[] args) throws Exception {
? Workbook workbook =?
? ?//new XSSFWorkbook();
? ?new HSSFWorkbook();
? Name name;
? String formula;
? Object result;
? // example 1 concatenating strings - your example
? name = workbook.createName();
? name.setNameName("_a");
? name.setRefersToFormula("\"Text A \"");
? name = workbook.createName();
? name.setNameName("_b");
? name.setRefersToFormula("\"Text B \"");
? name = workbook.createName();
? name.setNameName("_c");
? name.setRefersToFormula("\"Text C \"");
? formula = "CONCATENATE(_a, _b, _c)";
? result = evaluateExcelFormula(formula, workbook);
? System.out.println(result);
? // example 2 Pythagorean theorem
? name = workbook.getName("_a");?
? name.setRefersToFormula("12.34");
? name = workbook.getName("_b");
? name.setRefersToFormula("56.78");
? formula = "SQRT(_a^2 + _b^2)";
? result = evaluateExcelFormula(formula, workbook);
? System.out.println(result);
? // example 3 complex math formula
? name = workbook.getName("_a");?
? name.setRefersToFormula("12.34");
? name = workbook.getName("_b");
? name.setRefersToFormula("56.78");
? name = workbook.getName("_c");
? name.setRefersToFormula("90.12");
? formula = "((_a+_b+_c)*_c/_b-_a)/2";
? result = evaluateExcelFormula(formula, workbook);
? System.out.println(result);
? // example 4 faulty formulas
? name = workbook.getName("_a");?
? name.setRefersToFormula("56.78");
? name = workbook.getName("_b");
? name.setRefersToFormula("190.12");
? name = workbook.getName("_c");
? name.setRefersToFormula("\"text\"");
? formula = "_a + _c";
? result = evaluateExcelFormula(formula, workbook);
? System.out.println(result);
? formula = "((_a + _b";
? result = evaluateExcelFormula(formula, workbook);
? System.out.println(result);
? formula = "_a \\ 2";
? result = evaluateExcelFormula(formula, workbook);
? System.out.println(result);
? formula = "_a^_b";
? result = evaluateExcelFormula(formula, workbook);
? System.out.println(result);
? formula = "_a/(_b-_b)";
? result = evaluateExcelFormula(formula, workbook);
? System.out.println(result);
? formula = "CONCAT(_a, _b)";
? result = evaluateExcelFormula(formula, workbook);
? System.out.println(result);
? workbook.close();
?}??
}
此代碼使用apache poi 4.1.0.
注意,Excel名稱不能是所有可能的變量名。例如,Excel名稱不能是c或,因為這會與可能的單元格引用C發生沖突。R1C1這就是為什么我命名我的名字_a,_b和_c。

TA貢獻1875條經驗 獲得超3個贊
要使用 IF 函數,我必須創建一個 Cell 實例以防止出現 NullPointerException:
Cell cell = workbookWithVariables.getSheet(workbookWithVariables.getSheetName(0)).createRow(0).createCell(0);
CellReference reference = new CellReference(cell);
添加回答
舉報