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
29 Answers:
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
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
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>
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
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
Wrote:
| By the way ,I have 1 more doubt how to make use of
|
I meant propertyExpression tag ..here..
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
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
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
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..
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
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
Wrote:
|
honeyani
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!!
lucianc
Wrote:
|
honeyani
Please post this on the JasperForge.org feedback forum. Regards, Lucian |
Yes ..I have posted it Lucian..Thanks !
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
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
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 !
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
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..
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?
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
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...!
