Excel formula not getting set

0

I am exporting a report using JRXlsExporter and the following parameters

xlsExporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, fileName);
xlsExporter.setParameter(JRXlsExporterParameter.OUTPUT_FILE_NAME, realReportPath);
xlsExporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE);

xlsExporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
                  xlsExporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS, Boolean.TRUE);
xlsExporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);

The report itself uses the property...

<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>

And the field in the Detail band that is used to hold the formula is defined as ...

<textField pattern="$#,##0.00;($#,##0.00)" isBlankWhenNull="true" hyperlinkType="None">
      <reportElement key="textField-6" x="614" y="1" width="102" height="15">
                <property name="net.sf.jasperreports.export.xls.formula" value="ROUND(K9*N9,2)"/>
         </reportElement>
         <textFieldExpression class="java.lang.Double"></textFieldExpression>

</textField>

The report exports fine with all the other columns set with the proper data. However, the formula column is empty. Any ideas? Or has anyone seen a working example that uses a formula in the Detail band?
 

Thanks

mrobinson's picture
Joined: Oct 3 2006 - 8:26am
Last seen: 1 year 2 months ago

29 Answers:

0

Maybe the Excel viewer you use could be the problem. If you visualize the generated Excel file with Open Office's viewer, then the comma used in the formula should be replaced by  a semicolon. Try this:

value="ROUND(K9*N9;2)"

If the problem persists, please attach a self consistent sample in order to help us to reproduce the problem.

hth,

sanda

shertage's picture
1917
Joined: Sep 26 2006 - 8:06pm
Last seen: 2 months 3 weeks ago
0

Sandra,

Thanks for your help. That was indeed the problem.

Can you point me to some reference as to how to create dynamic formulas? I saw your note on one of the earlier forum posts about using <propertyExpression /> instead of <property .../> but I cannot find any detailed info about this usage. Or could you provide a simple example?

Thanks,

Mike

mrobinson's picture
Joined: Oct 3 2006 - 8:26am
Last seen: 1 year 2 months ago
0

Hi,

Due to the new site's attachment incertitude, I'll write down here the code of a jrxml sample, containing both a static and a dinamic formula usage examples. Of course, I will attach the file itself, but I'm not sure if this operation will succeed. Wish me luck :)

The code is:

<?xml version="1.0" encoding="UTF-8"  ?>
<!-- Created with iReport - A designer for JasperReports -->
<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
<jasperReport
         name="formulaSample"
         columnCount="1"
         printOrder="Vertical"
         orientation="Portrait"
         pageWidth="595"
         pageHeight="842"
         columnWidth="535"
         columnSpacing="0"
         leftMargin="0"
         rightMargin="0"
         topMargin="0"
         bottomMargin="0"
         whenNoDataType="NoPages"
         isTitleNewPage="false"
         isSummaryNewPage="false">
    <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true" />
    <property name="ireport.scriptlethandling" value="0" />
    <property name="ireport.encoding" value="UTF-8" />
    <import value="java.util.*" />
    <import value="net.sf.jasperreports.engine.*" />
    <import value="net.sf.jasperreports.engine.data.*" />


        <background>
            <band height="0"  isSplitAllowed="true" >
            </band>
        </background>
        <title>
            <band height="93"  isSplitAllowed="true" >
                <textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
                    <reportElement
                        x="0"
                        y="0"
                        width="68"
                        height="23"
                        key="textField-1"/>
                    <box></box>
                    <textElement>
                        <font/>
                    </textElement>
                <textFieldExpression   class="java.lang.Integer"><![CDATA[new Integer(7)]]></textFieldExpression>
                </textField>
                <textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
                    <reportElement
                        x="0"
                        y="23"
                        width="68"
                        height="23"
                        key="textField-2"/>
                    <box></box>
                    <textElement>
                        <font/>
                    </textElement>
                <textFieldExpression   class="java.lang.Integer"><![CDATA[new Integer(4)]]></textFieldExpression>
                </textField>
                <textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
                    <reportElement
                        x="0"
                        y="46"
                        width="68"
                        height="23"
                        key="textField-3">
                            <property name="net.sf.jasperreports.export.xls.formula" value="SUM(A1;A2)" />
                        </reportElement>
                    <box></box>
                    <textElement>
                        <font/>
                    </textElement>
                <textFieldExpression   class="java.lang.Integer"><![CDATA[]]></textFieldExpression>
                </textField>
                <textField isStretchWithOverflow="false" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None"  hyperlinkTarget="Self" >
                    <reportElement
                        x="0"
                        y="69"
                        width="68"
                        height="23"
                        key="textField-4">
                            <propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["A" + new Integer(1).intValue() + "-A" + 2]]></propertyExpression>
                        </reportElement>
                    <box></box>
                    <textElement>
                        <font/>
                    </textElement>
                <textFieldExpression   class="java.lang.Integer"><![CDATA[]]></textFieldExpression>
                </textField>
            </band>
        </title>
