Bug 57196 - When Re-calculating all formulas in a Workbook which include "HEX2DEC" function get unexpected exception
Summary: When Re-calculating all formulas in a Workbook which include "HEX2DEC" functi...
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.10-FINAL
Hardware: PC All
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2014-11-10 09:38 UTC by jzhao
Modified: 2016-05-31 17:38 UTC (History)
1 user (show)

test file (9.14 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2014-11-10 09:38 UTC, jzhao

Note You need to log in before you can comment on or make changes to this bug.
Description jzhao 2014-11-10 09:38:58 UTC
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);
ileOutputStream fileOutput = new FileOutputStream("E:/test_file/toto.xlsx");

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)
Comment 1 Nick Burch 2014-11-10 09:50:04 UTC
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)
Comment 2 jzhao 2014-11-10 09:57:40 UTC
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)
Comment 3 Gaillac 2014-11-20 14:57:43 UTC

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.

Comment 4 Dominik Stadler 2015-01-09 16:52:17 UTC
Fixed in r1650597, the Hex2Dec function implementation needs to take RefEvals into account to work in these cases.
Comment 5 Dominik Stadler 2015-01-09 20:37:31 UTC
BTW, I found and fixed similar problems in Bin2Dec and some unrelated smaller issues in Dec2Bin, see r1650654
Comment 6 Gaillac 2016-05-31 17:38:19 UTC
Thank you, it works perfectly now.