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

  • Tip : Excel Export Expand(+) and Collapse(-) for grouped data in Jasper Reports (or) The Outline Row Level Property for Excel Extract Reports in Jaspersoft

    sadakar
     Share
    Hi,

    In this post you can learn how to get Expand and Collapse options when exporting a grouped by data excel report.

    Steps : 
    1) Design report as follows 
         
          Group Header  ($F{city})
                 Detail (some fields)
          Group Footer (Dummy Text field with 0 height). 

    (Click on image to get the best view)

    expand%2Band%2Bcollapse.png

    2) Apply Body property to the fields that you keep in Detail Band
    net.sf.jasperreports.export.xls.row.outline.level.1=Body

    (click on Image to get the best of content)
    2.png
     

    3) Apply End property to the dummy field that you keep in Footer band.
    net.sf.jasperreports.export.xls.row.outline.level.1=END

    (Click on image to get the best view)
    3.png
    4) Sample Excel outputs
    (Collapsed Stage)
    excel.png
    A group expansion stage : 
    expand%2Band%2Bco.png

    References : 
    http://jasperreports.sourceforge.net/sample.reference/xlsfeatures/


    Sample JRXML ( works on 6.2 or later Jasper Studio professional with foodmart database )
     <?xml version="1.0" encoding="UTF-8"?>
    <!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  -->
    <!-- 2016-03-29T18:23:38 -->
    <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="Expand and Collapse Excel" pageWidth="595" pageHeight="842" columnWidth="595" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="58e9ce0f-5fbd-48ec-8645-53f5a0b2ad01">
        <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
        <property name="com.jaspersoft.studio.unit." value="pixel"/>
        <property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/>
        <property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/>
        <property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/>
        <property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/>
        <property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/>
        <property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/>
        <property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/>
        <property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/>
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="foodmart"/>
        <property name="ireport.jasperserver.url" value="http://localhost:8081/jasperserver-pro/"/>
        <property name="ireport.jasperserver.user" value="superuser"/>
        <property name="ireport.jasperserver.report.resource" value="/2_Training_Naresh/Reports/21_Expand_and_Collapse_Excel_Export_files/main_jrxml"/>
        <property name="ireport.jasperserver.reportUnit" value="/2_Training_Naresh/Reports/21_Expand_and_Collapse_Excel_Export"/>
        <queryString>
            <![CDATA[sELECT * FROM customer ORDER BY city]]>
        </queryString>
        <field name="customer_id" class="java.lang.Integer"/>
        <field name="account_num" class="java.lang.Long"/>
        <field name="lname" class="java.lang.String"/>
        <field name="fname" class="java.lang.String"/>
        <field name="mi" class="java.lang.String"/>
        <field name="address1" class="java.lang.String"/>
        <field name="address2" class="java.lang.String"/>
        <field name="address3" class="java.lang.String"/>
        <field name="address4" class="java.lang.String"/>
        <field name="city" class="java.lang.String"/>
        <field name="state_province" class="java.lang.String"/>
        <field name="postal_code" class="java.lang.String"/>
        <field name="country" class="java.lang.String"/>
        <field name="customer_region_id" class="java.lang.Integer"/>
        <field name="phone1" class="java.lang.String"/>
        <field name="phone2" class="java.lang.String"/>
        <field name="birthdate" class="java.sql.Date"/>
        <field name="marital_status" class="java.lang.String"/>
        <field name="yearly_income" class="java.lang.String"/>
        <field name="gender" class="java.lang.String"/>
        <field name="total_children" class="java.lang.Integer"/>
        <field name="num_children_at_home" class="java.lang.Integer"/>
        <field name="education" class="java.lang.String"/>
        <field name="date_accnt_opened" class="java.sql.Date"/>
        <field name="member_card" class="java.lang.String"/>
        <field name="occupation" class="java.lang.String"/>
        <field name="houseowner" class="java.lang.String"/>
        <field name="num_cars_owned" class="java.lang.Integer"/>
        <field name="fullname" class="java.lang.String"/>
        <group name="city">
            <groupExpression><![CDATA[$F{city}]]></groupExpression>
            <groupHeader>
                <band height="16">
                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    <textField>
                        <reportElement x="0" y="0" width="100" height="16" uuid="752494bc-a3a7-447b-b24a-16dd57b4e203">
                            <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                        </reportElement>
                        <box leftPadding="5"/>
                        <textElement verticalAlignment="Middle">
                            <font isBold="true"/>
                        </textElement>
                        <textFieldExpression><![CDATA[$F{city}]]></textFieldExpression>
                    </textField>
                    <textField>
                        <reportElement x="100" y="0" width="100" height="16" uuid="4edb4831-d823-471c-8cdf-9621627e0fc9">
                            <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                        </reportElement>
                        <textElement verticalAlignment="Middle">
                            <font isBold="true"/>
                        </textElement>
                        <textFieldExpression><![CDATA[""]]></textFieldExpression>
                    </textField>
                    <textField>
                        <reportElement x="200" y="0" width="100" height="16" uuid="925b91cc-0b9e-4c68-b43e-01546ae19316">
                            <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                            <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                        </reportElement>
                        <textElement textAlignment="Center" verticalAlignment="Middle"/>
                        <textFieldExpression><![CDATA[""]]></textFieldExpression>
                    </textField>
                    <textField>
                        <reportElement x="300" y="0" width="120" height="16" uuid="607f71cb-a9c1-4417-bfa3-cc4bac1f81c2">
                            <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                            <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                        </reportElement>
                        <textElement textAlignment="Center" verticalAlignment="Middle"/>
                        <textFieldExpression><![CDATA[""]]></textFieldExpression>
                    </textField>
                    <textField>
                        <reportElement x="420" y="0" width="175" height="16" uuid="1508d978-b950-4f89-a362-d944afcd326c">
                            <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                            <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                        </reportElement>
                        <textElement textAlignment="Center" verticalAlignment="Middle"/>
                        <textFieldExpression><![CDATA[""]]></textFieldExpression>
                    </textField>
                </band>
            </groupHeader>
            <groupFooter>
                <band>
                    <textField>
                        <reportElement x="0" y="0" width="100" height="0" uuid="24a95893-5789-4871-bca0-f6d7f8a9bcf4">
                            <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="END"/>
                        </reportElement>
                        <textFieldExpression><![CDATA["Text Field"]]></textFieldExpression>
                    </textField>
                </band>
            </groupFooter>
        </group>
        <columnHeader>
            <band height="16" splitType="Stretch">
                <printWhenExpression><![CDATA[$V{PAGE_COUNT}==1]]></printWhenExpression>
                <staticText>
                    <reportElement x="100" y="0" width="100" height="16" uuid="0d2dd0ef-ad82-40cb-b7af-6cdbedc6fa12"/>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <text><![CDATA[Customer ID]]></text>
                </staticText>
                <staticText>
                    <reportElement x="200" y="0" width="100" height="16" uuid="b1f7385d-2678-405c-bdc1-1ebf363d1f16"/>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <text><![CDATA[Customer Name]]></text>
                </staticText>
                <staticText>
                    <reportElement x="300" y="0" width="120" height="16" uuid="72efd2e8-b27c-49f5-bbf6-7f0cf7b65868"/>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <text><![CDATA[Occupation]]></text>
                </staticText>
                <staticText>
                    <reportElement x="420" y="0" width="175" height="16" uuid="71a0dffd-4d85-40f0-b04e-b966697b8a14">
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <textElement textAlignment="Center"/>
                    <text><![CDATA[Phone Number]]></text>
                </staticText>
                <textField>
                    <reportElement x="0" y="0" width="100" height="16" uuid="126c9bef-1748-4333-b3c4-dd641268e523">
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <textElement verticalAlignment="Middle">
                        <font isBold="true"/>
                    </textElement>
                    <textFieldExpression><![CDATA[""]]></textFieldExpression>
                </textField>
            </band>
        </columnHeader>
        <detail>
            <band height="16" splitType="Stretch">
                <textField>
                    <reportElement x="100" y="0" width="100" height="16" uuid="262d3c0f-c9d4-4198-a6f1-4235e12967f6">
                        <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[$F{customer_id}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="200" y="0" width="100" height="16" uuid="23a4613e-cbc2-49fa-8b85-c8c1c2b85538">
                        <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[$F{fullname}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="300" y="0" width="120" height="16" uuid="9bdd418b-5ed5-4731-bb47-5f662f214693">
                        <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[$F{occupation}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="420" y="0" width="175" height="16" uuid="1f727d26-1462-4c6a-8e6b-5dbb0e05668d">
                        <property name="net.sf.jasperreports.export.xls.row.outline.level.1" value="Body"/>
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <textElement textAlignment="Center" verticalAlignment="Middle"/>
                    <textFieldExpression><![CDATA[$F{phone1}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="0" y="0" width="100" height="16" uuid="282bb881-bc06-490f-83b2-a34764648547">
                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
                    </reportElement>
                    <textElement verticalAlignment="Middle">
                        <font isBold="true"/>
                    </textElement>
                    <textFieldExpression><![CDATA[""]]></textFieldExpression>
                </textField>
            </band>
        </detail>
    </jasperReport>
     

    I hope it helps some one. .! & thank you to the friend who explained this to me today.  :-) :-)

    References: 
    http://jasperreports.sourceforge.net/sample.reference/xlsfeatures/ 
     Share
    Go to blogs

    User Feedback

    Recommended Comments

    There are no comments to display.



    Guest
    This is now closed for further comments

×
×
  • Create New...

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

Products

Explore