POI / Excel : applying formulas in a "relative" way

Question

Hi,

I'm using Apache's POI to manipulate Excel (.xls) files with Java.

I'm trying to create a new cell whom content is the result of a formula as if the user had copied/pasted the formula (what i call the "relative" way, as opposite to "absolute").

To make myself clearer, here is a simple example : Cell A1 contains "1",B1 contains "2", A2 contains "3", B2 contains "4". Cell A3 contains the following formula "=A1+B1". If I copy the formula to the A4 cell under excel, it becomes "=A2+B2" : excel is adapting the content of the formula dynamically.

Unfortunately I cannot get the same result programatically. The only solution I found is to tokenize the formula and do the dirty work myself, but I really doubt that this is supposed to be done that way. I was not able to find what I'm looking for in the guides or in the API.

Is there an easier way to solve this problem ? If it's the case, can you please point me in the right direction ?

Best regards,

Nils

Answer 1

I don't think there is. POI would have to parse the formula (taking into account A1 vs. $A$1 vs. $A1 etc.) and I don't believe it has that capacity. When I've done this in the past I've always had to manage this myself. Sorry - not the answer you hoped for!

Answer 2

I too think that there isn't an easy way to do this.

Even the HSSF and XSSD examples on the POI site e.g. TimesheetDemo do the formula construction manually. e.g. around line 110

String ref = (char)('A' + j) + "3:" + (char)('A' + j) + "12";
cell.setCellFormula("SUM(" + ref + ")");

Answer 3

you can try some third party excel librarys,most of them can handle the relative/absolute range formulas.

Answer 4

Reed POI manual first and lern to use FormulaEvaluator!