Blank rows in an excel ireport exported format

[rudy]

 

Dear All,

I am having blank rows in all my excel format exported reports (Jasper Ireport)

any way to remove those blank rows ??

Thank you

Best Regards,

Rudy

[svenn]

Could you post an example of what is happening.

[gabrielinux]

If you check the box for "suppress when blank" of *every* field in the details section, that would make rows with null values not show up. You will also want to check the height of the bands in your report. If they are too long compared to the fields inside, the exporter might convert them to empty rows in Excel.

[rudy]

dear sir,

the "remove when balnk" didn't work..(right click on all fields , remove line when blank under the common task bar...)

any help please...

Best regards

rudy

[rudy]

dear sir

this is an excel printscreen of the problem that am facing...

if u notice , am having blank rows (between 2 data rows , 2 blank unused rows).....

thanks

regards,

rudy

[kchaudhry]

Hi Rudy,

From looking at the screen shot it seems like you have some extra (white) space in your details band which is causing the extra row to appear in excel. In order to avoid this simply select all the fields in the details band and then click on the shorcut "enlarge to band height". Now run the report again and hopefuly you wont see the extra rows.

[svenn]

The key to Excel output is to make sure that all the items in the detail section line up horizontally and that each items on the detail line lines up vertically with its matching column header. If anything is off by even one pixel you will end up with extra spaces.

[prasun]

Hi,

I am facing kind of a similar issue with spaces after all the data has been displayed in the detail band.

Pls help.

I have attached the output xls and also the jrxml.

 

Thanks

Prasun

Code:

