Jump to content
Changes to the Jaspersoft community edition download ×

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


Recommended Posts


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.


Link to comment
Share on other sites

  • 2 weeks later...
  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

  • 3 years later...

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...