</jasperReport>

shertage's picture
1917
Joined: Sep 26 2006 - 8:06pm
Last seen: 2 months 3 weeks ago
0

Formula Cell doesn't works for me ...I'm using iReport 2.0.2 ,does this version support formula cell or i shud implement the same suign higher version? (3.0.0)

honeyani's picture
Joined: Sep 10 2008 - 3:07am
Last seen: 10 years 9 months ago
0

Hi honeyani,

The formula support was implemented in JasperReports 3.0.0 indeed. You'll need at least this version in order to apply formula on a cell.

At this moment formula cannot be generated using iReport. It has to be written "by hand" in the jrxml source file. This inconvenient will be solved soon. Until then, you can find some useful additional information here: jasperforge.org/plugins/espforum/view.php

Regards,

sanda

shertage's picture
1917
Joined: Sep 26 2006 - 8:06pm
Last seen: 2 months 3 weeks ago
0

Thanks a lot shertage.

I investigated this already and implemented formula using iReport 3.0.0 .

I'm able to set formula using <property> tag in jrxml.This works in a static manner.Is it possible to set formula dynamically.

Example In excel sheet record will be generated dynamically ,Scenario is

For an account number multiple records(count will vary for each account number and its dynamic) ,need to display Account total for each account number .I have a label account total at the end of each account number details and i have a column that holds the balance value  .Whenever 'Account Total' label is encountered for an account number have to display sum for that account .

Please find attachment template.xls to find the template of our output ,dynamically account total value should be set .number of record for each account will vary.Can you please guide on how to set formula for these kind of issues in jrxml file?

Waiting for your reply..

 

honeyani's picture
Joined: Sep 10 2008 - 3:07am
Last seen: 10 years 9 months ago
0
By the way ,I have 1 more doubt how to make use of tag? I'm new to Jasper and i feel v.hard to get any tutorial or online material to do a study.Please suggest me some online tutorial link that will help me to understand more about setting formula's to a cell dynamically .. Thanks in advance.
honeyani's picture
Joined: Sep 10 2008 - 3:07am
Last seen: 10 years 9 months ago
0

honeyani
Wrote:
 

By the way ,I have 1 more doubt how to make use of tag? I'm new to Jasper and i feel v.hard to get any tutorial or online material to do a study.Please suggest me some online tutorial link that will help me to understand more about setting formula's to a cell dynamically .. Thanks in advance.

I meant propertyExpression tag ..here..

honeyani's picture
Joined: Sep 10 2008 - 3:07am
Last seen: 10 years 9 months ago
0

I see the real problem you have, and it's not an ordinary one. The main goal is to determine where exactly in the sheet is the starting cell for the current account total. It depends on each report, and there are some report variables which could help you in this case, such as REPORT_COUNT and GROUP_COUNT.

For a more detailed discussion, it would be very useful if you'd post your jrxml sample here, in order to see the actual report structure.

Regards,

sanda

shertage's picture
1917
Joined: Sep 26 2006 - 8:06pm
Last seen: 2 months 3 weeks ago
0

Sanda,

 

I have posted the jrxml in my previous reply(it has to be considered confidential).How to delete it or hide it from outside users ?any option available for that in Jasper forum?

honeyani's picture
Joined: Sep 10 2008 - 3:07am
Last seen: 10 years 9 months ago
0

honeyani
Wrote:

Sanda,

 

I have posted the jrxml in my previous reply(it has to be considered confidential).How to delete it or hide it from outside users ?any option available for that in Jasper forum?

I've deleted your previous post.  There is no option to hide a post from certain JasperForge users.

When someone asks you for a sample report/data/anything, you don't have to provide the exact data which you are working on, but you should create a simple example that illustrates the problem.  If you still need to send some confidential data, discuss with the person that has asked you for that data how you can send it privately.  The JasperForge forums are public and should not be used to post confindentail information.

Regards,

Lucian

lucianc's picture
7055
Joined: Jul 17 2006 - 1:10am
Last seen: 2 weeks 6 days ago
0

Hi Ani,

As you already saw, the uncomfortable attachment's problem was solved. Sorry for this kind of trouble you had.

Now, I'll try to lookup for an appropriate formula to use in the jrxml you sent.

 

See you again,

sanda

shertage's picture
1917
Joined: Sep 26 2006 - 8:06pm
Last seen: 2 months 3 weeks ago
0

Ani,

I'm afraid that here we have a case where the classic total calculation mode still remains the best choice. A formula based on report variables in this particular case requires  a sum of changes in the actual expression evaluation mechanism.

 

