Extra Column added when exported Xls

[2006 IR Open Dicussion]

By: subesingh - subesingh1

Extra Column added when exported Xls

2006-03-28 07:01

In my jasper report design I have one main report and two subreports . one subreport print the label and second subreport print the value corresponding to each label.Both subreport are populated horizontally. Both the subreport are added in main report.When I export the report in xls one extra column added after each column. and one extra row is added after each label row.

 

I have use the following code

 

 

 

} else if (TASK_XLS.equals(format)) {

System.out.println("<<<<<<<<<<<<<<<< CREATE REPORT IN EXCEL FORM >>>>>>>>>> ");

File sourceFile = new File(templateName);

JasperPrint jasperPrint = (JasperPrint) JRLoader.loadObject(sourceFile);

File destFile = new File(sourceFile.getParent(),jasperPrint.getName() + ".xls");

// JRXlsExporter exporterXLS = new JRXlsExporter();

// exporterXLS.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);

// exporterXLS.setParameter(JRExporterParameter.OUTPUT_FILE_NAME,destFile.toString());

// exporterXLS.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET,Boolean.TRUE);

// exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);

// exporterXLS.setParameter(JRXlsExporterParameter.IS_AUTO_DETECT_CELL_TYPE, Boolean.FALSE);

// exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.FALSE);

JExcelApiExporter exporterXLS = new JExcelApiExporter();

exporterXLS.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);

exporterXLS.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, destFile.toString());

exporterXLS.setParameter(JExcelApiExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.TRUE);

exporterXLS.setParameter(JExcelApiExporterParameter.IS_WHITE_PAGE_BACKGROUND,Boolean.FALSE);

 

