fixes issue 31683: Some values are not shown in XLS format using MS Office
authorCarlos Aristu <carlos.aristu@openbravo.com>
Mon, 14 Dec 2015 16:48:42 +0100
changeset 28137 e2a3526d0bf7
parent 28136 711164dd549b
child 28138 f698a5f953c8
fixes issue 31683: Some values are not shown in XLS format using MS Office

The detect cell type feature was not working properly for BigInteger values in Office after the Jasper Library Upgrade. A personalized format was applied causing the data not be shown when opening the report.

To solve this problem the detect cell type feature has been disabled. This way, by default, all the values in the XLS will be exported as strings.

This default configuration can be overriden at report level. As an example, the General Ledger Report and Trial Balance report have been updated properly. With this configuration, those reports are now using number formatting for the cells showing the BigDecimal values.
modules/org.openbravo.client.application/src/org/openbravo/client/application/report/ReportingUtils.java
src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerExcel.jrxml
src/org/openbravo/erpCommon/ad_reports/ReportTrialBalanceExcel.jrxml
--- a/modules/org.openbravo.client.application/src/org/openbravo/client/application/report/ReportingUtils.java	Wed Dec 02 10:38:04 2015 +0100
+++ b/modules/org.openbravo.client.application/src/org/openbravo/client/application/report/ReportingUtils.java	Mon Dec 14 16:48:42 2015 +0100
@@ -518,7 +518,6 @@
       SimpleXlsReportConfiguration reportExportConfiguration = new SimpleXlsReportConfiguration();
       reportExportConfiguration.setOnePagePerSheet(false);
       reportExportConfiguration.setRemoveEmptySpaceBetweenRows(true);
-      reportExportConfiguration.setDetectCellType(true);
       excelExporter.setConfiguration(reportExportConfiguration);
     }
     excelExporter.setExporterInput(exporterInput);
@@ -553,7 +552,6 @@
       SimpleXlsReportConfiguration reportExportConfiguration = new SimpleXlsReportConfiguration();
       reportExportConfiguration.setOnePagePerSheet(false);
       reportExportConfiguration.setRemoveEmptySpaceBetweenRows(true);
-      reportExportConfiguration.setDetectCellType(true);
       excelExporter.setConfiguration(reportExportConfiguration);
     }
     excelExporter.setExporterInput(exporterInput);
--- a/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerExcel.jrxml	Wed Dec 02 10:38:04 2015 +0100
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportGeneralLedgerExcel.jrxml	Mon Dec 14 16:48:42 2015 +0100
@@ -5,6 +5,7 @@
 	<property name="ireport.zoom" value="1.0"/>
 	<property name="ireport.x" value="0"/>
 	<property name="ireport.y" value="0"/>
+	<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
 	<import value="net.sf.jasperreports.engine.*"/>
 	<import value="java.util.*"/>
 	<import value="net.sf.jasperreports.engine.data.*"/>
@@ -350,7 +351,8 @@
 				<textElement textAlignment="Right">
 					<font size="8"/>
 				</textElement>
-				<textFieldExpression><![CDATA[($F{AMTACCTCR}!=null)?$P{NUMBERFORMAT}.format($F{AMTACCTCR}):new String("0")]]></textFieldExpression>
+				<textFieldExpression><![CDATA[$F{AMTACCTCR}]]></textFieldExpression>
+				<patternExpression><![CDATA[$P{NUMBERFORMAT}.toPattern()]]></patternExpression>
 			</textField>
 			<textField pattern="" isBlankWhenNull="true">
 				<reportElement key="textField-21" stretchType="RelativeToBandHeight" x="350" y="0" width="80" height="13" uuid="61ebce32-48ca-4823-911f-c009e581b4e1"/>
@@ -363,7 +365,8 @@
 				<textElement textAlignment="Right">
 					<font size="8"/>
 				</textElement>
-				<textFieldExpression><![CDATA[($F{AMTACCTDR}!=null)?$P{NUMBERFORMAT}.format($F{AMTACCTDR}):new String("0")]]></textFieldExpression>
+				<textFieldExpression><![CDATA[$F{AMTACCTDR}]]></textFieldExpression>
+				<patternExpression><![CDATA[$P{NUMBERFORMAT}.toPattern()]]></patternExpression>
 			</textField>
 			<textField pattern="dd/MM/yyyy" isBlankWhenNull="false">
 				<reportElement key="textField-22" stretchType="RelativeToBandHeight" x="280" y="0" width="70" height="13" uuid="5afdc4b7-edf6-4e06-960b-ca4914f50e5c"/>