I'll keep you informed about this.

sanda

 

shertage's picture
1917
Joined: Sep 26 2006 - 8:06pm
Last seen: 2 months 3 weeks ago
0

Sure .I will take care of this .Sorry for the inconvenience caused .

My suggestion ,Eventhoug it a public forum,why can't we have delete option for the attachments posted on the forum?If end user attaches a wrong file by mistake no option to delete the same is available.

Its a request..

honeyani's picture
Joined: Sep 10 2008 - 3:07am
Last seen: 10 years 9 months ago
0

Hi sanda,

Thansk a lot for deleting the file from the forum.you can reach me through this email id too - anitha.mca@gmail.com so that we can have a detailed discussion on the issue.

honeyani's picture
Joined: Sep 10 2008 - 3:07am
Last seen: 10 years 9 months ago
0

honeyani
Wrote:

My suggestion ,Eventhoug it a public forum,why can't we have delete option for the attachments posted on the forum?If end user attaches a wrong file by mistake no option to delete the same is available.

Its a request..

Please post this on the JasperForge.org feedback forum.

Regards,

Lucian

lucianc's picture
7055
Joined: Jul 17 2006 - 1:10am
Last seen: 2 weeks 6 days ago
0

lucianc
Wrote:
 

honeyani
Wrote:

Sanda,

 

I have posted the jrxml in my previous reply(it has to be considered confidential).How to delete it or hide it from outside users ?any option available for that in Jasper forum?

I've deleted your previous post.  There is no option to hide a post from certain JasperForge users.

When someone asks you for a sample report/data/anything, you don't have to provide the exact data which you are working on, but you should create a simple example that illustrates the problem.  If you still need to send some confidential data, discuss with the person that has asked you for that data how you can send it privately.  The JasperForge forums are public and should not be used to post confindentail information.

Regards,

Lucian

Thanks a lot Lucian!!

honeyani's picture
Joined: Sep 10 2008 - 3:07am
Last seen: 10 years 9 months ago
0

lucianc
Wrote:
 

honeyani
Wrote:

My suggestion ,Eventhoug it a public forum,why can't we have delete option for the attachments posted on the forum?If end user attaches a wrong file by mistake no option to delete the same is available.

Its a request..

Please post this on the JasperForge.org feedback forum.

Regards,

Lucian

Yes ..I have posted it Lucian..Thanks !

honeyani's picture
Joined: Sep 10 2008 - 3:07am
Last seen: 10 years 9 months ago
0

shertage
Wrote:
 

Ani,

I'm afraid that here we have a case where the classic total calculation mode still remains the best choice. A formula based on report variables in this particular case requires  a sum of changes in the actual expression evaluation mechanism.

 

I'll keep you informed about this.

sanda

 

Sanda,

Please provide me a detailed explanation on this . Eagerly waiting for your reply.

Thanks,

Ani

 

honeyani's picture
Joined: Sep 10 2008 - 3:07am
Last seen: 10 years 9 months ago
0

JasperReports does already have an embedded mechanism to calculate global quantities such totals, averages, etc. Because in the case of your document giving the exact position of the starting cell is almost impossible at the moment, using a formula is rather unappropriate.

Using the embedded calculation mechanism seems to be a better solution. In order to do so, you could download the First Jasper report sample from here:

jasperforge.org/espdocs/docsbrowse.php

There you'll find the jrxml file and some generated reports as samples. Please take a look on all variables used there to determine total quantities per group, per page, or per report.

 

HTH,

sanda



Post Edited by Lucian Chiriţă at 09/19/08 11:44
shertage's picture
1917
Joined: Sep 26 2006 - 8:06pm
Last seen: 2 months 3 weeks ago
0

shertage
Wrote:
 

JasperReports does already have an embedded mechanism to calculate global quantities such totals, averages, etc. Because in the case of your document giving the exact position of the starting cell is almost impossible at the moment, using a formula is rather unappropriate.

Using the embedded calculation mechanism seems to be a better solution. In order to do so, you could download the First Jasper report sample from here:

jasperforge.org/espdocs/docsbrowse.php

There you'll find the jrxml file and some generated reports as samples. Please take a look on all variables used there to determine total quantities per group, per page, or per report.

 

HTH,

sanda



Post Edited by Lucian Chiriţă at 09/19/08 11:44

Sanda,

 

Thanks a million for your suggestions..I'll investigate on this today and get back you !

honeyani's picture
Joined: Sep 10 2008 - 3:07am
Last seen: 10 years 9 months ago
0

Sanda ,

