tim_18 Posted March 21, 2014 Share Posted March 21, 2014 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now