Export to excel causes merged cells

[vinithav]

I'm exporting a jasper report to excel using - JRXlsExporter - which causes merged cells in the excel spreadsheet.

Have tried following with no luck.

1. jrxl.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS, Boolean.TRUE);

2. jrxl.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);

3. jrxl.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN, Boolean.TRUE);

[vinithav]

This is the report - 

 

<?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="OperatingStatementReport" pageWidth="595" pageHeight="842" columnWidth="535" leftMargin="50" rightMargin="10" topMargin="30" bottomMargin="20" isIgnorePagination="true" uuid="89b6ecb5-7c53-4338-b654-7cd2a4d6b027">

<property name="ireport.zoom" value="1.0"/>

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

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

<template><![CDATA["/Users/kevin/jasper/RootStyle.jrtx"]]></template>

<style name="column_detail_style" mode="Transparent" fill="Solid" isBlankWhenNull="false" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false">

<conditionalStyle>

<conditionExpression><![CDATA[$V{COLUMN_COUNT}.intValue() % 2 == 0 ? Boolean.TRUE : Boolean.FALSE]]></conditionExpression>

<style mode="Opaque" backcolor="#FFFFFF" isStrikeThrough="false"/>

</conditionalStyle>

</style>

<style name="account_type_title" style="text_field" fontSize="8">

<conditionalStyle>

<conditionExpression><![CDATA[$F{isTitle}]]></conditionExpression>

<style fontSize="10" isBold="true">

<paragraph leftIndent="1"/>

</style>

</conditionalStyle>

<conditionalStyle>

<conditionExpression><![CDATA[!$F{isTitle}]]></conditionExpression>

<style>

<paragraph leftIndent="10"/>

</style>

</conditionalStyle>

</style>

<style name="sum_underline_style" fill="Solid">

<conditionalStyle>

<conditionExpression><![CDATA[(new Boolean(!$F{accountType}.toUpperCase().contains("TOTAL") && !$F{accountType}.toUpperCase().contains("AS A RESULT OF OPERATIONS")) || ($F{curSum}.compareTo( java.math.BigDecimal.ZERO)==0 && $F{preSum}.compareTo( java.math.BigDecimal.ZERO)==0))]]></conditionExpression>

<style pattern="">

<pen lineWidth="0.0"/>

</style>

</conditionalStyle>

</style>

<style name="sum_underline_style_1" fill="Solid">

<conditionalStyle>

<conditionExpression><![CDATA[(new Boolean(!$F{accountType}.toUpperCase().contains("TOTAL") && !$F{accountType}.toUpperCase().contains("AS A RESULT OF OPERATIONS")) || ($F{curSum}.compareTo( java.math.BigDecimal.ZERO)==0 && $F{preSum}.compareTo( java.math.BigDecimal.ZERO)==0))]]></conditionExpression>

<style pattern="">

<pen lineWidth="0.0"/>

</style>

</conditionalStyle>

</style>

<style name="style_number_field" isDefault="true" style="number_field" pattern="#,##0.00;(#,##0.00)">

<conditionalStyle>

<conditionExpression><![CDATA[$P{SETTING_IS_ROUNDING}]]></conditionExpression>

<style pattern="#,##0;(#,##0)"/>

</conditionalStyle>

</style>

<parameter name="FUND_NAME" class="java.lang.String" isForPrompting="false">

<defaultValueExpression><![CDATA[new String("360 Report Sample Fund")]]></defaultValueExpression>

</parameter>

<parameter name="REPORT_DATE" class="java.util.Date" isForPrompting="false">

<defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression>

</parameter>

<parameter name="DRAFT" class="java.lang.Boolean" isForPrompting="false">

<defaultValueExpression><![CDATA[new Boolean(true)]]></defaultValueExpression>

</parameter>

<parameter name="BADGE_LOGO_INPUTSTREAM" class="java.io.InputStream" isForPrompting="false">

<defaultValueExpression><![CDATA[]]></defaultValueExpression>

</parameter>

<parameter name="FUND_CODE" class="java.lang.String" isForPrompting="false">

<defaultValueExpression><![CDATA[new String("Sample fund id")]]></defaultValueExpression>

</parameter>

<parameter name="TO_DATE" class="java.util.Date" isForPrompting="false">

<defaultValueExpression><![CDATA[new Date()]]></defaultValueExpression>

</parameter>

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

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

<parameter name="EXPORT_FORMAT" class="java.lang.String" isForPrompting="false">

