I'm new with Jasper Reports. I'm using 5.6.0 version and I can export some data to PDF and all fields are filled correct but when I try to export the same data to Excel, some fields are missing. The code I use it's the some on both clases (I have one class for each file export)
I also read that the template to export to PDF or Excel it's the same, so I don't know why fills all in PDF and not in Excel.
When I generate the PDF I pass all the data to:
JasperExportManager.exportReportToPdfStream(jasperPrint,fileOutputStream);
And when I try to generate the Excel I call this funcion:
private void generateExcel(JasperPrint jasperPrint) throws IOException, JRException {
JRXlsExporter exporterXLS = new JRXlsExporter();
JFileChooser jFileChooser = new JFileChooser(".");
jFileChooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
int status = jFileChooser.showOpenDialog(SIXACOPWorkbench.getWorkbenchContext().getWorkbench().getFrame());
if (status == JFileChooser.APPROVE_OPTION) {
if (jFileChooser.getSelectedFile() != null) {
String pathPDFs = (jFileChooser.getSelectedFile().getAbsolutePath());
File ficheroXLS = new File(pathPDFs+"//testing.xls");
FileOutputStream fos = new FileOutputStream(ficheroXLS);
exporterXLS.setExporterInput(new SimpleExporterInput(jasperPrint));
exporterXLS.setExporterOutput(new SimpleOutputStreamExporterOutput(fos));
SimpleXlsReportConfiguration configuration = new SimpleXlsReportConfiguration();
configuration.setRemoveEmptySpaceBetweenRows(true);
configuration.setDetectCellType(true);
configuration.setWhitePageBackground(false);
exporterXLS.setConfiguration(configuration);
exporterXLS.exportReport();
fos.close();
}
}
}
¿What I'm doing wrong when exporting to Excel?
Regards.
--- Update ---
I thoguht that if the PDF was generating well the Excel should do it but after read Dave Jarvis comment I'm updating with the jrxml:
1 - There are 3 informs: Inform A - Inform B - Inform C. 2 - Inform A has a header and footer and each row it's filled by Inform B. 3 - On Inform B the field that it's nos displayed on Excel it's numberIndex:
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="SubinformeAtribucionesPorPropietario1" pageWidth="813" pageHeight="842" columnWidth="813" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="5af05b05-4f4d-4ff8-8bcb-ef8fa6c2b067">
<property name="ireport.scriptlethandling" value="0"/>
<property name="ireport.encoding" value="UTF-8"/>
<property name="ireport.zoom" value="1.4641000000000008"/>
<property name="ireport.x" value="178"/>
<property name="ireport.y" value="0"/>
<import value="net.sf.jasperreports.engine.*"/>
<import value="java.util.*"/>
<import value="net.sf.jasperreports.engine.data.*"/>
<parameter name="Inform C" class="net.sf.jasperreports.engine.JasperReport" isForPrompting="false"/>
<parameter name="useNumberIndex" class="java.lang.Boolean" isForPrompting="false"/>
<field name="fieldA" class="java.util.List"/>
<field name="fieldB" class="java.lang.String"/>
<field name="fieldC" class="java.lang.String"/>
<field name="fieldD" class="java.lang.String"/>
<field name="fieldF" class="java.lang.String"/>
<field name="numberIndex" class="java.lang.String"/>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band splitType="Stretch"/>
</title>
<pageHeader>
<band splitType="Stretch"/>
</pageHeader>
<columnHeader>
<band splitType="Stretch"/>
</columnHeader>
<detail>
<band height="21" splitType="Stretch">
<textField pattern="" isBlankWhenNull="false">
<reportElement key="textField-1" mode="Transparent" x="1" y="1" width="53" height="19" forecolor="#000000" backcolor="#FFFFFF" uuid="974d00c4-7438-4d1f-92ff-6b720e113ecd"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="0.0" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
<font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<textFieldExpression><![CDATA[$P{useNumberIndex}.booleanValue() ? $F{numberIndex} : $F{fieldC}]]></textFieldExpression>
</textField>
<textField pattern="" isBlankWhenNull="false">
<reportElement key="textField-2" mode="Transparent" x="53" y="1" width="297" height="19" forecolor="#000000" backcolor="#FFFFFF" uuid="6dee9995-5734-4e42-b7e1-4c2cbe02739b"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="0.0" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
<font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<textFieldExpression><![CDATA[$F{fieldB}]]></textFieldExpression>
</textField>
<subreport isUsingCache="true">
<reportElement key="subreport-1" mode="Opaque" x="350" y="1" width="463" height="18" forecolor="#000000" backcolor="#FFFFFF" uuid="a40d7385-072d-4cfc-a952-88b23a906b85"/>
<subreportParameter name="name1">
<subreportParameterExpression><![CDATA[$F{fieldD}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="name2">
<subreportParameterExpression><![CDATA[$F{fieldE}]]></subreportParameterExpression>
</subreportParameter>
<subreportParameter name="Inform C">
<subreportParameterExpression><![CDATA[$P{Inform C}]]></subreportParameterExpression>
</subreportParameter>
<dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.data.JRBeanCollectionDataSource((Collection) $F{fieldA})]]></dataSourceExpression>
<subreportExpression><![CDATA[$P{Inform C}]]></subreportExpression>
</subreport>
</band>
</detail>
<columnFooter>
<band splitType="Stretch"/>
</columnFooter>
<pageFooter>
<band splitType="Stretch"/>
</pageFooter>
<summary>
<band splitType="Stretch"/>
</summary>
</jasperReport>
4 - On Inform B there is a section filled by Inform C and on Inform C, the field that isn't display it's extension:
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="SubinformeAtribucionesPorPropietarios" pageWidth="463" pageHeight="842" columnWidth="463" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="92989482-f12d-409d-bbbc-5168396c7b22">
<property name="ireport.scriptlethandling" value="0"/>
<property name="ireport.encoding" value="UTF-8"/>
<property name="ireport.zoom" value="1.9487171000000016"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<import value="net.sf.jasperreports.engine.*"/>
<import value="java.util.*"/>
<import value="net.sf.jasperreports.engine.data.*"/>
<parameter name="paramA" class="java.lang.String" isForPrompting="false"/>
<parameter name="paramB" class="java.lang.String" isForPrompting="false"/>
<field name="fieldA" class="java.lang.String"/>
<field name="fieldB" class="java.lang.String"/>
<field name="fieldC" class="java.lang.String"/>
<field name="fieldD" class="java.lang.String"/>
<field name="extension" class="java.lang.String"/>
<field name="fieldE" class="java.lang.String"/>
<field name="fieldF" class="java.lang.String"/>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band splitType="Stretch"/>
</title>
<pageHeader>
<band splitType="Stretch"/>
</pageHeader>
<columnHeader>
<band splitType="Stretch"/>
</columnHeader>
<detail>
<band height="17" splitType="Stretch">
<textField pattern="" isBlankWhenNull="false">
<reportElement key="textField-1" mode="Transparent" x="0" y="0" width="109" height="16" forecolor="#000000" backcolor="#FFFFFF" uuid="4e1802bf-4ad3-4ed8-a378-e451866ef365"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="0.0" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
<font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<textFieldExpression><![CDATA[$F{fieldA}]]></textFieldExpression>
</textField>
<textField pattern="" isBlankWhenNull="false">
<reportElement key="textField-2" mode="Transparent" x="109" y="0" width="71" height="16" forecolor="#000000" backcolor="#FFFFFF" uuid="a87c767c-8b6e-4dcf-b397-2412a752acaa"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="0.0" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
<font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<textFieldExpression><![CDATA[$F{fieldD}]]></textFieldExpression>
</textField>
<textField pattern="" isBlankWhenNull="false">
<reportElement key="textField-3" mode="Transparent" x="180" y="0" width="71" height="16" forecolor="#000000" backcolor="#FFFFFF" uuid="82e3a926-8f8f-41af-b478-db4eed521356"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="0.0" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
<font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<textFieldExpression><![CDATA[$F{fieldC}]]></textFieldExpression>
</textField>
<textField pattern="" isBlankWhenNull="false">
<reportElement key="textField-4" mode="Transparent" x="251" y="0" width="71" height="16" forecolor="#000000" backcolor="#FFFFFF" uuid="b8954bd1-387e-4a5d-a762-9b1c19a86a78"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="0.0" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
<font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<textFieldExpression><![CDATA[$F{fieldB} + $F{fieldF}]]></textFieldExpression>
</textField>
<textField pattern="" isBlankWhenNull="false">
<reportElement key="textField-6" mode="Transparent" x="322" y="0" width="71" height="16" forecolor="#000000" backcolor="#FFFFFF" uuid="80594584-7236-407f-99b1-a5bd8b7cb91f"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="0.0" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
<font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<textFieldExpression><![CDATA[$F{extension}]]></textFieldExpression>
</textField>
<textField pattern="" isBlankWhenNull="false">
<reportElement key="textField-7" mode="Transparent" x="392" y="0" width="71" height="16" forecolor="#000000" backcolor="#FFFFFF" uuid="4792a89e-5852-4fcb-9f34-510d411a3cc3"/>
<box>
<topPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="0.0" lineColor="#000000"/>
<rightPen lineWidth="0.0" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
<font fontName="Arial" size="7" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<textFieldExpression><![CDATA[$F{fieldE}]]></textFieldExpression>
</textField>
</band>
</detail>
<columnFooter>
<band splitType="Stretch"/>
</columnFooter>
<pageFooter>
<band splitType="Stretch"/>
</pageFooter>
<lastPageFooter>
<band height="22" splitType="Stretch">
<staticText>
<reportElement x="221" y="3" width="101" height="17" uuid="45817d74-5748-40d9-ac2a-f07baa316e7e"/>
<box>
<pen lineWidth="0.5"/>
<topPen lineWidth="0.5"/>
<leftPen lineWidth="0.5"/>
<bottomPen lineWidth="0.5"/>
<rightPen lineWidth="0.5"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="8" isBold="true"/>
</textElement>
<text><![CDATA[Text]]></text>
</staticText>
<textField pattern="" isBlankWhenNull="false">
<reportElement key="textField-8" mode="Transparent" x="322" y="3" width="70" height="17" forecolor="#000000" backcolor="#FFFFFF" uuid="2d0cc5e5-1e50-4196-867f-a729d1c5a2f9"/>
<box>
<pen lineWidth="0.5"/>
<topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="0.5" lineColor="#000000"/>
<rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
<font fontName="Arial" size="7" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<textFieldExpression><![CDATA[$P{paramA}]]></textFieldExpression>
</textField>
<textField pattern="" isBlankWhenNull="false">
<reportElement key="textField-9" mode="Transparent" x="392" y="3" width="71" height="17" forecolor="#000000" backcolor="#FFFFFF" uuid="19d787cb-e1df-473d-9f52-3c3aa7bed427"/>
<box>
<pen lineWidth="0.5"/>
<topPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
<leftPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
<bottomPen lineWidth="0.5" lineColor="#000000"/>
<rightPen lineWidth="0.5" lineStyle="Solid" lineColor="#000000"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle" rotation="None">
<font fontName="Arial" size="7" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<paragraph lineSpacing="Single"/>
</textElement>
<textFieldExpression><![CDATA[$P{paramB}]]></textFieldExpression>
</textField>
</band>
</lastPageFooter>
<summary>
<band splitType="Stretch"/>
</summary>
</jasperReport>
I don't know if was a bug, but I copied the line of other textFieldExpression of the jrxml that was working, rename it to the value that wasn't displaying and now the value it's displayed.
What the hell...