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


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">
            <band height="25">
                <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.grid.JSSGridBagLayout"/>
                    <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"/>
                    <textFieldExpression><![CDATA["#" + $F{store}]]></textFieldExpression>
            <band height="15">
                <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>

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


kmarchewa's picture
Joined: Aug 23 2019 - 10:43am
Last seen: 1 month 3 weeks ago

2 Answers:

You could try the property keepTogether:

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

anji.viper's picture
Joined: Mar 8 2017 - 2:59am
Last seen: 3 years 8 months ago

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... , I was able to dynamically set the name of the sheet by adding

<propertyExpression name="net.sf.jasperreports.export.xls.sheet.name">

On that same first element in the group.

Hope this helps someone else.

davidfritch's picture
Joined: May 24 2016 - 9:52am
Last seen: 1 month 3 days ago