<parameterDescription><![CDATA["PDF"/"WORD"/"EXCEL"]]></parameterDescription>

<defaultValueExpression><![CDATA["PDF"]]></defaultValueExpression>

</parameter>

<parameter name="TO_DATE_STR" class="java.lang.String" isForPrompting="false"/>

<parameter name="SETTING_IS_ROUNDING" class="java.lang.Boolean" isForPrompting="false">

<defaultValueExpression><![CDATA[false]]></defaultValueExpression>

</parameter>

<parameter name="disclaimer" class="java.lang.String" isForPrompting="false"/>

<queryString>

<![CDATA[]]>

</queryString>

<field name="accountType" class="java.lang.String">

<fieldDescription><![CDATA[accountType]]></fieldDescription>

</field>

<field name="curYear" class="java.lang.String">

<fieldDescription><![CDATA[curYear]]></fieldDescription>

</field>

<field name="preSum" class="java.math.BigDecimal">

<fieldDescription><![CDATA[preSum]]></fieldDescription>

</field>

<field name="isTitle" class="java.lang.Boolean">

<fieldDescription><![CDATA[isTitle]]></fieldDescription>

</field>

<field name="preYear" class="java.lang.String">

<fieldDescription><![CDATA[preYear]]></fieldDescription>

</field>

<field name="note" class="java.lang.String">

<fieldDescription><![CDATA[note]]></fieldDescription>

</field>

<field name="curSum" class="java.math.BigDecimal">

<fieldDescription><![CDATA[curSum]]></fieldDescription>

</field>

<background>

<band height="541" splitType="Stretch">

<staticText>

<reportElement style="watermark_field" x="457" y="-13" width="98" height="13" forecolor="#CCCCCC" uuid="d814c7c8-8372-4c33-94a7-da698402dc85">

<printWhenExpression><![CDATA[$P{DRAFT}.booleanValue()==true]]></printWhenExpression>

</reportElement>

<text><![CDATA[Draft Example]]></text>

</staticText>

<staticText>

<reportElement style="watermark_field" x="635" y="528" width="98" height="13" forecolor="#CCCCCC" uuid="35ca321b-36e8-448c-8d35-e34908f74dc0">

<printWhenExpression><![CDATA[$P{DRAFT}.booleanValue()==true]]></printWhenExpression>

</reportElement>

<text><![CDATA[Draft Example]]></text>

</staticText>

</band>

</background>

<pageHeader>

<band height="88" splitType="Stretch">

<frame>

<reportElement style="page_header_frame_style" mode="Opaque" x="0" y="58" width="532" height="30" uuid="c76e9e34-23c1-4b97-95c4-9aba5c470005"/>

<staticText>

<reportElement style="column_header_style" x="339" y="0" width="29" height="30" uuid="5030c9a0-ed4b-4d31-ab04-9571a9a6b416"/>

<textElement textAlignment="Left">

<font size="8" isBold="true"/>

</textElement>

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

</staticText>

<textField pattern="" isBlankWhenNull="false">

<reportElement style="column_header_style" x="455" y="0" width="70" height="16" uuid="446ee779-e380-47cc-b8d9-3cc04df86fe8"/>

<textElement>

<font size="8" isBold="true"/>

</textElement>

<textFieldExpression><![CDATA[$P{LAST_YEAR}]]></textFieldExpression>

</textField>

<textField pattern="" isBlankWhenNull="false">

<reportElement style="column_header_style" x="380" y="0" width="70" height="16" uuid="e0d2385a-50d4-4988-a839-dcdc533ea89e"/>

<textElement>

<font size="8" isBold="true"/>

</textElement>

<textFieldExpression><![CDATA[$P{THIS_YEAR}]]></textFieldExpression>

</textField>

<staticText>

<reportElement style="column_header_style" x="380" y="16" width="70" height="14" uuid="a92b1c6a-7340-4ea1-9e3c-e0c3150d829b"/>

<textElement textAlignment="Right">

<font size="8" isBold="true"/>

</textElement>

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

</staticText>

<staticText>

<reportElement style="column_header_style" x="455" y="16" width="70" height="14" uuid="32d807f3-e884-4948-b2d1-5fcd7b3fb2ed"/>

<textElement textAlignment="Right">

<font size="8" isBold="true" isStrikeThrough="false"/>

</textElement>

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

</staticText>

</frame>

<textField>

