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.

Attachments: 
mhn_1's picture
2
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'.

http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasper...

 

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.

 

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