XLS Formula in Jasper Report using iReport

Prajul P T's picture

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 Tutorial : http://spreadsheets.about.com/od/excelformulas/ss/formula_begin.htm



Edgar's picture

Hi. Thank you for the

Hi. Thank you for the article. How can we set row numbers dynamically?

Anoj's picture

Hello sir, I am presently

Hello sir,

I am presently working with excel based reports in ireport,

you have aware of excel insert comments property in ms-excel ,which will provides information to particular cell,

so my question is is any option is available in jasper soft ireport or any other solution is there to get dynamic data in each excel cell ?

Prajul P T's picture

Hi,     I didn't get your

Hi,

    I didn't get your question exactly. But there are ways in ireport itself to get dynamic data in excel cell. Please give somemore details so that it would be helpful to me to solve your query correctly.

Anonymous's picture

hi sir... i want to store my

hi sir...
i want to store my jrxml and .jasper files in my running application rather than saving it in other folders.i came to know that we can create a package and store only .jasper files and we can run the application but i am getting file not found exception.
do we have to change it here also?

  1. <defaultValueExpression><![CDATA["C://JasperReports//"]]></defaultValueExpression>

please tell me the way to store these files in my application itself.initially i stored these in c://jasperreports//
what is the way and what are the changes to be made in jrxmls or the code?
please help it is important and urgent and i dont have any idea about it ..
thanks in advance...

Prajul P T's picture

Hi,     I didn't get your

Hi,

    I didn't get your requirement exactly. Are you trying to develop a module with jasper report?

Anonymous's picture

hi sir... i want to store my

hi sir...
i want to store my jrxml and .jasper files in my running application rather than saving it in other folders.i came to know that we can create a package and store only .jasper files and we can run the application but i am getting file not found exception.
do we have to change it here also?

please tell me the way to store these files in my application itself.initially i stored these in c://jasperreports//
what is the way and what are the changes to be made in jrxmls or the code?
please help it is important and urgent and i dont have any idea about it ..
thanks in advance...

wissemnadia's picture

could you send me links to

could you send me links to download Jasper report 4.5.1, iReport 4.5.1 please ?

Prajul P T's picture

Hi, You can download the

Hi,

You can download the iReport 4.5.1 from here . There is no need to download jasper_report4.5.1.jar file. You can find it inside iReport4.5.1 itself. 

  • Extract iReport-4.5.1.tar.gz
  • Go to iReport-4.5.1(Extracted folder) folder -> ireport -> modules -> ext
  • You can find jasperreports-4.5.1.jar file inside.

    If you are using OpenERP jasper_reports module, to update jasperreports-4.5.1.jar and other lib files to latest for iReport_4.5.1, 

  • Open a new terminal window
  • Go to location jasper_reports -> jave -> lib
  • type ./update-jasper-reports.sh <path to iReport 4.5.1 directory>

This will update all the libraries in jasper_reports module to the latest.

Hemang Thanki's picture

Can I use any other formula?

Can I use any other formula? e.g. =BDP(AB21,"PX_CLOSE")