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



Comments

alex0: Very glad to give, your post is astoundingly disarranging to keep. I never stop myself to state something concerning it. You're working unequivocally. Keep it up. forest hills queens pain doctor This site is related to musclebonewellness.".

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. wissemnadia says:
    May 30, 2012 at 12:59 am

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

    Reply
    1. prajul07 says:
      May 31, 2012 at 10:24 am

      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.

      Reply
      1. Hemang Thanki says:
        August 20, 2012 at 9:32 pm

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

        Reply
  2. Anonymous says:
    August 22, 2012 at 11:42 am

    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…

    Reply
  3. Anonymous says:
    August 22, 2012 at 11:44 am

    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…

    Reply
    1. Prajul P T says:
      September 19, 2012 at 6:30 pm

      Hi,

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

      Reply
  4. Anoj says:
    March 28, 2013 at 3:49 pm

    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 ?

    Reply
    1. Prajul P T says:
      April 26, 2013 at 7:31 pm

      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.

      Reply
  5. Edgar says:
    August 25, 2013 at 6:21 pm

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

    Reply
  6. alex0 says:
    June 27, 2020 at 11:28 am

    Astonishing karma for your next blog.
    I respect you for such a charging article and sharing. God vitalizes you.!
    This site is about iPhone mobile repair. Every iPhone is repaired here. If there is a fault in your iPhone, visit this site and have your phone repaired

    Reply
  7. alex0 says:
    June 29, 2020 at 2:58 pm

    Very glad to give, your post is astoundingly disarranging to keep. I never stop myself to state something concerning it. You’re working unequivocally. Keep it up. forest hills queens pain doctor This site is related to musclebonewellness.

    Reply
© 2020 Zesty Beanz Pvt Ltd All Rights Reserved.