Unable to create a multi-sheet excel jasper report when executed on a Tomcat application server

Question

I'm new in JasperSoft and I'm finding issues exporting a report into multi-tabs (multi-sheets) excel file when I upload the jrxml and try to execute it on a web application that runs with Apache Tomcat 8 . I used iReport 4.6.0 CE to build this report where I'm using two subreports contained into two headergroups separated with page breaks to determine the end of the first tab and the begining of the second tab of the excel file . When I ran this report using iReport , I get the result I wanted in the first place However, When I run the same report on the web application , I get all the data from the two subreports under the same tab. Any body could help me here?

Here is my main report xml code

<?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="reportGlobalDOD" language="groovy" pageWidth="1310" pageHeight="842" columnWidth="1270" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isIgnorePagination="true" uuid="3eb83bbf-8ce6-4d69-b25a-288780da1894">
<property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>
<property name="ireport.zoom" value="0.75"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<property name="net.sf.jasperreports.export.xls.sheet.names.1" value="Broker"/>
<property name="net.sf.jasperreports.page.break.no.pagination" value="apply"/>
<property name="net.sf.jasperreports.export.xls.sheet.names.2" value="PMA"/>

<parameter name="EffDate" class="java.util.Date">
    <defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>
<parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
    <defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>
<queryString>


<![CDATA[SET LANGUAGE ENGLISH;
Declare     @CurrentDay smalldatetime = NULL

select @CurrentDay =$P{EffDate}
select @CurrentDay as effective_date]]>
    </queryString>
    <field name="effective_date" class="java.sql.Timestamp"/>
    <group name="subreport1">
        <groupExpression><![CDATA[dummy1]]></groupExpression>
        <groupHeader>
            <band height="152">
                <subreport>
                    <reportElement uuid="22345c46-948d-435f-99de-dbec39e586e2" x="-20" y="0" width="1310" height="134"/>
                    <subreportParameter name="EffDate">
                        <subreportParameterExpression><![CDATA[$P{EffDate}]]></subreportParameterExpression>
                    </subreportParameter>
                    <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                    <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "/BrokerDayOverDayReconciliation.jasper"]]></subreportExpression>
                </subreport>
                <break>
                    <reportElement uuid="c23c613d-444f-4b61-84f2-ee14d39df15c" x="0" y="140" width="100" height="1"/>
                </break>
            </band>
        </groupHeader>
    </group>
    <group name="subreport2">
        <groupExpression><![CDATA[dummy1]]></groupExpression>
        <groupHeader>
            <band height="115">
                <subreport>
                    <reportElement uuid="1a03a62a-4372-4a42-8910-bd8e79cfb318" x="-20" y="0" width="1310" height="115"/>
                    <subreportParameter name="EffDate">
                        <subreportParameterExpression><![CDATA[$P{EffDate}]]></subreportParameterExpression>
                    </subreportParameter>
                    <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                    <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "/PMADayOverDayReconciliation.jasper"]]></subreportExpression>
                </subreport>
            </band>
        </groupHeader>
    </group>
</jasperReport>

PS : I've used all related or similar question to mine in building this report , and as indicated it worked in local using iReport but not when I try to run the report on the web application

Also please find bellow my Java code part of Excel export

 JRXlsExporter exporterXLS = new JRXlsExporter();

         exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
         exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, outputByte);
         exporterXLS.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.TRUE);
        //               exporterXLS.setParameter(JRXlsExporterParameter.IS_AUTO_DETECT_CELL_TYPE, Boolean.TRUE);
         exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
         exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
         exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS, Boolean.TRUE);
         exporterXLS.setParameter(JExcelApiExporterParameter.IS_DETECT_CELL_TYPE,Boolean.TRUE);
         exporterXLS.exportReport();

Also, I tried to execute the same report using a newer version of ireport (specifically 5.0.0) and it is bringing the 2 sheets data into the same one (the result is an excel file with a unique tab combining the 2 subreports data extract). Please help me what I'm missing here .

