如何在单独的Excel工作表中显示每个子报表 [英] How to show each subreport in a separate Excel sheet

问题描述

我想使用 iReport 为 MS Excel 创建报告。

我的报告包含两个子报告。我需要在单独的 Excel 表单中的每个子报表，但生成的导出文件将所有数据放在一个表格中。

我不知道哪个需要此选项的设置是。

解决方案

您可以在帖子中的信息。

I would like to create a report with iReport for MS Excel.

My report includes two subreports. I need each subreport in a separate Excel sheet, but the generated export file has all data in one sheet.

I don't know which settings for this option required is.

解决方案

You can solve this issue with help of net.sf.jasperreports.export.xls.break.after.row property.

You can set this net.sf.jasperreports.export.xls.break.after.row property for the "sheets break" element in report (for example, line or staticText element).

In my sample below I've put line element to the Group Footer band for generating new sheet in Excel for a new group.

Here is my sample, master report:

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport ..>
    <parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA["path_to_subreport"]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT id, addressId FROM document ORDER BY addressId]]>
    </queryString>
    <field name="ID" class="java.lang.Integer"/>
    <field name="ADDRESSID" class="java.lang.Integer"/>
    <group name="addressGroup">
        <groupExpression><![CDATA[$F{ADDRESSID}]]></groupExpression>
        <groupHeader>
            <band height="53">
                <subreport>
                    <reportElement x="189" y="0" width="200" height="31">
                        <property name="net.sf.jasperreports.export.xls.break.after.row" value="&quot;true&quot;"/>
                    </reportElement>
                    <subreportParameter name="addressId">
                        <subreportParameterExpression><![CDATA[$F{ADDRESSID}]]></subreportParameterExpression>
                    </subreportParameter>
                    <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                    <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "subreport_new_sheet_for_excel.jasper"]]></subreportExpression>
                </subreport>
                <staticText>
                    <reportElement x="0" y="33" width="100" height="20"/>
                    <textElement textAlignment="Center" verticalAlignment="Middle">
                        <font isItalic="true"/>
                    </textElement>
                    <text><![CDATA[ID]]></text>
                </staticText>
            </band>
        </groupHeader>
        <groupFooter>
            <band height="9">
                <line>
                    <reportElement x="1" y="5" width="100" height="1">
                        <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>
                    </reportElement>
                    <graphicElement>
                        <pen lineWidth="0.0"/>
                    </graphicElement>
                </line>
            </band>
        </groupFooter>
    </group>
    <detail>
        <band height="20" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="100" height="20"/>
                <box>
                    <topPen lineWidth="1.0"/>
                    <leftPen lineWidth="1.0"/>
                    <bottomPen lineWidth="1.0"/>
                    <rightPen lineWidth="1.0"/>
                </box>
                <textElement/>
                <textFieldExpression><![CDATA[$F{ID}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

My subreport:

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport ...>
    <parameter name="addressId" class="java.lang.Integer" isForPrompting="false"/>
    <queryString>
        <![CDATA[SELECT city, street FROM address WHERE id = $P{addressId}]]>
    </queryString>
    <field name="CITY" class="java.lang.String"/>
    <field name="STREET" class="java.lang.String"/>
    <detail>
        <band height="20" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="258" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA["City: " + $F{CITY}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="258" y="0" width="297" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA["Street: " + $F{STREET}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

The result will be:

The result without using of "break" element (with net.sf.jasperreports.export.xls.break.after.row) will be:

---

You can find more about info in Advanced Excel Features post.

这篇关于如何在单独的Excel工作表中显示每个子报表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！