0008776: Improve the number format handling on jasper report based reports

Description

Current:
* The current approach to handling number formatting is through the NUMBERFORMAT parameter, that is a java.text.DecimalFormat class
* The textfield is of java.lang.String format and the paramter is used in the textfield expression: $P{NUMBERFORMAT}.format($F{somefield})
* This has the problem that when exporting to other formats like MS Excel, the numbers are 'strings' so you cannot make calculations with the cells

Proposed Solution

* Attached you'll find a patch with part of the proposed solution
* There should be a way to make the format map dynamically, so can modifiable
by the consultant

Tags

No tags attached.

Attached Files

HttpSecureAppServlet_2.40.diff [^] (1,205 bytes) 2009-04-27 12:20 [Show Content] [Hide Content]

diff --git a/src/org/openbravo/base/secureApp/HttpSecureAppServlet.java b/src/org/openbravo/base/secureApp/HttpSecureAppServlet.java
--- a/src/org/openbravo/base/secureApp/HttpSecureAppServlet.java
+++ b/src/org/openbravo/base/secureApp/HttpSecureAppServlet.java
@@ -768,10 +768,14 @@
 		  JasperExportManager.exportReportToPdfFile(jp, outputFile);
 	  } else if(reportType.equalsIgnoreCase("xls")) {	  
 		  JExcelApiExporter exporter = new JExcelApiExporter();
+		  HashMap<String, String> numberFormats = new HashMap<String, String>();
+		  numberFormats.put("#,##0.00", "#,##0.0000;[RED]-#,##0.0000");
 		  exportParameters.put(JRExporterParameter.JASPER_PRINT, jp);
 		  exportParameters.put(JRExporterParameter.OUTPUT_FILE_NAME, outputFile);
 		  exportParameters.put(JExcelApiExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE);
 		  exportParameters.put(JExcelApiExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
+		  exportParameters.put(JExcelApiExporterParameter.IS_DETECT_CELL_TYPE, true);
+		  exportParameters.put(JExcelApiExporterParameter.FORMAT_PATTERNS_MAP, numberFormats);
 		  exporter.setParameters(exportParameters);
 		  exporter.exportReport();
 	  } else {

Relationships [ Relation Graph ] [ Dependency Graph ]

related to

defect

0008513

closed

iperdomo

Number cells exported to excel are not recognised as numeric values

Notes
There are no notes attached to this issue.

Issue History
Date Modified	Username	Field	Change
2009-04-27 12:20	iperdomo	New Issue
2009-04-27 12:20	iperdomo	Assigned To	=> iperdomo
2009-04-27 12:20	iperdomo	File Added: HttpSecureAppServlet_2.40.diff
2009-04-27 12:20	iperdomo	OBNetwork customer	=> No
2009-04-27 12:20	iperdomo	Regression testing	=> No
2009-04-27 12:20	iperdomo	Status	new => acknowledged
2009-05-08 17:04	iperdomo	Relationship added	related to 0008513
2012-10-11 09:27	iperdomo	Status	acknowledged => scheduled
2012-10-11 09:27	iperdomo	fix_in_branch	=> pi
2012-10-11 09:27	iperdomo	Status	scheduled => closed
2012-10-11 09:27	iperdomo	Resolution	open => out of date