empty rows and columns in excel

[adamzrk]

Hi

 

I've got the problem with exporting report to excel file. In the exported file between rows are added additional empty rows and columns, which I don't want to. Header are not in the one column - how to do that columns are properly stretched? I want just a pretty xls file with one row after another without empty rows.

How to do that? In my java code I export that way:

 

JExcelApiExporter xlsExporter = new JExcelApiExporter();

xlsExporter.setParameter(JRExporterParameter.

JASPER_PRINT, jasperPrint);

xlsExporter.setParameter(JRExporterParameter.OUTPUT_STREAM,

output);

xlsExporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);

xlsExporter.setParameter(JRXlsExporterParameter.IS_FONT_SIZE_FIX_ENABLED,Boolean.TRUE);

xlsExporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.FALSE);

xlsExporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);

 

xlsExporter.exportReport();

 

 

Regards

Adam

[rsilverns.sympatico.ca]

Adam,

 

The algorithm for positioning elements inside of an XLS file is pretty strict. You have to be cautious when designing your report that elements line up properly or they will be seperated by these rows and columns that you are seeing.

 

I would check the layout of your elements and see where the items can be more closely aligned. This may help you remove some of these extra rows/columns.

 

HTH,

Robin

[adamzrk]

Thanks for the answer

Here's my jrxml file, i really don't know what to do :

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE jasperReport PUBLIC "-//JasperReports//DTD Report Design//EN"

"http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">

<jasperReport name="aaa"

pageWidth="695" pageHeight="842" columnWidth="675" columnSpacing="0"

leftMargin="10" rightMargin="10" topMargin="0" bottomMargin="0"

columnCount="1" whenNoDataType="AllSectionsNoDetail" isFloatColumnFooter="false"

isTitleNewPage="false" isSummaryNewPage="false">

<style name="Arial_Normal" isDefault="true" fontName="Arial"

fontSize="12" isBold="false" isItalic="false" isUnderline="false"

isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1250"

isPdfEmbedded="false" />

<style name="Arial_Bold" isDefault="false" fontName="Arial"

fontSize="12" isBold="true" isItalic="false" isUnderline="false"

isStrikeThrough="false" pdfFontName="Helvetica-Bold"

pdfEncoding="Cp1250" isPdfEmbedded="false" hAlign="Center" />

<style name="Arial_Italic" isDefault="false" fontName="Arial"

fontSize="12" isBold="false" isItalic="true" isUnderline="false"

isStrikeThrough="false" pdfFontName="Helvetica-Oblique"

pdfEncoding="Cp1250" isPdfEmbedded="false" />

<style name="pageHeader" isDefault="false" style="Arial_Bold"

forecolor="#ffffff" backcolor="#333333" />

 

<parameter name="ReportTitle" class="java.lang.String" />

<parameter name="datesSubtitle" class="java.lang.String" />

 

<field name="a" class="java.lang.String" />

<field name="b" class="java.util.Date" />

<field name="c" class="java.util.Date" />

<field name="d" class="java.lang.Integer" />

<field name="e" class="java.lang.String" />

<field name="f" class="java.lang.Double" />

 

<title >

<band height="90" isSplitAllowed="true" >

<frame>

<reportElement x="0" y="0" width="675" height="90"

backcolor="#E0E0E0" mode="Transparent" />

<box topBorder="1Point" />

<textField isBlankWhenNull="true" isStretchWithOverflow="true" >

<reportElement x="50" y="5" width="625" mode="Transparent"

height="45" />

<textElement textAlignment="Right" lineSpacing="1_1_2" >

<font size="20" />

</textElement>

<textFieldExpression class="java.lang.String" >

<![CDATA[$P{ReportTitle}]]>

</textFieldExpression>

 

</textField>

<textField isBlankWhenNull="true" isStretchWithOverflow="true">

<reportElement width="400" y="50" x="275" height="30" mode="Transparent" />

<textElement textAlignment="Right" lineSpacing="Single" >

<font size="15"/>

</textElement>

<textFieldExpression class="java.lang.String" >

<![CDATA[$P{datesSubtitle}]]>

