Bug 38396

Summary: Formula Evaluator number range .
Product: POI Reporter: tudor ionut <tudorionut13>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: critical    
Priority: P3    
Version: 3.0-dev   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Excel File
Java File
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 2 tudor ionut 2006-02-09 16:39:06 UTC
Created attachment 17628 [details]
Excel File
Comment 3 tudor ionut 2006-02-09 16:39:22 UTC
Created attachment 17629 [details]
Java File
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.