<reportElement style="title_field_style" x="0" y="0" width="458" height="15" uuid="2bd9530c-f639-4478-baec-0aff667bc7ff"/>

<textElement>

<font size="11" isBold="true"/>

</textElement>

<textFieldExpression><![CDATA[$P{FUND_NAME}]]></textFieldExpression>

</textField>

<staticText>

<reportElement style="title_field_style" x="0" y="15" width="374" height="22" uuid="956e93bb-8f44-456f-b631-207b634be2d7"/>

<textElement>

<font size="16" isBold="true"/>

</textElement>

<text><![CDATA[Operating Statement]]></text>

</staticText>

<textField>

<reportElement x="0" y="37" width="213" height="16" uuid="72f476a7-4702-42e1-aada-efe637465f6e"/>

<textElement textAlignment="Left">

<font size="11"/>

</textElement>

<textFieldExpression><![CDATA["For the year ended " + $P{TO_DATE_STR}]]></textFieldExpression>

</textField>

<line>

<reportElement style="title_line_style" x="0" y="52" width="534" height="1" uuid="259dce2c-d323-4f86-97bc-57f219311e15"/>

</line>

<image onErrorType="Blank">

<reportElement x="466" y="0" width="69" height="40" uuid="13a769a6-6230-4dc6-9023-f2db6993fb19"/>

<imageExpression><![CDATA[$P{BADGE_LOGO_INPUTSTREAM}]]></imageExpression>

</image>

</band>

</pageHeader>

<detail>

<band height="22">

<frame>

<reportElement style="column_detail_style" positionType="FixRelativeToBottom" x="0" y="0" width="534" height="20" uuid="9c96a9f5-38df-469b-876d-e570c9eaa1bd"/>

<textField isStretchWithOverflow="true">

<reportElement style="account_type_title" stretchType="RelativeToBandHeight" isPrintRepeatedValues="false" mode="Transparent" x="0" y="2" width="328" height="17" isPrintWhenDetailOverflows="true" uuid="e3b2dc59-ffc9-4ea8-945b-120530ffc8e6"/>

<textElement verticalAlignment="Middle">

<font size="8"/>

</textElement>

<textFieldExpression><![CDATA[$F{accountType}]]></textFieldExpression>

</textField>

<textField isBlankWhenNull="true">

<reportElement style="style_number_field" x="380" y="1" width="70" height="17" uuid="2f28a4cf-a48c-467e-811f-7d9b54360e7a"/>

<textElement textAlignment="Right" verticalAlignment="Middle">

<font size="8"/>

</textElement>

<textFieldExpression><![CDATA[$F{curSum}]]></textFieldExpression>

</textField>

<textField>

<reportElement style="number_field" x="339" y="2" width="29" height="17" uuid="64dd15e6-6b62-4933-8846-e1e51b72e847"/>

<textElement verticalAlignment="Middle">

<font size="8"/>

</textElement>

<textFieldExpression><![CDATA[$F{note}]]></textFieldExpression>

</textField>

<textField isBlankWhenNull="true">

<reportElement style="style_number_field" x="455" y="2" width="70" height="17" uuid="5d0cc7b2-bad0-409f-a34c-4d33c37e33d2"/>

<textElement textAlignment="Right" verticalAlignment="Middle">

<font size="8"/>

</textElement>

<textFieldExpression><![CDATA[$F{preSum}]]></textFieldExpression>

</textField>

<line>

<reportElement style="sum_underline_style" x="380" y="18" width="70" height="1" uuid="1d6e36a1-7070-467e-9983-db6bcd884cd3">

<printWhenExpression><![CDATA[$P{EXPORT_FORMAT}.equals("PDF")]]></printWhenExpression>

</reportElement>

</line>

<line>

<reportElement style="sum_underline_style_1" x="380" y="1" width="70" height="1" uuid="25c0deaa-6727-4845-8ac7-538646898a63">

<printWhenExpression><![CDATA[$P{EXPORT_FORMAT}.equals("PDF")]]></printWhenExpression>

</reportElement>

</line>

<line>

<reportElement style="sum_underline_style" x="455" y="18" width="70" height="1" uuid="d9881e39-9a6a-48b9-a1ca-c69ea8373cac">

<printWhenExpression><![CDATA[$P{EXPORT_FORMAT}.equals("PDF")]]></printWhenExpression>

</reportElement>

</line>

<line>

