Jump to content
JasperReports Library 7.0 is now available ×

Problem with dynamic excel worksheet names


tim_18

Recommended Posts

I have a report that creates a separate excel worksheet for each group. 

 

The property net.sf.jasperreports.export.xls.sheet.name is set to the same field that is printed in the group header.

 

This does not work properly.  I have tried many different places to locate the sheet name property, but to no avail.

 

If there are 4 groups in sequence: GroupA, GroupB, GroupC, GroupD.  The worksheets in excel are named as follows: GroupD, GroupA, GroupA 2, GroupA 3.  The text box in the group header shows the correct value.

 


Below is the code

<?xml version="1.0" encoding="UTF-8"?><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="sales_log" language="groovy" pageWidth="792" pageHeight="612" orientation="Landscape" columnWidth="752" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isIgnorePagination="true" uuid="a3911ab5-df01-4860-8519-007b2a6faecb">    <property name="ireport.zoom" value="1.0"/>    <property name="ireport.x" value="0"/>    <property name="ireport.y" value="0"/>    <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>    <queryString>        <![CDATA[select pd.CLASS_NAME,       pd.CATEGORY_NAME,       concat(pd.CLASS_CODE, pd.CATEGORY_CODE) as CLASSCAT,       concat_ws(' ',       case            when substring(ph.SHIPPED_DATE,5,2) in ('01', '02', '03') then 'Q1'            when substring(ph.SHIPPED_DATE,5,2) in ('04', '05', '06') then 'Q2'            when substring(ph.SHIPPED_DATE,5,2) in ('07', '08', '09') then 'Q3'            when substring(ph.SHIPPED_DATE,5,2) in ('10', '11', '12') then 'Q4'            end, substring(ph.SHIPPED_DATE, 1, 4)) as QTR,       date_format(str_to_date(ph.SHIPPED_DATE, '%Y%m%d'), '%m/%d/%Y') as SHIPPED_DATE,       pd.PACK_SLIP_NO,       ph.CUST_NAME,       pd.ITEM_CODE,       pd.ITEM_NAME,       ifnull(round(pd.GROSS_WGT,2),0) as GROSS_WGT,       ifnull(round(pd.TARE_WGT,2),0) as TARE_WGT,       ifnull(round(pd.NET_WGT,2),0) as NET_WGT,       ifnull(round(pd.PRICE,3),0) as PRICE,       ifnull(round(pd.TOTAL_AMT,2),0) as TOTAL_AMTfrom PACKSLIP_H phjoin PACKSLIP_D pd on ph.COMPANY_CODE=pd.COMPANY_CODE and ph.YARD_CODE=pd.YARD_CODE and ph.PACK_SLIP_NO=pd.PACK_SLIP_NO and pd.DETAIL_LINE_NO=''where ph.SHIPPED_DATE>'20130101'order by pd.CLASS_NAME, pd.CATEGORY_NAME, ph.SHIPPED_DATE]]>    </queryString>    <field name="CLASS_NAME" class="java.lang.String">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="CATEGORY_NAME" class="java.lang.String">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="CLASSCAT" class="java.lang.String"/>    <field name="QTR" class="java.lang.String"/>    <field name="SHIPPED_DATE" class="java.lang.String">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="PACK_SLIP_NO" class="java.lang.String">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="CUST_NAME" class="java.lang.String">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="ITEM_CODE" class="java.lang.String">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="ITEM_NAME" class="java.lang.String">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="GROSS_WGT" class="java.math.BigDecimal">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="TARE_WGT" class="java.math.BigDecimal">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="NET_WGT" class="java.math.BigDecimal">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="PRICE" class="java.math.BigDecimal">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <field name="TOTAL_AMT" class="java.math.BigDecimal">        <fieldDescription><![CDATA[]]></fieldDescription>    </field>    <variable name="variable1" class="java.lang.String" resetType="Group" resetGroup="CategoryGroup">        <variableExpression><![CDATA[$F{CATEGORY_NAME}]]></variableExpression>        <initialValueExpression><![CDATA[$F{CATEGORY_NAME}]]></initialValueExpression>    </variable>    <group name="CategoryGroup">        <groupExpression><![CDATA[$F{CLASSCAT}]]></groupExpression>        <groupHeader>            <band height="59">                <textField>                    <reportElement uuid="6640b6e6-ae7e-49bb-aa7b-b0fb3a87f3ce" x="0" y="9" width="100" height="20"/>                    <textElement>                        <font size="11" isBold="true" isUnderline="true"/>                    </textElement>                    <textFieldExpression><![CDATA[$F{CATEGORY_NAME}]]></textFieldExpression>                </textField>                <staticText>                    <reportElement uuid="a02ed606-6e4a-4ae3-b40e-8015677d6d94" x="1" y="39" width="62" height="20"/>                    <textElement>                        <font size="11" isBold="true"/>                    </textElement>                    <text><![CDATA[Date]]></text>                </staticText>                <staticText>                    <reportElement uuid="d4803c84-3e13-4016-aa99-6ac3406dbf14" x="90" y="39" width="141" height="20"/>                    <textElement>                        <font size="11" isBold="true"/>                    </textElement>                    <text><![CDATA[Customer]]></text>                </staticText>                <staticText>                    <reportElement uuid="51a5e258-81f6-4c67-9b80-b62e4d274cdc" x="248" y="39" width="148" height="20"/>                    <textElement>                        <font size="11" isBold="true"/>                    </textElement>                    <text><![CDATA[item    ]]></text>                </staticText>                <staticText>                    <reportElement uuid="f412ed46-2532-4008-a333-588d5d625299" x="407" y="39" width="65" height="20"/>                    <textElement textAlignment="Right">                        <font size="11" isBold="true"/>                    </textElement>                    <text><![CDATA[Gross]]></text>                </staticText>                <staticText>                    <reportElement uuid="f406f52d-a722-465f-b95f-af94b82b6ac8" x="477" y="39" width="65" height="20"/>                    <textElement textAlignment="Right">                        <font size="11" isBold="true"/>                    </textElement>                    <text><![CDATA[Tare]]></text>                </staticText>                <staticText>                    <reportElement uuid="1df16bed-f5b1-4c4c-901a-eb07a145ec8f" x="547" y="39" width="65" height="20"/>                    <textElement textAlignment="Right">                        <font size="11" isBold="true"/>                    </textElement>                    <text><![CDATA[Net]]></text>                </staticText>                <staticText>                    <reportElement uuid="e15d0087-175b-4eaa-a574-cb55beb818b7" x="617" y="39" width="65" height="20"/>                    <textElement textAlignment="Right">                        <font size="11" isBold="true"/>                    </textElement>                    <text><![CDATA[Price]]></text>                </staticText>                <staticText>                    <reportElement uuid="dba4e303-93da-421e-b12e-313d771b3560" x="687" y="39" width="65" height="20"/>                    <textElement textAlignment="Right">                        <font size="11" isBold="true"/>                    </textElement>                    <text><![CDATA[Ext.]]></text>                </staticText>            </band>        </groupHeader>        <groupFooter>            <band height="9">                <line>                    <reportElement uuid="5f31c7b7-5d1d-402c-9cc5-b1ad239c8c37" x="1" y="5" width="100" height="1">                        <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>                        <propertyExpression name="net.sf.jasperreports.export.xls.sheet.name"><![CDATA[$F{CATEGORY_NAME}]]></propertyExpression>                    </reportElement>                    <graphicElement>                        <pen lineWidth="0.0"/>                    </graphicElement>                </line>            </band>        </groupFooter>    </group>    <group name="QuarterGroup">        <groupExpression><![CDATA[$F{QTR}]]></groupExpression>        <groupHeader>            <band height="34">                <textField>                    <reportElement uuid="d3e8f4ec-0aa1-49a2-9de2-1aad34c7bb7d" x="1" y="7" width="100" height="20"/>                    <textElement>                        <font size="11" isBold="true"/>                    </textElement>                    <textFieldExpression><![CDATA[""+$F{QTR}]]></textFieldExpression>                </textField>            </band>        </groupHeader>        <groupFooter>            <band height="50"/>        </groupFooter>    </group>    <background>        <band splitType="Stretch"/>    </background>    <pageHeader>        <band height="35" splitType="Stretch"/>    </pageHeader>    <detail>        <band height="20" splitType="Stretch">            <textField>                <reportElement uuid="535f2fe3-96d5-47b9-917d-8ff850c4ad02" x="23" y="0" width="62" height="20"/>                <textElement>                    <font size="9"/>                </textElement>                <textFieldExpression><![CDATA[$F{SHIPPED_DATE}]]></textFieldExpression>            </textField>            <textField>                <reportElement uuid="a2597712-bb9a-45ee-b993-3dbb4911a0e0" x="90" y="0" width="141" height="20"/>                <textElement>                    <font size="9"/>                </textElement>                <textFieldExpression><![CDATA[$F{CUST_NAME}]]></textFieldExpression>            </textField>            <textField>                <reportElement uuid="1a23c702-1fb7-4845-b231-ef2d75a97a16" x="407" y="0" width="65" height="20"/>                <textElement textAlignment="Right">                    <font size="9"/>                </textElement>                <textFieldExpression><![CDATA[$F{GROSS_WGT}]]></textFieldExpression>            </textField>            <textField>                <reportElement uuid="3160df9c-0680-433b-914c-690dda27d06f" x="477" y="0" width="65" height="20"/>                <textElement textAlignment="Right">                    <font size="9"/>                </textElement>                <textFieldExpression><![CDATA[$F{TARE_WGT}]]></textFieldExpression>            </textField>            <textField>                <reportElement uuid="d8de4d49-c183-416d-9e10-9ce64b910f3a" x="547" y="0" width="65" height="20"/>                <textElement textAlignment="Right">                    <font size="9"/>                </textElement>                <textFieldExpression><![CDATA[$F{NET_WGT}]]></textFieldExpression>            </textField>            <textField>                <reportElement uuid="bd238075-272f-42bd-b813-78dd4f3fe5ba" x="617" y="0" width="65" height="20"/>                <textElement textAlignment="Right">                    <font size="9"/>                </textElement>                <textFieldExpression><![CDATA[$F{PRICE}]]></textFieldExpression>            </textField>            <textField>                <reportElement uuid="0bea29fb-f4a3-425f-b914-b661145d5c5d" x="243" y="0" width="153" height="20"/>                <textElement>                    <font size="9"/>                </textElement>                <textFieldExpression><![CDATA[$F{ITEM_NAME}]]></textFieldExpression>            </textField>            <textField>                <reportElement uuid="b3707e2a-3dbe-4357-85de-d2b80eb70c68" x="687" y="0" width="65" height="20"/>                <textElement textAlignment="Right">                    <font size="9"/>                </textElement>                <textFieldExpression><![CDATA[$F{TOTAL_AMT}]]></textFieldExpression>            </textField>        </band>    </detail></jasperReport>[/code]

 

 

Can anyone see the problem here?  I am at a loss.

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

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