Possible duplicate of [How do you export a JasperReport to an Excel file with multiple worksheets?](http://stackoverflow.com/questions/3977658/how-do-you-export-a-jasperreport-to-an-excel-file-with-multiple-worksheets) & [How to show each subreport in a separate Excel sheet](http://stackoverflow.com/q/12599318/876298) — Alex K, Sep 12 '16 at 20:45
@AlexK I used the response of the question you indicated in building this report however it works only in local and not when I run it on the application . My issue remains here . — siri, Sep 12 '16 at 21:13
@PetterFriberg I do not have any errors or exceptions when I consulted the log . — siri, Sep 13 '16 at 17:45

score 0 · Accepted Answer · answered Oct 04 '16 at 13:24

My Solution was to set "ignorePagination" to false (I know this could cause some issues since it could create more sheets than wanted) and to increase the height of the 2 subreports and the two bands that contain them.This could not be a stable solution but for know it is working as a charm .

<?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="reportGlobalDOD" language="groovy" pageWidth="1600" pageHeight="15000" columnWidth="1560" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="3eb83bbf-8ce6-4d69-b25a-288780da1894">
 <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>
 <property name="net.sf.jasperreports.page.break.no.pagination" value="apply"/>
 <property name="ireport.zoom" value="0.75"/>
 <property name="ireport.x" value="0"/>
 <property name="ireport.y" value="0"/>
 <property name="net.sf.jasperreports.export.xls.sheet.names.1" value="Broker"/>
 <property name="net.sf.jasperreports.export.xls.sheet.names.2" value="PMA"/>
 <parameter name="EffDate" class="java.util.Date">
  <defaultValueExpression><![CDATA[]]></defaultValueExpression>
 </parameter>
 <parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
  <defaultValueExpression><![CDATA["C:\\Users\\user1\\Documents\\Reports\\"]]></defaultValueExpression>
 </parameter>
 <queryString>
  <![CDATA[SET LANGUAGE ENGLISH;
Declare     @CurrentDay smalldatetime = NULL

select @CurrentDay =$P{EffDate}
select @CurrentDay as effective_date]]>
 </queryString>
 <field name="effective_date" class="java.sql.Timestamp"/>
 <group name="subreport1" isStartNewPage="true">
  <groupExpression><![CDATA[dummy1]]></groupExpression>
  <groupHeader>
   <band height="300">
    <subreport isUsingCache="false" runToBottom="false">
     <reportElement uuid="22345c46-948d-435f-99de-dbec39e586e2" x="-20" y="0" width="1599" height="299"/>
     <subreportParameter name="EffDate">
      <subreportParameterExpression><![CDATA[$P{EffDate}]]></subreportParameterExpression>
     </subreportParameter>
     <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
     <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "/BrokerDayOverDayReconciliation.jasper"]]></subreportExpression>
    </subreport>
    <break>
     <reportElement uuid="c23c613d-444f-4b61-84f2-ee14d39df15c" x="0" y="299" width="138" height="1">
      <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>
     </reportElement>
    </break>
   </band>
  </groupHeader>
 </group>
 <group name="subreport2" isStartNewPage="true">
  <groupExpression><![CDATA[dummy1]]></groupExpression>
  <groupHeader>
   <band height="300">
    <break>
     <reportElement uuid="81337467-5a85-43e4-a23a-ba567317e3ce" x="0" y="0" width="100" height="1">
      <property name="net.sf.jasperreports.export.xls.break.before.row" value="true"/>
     </reportElement>
    </break>
    <subreport>
     <reportElement uuid="1a03a62a-4372-4a42-8910-bd8e79cfb318" x="-20" y="1" width="1599" height="299"/>
     <subreportParameter name="EffDate">
      <subreportParameterExpression><![CDATA[$P{EffDate}]]></subreportParameterExpression>
     </subreportParameter>
     <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
     <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "/PMADayOverDayReconciliation.jasper"]]></subreportExpression>
    </subreport>
   </band>
  </groupHeader>
 </group>
</jasperReport>

Unable to create a multi-sheet excel jasper report when executed on a Tomcat application server

1 Answers1