Jasper Reports Excel Output Problems

Question

I am new to Jasper Reports and iReports that I am using to design the reports. I am currently designing a report in iReport and want to export it in excel (xls). Here is the screen shot of what I want to design and the second screen is what I am getting.

Shot at 2012-08-04

Here is what I have managed to get. As you can see there are many problems in the output.

• Column A is missing
• There are some problems with column D and E and Column C is also invisible.
• I dont know how to add blank rows in my design from IReports

Shot at 2012-08-04

Here is the source to what I have done so far:

    <?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="report1" language="groovy" pageWidth="792" pageHeight="612" orientation="Landscape" columnWidth="792" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="1e5ee76a-b072-4f31-a1ce-d0c921d2ce55">
    <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.remove.empty.space.between.columns" value="true"/>
    <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
    <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="21" splitType="Stretch">
            <staticText>
                <reportElement uuid="557d6e9d-b389-4710-a66f-f15678ebb003" x="1" y="1" width="119" height="20"/>
                <textElement>
                    <font size="14" isBold="true"/>
                </textElement>
                <text><![CDATA[Sample Heading]]></text>
            </staticText>
        </band>
    </title>
    <pageHeader>
        <band height="71" splitType="Stretch">
            <staticText>
                <reportElement uuid="3d8bfb44-a4ce-409f-a626-4bdba125b0a4" x="0" y="11" width="100" height="20"/>
                <textElement verticalAlignment="Middle"/>
                <text><![CDATA[Start Date]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="ee347fa8-037a-4fe1-8de2-98867d4903a3" x="0" y="31" width="100" height="20"/>
                <textElement verticalAlignment="Middle"/>
                <text><![CDATA[Start Date]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="beaa366e-2976-4b46-86ee-55af14249675" x="0" y="51" width="100" height="20"/>
                <textElement verticalAlignment="Middle"/>
                <text><![CDATA[Start Date]]></text>
            </staticText>
            <textField pattern="dd-MMM-yy">
                <reportElement uuid="4f4124a7-5071-4ced-ba92-c39c6f6ffc54" x="100" y="11" width="100" height="20"/>
                <textElement verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
            </textField>
            <textField pattern="dd-MMM-yy">
                <reportElement uuid="9492ff27-6c11-417f-8ae9-43dddfcda405" x="100" y="31" width="100" height="20"/>
                <textElement verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
            </textField>
            <textField pattern="dd-MMM-yy">
                <reportElement uuid="b8baea82-84c4-42fa-bccd-62abc96eeded" x="100" y="51" width="100" height="20"/>
                <textElement verticalAlignment="Middle"/>
                <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
            </textField>
        </band>
    </pageHeader>
    <columnHeader>
        <band height="69" splitType="Stretch">
            <staticText>
                <reportElement uuid="1c5d41a5-a86b-4cd0-bac8-19950c3eb5b3" x="0" y="49" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[Heading]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="6d798309-0e10-4c11-8642-53edd66f8ed0" x="100" y="49" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[Heading]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="7ca5ae53-47e2-46f9-90c6-2d8f5d66dc5e" x="200" y="49" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[Heading]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="bae9670f-10fa-4932-9e1b-00c8e38cd009" x="300" y="49" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[Heading]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="bcf09004-a0ca-479f-9d6d-fe2aab932452" x="400" y="49" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font size="12" isBold="true"/>
                </textElement>
                <text><![CDATA[Heading]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="151" splitType="Stretch">
            <staticText>
                <reportElement uuid="418012e1-fbc2-4cb9-bf7f-2740061bbfd1" x="1" y="0" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Data]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="9b6a2c68-6327-4a8a-940f-41f5be8bc7ca" x="100" y="0" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Data]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="7e7ddc5f-31e7-4b76-bbd7-a6f0a2c11169" x="200" y="0" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Data]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="5f8c4ae6-d2c0-4ded-9e3e-8f1a69995043" x="300" y="0" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Data]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="f8f51e3b-5dcc-43f0-9f56-00a1bc703ad1" x="400" y="0" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Data]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="418012e1-fbc2-4cb9-bf7f-2740061bbfd1" x="0" y="20" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Data]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="418012e1-fbc2-4cb9-bf7f-2740061bbfd1" x="100" y="20" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Data]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="418012e1-fbc2-4cb9-bf7f-2740061bbfd1" x="200" y="20" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Data]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="418012e1-fbc2-4cb9-bf7f-2740061bbfd1" x="300" y="20" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Data]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="418012e1-fbc2-4cb9-bf7f-2740061bbfd1" x="400" y="20" width="100" height="20"/>
                <textElement textAlignment="Center" verticalAlignment="Middle"/>
                <text><![CDATA[Data]]></text>
            </staticText>
        </band>
    </detail>
    <columnFooter>
        <band height="45" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="54" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="42" splitType="Stretch"/>
    </summary>
</jasperReport>

Any help in this matter would be appreciated alot. Thanks a bunch!!

Answer 1

Every single time I had problems with that it was because the fields weren't properly aligned. So make sure the fields that should be vertically aligned have the same x value and have the same widths.

Your first Data field is located at x=1 (the other fields on that column are at x=0). Maybe this alone won't solve all the problems, but I am pretty sure the first Data will start to appear in the Excel output.

Answer 2

I ran into a similar issue with Excel exports. I had text fields in my title and report header bands that did not extend the whole width of the report, and some where the left position was not zero (that was intentional on my part, for design purposes). iReport then merged columns to handle this.

To get my Excel exports to look and behave the way I wanted, I had to make my text fields in my title and report header bands the full width of the report and make sure they were aligned to 0 at the left. You can add padding to text fields to position text within them. Anything that happens prior to your detail band needs to be wider than the output in your detail band.

Answer 3

I tried that before, it is because the layout setting doesnt match. You need to make sure the left starting position of each column a the same, and so as their width. If there are merged cells, the width of it need to be exact number of the total of the merged columns width.