<reportElement style="sum_underline_style_1" x="455" y="1" width="70" height="1" uuid="9e903ba3-d220-4a82-bf11-51bf5c2761ec">

<printWhenExpression><![CDATA[$P{EXPORT_FORMAT}.equals("PDF")]]></printWhenExpression>

</reportElement>

</line>

</frame>

</band>

</detail>

<pageFooter>

<band height="61">

<staticText>

<reportElement style="foot_field_style" x="0" y="19" width="47" height="10" forecolor="#666666" uuid="22afc7ea-6faf-4887-b1fe-aa06e664d218"/>

<textElement>

<font size="7"/>

</textElement>

<text><![CDATA[Fund Code: ]]></text>

</staticText>

<textField>

<reportElement style="foot_field_style" x="47" y="19" width="166" height="10" forecolor="#666666" uuid="3c630d08-d61b-4806-96bb-b853b7e0dbe9"/>

<textElement>

<font size="7"/>

</textElement>

<textFieldExpression><![CDATA[$P{FUND_CODE}]]></textFieldExpression>

</textField>

<line>

<reportElement style="separate_line_style" x="0" y="17" width="534" height="1" uuid="1a8e379f-8ea9-43a4-9914-9855ced9ee7d"/>

</line>

<textField>

<reportElement style="foot_date_day_style" x="366" y="19" width="122" height="10" uuid="48a2aa19-8a30-4dbe-994b-1f864b5c3e01"/>

<textElement textAlignment="Right">

<font size="7"/>

</textElement>

<textFieldExpression><![CDATA[$P{REPORT_DATE}]]></textFieldExpression>

</textField>

<textField>

<reportElement style="foot_date_time_style" x="488" y="19" width="36" height="10" uuid="e2babc24-d763-4df5-a2c8-a83f0d28b629"/>

<textElement textAlignment="Right">

<font size="7"/>

</textElement>

<textFieldExpression><![CDATA[$P{REPORT_DATE}]]></textFieldExpression>

</textField>

<frame>

<reportElement style="foot_field_style" x="428" y="29" width="97" height="12" uuid="2ecfcbd6-6141-4789-a2b8-62ddba0f3e73"/>

<textField evaluationTime="Report">

<reportElement style="foot_field_style" x="81" y="0" width="16" height="10" uuid="28b2578e-22ac-453e-8ed7-5a41f67fe2c1"/>

<textElement textAlignment="Right">

<font size="7"/>

</textElement>

<textFieldExpression><![CDATA[$V{PAGE_NUMBER}]]></textFieldExpression>

</textField>

<textField>

<reportElement style="foot_field_style" x="59" y="0" width="16" height="10" uuid="21d5aba3-c99b-4a57-a43b-0f73fe35a8a6"/>

<textElement textAlignment="Center">

<font size="7"/>

</textElement>

<textFieldExpression><![CDATA[$V{PAGE_NUMBER}]]></textFieldExpression>

</textField>

<staticText>

<reportElement style="foot_field_style" x="36" y="0" width="24" height="10" uuid="09bc8ba1-3335-4806-85fe-0547187e1840"/>

<textElement textAlignment="Right">

<font size="7"/>

</textElement>

<text><![CDATA[Page:]]></text>

</staticText>

<staticText>

<reportElement style="foot_field_style" x="74" y="0" width="8" height="10" uuid="710730d3-9424-4517-b5ce-e98ccaf2bb95"/>

<textElement textAlignment="Center">

<font size="7"/>

</textElement>

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

</staticText>

</frame>

<textField pattern="">

<reportElement style="footer_disclaimer_style" isPrintRepeatedValues="false" x="0" y="0" width="532" height="16" uuid="49979487-d3bc-4a70-ae9f-db1b0426d950"/>

<textFieldExpression><![CDATA[$P{disclaimer}]]></textFieldExpression>

</textField>

</band>

</pageFooter>

<summary>

<band height="26" splitType="Stretch"/>

</summary>

</jasperReport>

 

[athimmig]

I found that expanding the width of the entire report and shifting cells to the right of the area where the merge happens cleaned it up for me. The columns where the merge happened was reduced to 0 in width and empty when I ran the report again. If you don't mind the extra columns being there as they are all but invisible then this solution is confirmed to work. 

[simon.mullard_1]

Hi, that solution didn't work for me. I expanded the width of the report and shifted all my columns to the right but then I ended up with columns A,B & C all are expanded but without data. Report doesn't look very good as its for a customer. Any other options how to resolve this merge cells?