How to use Excel formulas?

[jim20100]

Hello,

My goal is to replace my fop and xsl application with jasperreports.

With my existing application, I generate the same report in different format:
- a pdf report
- an html file

The html file is containing excel formulas.
So My user can use Excel to add lines in all tables. The totals is automatically calculated.

I would like to get some samples of excel formulas with have been included in the 3.0.0.

I have tried with the crosstab example like this:


It does not work.

Please help me.

Thanks
Jim

[szaharia]

The formula expression expected by third party APIs used to export reports in Excel format should not contain the "=" sign.
Try to use

Code:

instead.

If you need to write a dynamic formula expression, you have to use the

Code:

instead.

hth,
sanda

[pipidog]

hi,shertage

I also tried formula expression,but failed.

could you plx show a sample of exporting excel formulas,thx:laugh:

[szaharia]

Hi,

The other common mistake which destroys any good and reliable result of a formula occurs when we are not explicitely instruct the exporter to detect the cell type.
By default, keeping in mind the importance of the text formatting, all cells are assumed to hold String objects within. When you need to apply a formula, Excel should know that you are talking about numbers there, and that's why you have to tell it about.

There are 2 possibilities to enable the cell type detection:
1. Use the general export property net.sf.jasperreports.export.xls.detect.cell.type and set it to "true" in your report

2. Use the JRXlsExporterParameter.IS_DETECT_CELL_TYPE export parameter, and set it to Boolean.TRUE in your code

You'll find in the attachment a report sample containing both and formula insertion, using the net.sf.jasperreports.export.xls.detect.cell.type general property.

Please, let me know if there are still problems.
sanda [file name=formulaSample.jrxml size=2195]

[pipidog]

Thx a million for ur help~~

[rajil]

I successfully implement static formula in jasper report.

formula sum(A1:A10)

now I want to do same thing dynamically, data fetch from database it may return 1 row it may return 100 rows.so how can I adjust sum(A1:A{x}) how can I replace at runtime ?

Please help me.

 

 

 

[tanguy_belot]

Hi,

i'd like to get this formula example , but i cannot find it ....

 

thanks in advance for your help.

 

 

 

 

[sateesh139]

Hi Sanda,

         I am new to iReport tool i have a requirement like we have XML file as a DataSource from that we need to export the report in the Excel format, Once the Excel has exported in the excel sheet use will edit the some cell for that the Grand Total should come automatically and the use will same the excel. How can i do this

Plz quide me on this that will be greatful

 

[szaharia]

Hi,

At the moment, one cannot set a formula field using iReport. You can accomplish this "by hand" in the generated jrxml file, as shown in the above attached sample. Just modify your field code by adding the net.sf.jasperreports.export.xls.formula property (or propertyExpression), and set its value with the appropriate formula.

Hope this helps,

sanda

[honeyani]

Sanda,

Can you please,provide some sample's and tutorials for self study on property expression tag ,just to learn and get exposed to dynamic way of setting formula to an excel cell..

Thanks in advance,

Ani

[honeyani]

Sanda,

Can you please,provide some sample's and tutorials for self study on property expression tag ,just to learn and get exposed to dynamic way of setting formula to an excel cell..

Thanks in advance,

Ani

[honeyani]

Thanks...