Summary:  Formula Evaluator number range .  

Product:  POI  Reporter:  tudor ionut <tudorionut13> 
Component:  HSSF  Assignee:  POI Developers List <dev> 
Status:  RESOLVED FIXED  
Severity:  critical  
Priority:  P3  
Version:  3.0dev  
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
20060126 09:10:45 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 POI3.0alpha120050704. Could you attach the xls and codesnip that demonstrates this bug? Created attachment 17628 [details]
Excel File
Created attachment 17629 [details]
Java File
Take a look. Thank you! 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 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. Thank you for your solution. You're doing a really good job! 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.
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.
