Excel formula not getting set

[mrobinson]

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

[szaharia]

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

[mrobinson]

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

[szaharia]

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>

[honeyani]

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)

[szaharia]

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

[honeyani]

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]

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]

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..

[szaharia]

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

[honeyani]

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?

[lucianc]

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

[szaharia]

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

[szaharia]

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

 

[honeyani]

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]

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.

[lucianc]

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

[honeyani]

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]

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]

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

 

[szaharia]

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

[honeyani]

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]

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]

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]

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?