BRIEF DESCRIPTION: ------------------------------------------------- Entering a simple cell formula string like : "40000" gives NumberFormatException, Value out of range. Value:"40000" Radix:10 This is bug because Excel accepts the formulas in this format (ie. if entered as =40000) and evaluates it correctly. (Also if formula is entered directly via excel it is retained on closing and reopening the file) CODE DEMONSTRATING THE BUG: ------------------------------------------------- import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class TestPOI { public static void main(String[] args) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell((short) 0); cell.setCellFormula("40000"); } } ------------------------------------------------- The exception trace is: java.lang.NumberFormatException: Value out of range. Value:"40000" Radix:10 at java.lang.Short.parseShort(Unknown Source) at java.lang.Short.parseShort(Unknown Source) at org.apache.poi.hssf.record.formula.IntPtg.<init>(IntPtg.java:58) at org.apache.poi.hssf.model.FormulaParser.Factor(FormulaParser.java:486) at org.apache.poi.hssf.model.FormulaParser.Term(FormulaParser.java:548) at org.apache.poi.hssf.model.FormulaParser.Expression(FormulaParser.java:596) at org.apache.poi.hssf.model.FormulaParser.parse(FormulaParser.java:700) at org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:678) at TestPOI.main(TestPOI.java:11)
***** fix suggestion ****** Following the stack trace in the source code, the exception is at foll. place in code: // class: org.apache.poi.hssf.model.FormulaParser, line 486 tokens.add(new IntPtg(number)); //TODO:what if the number is too big to be a short? ..add factory to return Int or Number! Perhaps IntPtg is used instead of NumberPtg for efficiency of storage? But since it breaks the functionality for large values (since internal representation for IntPtg is short), could a temporary fix be made that uses NumberPtg instead of IntPtg till the TODO is actually resolved? I have tried that change on my local source and it works fine.
(In reply to comment #1) > I have tried that change on my local > source and it works fine. > Patch? Pretty please! :)
Created attachment 14064 [details] Changed use of IntPtg to NumberPtg in class org.apache.poi.hssf.model.FormulaParser Only one line of code is changed by the proposed patch. The original line also had a comment: TODO:what if the number is too big to be a short? ..add factory to return Int or Number! If this original TODO task was implemented, the bug would be fixed. This patch does NOT implement the TODO task! But it provides a temporary workaround by using NumberPtg instead of IntPtg which avoids the NumberFormat exception thrown when parsing strings into numbers larger than Short.MAX_VALUE. The original comment is preserved in the patched code, additional comment has ben added indicating that this is a temporary fix.
Ignore patch submitted here: New Patch submitted as attachment to issue #33375
*** Bug 33375 has been marked as a duplicate of this bug. ***
Fixed.