We have implemented the same 'embedded calculation mechanism' which u have mentioned with 1 element of our report.i.e Grand Total. Grand Total to display sum of all Total field values ,it works with iReport tool .When we deploy the jrxml in jasper report after generating the excel file ,if we update any of the total field value grand total doesn't gets updated for the updated value(user-input values on Excel sheet). Will this mechanism ,support updation based on user input value on the Excel after generation of excel file using Jasper Report?

If we set formula to a cell by updating jrxml ,when we deploy on jasper report generated excel file when user updates cell value,formula cell gets updated .will the same updation will happen if we go for embedded calculation mechanism.We tried with grand total it doesn't works?please provide us your input on whether our requirement can be met using iReport or its not supported with iReport?

waiting for your confirmation.

Thanks,

Anitha

 

 

 

honeyani's picture
Joined: Sep 10 2008 - 3:07am
Last seen: 10 years 9 months ago
0

honeyani
Wrote:
 

Sanda ,

We have implemented the same 'embedded calculation mechanism' which u have mentioned with 1 element of our report.i.e Grand Total. Grand Total to display sum of all Total field values ,it works with iReport tool .When we deploy the jrxml in jasper report after generating the excel file ,if we update any of the total field value grand total doesn't gets updated for the updated value(user-input values on Excel sheet). Will this mechanism ,support updation based on user input value on the Excel after generation of excel file using Jasper Report?

If we set formula to a cell by updating jrxml ,when we deploy on jasper report generated excel file when user updates cell value,formula cell gets updated .will the same updation will happen if we go for embedded calculation mechanism.We tried with grand total it doesn't works?please provide us your input on whether our requirement can be met using iReport or its not supported with iReport?

waiting for your confirmation.

Thanks,

Anitha

 

 

 

Our major requirement is ,Jasper report generated Excel sheet content - total or grand total  should get updated for user input value.

We are able to set total or grand total using the embedded calculation mechanism you have mentioned .which sounds to be again static..

honeyani's picture
Joined: Sep 10 2008 - 3:07am
Last seen: 10 years 9 months ago
0

Sanda,

Through iReport is it possible to set header for the excel sheet properties,Ican set header for a page and header for each page through iReport.

My query : Throguh iReport i should be able to set single header for the generated excel file(this is done),but when i give for print header for each page should be set ,i mean to say header for each page will happen if we set it in Excel sheet page set up properties .is it possible to set anything like that ( Excel sheet page set  up properties)through iReport tool?

 

honeyani's picture
Joined: Sep 10 2008 - 3:07am
Last seen: 10 years 9 months ago
0

Hi Anitha,

In this case, applying a formula is not possible, because JasperReports does not localize individual cells in a generated sheet. Localizing cells at export time in order to set a formula field requires significant changes, and isn't an ordinary problem. If you think it's necessary, you can post a feature request here.

With the other solution, I mean with actually calculating totals using report variables, the calculated field does not act as a formula field, and its content will not be sensitive to the user input.

 

Regards,

sanda

shertage's picture
1917
Joined: Sep 26 2006 - 8:06pm
Last seen: 2 months 3 weeks ago
0

shertage
Wrote:
 

Hi Anitha,

In this case, applying a formula is not possible, because JasperReports does not localize individual cells in a generated sheet. Localizing cells at export time in order to set a formula field requires significant changes, and isn't an ordinary problem. If you think it's necessary, you can post a feature request here.

With the other solution, I mean with actually calculating totals using report variables, the calculated field does not act as a formula field, and its content will not be sensitive to the user input.

 

Regards,

sanda

Sanda,

 

Thanks a lot for all your replies! I think its time for us to close this discussion thread :) :) ! I have raised this as new feature request in provided link.Hope i have mentioned it in a right manner.Have look and confirm on the same!

Hope Jasper Team will fix this new Requirement at the earliest! We are waiting to make use of the tool with more and more advanced features support like this...

Hats off to the team on development and support provided by the team to end users...Its gr88 to find developers online for discussion...!

 

honeyani's picture
Joined: Sep 10 2008 - 3:07am
Last seen: 10 years 9 months ago
0

Hi ,

       Can u provide me the detailed explaination about the propertyExperssion tag what u have mentioned i have to use for one of my requirement if you provide this it ll be greatfull.

Satish

sateesh139's picture
Joined: Aug 28 2008 - 1:44am
Last seen: 10 years 9 months ago
0

i didnt get  value in upto 4 decimal place when using excel formula.I am getting only decimal place upto 2

wat to do

please help me

liyak9's picture
4
Joined: Apr 18 2011 - 10:50pm
Last seen: 8 years 2 months ago
0

please send me the jrxml file @ liyak9@gmail.com

liyak9's picture
4
Joined: Apr 18 2011 - 10:50pm
Last seen: 8 years 2 months ago
Feedback