Unable to export cell value as Excel Formula

Aspose.Cells Product Family
1

I am a developer and currently performing feasibility study on Jasper Report. Our requirement is to export editable charts and excel formula. I am able to export editable chart using ACXlsExporter. However, I am unable to export cell value as excel formula using the property:

{property name=“net.sf.jasperreports.export.xls.formula” value=“SUM(A2,A3)”}



The exporter always exports according to the value specified in {textFieldExpression}, or null if it is not set. If I use JRXlsxExporter from Jasper Report, all excel formula can be exported correctly.



I also tried to set the parameter before export:

exporter.setParameter(ACXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE) but still fail.



May I know if Aspose.cells support exporting excel formula? Or if I missed some configuration instead?



Thanks a lot in advance!



Thomas

2

Hi Thomas,

Thanks for your posting and using Aspose.Cells.JasperReports.

Please see the following documentation article and documentation in general to check if it is helpful for you in resolving your issue.

( Exporting .jrprint Files to XLS Formats|Documentation )
( Aspose.Cells for JasperReports|Documentation )

If your issue is unable to be resolved, then please share us your jasper report file which you want to export, your expected output xls file which you can create manually and some screenshots showing the problem in detail.

We will look into your issue and help you asap.

3

Hi Shakeel,



I have tested the suggested solution but it does not work. It seems to me that the excel formula was not processed in ACXlsExporter.



Attached please find my .jrxml (Formula.txt, which is exactly the file XlsFormulaReport come up with the JasperReport demo) and the code snapshot for generating the report.



*******************************

jasperPrint = JasperFillManager.fillReport(“F:\workspace\JasperReport\TestJasperReport\ReportTemplate\XlsFormulaReport.jasper”, hm, beanColDataSource);

File destFile = new File(“F:\workspace\JasperReport\TestJasperReport\Output\XlsFormulaReport.xls”);



ACXlsExporter exporter = new ACXlsExporter();



exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);

exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, destFile.toString());

exporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.TRUE);

exporter.setParameter(ACXlsExporterParameter.IS_USE_EXCEL_CHART, Boolean.FALSE);

exporter.exportReport();



********************



Thanks!



Thomas

4

Hi Thomas,

Thanks for your posting and considering Aspose.Cells.

This might be occurring due to bug or because of some unsupported feature. We will need to set up the environment to investigate this issue and log in our database for a fix.

Please share what DB you are using? Are you using JasperServer? Since you have provided the code, I assume you are not using JasperServer? Instead, you are running it through Eclipse by importing the JasperReport library and Aspose.Cells library. Please correct me if I got it wrong. How did you design the report? Did you use iReportDesigner to design it? In your code, you are using XlsFormulaReport.jasper? Is it the same file as Formula.txt (.jrxml)? Or did you compile Formula.txt to get .jasper file, then how?

5

Hi Shakeel,



The file XlsFormulaReport comes from the demo of Jasper report, and it did not use any

DB. It is just simply a demo showing the use of excel formula, setting 4 cell values as follow:



{property name=“net.sf.jasperreports.export.xls.formula” value=“7”/}



{property name=“net.sf.jasperreports.export.xls.formula” value=“4”/}



{property name=“net.sf.jasperreports.export.xls.formula” value=“SUM(A2,A3)”/}



Hence, the expected result in the excel should be “=7”, “=4”, “=SUM(A2,A3)” respectively. The result is correct if I use JRXlsExporter.

I did not use JasperServer nor any DB and yes you are correct that I run it in eclipse using JasperReport and Aspose.Cells library. The .jrxml comes from Jasper report demo and hence I did not use any IDE (iReportDesigner/Jaspersoft Studio) to develop the report. I use XlsFormulaReport.jrxml to compile the report into XlsFormulaReport.jasper. But the forum does NOT allow uploadinbg *.jrxml file, so I need to change it into a txt file before uploading, the file content of the txt file is exactly same as that in my jrxml.



If the demo run successfully, my next step would be to use a Java Bean class as datasource, since the data comes from multiple data source and hence I would prepare it in the form of Java Bean before printing the report. I the sample Java code segment that I attached in the last post, I also provide a data source in the call of JasperFillManager.fillReport(), but it has no use in the case of running the demo.



Thanks in advance!



Thomas

6

Hi Thomas,

Thanks for your valuable information and considering Aspose.Cells.

Please spare us some time around 2~3 days. We will work on your issue and update you asap.

7

Hi Thomas,

Thanks for considering Aspose.Cells.

We were able to replicate this issue by installing Aspose.Cells for JasperReports 1.1.3 on JasperServer 5.1 and found that Aspose.Cells does not export formulas nor it exports calculated values of the formulas. However, the built-in Excel export exports the JasperReport with formula.

I have attached the following things with this post for a reference.

1 - Customer.jrxml.txt (JasperReport file)
2 - Aspose.Cells - JasperServer.png
3 = Built-in Export.xls (Excel export by the built-in Excel engine of JasperServer)
4 - Aspose.Cells Export.xls (Excel export by Aspose.Cells)
5 - Issue description.png (highlights this issue)

We have logged this issue in our database for investigation. We will look into it and fix this issue. In case, it is a new feature, we will investigate and implement it if possible. Once, there is some fix or other update for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-41448 - JasperReports- Aspose.Cells does not export formulas nor it exports calculated values of the formulas

8

Hi Thomas,

Thanks for using Aspose.Cells.JasperReports.

We are afraid, we do not have any plan to fix this issue. We are sorry for inconvenience.

9

The issues you have found earlier (filed as CELLSJAVA-41448) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.