Bug 43399 - getCellFormula() returns incorrect formula if row numbers are very large
Summary: getCellFormula() returns incorrect formula if row numbers are very large
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: Sun Solaris
: P1 major (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2007-09-14 09:55 UTC by Ross Mills
Modified: 2007-09-18 09:34 UTC (History)
0 users


Note You need to log in before you can comment on or make changes to this bug.
Description Ross Mills 2007-09-14 09:55:50 UTC
If you use setCellFormula and getCellFormula to set/get formulas with large 
row numbers, the formula gets mangled.  For example...

String myFormula = "E35398&\" Total\"";
myFormula = cell.getCellFormula();

The value of myFormula will become "E-30138&\" Total\""

I suspect that these functions are using short row values instead of int.
Comment 1 Ross Mills 2007-09-14 15:01:43 UTC
I downloaded the source code and investigated.  The problem is the result of 
taking an int value, writing to 2 bytes and then loading it back into an int.  
This turns numbers greater than 32767 negative.  I was able to resolve this 
problem by placing the following code at the end of the constructors of 

        if (row < 0)
            row = (Math.abs(row) - 1) ^ 0xFFFF;

Could someone please add this (or something like it) to the codebase.  Thanks.
Comment 2 Nick Burch 2007-09-17 10:00:11 UTC
If I understand correctly, your patch suggests fixing CellReference so it
handles negative numbers properly

I think we'd be better off tweaking the thing that created / loaded the
reference, so it correctly ends up with a positive number. Any chance you could
identify where the short/int problem actually occurs, so we can patch that?
Comment 3 Ross Mills 2007-09-17 10:37:40 UTC
You're right.

What really needs to be done is to modify 
org.apache.poi.hssf.record.formula.ReferencePtg.  There is a private class 
property field_1_row that is defined as a short.  That property needs to be 
changed to an int.  Then all the methods in the class that read/write to this 
property need to be fixed.
Comment 4 Nick Burch 2007-09-18 08:03:33 UTC
It turns out that we can't simply tweak ReferencePtg to use an integer for the
row, as that breaks everything else (which assumes a short)

I've added a setRow(int) method, which takes a 0<=row<63566 argument, and
handles wrapping. I've also added a a getRowAsInt, which again does the
wrapping. With those in, the toForumulaString method can then call getRowAsInt,
instead of getRow, and everything's happy.

Testcase committed too, so we can be sure it'll stay fixed in future!
Comment 5 Ross Mills 2007-09-18 09:34:58 UTC
Thanks for all your help

This API has really had a big impact on our business.  The ability to 
automatically generate nicely formatted excel documents has saved us countless 
hours of work.