<?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="DebitAdvice_Spec_Stp_Excel" pageWidth="923" pageHeight="802" columnWidth="923" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0">	<field name="chargeCodeName" class="java.lang.String"/>	<field name="currency" class="java.lang.String"/>	<field name="pricePerItem" class="java.lang.Double"/>	<field name="txnDate" class="java.util.Date"/>	<field name="originalReference" class="java.lang.String"/>	<field name="amount" class="java.lang.Double"/>	<field name="stpCompliant" class="java.lang.String"/>	<field name="sendingBic" class="java.lang.String"/>	<field name="site" class="java.lang.String"/>	<field name="accountNumber" class="java.lang.String"/>	<background>		<band splitType="Stretch"/>	</background>	<title>		<band splitType="Stretch"/>	</title>	<pageHeader>		<band splitType="Stretch"/>	</pageHeader>	<columnHeader>		<band height="21" splitType="Stretch">			<staticText>				<reportElement x="231" y="0" width="66" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font fontName="SansSerif" size="9" isBold="true" isItalic="true"/>				</textElement>				<text><![CDATA[Date]]></text>			</staticText>			<staticText>				<reportElement x="572" y="1" width="248" height="19"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font fontName="SansSerif" size="9" isBold="true" isItalic="true"/>				</textElement>				<text><![CDATA[Product/Option]]></text>			</staticText>			<staticText>				<reportElement x="820" y="0" width="73" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font fontName="SansSerif" size="9" isBold="true" isItalic="true"/>				</textElement>				<text><![CDATA[Price/Item]]></text>			</staticText>			<staticText>				<reportElement x="424" y="0" width="60" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font fontName="SansSerif" size="9" isBold="true" isItalic="true"/>				</textElement>				<text><![CDATA[Currency]]></text>			</staticText>			<staticText>				<reportElement x="297" y="0" width="127" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font fontName="SansSerif" size="9" isBold="true" isItalic="true"/>				</textElement>				<text><![CDATA[Original Reference]]></text>			</staticText>			<staticText>				<reportElement x="484" y="1" width="88" height="19"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font fontName="SansSerif" size="9" isBold="true" isItalic="true"/>				</textElement>				<text><![CDATA[Orig. Amount]]></text>			</staticText>			<staticText>				<reportElement x="893" y="0" width="29" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font fontName="SansSerif" size="9" isBold="true" isItalic="true"/>				</textElement>				<text><![CDATA[sTP]]></text>			</staticText>			<staticText>				<reportElement x="168" y="0" width="63" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font fontName="SansSerif" size="9" isBold="true" isItalic="true"/>				</textElement>				<text><![CDATA[sEB Site]]></text>			</staticText>			<staticText>				<reportElement x="68" y="0" width="100" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font fontName="SansSerif" size="9" isBold="true" isItalic="true"/>				</textElement>				<text><![CDATA[Account number]]></text>			</staticText>			<staticText>				<reportElement x="0" y="0" width="68" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font fontName="SansSerif" size="9" isBold="true" isItalic="true"/>				</textElement>				<text><![CDATA[sending BIC]]></text>			</staticText>		</band>	</columnHeader>	<detail>		<band height="20" splitType="Stretch">			<textField>				<reportElement x="231" y="0" width="66" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font size="9"/>				</textElement>				<textFieldExpression class="java.util.Date"><![CDATA[$F{txnDate}]]></textFieldExpression>			</textField>			<textField>				<reportElement x="297" y="0" width="127" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font size="9"/>				</textElement>				<textFieldExpression class="java.lang.String"><![CDATA[$F{originalReference}]]></textFieldExpression>			</textField>			<textField>				<reportElement x="820" y="0" width="73" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font size="9"/>				</textElement>				<textFieldExpression class="java.lang.Double"><![CDATA[$F{pricePerItem}]]></textFieldExpression>			</textField>			<textField>				<reportElement x="424" y="0" width="60" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font size="9"/>				</textElement>				<textFieldExpression class="java.lang.String"><![CDATA[$F{currency}]]></textFieldExpression>			</textField>			<textField>				<reportElement x="484" y="0" width="88" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font size="9"/>				</textElement>				<textFieldExpression class="java.lang.Double"><![CDATA[$F{amount}]]></textFieldExpression>			</textField>			<textField>				<reportElement x="572" y="0" width="248" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font size="9"/>				</textElement>				<textFieldExpression class="java.lang.String"><![CDATA[$F{chargeCodeName}]]></textFieldExpression>			</textField>			<textField>				<reportElement x="893" y="0" width="29" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font size="9"/>				</textElement>				<textFieldExpression class="java.lang.String"><![CDATA[$F{stpCompliant}]]></textFieldExpression>			</textField>			<textField>				<reportElement x="168" y="0" width="63" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font size="9"/>				</textElement>				<textFieldExpression class="java.lang.String"><![CDATA[$F{site}]]></textFieldExpression>			</textField>			<textField>				<reportElement x="68" y="0" width="100" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font size="9"/>				</textElement>				<textFieldExpression class="java.lang.String"><![CDATA[$F{accountNumber}]]></textFieldExpression>			</textField>			<textField>				<reportElement x="0" y="0" width="68" height="20"/>				<box>					<bottomPen lineWidth="0.5"/>					<rightPen lineWidth="0.5"/>				</box>				<textElement textAlignment="Center" verticalAlignment="Middle">					<font size="9"/>				</textElement>				<textFieldExpression class="java.lang.String"><![CDATA[$F{sendingBic}]]></textFieldExpression>			</textField>		</band>	</detail>	<columnFooter>		<band splitType="Stretch"/>	</columnFooter>	<pageFooter>		<band splitType="Stretch"/>	</pageFooter>	<summary>		<band splitType="Stretch"/>	</summary></jasperReport>

[prasun]

I got the solution for the above problem.

must define a parameter through your java code as below: -

 

exporterXLS.setParameter(JRXlsAbstractExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);

 

Where exporterXLS is your JRXlsExporter object.

[kusi]

After doing the following it was working for me properly (no more empty rows in excel export):

1) Set param as mentioned before: exporterXLS.setParameter(JRXlsAbstractExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);

 

2) Making the heigh of all fields exactly the same as the band they are located on

 

3) Setting the property "Stretch Type" for all fields on the band to the value "Relative to Band Height" for all fields on the band.