Jasper reports Excel output problems

Question

I am new to Jasper Reports and iReports, which I use to develop reports. I am currently developing a report in iReport and want to export it to excel (xls). Here is a screenshot of what I want to create, and the second screen is what I get.

Taken on 2012-08-04

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

• Column A is missing
• There are some problems with columns D and E, and column C is also invisible.
• I don’t know how to add blank lines to my design from IReports

Taken on 2012-08-04

Here is the source of what I have already done:

<?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 much appreciated. Thank you so much!!

Answer 1

Every time I had problems with this because the fields were not aligned correctly. Therefore, make sure that the fields that need to be aligned vertically have the same x value and the same width.

Your first Data field is located at x = 1 (the remaining fields of this column are at x = 0). Maybe this alone will not solve all the problems, but I am sure that the first line of Data will begin to appear in the output of Excel.

Answer 2

I had a similar problem with Excel export. I had text boxes in the title and the titles of the reports that did not extend the entire width of the report, and some where the left position was not zero (which was intentional for my part, for design purposes). Then iReport concatenates the columns to handle this.

In order for my Excel export to look and behave the way I wanted, I needed to make my text fields in my heading and tell the headings of the headers the entire width of the report and make sure that they coincided with 0 on the left. You can indent text fields to place text inside them. Everything that happens before your part group should be wider than the output in your part group.

Answer 3

I tried this before because the layout setting does not match. You must make sure that the left starting position of each column is the same, and so is their width. If there are merged cells, their width should be the exact number of the total width of the merged columns.

Answer 4

go to Tools -> Options -> Export Tools -> Excel -> tick 2nd, 3rd and 4th ...

Enjoy