exporterXLS.exportReport();

} else if (TASK_CSV.equals(format)) {

 

 

 

 

By: legion - cajinadiaz

RE: Extra Column added when exported Xls

2006-03-28 07:09

Well.. I have a similar problem exporting a report in XLS format..

My solution is the careful in the design of the report.. Look this :

http://jasperreports.sourceforge.net/tips.tricks.html#friendly

 

Good look..

 

 

 

 

By: subesingh - subesingh1

RE: Extra Column added when exported Xls

2006-03-30 23:17

A single blank row has been added after each row when I populate the report and single blank column has been added after value of each column when I export the report in XML.In text element property I have set lineSpacing="Single".Reprt structure of all three report is given below.

 

MASTER REPORT

-------------

 

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

<!-- Created with iReport - A designer for JasperReports -->

<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">

<jasperReport

name="TableTemplate"

columnCount="1"

printOrder="Vertical"

orientation="Portrait"

pageWidth="595"

pageHeight="842"

columnWidth="100"

columnSpacing="0"

leftMargin="0"

rightMargin="0"

topMargin="0"

bottomMargin="0"

whenNoDataType="NoPages"

isTitleNewPage="false"

isSummaryNewPage="false">

<property name="ireport.scriptlethandling" value="0" />

<property name="ireport.encoding" value="UTF-8" />

<import value="java.util.*" />

<import value="net.sf.jasperreports.engine.*" />

<import value="net.sf.jasperreports.engine.data.*" />

 

<parameter name="attributeDataSource" isForPrompting="false" class="net.sf.jasperreports.engine.JRDataSource"/>

 

<background>

<band height="0" isSplitAllowed="false" >

</band>

</background>

<title>

<band height="0" isSplitAllowed="false" >

</band>

</title>

<pageHeader>

<band height="0" isSplitAllowed="false" >

</band>

</pageHeader>

<columnHeader>

<band height="13" isSplitAllowed="false" >

<subreport isUsingCache="true">

<reportElement

mode="Opaque"

x="0"

y="0"

width="595"

height="13"

forecolor="#000000"

backcolor="#FFFFFF"

key="subreport-1"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<dataSourceExpression><![CDATA[$P{attributeDataSource}]]></dataSourceExpression>

<subreportExpression class="java.lang.String"><![CDATA[getClass().getResource("/com/daffodilwoods/framework/jasperReports/reports").getPath()+File.separator+"TableLabelTemplate.jasper"]]></subreportExpression>

</subreport>

</band>

</columnHeader>

<detail>

<band height="13" isSplitAllowed="false" >

<subreport isUsingCache="true">

<reportElement

mode="Opaque"

x="0"

y="0"

width="595"

height="13"

forecolor="#000000"

backcolor="#FFFFFF"

key="subreport-2"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<dataSourceExpression><![CDATA[$P{attributeDataSource}]]></dataSourceExpression>

<subreportExpression class="java.lang.String"><![CDATA[getClass().getResource("/com/daffodilwoods/framework/jasperReports/reports").getPath()+File.separator+"TableDataTemplate.jasper"]]></subreportExpression>

</subreport>

</band>

</detail>

<columnFooter>

<band height="0" isSplitAllowed="false" >

</band>

</columnFooter>

<pageFooter>

<band height="0" isSplitAllowed="false" >

</band>

</pageFooter>

<summary>

<band height="0" isSplitAllowed="false" >

</band>

</summary>

</jasperReport>

 

----------------------------

SUBREPORT ONE

----------------------------

 

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

<!-- Created with iReport - A designer for JasperReports -->

<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">

<jasperReport

name="TableDataTemplate"

columnCount="5"

printOrder="Horizontal"

orientation="Portrait"

pageWidth="595"

pageHeight="842"

columnWidth="100"

columnSpacing="0"

leftMargin="0"

rightMargin="0"

topMargin="0"

bottomMargin="0"

whenNoDataType="NoPages"

isTitleNewPage="false"

isSummaryNewPage="false">

<property name="ireport.scriptlethandling" value="0" />

<property name="ireport.encoding" value="UTF-8" />

<import value="java.util.*" />

<import value="net.sf.jasperreports.engine.*" />

<import value="net.sf.jasperreports.engine.data.*" />

 

 

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

 

<background>

<band height="0" isSplitAllowed="false" >

</band>

</background>

<title>

<band height="0" isSplitAllowed="false" >

</band>

</title>

<pageHeader>

<band height="0" isSplitAllowed="false" >

</band>

</pageHeader>

<columnHeader>

<band height="0" isSplitAllowed="false" >

</band>

</columnHeader>

<detail>

<band height="13" isSplitAllowed="false" >

<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >

<reportElement

mode="Transparent"

x="0"

y="0"

width="89"

height="13"

forecolor="#000000"

backcolor="#FFFFFF"

key="textField-1"

stretchType="NoStretch"

positionType="Float"

isPrintRepeatedValues="false"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

<textElement textAlignment="Left" verticalAlignment="Top" rotation="None" lineSpacing="Single">

<font fontName="Arial" pdfFontName="Helvetica" size="10" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="Cp1252" isStrikeThrough="false" />

</textElement>

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

</textField>

</band>

</detail>

<columnFooter>

<band height="0" isSplitAllowed="false" >

</band>

</columnFooter>

<pageFooter>

<band height="0" isSplitAllowed="false" >

</band>

</pageFooter>

<summary>

<band height="0" isSplitAllowed="false" >

</band>

</summary>

</jasperReport>

 

 

--------------------------

SUBREPORT 2

--------------------------

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

<!-- Created with iReport - A designer for JasperReports -->

<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">

<jasperReport

name="TableLabelTemplatel"

columnCount="5"

printOrder="Horizontal"

orientation="Portrait"

pageWidth="595"

pageHeight="842"

columnWidth="100"

columnSpacing="0"

leftMargin="0"

rightMargin="0"

topMargin="0"

bottomMargin="0"

whenNoDataType="NoPages"

isTitleNewPage="false"

isSummaryNewPage="false">

<property name="ireport.scriptlethandling" value="0" />

<property name="ireport.encoding" value="UTF-8" />

<import value="java.util.*" />

<import value="net.sf.jasperreports.engine.*" />

<import value="net.sf.jasperreports.engine.data.*" />

 

 

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

 

<background>

<band height="0" isSplitAllowed="false" >

</band>

</background>

<title>

<band height="0" isSplitAllowed="false" >

</band>

</title>

<pageHeader>

<band height="0" isSplitAllowed="false" >

</band>

</pageHeader>

<columnHeader>

<band height="0" isSplitAllowed="false" >

</band>

</columnHeader>

<detail>

<band height="13" isSplitAllowed="false" >

<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="true" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >

<reportElement

mode="Transparent"

x="0"

y="0"

width="100"

height="13"

forecolor="#000000"

backcolor="#FFFFFF"

key="textField-1"

stretchType="NoStretch"

positionType="Float"

isPrintRepeatedValues="false"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

<textElement textAlignment="Left" verticalAlignment="Top" rotation="None" lineSpacing="Single">

<font fontName="Arial" pdfFontName="Helvetica" size="10" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="Cp1252" isStrikeThrough="false" />

</textElement>

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

</textField>

</band>

</detail>

<columnFooter>

<band height="0" isSplitAllowed="false" >

</band>

</columnFooter>

<pageFooter>

<band height="0" isSplitAllowed="false" >

</band>

</pageFooter>

<summary>

<band height="0" isSplitAllowed="false" >

</band>

</summary>

</jasperReport>

[montero]

The reason why you have any extra column is because you have spaces between your fields and text boxes on your report layout. Bump them together or increase the width of the boxes themselves until they butt eachother. I had this same problem and it worked for me. Like i am displaying below...try it..it will work.

 

_______________________________

_________|__________|__________|

Post edited by: rubedogg04, at: 2007/03/22 18:04

[kcsrinath]

Thanks Montero.. It is working perfectly

Also if there are header values like date of the report, Report title etc... That has to be alligned with any one of the column... Else it is creating an extra space..