Problem with dynamic excel worksheet names

0

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_AMT
 
 
from PACKSLIP_H ph
join 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>

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

tim_18's picture
2
Joined: Mar 21 2014 - 1:30pm
Last seen: 5 years 5 months ago

0 Answers:

No answers yet
Feedback