Jump to content
We've recently updated our Privacy Statement, available here ×

  • Sample Report Demonstrating Dynamically Set Sheet Names in Excel Output

    Tom C

    By Tom C

      • Features: Exporters Version: v5.6 Product: JasperReports® Library
     Share

    Summary

    The following sample reports use jasperserver data source to display all users under a role. Each report page contains one role and the report excel output will have one page per sheet with role name as the sheet name.

    Tabular report:

    		[toc]
    <?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.0.0.beta-v201410301058 -->
<!--  using JasperReports Library version 5.6.2  -->
<!-- 2014-11-25T13:53:25 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"
              name="report55617"
              pageWidth="612" pageHeight="792" columnWidth="555"
              leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20"
              uuid="85ebfc4c-73f9-47ad-a5cb-d4e2bc39299e">
    <property name="com.jaspersoft.studio.data.defaultdataadapter"
              value="JRSrepo-Postgres2"/>
    <property name="net.sf.jasperreports.export.xls.one.page.per.sheet"
              value="true"/>
    <queryString language="SQL">
        <![CDATA[select u.username, r.rolename, o.tenantname from jiuser u
inner join jiuserrole j on j.userid = u.id
inner join jirole r on r.id = j.roleid
inner join jitenant o on o.id = u.tenantid
order by 2, 3, 1]]>
    </queryString>
    <field name="username" class="java.lang.String"/>
    <field name="rolename" class="java.lang.String">
        <property name="net.sf.jasperreports.export.xls.sheet.name"
                  value="$F{rolename}"/>
    </field>
    <field name="tenantname" class="java.lang.String"/>
    <group name="role" isStartNewPage="true">
        <groupExpression><![CDATA[$F{rolename}]]></groupExpression>
    </group>
    <background><band splitType="Stretch"/></background>
    <title>     <band splitType="Stretch"/></title>
    <pageHeader><band splitType="Stretch"/></pageHeader>
    <columnHeader>
        <band height="30" splitType="Stretch">
            <property name="local_mesure_unitheight" value="pixel"/>
            <property name="com.jaspersoft.studio.unit.height"
                      value="px"/>
            <staticText>
                <reportElement x="0" y="0" width="150" height="30"
                               uuid="25508467-d654-4807-ad17-a31a0a4bc977">
                    <property name="local_mesure_unitwidth" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <text><![CDATA[rolename]]></text>
            </staticText>
            <staticText>
                <reportElement x="150" y="0" width="150" height="30"
                               uuid="24d6bb28-17fc-44ba-b959-448deda71a12">
                    <property name="local_mesure_unitwidth" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <text><![CDATA[tenantname]]></text>
            </staticText>
            <staticText>
                <reportElement x="300" y="0" width="150" height="30"
                               uuid="a6544345-cac6-4689-84b2-3a9e4a0198fb">
                    <property name="local_mesure_unitwidth" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <text><![CDATA[username]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="30" splitType="Stretch">
            <property name="local_mesure_unitheight" value="pixel"/>
            <property name="com.jaspersoft.studio.unit.height" value="px"/>
            <textField>
                <reportElement x="0" y="0" width="150" height="30"
                               uuid="85006485-57d5-4b24-979d-f4c1212363c7">
                    <property name="local_mesure_unitwidth" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                    <propertyExpression name="net.sf.jasperreports.export.xls.sheet.name">
                        <![CDATA[$F{rolename}]]>
                    </propertyExpression>
                </reportElement>
                <textFieldExpression><![CDATA[$F{rolename}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="150" y="0" width="150" height="30"
                               uuid="df489ae8-967d-48bf-9bbe-05d0c64df431">
                    <property name="local_mesure_unitwidth" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{tenantname}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="300" y="0" width="150" height="30"
                               uuid="05960d09-8181-4842-b8db-3e63656cc98f">
                    <property name="local_mesure_unitwidth" value="pixel"/>
                    <property name="com.jaspersoft.studio.unit.width" value="px"/>
                </reportElement>
                <textFieldExpression><![CDATA[$F{username}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <columnFooter><band splitType="Stretch"/></columnFooter>
    <pageFooter>  <band splitType="Stretch"/></pageFooter>
    <summary>     <band splitType="Stretch"/></summary>
</jasperReport>

    1(78).png.f14713a0b441e3f1ca9db41d778924f6.png

    Table report:

    <?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.0.0.beta-v201410301058 -->
<!--  using JasperReports Library version 5.6.2  -->
<!-- 2014-11-26T08:38:32 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd"
              name="report56117table"
              pageWidth="612" pageHeight="792" columnWidth="555"
              leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20"
              uuid="11c460d3-d1c7-4102-9fbc-5b640a5de06e">
    <property name="net.sf.jasperreports.export.xls.one.page.per.sheet"
              value="true"/>
    <property name="com.jaspersoft.studio.data.defaultdataadapter"
              value="JRSrepoPostgres2"/>
    <style name="Table_TH" mode="Opaque" backcolor="#F0F8FF">
        <box>
            <pen       lineWidth="0.5" lineColor="#000000"/>
            <topPen    lineWidth="0.5" lineColor="#000000"/>
            <leftPen   lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen  lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Table_CH" mode="Opaque" backcolor="#BFE1FF">
        <box>
            <pen       lineWidth="0.5" lineColor="#000000"/>
            <topPen    lineWidth="0.5" lineColor="#000000"/>
            <leftPen   lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen  lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Table_TD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen       lineWidth="0.5" lineColor="#000000"/>
            <topPen    lineWidth="0.5" lineColor="#000000"/>
            <leftPen   lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen  lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <subDataset name="Dataset1"
                uuid="963178aa-7087-4b7c-9ac7-ab9dd82aee6b">
        <property name="com.jaspersoft.studio.data.defaultdataadapter"
                  value="JRSrepoPostgres2"/>
        <queryString>
            <![CDATA[select u.username, r.rolename, o.tenantname from jiuser u
inner join jiuserrole j on j.userid = u.id
inner join jirole r on r.id = j.roleid
inner join jitenant o on o.id = u.tenantid
order by 2, 3, 1]]>
        </queryString>
        <field name="username"   class="java.lang.String"/>
        <field name="rolename"   class="java.lang.String"/>
        <field name="tenantname" class="java.lang.String"/>
        <group name="Group1" isStartNewColumn="true">
            <groupExpression><![CDATA[$F{rolename}]]></groupExpression>
        </group>
    </subDataset>
    <queryString language="SQL"><![CDATA[select 1]]></queryString>
    <background>  <band splitType="Stretch"/></background>
    <title>       <band splitType="Stretch"/></title>
    <pageHeader>  <band splitType="Stretch"/></pageHeader>
    <columnHeader><band splitType="Stretch"/></columnHeader>
    <detail>      <band splitType="Stretch"/></detail>
    <columnFooter><band splitType="Stretch"/></columnFooter>
    <pageFooter>  <band splitType="Stretch"/></pageFooter>
    <summary>
        <band height="500" splitType="Stretch">
            <property name="local_mesure_unitheight" value="pixel"/>
            <property name="com.jaspersoft.studio.unit.height"
                      value="px"/>
            <componentElement>
                <reportElement x="-12" y="2" width="583" height="68"
                               uuid="efd53bcf-1b3c-4d85-a4db-004f4e61a2ac"/>
                <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components"
                          xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
                    <datasetRun subDataset="Dataset1"
                                uuid="1317b399-5e1c-488e-9a6b-1da3bce1e70e">
                        <connectionExpression>
                            <![CDATA[$P{REPORT_CONNECTION}]]>
                        </connectionExpression>
                    </datasetRun>
                    <jr:column width="66" uuid="740b583b-f94a-43df-a7bf-5c673b354fbe">
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="66" height="30"
                                               uuid="b688b9a7-cdc0-47f3-bac8-bd99f11b9182"/>
                                <text><![CDATA[rolename]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="66" height="30"
                                               uuid="689c1f09-3023-4a11-a9d0-2c910fbd3293">
                                    <propertyExpression name="net.sf.jasperreports.export.xls.sheet.name">
                                        <![CDATA[$F{rolename}]]>
                                    </propertyExpression>
                                </reportElement>
                                <textFieldExpression><![CDATA[$F{rolename}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="66" uuid="208c9f71-27a2-45f6-bcfd-1267f18cc790">
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="66" height="30"
                                               uuid="a78a3a41-c56a-41ae-a154-d881beccbdce"/>
                                <text><![CDATA[tenantname]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="66" height="30"
                                               uuid="ab755f16-e2e4-4562-be1f-98fd29b34eec"/>
                                <textFieldExpression>
                                    <![CDATA[$F{tenantname}]]>
                                </textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="66" uuid="a2d75333-ccbb-432e-abf4-564d5b152685">
                        <jr:columnHeader style="Table_CH" height="30">
                            <staticText>
                                <reportElement x="0" y="0" width="66" height="30"
                                               uuid="9c49f493-14fb-4a64-b3a9-37ff89dcb327"/>
                                <text><![CDATA[username]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="66" height="30"
                                               uuid="3f090fe6-08b3-4ef8-b404-9f2f66c809ce"/>
                                <textFieldExpression>
                                    <![CDATA[$F{username}]]>
                                </textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                </jr:table>
            </componentElement>
        </band>
    </summary>
</jasperReport>

    2(62).png.bf8027ff35707cd0e896f3b523974539.png

    TTC-20141126-56117-20181127

    1(78).png.444974491933d229d1b902c381ccac90.png

    2(62).png.c0019f505616c660c0f29cb4cf41ee8e.png

     Share
    Go to entries

    User Feedback

    Recommended Comments

    There are no comments to display.



    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...

© 2024 Cloud Software Group,
Inc. All rights reserved.

Products

Explore