Search code examples
jasper-reportsapache-poiexport-to-excel

JasperReport Excel export cuts content after wordwrap (Stretch with overflow)

I am having Jasper Report API 5.0.1. I have a report having sub report to export data in excel format.

When I export report in excel, some overflowed content of cell slightly cuts down and not able to view until we resize row height. Here is the snapshot of how it looks.

enter image description here

I have checked for solution on Stackoverflow and Jasper report community forum and tried solutions suggested in there. But none have worked for me.

I tried:

  • net.sf.jasperreports.print.keep.full.text to true
  • Set Stretch Type of all band elements to Relative to Band Height

But none of the options worked for me.

Can anyone please point out where I might be doing wrong?

If there is a solution with Apache POI then I am open to apply it, if it fixes the issue.

Update with code and jasper report:

jrxml file:

<?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="MemberWorkListing_subreport" pageWidth="750" pageHeight="802" whenNoDataType="AllSectionsNoDetail" columnWidth="750" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="50014970-029e-488c-9e4b-b42b8d743e84">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <queryString>
        <![CDATA[SELECT * FROM TMP_WK_DTL]]>
    </queryString>
    <field name="WK_INT_NO" class="java.math.BigDecimal"/>
    <field name="WID_NO" class="java.math.BigDecimal"/>
    <field name="ISWC_NO" class="java.lang.String"/>
    <field name="TTL_ENG" class="java.lang.String"/>
    <field name="TTL_LOCAL" class="java.lang.String"/>
    <field name="DURATION" class="java.sql.Timestamp"/>
    <field name="WK_LG" class="java.lang.String"/>
    <field name="WK_CAT" class="java.lang.String"/>
    <field name="WK_STATUS" class="java.lang.String"/>
    <field name="ART_NAME_EL" class="java.lang.String"/>
    <field name="ART_NAME_LE" class="java.lang.String"/>
    <field name="IP_SET_NO" class="java.math.BigDecimal"/>
    <field name="IP_INT_NO" class="java.math.BigDecimal"/>
    <field name="IP_NAMETYPE" class="java.lang.String"/>
    <field name="IP_WK_ROLE" class="java.lang.String"/>
    <field name="IP_NAME_ENG" class="java.lang.String"/>
    <field name="IP_NAME_LOCAL" class="java.lang.String"/>
    <field name="SOC_NAME" class="java.lang.String"/>
    <field name="PER_OWN_SHR" class="java.math.BigDecimal"/>
    <field name="PER_COLL_SHR" class="java.math.BigDecimal"/>
    <field name="MEC_OWN_SHR" class="java.math.BigDecimal"/>
    <field name="MEC_COLL_SHR" class="java.math.BigDecimal"/>
    <field name="SYN_OWN_SHR" class="java.math.BigDecimal"/>
    <field name="SYN_COLL_SHR" class="java.math.BigDecimal"/>
    <field name="SEQ" class="java.math.BigDecimal"/>
    <field name="AGR_NO" class="java.math.BigDecimal"/>
    <variable name="workCount" class="java.lang.Integer" incrementType="Group" incrementGroup="internalNoGroup">
        <variableExpression><![CDATA[$V{workCount} + 1]]></variableExpression>
        <initialValueExpression><![CDATA[0]]></initialValueExpression>
    </variable>
    <group name="internalNoGroup">
        <groupExpression><![CDATA[$F{WK_INT_NO}]]></groupExpression>
    </group>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="34">
            <staticText>
                <reportElement uuid="6bca70f4-3fc8-42b9-bc88-0b491f747c97" mode="Opaque" x="0" y="0" width="80" height="34" backcolor="#C0C0C0"/>
                <box>
                    <pen lineWidth="0.75"/>
                    <topPen lineWidth="0.75"/>
                    <leftPen lineWidth="0.75"/>
                    <bottomPen lineWidth="0.75"/>
                    <rightPen lineWidth="0.75"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font fontName="Arial Narrow" isBold="true"/>
                </textElement>
                <text><![CDATA[ INTERNAL NO]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="e20675cf-ac73-4baa-95cd-a4ff4a8365c8" mode="Opaque" x="80" y="0" width="70" height="34" backcolor="#C0C0C0"/>
                <box>
                    <pen lineWidth="0.75"/>
                    <topPen lineWidth="0.75"/>
                    <leftPen lineWidth="0.75"/>
                    <bottomPen lineWidth="0.75"/>
                    <rightPen lineWidth="0.75"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font fontName="Arial Narrow" isBold="true"/>
                </textElement>
                <text><![CDATA[ISWC NO]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="c23427f6-11da-4b52-a7dd-df63116c8f6f" mode="Opaque" x="150" y="0" width="180" height="34" backcolor="#C0C0C0"/>
                <box>
                    <pen lineWidth="0.75"/>
                    <topPen lineWidth="0.75"/>
                    <leftPen lineWidth="0.75"/>
                    <bottomPen lineWidth="0.75"/>
                    <rightPen lineWidth="0.75"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font fontName="Arial Narrow" isBold="true"/>
                </textElement>
                <text><![CDATA[TITLE]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="9cd3477c-5763-40fe-9565-0dd412830fc2" mode="Opaque" x="330" y="0" width="80" height="34" backcolor="#C0C0C0"/>
                <box>
                    <pen lineWidth="0.75"/>
                    <topPen lineWidth="0.75"/>
                    <leftPen lineWidth="0.75"/>
                    <bottomPen lineWidth="0.75"/>
                    <rightPen lineWidth="0.75"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font fontName="Arial Narrow" isBold="true"/>
                </textElement>
                <text><![CDATA[LANGUAGE]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="9863dbce-c990-44b7-b56c-fa8f3bbb0a00" mode="Opaque" x="410" y="0" width="80" height="34" backcolor="#C0C0C0"/>
                <box>
                    <pen lineWidth="0.75"/>
                    <topPen lineWidth="0.75"/>
                    <leftPen lineWidth="0.75"/>
                    <bottomPen lineWidth="0.75"/>
                    <rightPen lineWidth="0.75"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font fontName="Arial Narrow" isBold="true"/>
                </textElement>
                <text><![CDATA[CATEGORY]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="3baef4d4-aaf6-4024-9ebd-d47bdf348871" mode="Opaque" x="490" y="0" width="80" height="34" backcolor="#C0C0C0"/>
                <box>
                    <pen lineWidth="0.75"/>
                    <topPen lineWidth="0.75"/>
                    <leftPen lineWidth="0.75"/>
                    <bottomPen lineWidth="0.75"/>
                    <rightPen lineWidth="0.75"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font fontName="Arial Narrow" isBold="true"/>
                </textElement>
                <text><![CDATA[STATUS]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="72f3706d-885b-4a44-9608-bea5afe6186e" mode="Opaque" x="570" y="0" width="180" height="34" backcolor="#C0C0C0"/>
                <box>
                    <pen lineWidth="0.75"/>
                    <topPen lineWidth="0.75"/>
                    <leftPen lineWidth="0.75"/>
                    <bottomPen lineWidth="0.75"/>
                    <rightPen lineWidth="0.75"/>
                </box>
                <textElement textAlignment="Center" verticalAlignment="Middle">
                    <font fontName="Arial Narrow" isBold="true"/>
                </textElement>
                <text><![CDATA[ARTISTE]]></text>
            </staticText>
        </band>
    </title>
    <detail>
        <band height="15" splitType="Stretch">
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement uuid="660806ed-1806-4020-9a0f-c14408440422" stretchType="RelativeToBandHeight" isPrintRepeatedValues="false" x="0" y="0" width="80" height="15"/>
                <textElement textAlignment="Right" verticalAlignment="Top">
                    <font fontName="Arial Narrow"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{WK_INT_NO}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement uuid="ad71b230-bfa8-480e-93c1-ccc6e48f57c5" stretchType="RelativeToBandHeight" isPrintRepeatedValues="false" x="80" y="0" width="70" height="15"/>
                <textElement>
                    <font fontName="Arial Narrow"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{ISWC_NO}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement uuid="eec7d04e-6f4c-4b28-a3fe-8a3433339d14" stretchType="RelativeToBandHeight" isPrintRepeatedValues="false" x="150" y="0" width="180" height="15"/>
                <textElement>
                    <font fontName="Arial Narrow"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{TTL_ENG}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement uuid="d53915b9-edb6-439d-bec5-fd6231043a37" stretchType="RelativeToBandHeight" isPrintRepeatedValues="false" x="330" y="0" width="80" height="15"/>
                <textElement>
                    <font fontName="Arial Narrow"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{WK_LG}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement uuid="30e29311-5da3-481f-bb34-d78e4b5b7510" stretchType="RelativeToBandHeight" isPrintRepeatedValues="false" x="410" y="0" width="80" height="15"/>
                <textElement>
                    <font fontName="Arial Narrow"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{WK_CAT}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement uuid="375efa25-af3b-4699-9a75-56998b52b62e" stretchType="RelativeToBandHeight" isPrintRepeatedValues="false" x="490" y="0" width="80" height="15"/>
                <textElement>
                    <font fontName="Arial Narrow"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{WK_STATUS}]]></textFieldExpression>
            </textField>
            <textField isStretchWithOverflow="true" isBlankWhenNull="true">
                <reportElement uuid="ee80e636-4a4c-4fbf-90c0-fc61d8c0b894" stretchType="RelativeToBandHeight" isPrintRepeatedValues="false" x="570" y="0" width="180" height="15"/>
                <textElement>
                    <font fontName="Arial Narrow"/>
                </textElement>
                <textFieldExpression><![CDATA[$F{ART_NAME_EL}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

Export code:

JasperCompileManager.compileReportToFile(jrxmlfile, jasperfile);

JasperPrint jasperPrint = JasperFillManager.fillReport(jasperfile, reportParameter, dbConnection);

JRXlsExporter exporterXLS = new JRXlsExporter();
exporterXLS.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);

exporterXLS.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, reportFile);
exporterXLS.setParameter(JRExporterParameter.IGNORE_PAGE_MARGINS,Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_COLUMNS, Boolean.TRUE);

exporterXLS.exportReport();
Solution

  • In order to properly export to Excel, you should prepare your reports following the guidelines - Making HTML, XLS or CSV friendly reports

    The issue is that some of the elements composing your report are a bit overlapping or not properly aligned.