Jasper report excel: How to hide column if no data present (but with title)

Question

I have a report with a title and a couple of columns. When report is generated, and no data is present column must be removed/hidden, but is it not. I am sure that's because of title, because when I removed a title, column is also removed during generation (when it's empty). Here is my report:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 8.1.0.final using JasperReports Library version 6.20.0-2bc7ab61c56f459e8176eb05c7705e145cd400ad  -->
<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="account_statement" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="0a49bb24-5523-475c-a23b-7afd45c4ce2d">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="test adapter"/>
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <property name="net.sf.jasperreports.export.xls.cell.hidden" value="true"/>
    <queryString>
        <![CDATA[]]>
    </queryString>
    <field name="number" class=“java.lang.String”/>
    <field name="address" class=“java.lang.String”/>
    <field name="end_date" class=“java.sql.Timestamp”/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="55" splitType="Stretch">
            <textField>
                <reportElement x="0" y="5" width="557" height="20" uuid="5edad836-5c99-4a88-9f0c-29a85c0e0902">
                    <property name="net.sf.jasperreports.style.isBold" value="true"/>
                </reportElement>
                <textFieldExpression><![CDATA["Generate Date: "]]></textFieldExpression>
            </textField>
        </band>
    </title>
    <pageHeader>
        <band height="86" splitType="Stretch">
            <printWhenExpression><![CDATA[$V{PAGE_NUMBER} <=1]]></printWhenExpression>
        </band>
    </pageHeader>
    <columnHeader>
        <band height="68" splitType="Stretch">
            <printWhenExpression><![CDATA[$V{PAGE_NUMBER} <=1]]></printWhenExpression>
            <staticText>
                <reportElement x="0" y="37" width="40" height="31" isRemoveLineWhenBlank="true" uuid="64385272-6745-4b8d-9d1a-1b386bf404d8">
                    <property name="net.sf.jasperreports.style.isBold" value="true"/>
                </reportElement>
                <text><![CDATA[№]]></text>
            </staticText>
            <staticText>
                <reportElement x="40" y="37" width="160" height="31" isRemoveLineWhenBlank="true" uuid="184d1c1d-1a5c-4439-ba35-dc0276530450">
                    <property name="net.sf.jasperreports.style.isBold" value="true"/>
                </reportElement>
                <text><![CDATA[Аddress]]></text>
            </staticText>
            <staticText>
                <reportElement stretchType="ElementGroupHeight" mode="Opaque" x="200" y="37" width="100" height="31" isRemoveLineWhenBlank="true" uuid="62978778-75d7-4f1b-90ec-a6c85c910e48">
                    <property name="net.sf.jasperreports.style.isBold" value="true"/>
                    <property name="net.sf.jasperreports.export.xls.cell.hidden" value="true"/>
                    <printWhenExpression><![CDATA[$F{end_date} != null]]></printWhenExpression>
                </reportElement>
                <text><![CDATA[End date]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="125" splitType="Stretch">
            <textField>
                <reportElement x="40" y="0" width="160" height="30" isRemoveLineWhenBlank="true" uuid="5fcebbb2-1e7c-49f7-8f60-7571adea4afd"/>
                <textFieldExpression><![CDATA[$F{address} + " " + $F{number}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement mode="Opaque" x="200" y="0" width="100" height="30" isRemoveLineWhenBlank="true" uuid="a471c37f-c2e1-4e79-b27d-0df514c3993b">
                    <property name="net.sf.jasperreports.export.xls.cell.hidden" value="true"/>
                    <printWhenExpression><![CDATA[$F{end_date} != null]]></printWhenExpression>
                </reportElement>
                <textFieldExpression><![CDATA[$F{end_date}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="0" y="0" width="40" height="30" isRemoveLineWhenBlank="true" uuid="6a2ce9df-1991-4106-a32d-b0bbe2a8c1e1"/>
                <textFieldExpression><![CDATA[$V{COLUMN_COUNT}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

I am using <printWhenExpression><![CDATA[$F{end_date} != null]]></printWhenExpression>, but with title it doesn't help. Could you pls help me with it? Any idea?

Answer 1

When working with Excel exports it is generally a good idea to make sure that your elements in your Title and Column Header band line up with an element in your detail.

If you adjust the 'Generated Date:' in the Title to end up in line with the end of 'Address' column hiding the 'End Date' column should work as expected. (Width of 'Generated Date' as 200px, same as 'No' + 'Address')

I'd also recommend to use the following properties on your report:

<property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/>
<property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/>
<property name="net.sf.jasperreports.export.xls.white.page.background" value="false"/>

Further reading: http://jasperreports.sourceforge.net/sample.reference/xlsfeatures/