</textFieldExpression>

</textField>

</frame>

</band>

</title>

 

<pageHeader >

<band height="20" isSplitAllowed="true" >

<!--<rectangle>

<reportElement x="0" y="5" width="515" height="15" forecolor="#333333" backcolor="#333333"/>

<graphicElement/>

</rectangle>

--><staticText>

<reportElement x="0" y="5" width="161" height="15" forecolor="#ffffff" backcolor="#333333" style="Arial_Bold" mode="Transparent"/>

<textElement textAlignment="Center"/>

<text><![CDATA[title]]></text>

</staticText>

<staticText>

<reportElement x="161" y="5" width="100" height="15" forecolor="#ffffff" backcolor="#333333" style="Arial_Bold" mode="Transparent"/>

<text><![CDATA[date1]]></text>

</staticText>

<staticText>

<reportElement x="261" y="5" width="100" height="15" forecolor="#ffffff" backcolor="#333333" style="Arial_Bold" mode="Transparent"/>

<text><![CDATA[date2]]></text>

</staticText>

<staticText>

<reportElement isPrintWhenDetailOverflows="true" x="361" y="5" width="80" height="15" forecolor="#ffffff" backcolor="#333333" style="Arial_Bold" mode="Transparent"/>

<textElement textAlignment="Center" />

<text><![CDATA[count]]></text>

</staticText>

<staticText>

<reportElement x="441" y="5" width="240" height="15" forecolor="#ffffff" backcolor="#333333" style="Arial_Bold" mode="Transparent"/>

<text><![CDATA[answers]]></text>

</staticText>

</band>

</pageHeader>

 

 

<detail>

<band height="20" isSplitAllowed="true" >

<textField isBlankWhenNull="true" isStretchWithOverflow="true" >

<reportElement x="0" y="4" width="161" height="15" mode="Transparent" />

<textElement textAlignment="Center"/>

<textFieldExpression class="java.lang.String"><![CDATA[$F{a}]]></textFieldExpression>

</textField>

<textField isBlankWhenNull="true" isStretchWithOverflow="true" >

<reportElement positionType="Float" x="161" y="4" width="100" height="15" mode="Transparent"/>

<textElement textAlignment="Center" />

<textFieldExpression class="java.lang.String" >new java.text.SimpleDateFormat("dd-MM-yyyy").format($F{b})</textFieldExpression>

</textField>

 

<textField isStretchWithOverflow="true">

<reportElement positionType="Float" x="261" y="4" width="100" height="15" mode="Transparent"/>

<textElement textAlignment="Center" />

<textFieldExpression class="java.lang.String">new java.text.SimpleDateFormat("dd-MM-yyyy").format($F{c})</textFieldExpression>

</textField>

<textField isStretchWithOverflow="true">

<reportElement positionType="Float" x="361" y="4" width="80" height="15" mode="Transparent"/>

<textElement textAlignment="Center" />

<textFieldExpression class="java.lang.Integer"><![CDATA[$F{d}]]></textFieldExpression>

</textField>

<textField isStretchWithOverflow="true">

<reportElement positionType="Float" x="441" y="4" width="240" height="15" mode="Transparent"/>

<textElement/>

<textFieldExpression class="java.lang.String"><![CDATA[$F{e}]]></textFieldExpression>

</textField>

</band>

</detail>

 

<pageFooter>

<band height="40">

<textField>

<reportElement x="200" y="20" width="80" height="15" mode="Transparent"/>

<textElement textAlignment="Right" />

<textFieldExpression class="java.lang.String"><![CDATA["page " + String.valueOf($V{PAGE_NUMBER}) + " of "]]></textFieldExpression>

</textField>

<textField evaluationTime="Report" isStretchWithOverflow="true">

<reportElement x="280" y="20" width="75" height="15" mode="Transparent"/>

<textElement/>

<textFieldExpression class="java.lang.String"><![CDATA[" " + String.valueOf($V{PAGE_NUMBER})]]></textFieldExpression>

</textField>

</band>

</pageFooter>

 

<summary>

 

</summary>

 

 

 

 

</jasperReport>

 

 

Regards

Adam