|Summary:||Formula Evaluator number range .|
|Product:||POI||Reporter:||tudor ionut <tudorionut13>|
|Component:||HSSF||Assignee:||POI Developers List <dev>|
Patch for enabling parsing number strings in exponential notation
Corrects deficiencies in previously submitted patch
Description tudor ionut 2006-01-26 09:10:45 UTC
I have a formula in an Excel file , something like that : =30.000.000 / 100 In my code i evaluate formula with HSSFFormulaEvaluator, but the result evaluated is 3, not 30.000 So, i tested over and over again and i observe that a number used in a formula must be smaller than 9.999.999 to have a corect evaluation. But i think that is a too small limit. Maybe you can fix that bug. Thank You!
Comment 1 Amol Deshmukh 2006-02-08 21:41:34 UTC
I have tried with the exact same values (=30,000,000/100) and Formula is evaluated correctly to 300,000. I have tried it with POI-3.0-alpha1-2005-07-04. Could you attach the xls and code-snip that demonstrates this bug?
Comment 4 tudor ionut 2006-02-09 16:40:14 UTC
Take a look. Thank you!
Comment 5 Amol Deshmukh 2006-02-11 20:17:07 UTC
The code I used to test had a bug :) So yes, this is a bug in the current implementation. However after some debugging it seems like excel stores large numbers (in formula strings) with the exponential notation. Thus 30000000/100 is stored as 3.0E7/100. Apparently, FormulaParser cannot handle this and it simply returns a Ptg array of size 1 containing the NumberPtg with numeric value "3.0" instead of Ptg array of size 3 containing NumberPtg, DividePtg and NumberPtg
Comment 6 tudor ionut 2006-02-12 11:07:10 UTC
See my comment on 30248 bug : " Yes, you cannot parse number greater than 10.000.000. I have a formula , something like : =10.000.000/10 and HSSFFormulaEvaluator evaluateCell method return 1 after the evaluation. So i try to see how looks the string from that cell with HSSFCell getCellFormula() method, and the result was : 1.0E7/100 . You cannot do nothing! :( " But, if u do a method that search the string for "E" character and replace it with a power of 10 and then evaluate corectly the formula i think that would be fine.
Comment 7 tudor ionut 2006-05-02 12:41:52 UTC
Thank you for your solution. You're doing a really good job!
Comment 8 Amol Deshmukh 2006-06-05 14:33:22 UTC
Created attachment 18403 [details] Patch for enabling parsing number strings in exponential notation Patch contains testcases and bug fix to the formula parser to allow parsing of numbers in exponential notation (required for very large numbers). Bug fix: FormulaParser.GetNum() updated to check for the presence of 'E' char in the number and append appropriate number of '0' chars to the token string before returning it.
Comment 9 Amol Deshmukh 2006-06-05 16:57:24 UTC
Created attachment 18405 [details] Corrects deficiencies in previously submitted patch Previous patch would fail for numbers in exp notation where the mantissa is not a whole number; and also for cases where the exponent was negative. Both these problems are fixed in the latest patch. Corresponding test cases have been added. Also fixes an issue with the cell.setCellFormula(..) not updating the underlying value correctly.