Summary: | When Re-calculating all formulas in a Workbook which include "HEX2DEC" function get unexpected exception | ||
---|---|---|---|
Product: | POI | Reporter: | jzhao <jzhao> |
Component: | HSSF | Assignee: | POI Developers List <dev> |
Status: | RESOLVED FIXED | ||
Severity: | major | CC: | o.odent |
Priority: | P2 | ||
Version: | 3.10-FINAL | ||
Target Milestone: | --- | ||
Hardware: | PC | ||
OS: | All | ||
Attachments: | test file |
Can you try with POI 3.11 beta 2, or even beta the Release Candidate for POI 3.11 beta 3? (Link for the latter available on the dev@poi.apache.org mailing list archive) I have been test the latest beta version, the problem still exists.
> Can you try with POI 3.11 beta 2, or even beta the Release Candidate for POI
> 3.11 beta 3? (Link for the latter available on the dev@poi.apache.org
> mailing list archive)
Hello, I just encounter the same problem, with the same Appache POI version (3.10). My formulae look like "DEC2HEX(HEX2DEC(O8)-O2+D2)" with O8 = "0005B0A3", O2 = "372736" and D2 = "376832". I've tryed with the simplified (and meaningless for my application) formulae of the kind "DEC2HEX(O2+D2)" and the evaluations are successful. Therefore it seems to be specific to HEX2DEC (or to a bad use of the function). Thanks to every people participating to this valuable project. Gaillac Fixed in r1650597, the Hex2Dec function implementation needs to take RefEvals into account to work in these cases. BTW, I found and fixed similar problems in Bin2Dec and some unrelated smaller issues in Dec2Bin, see r1650654 Thank you, it works perfectly now. |
Created attachment 32200 [details] test file Please see the attache file[toto.xlsx] and code: InputStream inp = new FileInputStream("E:/test_file/toto.xlsx"); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheet("Feuil1"); Row mod=sheet.getRow(1); mod.getCell(1).setCellValue(3); HSSFFormulaEvaluator.evaluateAllFormulaCells(wb); ileOutputStream fileOutput = new FileOutputStream("E:/test_file/toto.xlsx"); wb.write(fileOutput); fileOutput.close(); 1.toto.xlsx include "HEX2DEC" function 2.We change one cell value of the sheet. 3.call HSSFFormulaEvaluator.evaluateAllFormulaCells(wb) to Re-calculating the cell value When run the code we got the exception: java.lang.IllegalArgumentException: Unexpected eval class (org.apache.poi.ss.formula.LazyRefEval) at org.apache.poi.ss.formula.eval.OperandResolver.coerceValueToString(OperandResolver.java:275) at org.apache.poi.ss.formula.functions.Hex2Dec.evaluate(Hex2Dec.java:46) at org.apache.poi.ss.formula.functions.Hex2Dec.evaluate(Hex2Dec.java:58) at org.apache.poi.ss.formula.UserDefinedFunction.evaluate(UserDefinedFunction.java:64) at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:129) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:525) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:288) at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:230) at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCellValue(XSSFFormulaEvaluator.java:264) at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateFormulaCell(XSSFFormulaEvaluator.java:151) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:324) at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:315) at org.talend.test.TestPOI.main(TestPOI.java:49)