syrine syrine - 1 month ago 17
Java Question

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

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 .

Answer

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>

Comments