Create excel template with fixed formulas with Jasper Reports

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.

mhn_1's picture
Joined: Feb 18 2019 - 3:00am
Last seen: 3 months 4 weeks ago

1 Answer:

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




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.

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.


yama818's picture
Joined: Aug 17 2018 - 3:48pm
Last seen: 6 days 22 hours ago