Create excel template with fixed formulas with Jasper Reports

[mhn_1]

I have a report with two columns and when exporting it to excel, in the cells (A1, A2, A3,..) I have a field obtained by database, in column two (B1,B2,B3,..) I need the formula '=A1*0,5'... and that Jasper does not perform the calculation and presents it as another number. So that I can change the value of A2 by hand and automatically update B2.

Also, the cell must change in the formula. I'm passing an image of what I need to get when exporting in excel.

Thanks in advance.

[yama818]

It is possible to embed Excel formulas by using the property 'net.sf.jasperreports.export.xls.formula'.

http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.export.xls.formula

 

Ex.)
net.sf.jasperreports.export.xls.formula="=A1*0.5"

 

However, we had to be creative to meet your requirements.
That is because it is difficult to make the referenced cell in the formula variable, like 'A1' or 'A2'.
So, I thought I could solve the problem by devising a way to write the formula.

Ex.)
net.sf.jasperreports.export.xls.formula="=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, -1)*0.5"

 

This is an image of the result displayed in Excel.