India

ZestyBeanz Technologies Pvt Ltd 4th Floor, Nila, Technopark Thiruvananthapuram,
India – 695581
Phone: +91 471 4063254
Fax : +91 471 2700171

   .

ZestyBeanz Technologies Pvt Ltd
61/3236, Manikkath Cross Road
Ravipuram, Kochi, India - 682016
Phone: +91 484 4063254

  UAE

Zesty Labs
Office # 2003, Millennium Plaza Building
Sheikh Zayed Rd, Dubai, UAE
Phone: +971 4333 2222​
Mobile: +971-52-7553466

  Germany​

ZestyBeanz Technologies GmbH
Reuterstraße 1
90408 Nürnberg
Fon: +49 911 4801 444
Fax: +49 911 4801 445

Home » Blogs » Prajul P T's blog

XLS Formula in Jasper Report using iReport

Contact Form


Prajul P T's picture

XLS Formula in Jasper Report using iReport

May 22, 2012 By Prajul P T

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



Anoj's picture

Hello sir, I am presently

Submitted by Anoj (not verified) on Thu, 03/28/2013 - 10:19.

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

Submitted by Prajul P T on Fri, 04/26/2013 - 14:01.

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

Submitted by Anonymous (not verified) on Wed, 08/22/2012 - 06:14.

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

Submitted by Prajul P T on Wed, 09/19/2012 - 13:00.

Hi,

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

Farhan malhotra's picture

Purchase Final Fantasy Swords

Submitted by Farhan malhotra (not verified) on Thu, 08/29/2019 - 07:49.

Purchase Final Fantasy Swords and Gun-edges worked from the best-tempered steel from  https://www.kniveskingdom.com – the acknowledged online swords and sharp edges store for protected and exuberant.

Anonymous's picture

hi sir... i want to store my

Submitted by Anonymous (not verified) on Wed, 08/22/2012 - 06:12.

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

Submitted by wissemnadia (not verified) on Tue, 05/29/2012 - 19:29.

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

Zedd's picture

Urges EVERYONE to peruse this

Submitted by Zedd (not verified) on Mon, 08/26/2019 - 13:16.

Urges EVERYONE to peruse this data and be educated about what goes on in the body when taking CBD and why it makes a difference. Our CBD products are THC free besides our CBD flower which are less than .3% delta 9 THC and legal. buylegalmeds.com

Prajul P T's picture

Hi, You can download the

Submitted by Prajul P T on Thu, 05/31/2012 - 04:54.

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?

Submitted by Hemang Thanki (not verified) on Mon, 08/20/2012 - 16:02.

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