Jasper Reports - How to Create One Sheet per Group in Excel Export

[kmarchewa]

Greetings:

I am attempting to design some reports in Jaspersoft Studio. The end goal is to incorporate the compiled reports into a Java application (using Jasper Reports Library), but I am doing the initial design in Jaspersoft Studio. I have a need to create an Excel export with one tab per group. It works fine EXCEPT in the case when a group spans multiple pages; in such cases, the group is split into multiple tabs.

I have referenced https://community.jaspersoft.com/wiki/splitting-excel-output-multiple-tabs and some questions on this community forum but have yet to find a solution.

What I have tried:

1. Setting "net.sf.jasperreports.export.xls.break.after.row" equal to true in my group footer and "net.sf.jasperreports.export.xls.one.page.per.sheet" to true. Result: Described above. Some groups take up more than one sheet.

2. Same as (1), but checking Ignore Pagination and setting "net.sf.jasperreports.page.break.no.pagination " to "apply". Result: All groups on one page.

3. Setting "net.sf.jasperreports.export.xls.break.after.row" equal to true in my group footer and "net.sf.jasperreports.export.xls.one.page.per.sheet" to false. Result: All groups on one page.

Group definition:

<group name="PropertyGroup">        <groupExpression><![CDATA[$F{store}]]></groupExpression>        <groupHeader>            <band height="25">                <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.grid.JSSGridBagLayout"/>                <textField>                    <reportElement mode="Opaque" x="0" y="0" width="555" height="25" uuid="e128eeba-9972-4aaa-94a3-eae4ca1b0f65">                        <property name="com.jaspersoft.studio.unit.height" value="px"/>                        <property name="com.jaspersoft.layout.grid.x" value="1"/>                        <property name="com.jaspersoft.layout.grid.y" value="-1"/>                        <property name="com.jaspersoft.layout.grid.weight.x" value="1.0"/>                        <property name="com.jaspersoft.layout.grid.weight.y" value="1.0"/>                        <property name="com.jaspersoft.layout.grid.rowspan" value="1"/>                        <property name="com.jaspersoft.layout.grid.colspan" value="1"/>                        <property name="com.jaspersoft.layout.grid.weight.fixed" value="false"/>                    </reportElement>                    <textFieldExpression><![CDATA["#" + $F{store}]]></textFieldExpression>                </textField>            </band>        </groupHeader>        <groupFooter>            <band height="15">                <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>            </band>        </groupFooter>    </group>[/code]

Any advice would be appreciated. If this is an option that is better performed programmatically through the library that is an option as well.

Thanks.

[anji.viper]

You could try the property keepTogether:

<group name="group1" keepTogether="true">

[davidfritch]

Thanks for asking this question.

I think I was able to accomplish a proof of concept by following the steps in the link you referenced https://community.jaspersoft.com/wiki/splitting-excel-output-multiple-tabs. The key for me was to not have a Title Band in the report nor a Column Header Band. I just moved all of those fields into the Group Header. That way on each sheet the freezing of the top 3 or 4 rows for me applied to each sheet.

Then in the link it says to add 

<property name="net.sf.jasperreports.export.xls.break.before.row" value="true" />

to the "first <reportelement> contained in the group header", not on the group header itself. Furthermore, after reading https://community.jaspersoft.com/wiki/excel-export-dynamically-set-sheet-names , I was able to dynamically set the name of the sheet by adding 

<propertyExpression name="net.sf.jasperreports.export.xls.sheet.name">    <![CDATA[$F{FieldName}]]></propertyExpression>

On that same first element in the group.

Hope this helps someone else.