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>
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.
2 Answers:
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">
<![CDATA[$F{FieldName}]]>
</propertyExpression>On that same first element in the group.
Hope this helps someone else.