--- a/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalanceExcel.jrxml	Wed Dec 02 10:38:04 2015 +0100
+++ b/src/org/openbravo/erpCommon/ad_reports/ReportTrialBalanceExcel.jrxml	Mon Dec 14 16:48:42 2015 +0100
@@ -5,6 +5,7 @@
 	<property name="ireport.zoom" value="1.0"/>
 	<property name="ireport.x" value="0"/>
 	<property name="ireport.y" value="0"/>
+	<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
 	<import value="net.sf.jasperreports.engine.*"/>
 	<import value="java.util.*"/>
 	<import value="net.sf.jasperreports.engine.data.*"/>
@@ -381,7 +382,8 @@
 				<textElement textAlignment="Right">
 					<font size="10"/>
 				</textElement>
-				<textFieldExpression><![CDATA[($F{SALDO_FINAL})!=null?$P{NUMBERFORMAT}.format($F{SALDO_FINAL}):new String(" ")]]></textFieldExpression>
+				<textFieldExpression><![CDATA[$F{SALDO_FINAL}]]></textFieldExpression>
+				<patternExpression><![CDATA[$P{NUMBERFORMAT}.toPattern()]]></patternExpression>
 			</textField>
 			<textField pattern="" isBlankWhenNull="false">
 				<reportElement key="textField-20" stretchType="RelativeToBandHeight" x="460" y="0" width="85" height="13" uuid="ece58c00-da3b-409b-b21e-ef1e34ac0039"/>
@@ -394,7 +396,8 @@
 				<textElement textAlignment="Right">
 					<font size="10"/>
 				</textElement>
-				<textFieldExpression><![CDATA[($F{AMTACCTCR})!=null?$P{NUMBERFORMAT}.format($F{AMTACCTCR}):new String(" ")]]></textFieldExpression>
+				<textFieldExpression><![CDATA[$F{AMTACCTCR}]]></textFieldExpression>
+				<patternExpression><![CDATA[$P{NUMBERFORMAT}.toPattern()]]></patternExpression>
 			</textField>
 			<textField pattern="" isBlankWhenNull="true">
 				<reportElement key="textField-21" stretchType="RelativeToBandHeight" x="375" y="0" width="85" height="13" uuid="66e1e5da-791a-421e-a4d7-bb3987eba601"/>
@@ -407,7 +410,8 @@
 				<textElement textAlignment="Right">
 					<font size="10"/>
 				</textElement>
-				<textFieldExpression><![CDATA[($F{AMTACCTDR})!=null?$P{NUMBERFORMAT}.format($F{AMTACCTDR}):new String(" ")]]></textFieldExpression>
+				<textFieldExpression><![CDATA[$F{AMTACCTDR}]]></textFieldExpression>
+				<patternExpression><![CDATA[$P{NUMBERFORMAT}.toPattern()]]></patternExpression>
 			</textField>
 			<textField pattern="" isBlankWhenNull="false">
 				<reportElement key="textField-22" stretchType="RelativeToBandHeight" x="279" y="0" width="96" height="13" uuid="9fead840-b567-4904-925a-e1264cef905a"/>
@@ -420,7 +424,8 @@
 				<textElement textAlignment="Right">
 					<font size="10"/>
 				</textElement>
-				<textFieldExpression><![CDATA[($F{SALDO_INICIAL})!=null?$P{NUMBERFORMAT}.format($F{SALDO_INICIAL}):new String(" ")]]></textFieldExpression>
+				<textFieldExpression><![CDATA[$F{SALDO_INICIAL}]]></textFieldExpression>
+				<patternExpression><![CDATA[$P{NUMBERFORMAT}.toPattern()]]></patternExpression>
 			</textField>
 			<textField isStretchWithOverflow="true" isBlankWhenNull="true">
 				<reportElement key="textField" stretchType="RelativeToBandHeight" x="641" y="0" width="189" height="13" uuid="9b147e4b-23d1-4eb4-9e35-ee119ae34967">