XLS Formula in Jasper Report using iReport

XLS Formula in Jasper Report using iReport

Jasper report is an reporting tool used in OpenERP for printing the reports in formats like pdf, Excel, HTML etc. Lets consider an example about the accounting reports. The report contain details regarding the credit amount, debit amount balance etc. It would be very useful if the report is printed in excel format. In excel sheet we will be able to specify the formula based on which each cell value is calculated. So if we are generating the excel sheet from jasper report we should be able to specify the formule in jasper report which should be used in the excel sheet.

I am using OpenERP 6.1, Jasper report 4.5.1, iReport 4.5.1 and JDBC Data source for developing the report. To use the formula in excel report, each cell in excel should understand what is the datatype of the data that it is holding. For this we have to set a property in ireport. Go to report properties and add an property as "net.sf.jasperreports.export.xls.detect.cell.type" to True. By setting this property, if we give float for an element in ireport, it will be considered as a float value in excel. 

Now to set the formula based on which the cell value is calculated, select the element in ireport for which the formula is to be applied  and go to peroperties, select property expression, click add. Set Property Name as "net.sf.jasperreports.export.xls.formula".  Since we are using an expression, check the "Use Expression" field. Now add the expression or formula like "Sum(D"+$V{first_row}+":D"+$V{last_row}+")" where $V{first_row} and $V{last_row} are the row numbers that is needed to be applied on the element. An sreenshot is shown below:

Excel Formula Tutorials :  https://www.guru99.com/excel-tutorials.html
http://spreadsheets.about.com/od/excelformulas/ss/formula_begin.htm