If you use setCellFormula and getCellFormula to set/get formulas with large row numbers, the formula gets mangled. For example... String myFormula = "E35398&\" Total\""; cell.setCellFormula(myFormula); 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.
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 org.apache.poi.hssf.util.CellReference... if (row < 0) row = (Math.abs(row) - 1) ^ 0xFFFF; Could someone please add this (or something like it) to the codebase. Thanks.
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?
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.
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!
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.