Bug 33160

Summary: cell formula "40000" throws NumberFormatException: Value out of range
Product: POI Reporter: Amol Deshmukh <amolweb>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: critical    
Priority: P1    
Version: 2.5-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Changed use of IntPtg to NumberPtg in class org.apache.poi.hssf.model.FormulaParser

Description Amol Deshmukh 2005-01-18 22:15:52 UTC
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)
Comment 1 Amol Deshmukh 2005-01-21 15:51:16 UTC
***** 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.

Comment 2 Avik Sengupta 2005-01-21 16:05:19 UTC
(In reply to comment #1)

> I have tried that change on my local
> source and it works fine.
> 
Patch? Pretty please! :)

Comment 3 Amol Deshmukh 2005-01-21 20:22:01 UTC
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.
Comment 4 Amol Deshmukh 2005-02-02 23:10:42 UTC
Ignore patch submitted here: New Patch submitted as attachment to issue #33375
Comment 5 Avik Sengupta 2005-04-21 15:02:35 UTC
*** Bug 33375 has been marked as a duplicate of this bug. ***
Comment 6 Avik Sengupta 2005-04-21 15:02:51 UTC
Fixed.