Excel cells segmentation problem when exporting sub reports to worksheets

[harun.prasu]

• I have 10 sub reports placed on main.jrxml on separate detail bands
• placed a text element after each sub reports and added the property net.sf.jasperreports.export.xls.break.after.row="true" to split each sub reports in excel worksheets.

I managed to get separate work sheets for sub reports using the above property.But in the xls out put I am getting  merged cells (or same cell alignments for all sheets)

I am able to get correct cell alignment when exporting the sub report individually.But after placing sub report in the main report it mesess the  whole cells alignment.

I am attaching the out put I am getting and the JRXML also.

Is there any solution for this?

<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.1.0.final using JasperReports Library version 6.1.0 --><!-- 2016-01-06T07:13:21 --><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="TOSMetricsReport"    pageWidth="3000"    pageHeight="595"    orientation="Landscape"    columnWidth="2780"    leftMargin="200"    rightMargin="20"    topMargin="20"    bottomMargin="20"    uuid="82356077-f8a3-4fb5-92de-af77228a6b21">       <property name="com.jaspersoft.studio.data.defaultdataadapter" value="CFTransactionalDB"/>    <property name="com.jaspersoft.studio.unit." value="pixel"/>    <property name="ireport.jasperserver.url" value="http://sv-clrty14-d3:8080/reportservice/"/>    <property name="ireport.jasperserver.user" value="superuser"/>    <property name="ireport.jasperserver.report.resource"    value="/organizations/CareFirst/ca_ppm/ReportsCustom/TOSMetricsReport_files/main_jrxml"/>    <property name="ireport.jasperserver.reportUnit"    value="/organizations/CareFirst/ca_ppm/ReportsCustom/TOSMetricsReport"/>    <property name="net.sf.jasperreports.export.xls.one.page.per.sheet " value="true"/>    <property name="net.sf.jasperreports.export.pdf.create.batch.mode.bookmarks" value="true"/>    <property name="net.sf.jasperreports.export.xls.exclude.origin.band.2" value="background"/>    <property name="net.sf.jasperreports.export.xls.exclude.origin.group.1" value="break"/>    <property name="net.sf.jasperreports.export.pdf.exclude.origin.band.1" value="background"/>    <property name="net.sf.jasperreports.export.xls.collapse.row.span" value="true"/>    <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/>    <property name="net.sf.jasperreports.export.xls.ignore.cell.border" value="true"/>    <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>    <property name="net.sf.jasperreports.print.keep.full.text" value="true"/>    <property name="net.sf.jasperreports.export.xls.wrap.text" value="false"/>    <property name="net.sf.jasperreports.export.xls.auto.fit.row" value="true"/>    <property name="net.sf.jasperreports.export.xls.auto.fit.column" value="true"/>    <parameter name="year" class="java.lang.String"/>    <parameter name="date" class="java.lang.String"/>    <queryString>        <![CDATA[select 1]]>    </queryString>    <group name="Group1"/>    <group name="Group2">        <groupHeader>            <band height="44">                <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/>                <property name="net.sf.jasperreports.export.xls.column.width.ratio" value="1000"/>                <subreport runToBottom="true">                    <reportElement isPrintRepeatedValues="false" x="10" y="6" width="60" height="38"                                   isPrintInFirstWholeBand="true" uuid="dca34dc6-3ae1-4154-9f39-aefda281942b"/>                    <parametersMapExpression>                        <![CDATA[$P{REPORT_PARAMETERS_MAP}]]>                    </parametersMapExpression>                    <connectionExpression>                        <![CDATA[$P{REPORT_CONNECTION}]]>                    </connectionExpression>                    <subreportExpression>                        <![CDATA["ReportDevelopment/PMO/Roadmap.jasper"]]>                    </subreportExpression>                </subreport>            </band>        </groupHeader>    </group>    <group name="break">        <groupHeader>            <band height="11">                <printWhenExpression>                    <![CDATA[$V{PAGE_NUMBER}==0]]>                </printWhenExpression>                <textField>                    <reportElement x="0" y="10" width="100" height="1" uuid="ddf8561d-a742-4ee5-9504-4e2b4a979735">                        <property name="com.jaspersoft.studio.unit.y" value="pixel"/>                        <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                        <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>                    </reportElement>                    <textFieldExpression>                        <![CDATA["break Text Field"]]>                    </textFieldExpression>                </textField>            </band>        </groupHeader>    </group>    <background>        <band height="332" splitType="Stretch">            <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.HorizontalRowLayout"/>            <textField hyperlinkType="LocalAnchor" hyperlinkTarget="Top" bookmarkLevel="1">                <reportElement positionType="FixRelativeToBottom" x="-170" y="0" width="170" height="30"                uuid="e8a3ca2e-8d8d-4913-8da7-dfdd33561fc8"/>                <textElement textAlignment="Left" verticalAlignment="Middle">                    <font fontName="Times New Roman" size="12" isBold="true"/>                </textElement>                <textFieldExpression>                    <![CDATA["Roadmap"]]>                </textFieldExpression>                <hyperlinkAnchorExpression>                    <![CDATA["Roadmap"]]>                </hyperlinkAnchorExpression>                <hyperlinkTooltipExpression>                    <![CDATA["Roadmap"]]>                </hyperlinkTooltipExpression>            </textField>            <textField hyperlinkType="LocalAnchor" hyperlinkTarget="Top" bookmarkLevel="1">                <reportElement positionType="FixRelativeToBottom" x="-170" y="240" width="170" height="30"                uuid="2cbd23e8-4e2c-4107-a0f7-3997ae2f4a2f"/>                <textElement textAlignment="Left" verticalAlignment="Middle">                    <font fontName="Times New Roman" size="12" isBold="true"/>                </textElement>                <textFieldExpression>                    <![CDATA["Cost Center FINApprovers"]]>                </textFieldExpression>                <hyperlinkAnchorExpression>                    <![CDATA["CostCenter"]]>                </hyperlinkAnchorExpression>            </textField>            <textField hyperlinkType="LocalAnchor" bookmarkLevel="1">                <reportElement x="-170" y="30" width="170" height="30" uuid="460205c5-cffd-42bb-bf05-9292f2120c31"/>                <textElement verticalAlignment="Middle">                    <font fontName="Times New Roman" size="12" isBold="true"/>                </textElement>                <textFieldExpression>                    <![CDATA["Performance"]]>                </textFieldExpression>                <hyperlinkAnchorExpression>                    <![CDATA["per"]]>                </hyperlinkAnchorExpression>            </textField>            <textField hyperlinkType="LocalAnchor" bookmarkLevel="1">                <reportElement x="-170" y="60" width="170" height="30" uuid="e7425be8-cf87-48e7-8a93-7b1b91f55c40"/>                <textElement verticalAlignment="Middle">                    <font fontName="Times New Roman" size="12" isBold="true"/>                </textElement>                <textFieldExpression>                    <![CDATA["CC Owners & Leads"]]>                </textFieldExpression>                <hyperlinkAnchorExpression>                    <![CDATA["cco"]]>                </hyperlinkAnchorExpression>            </textField>            <textField hyperlinkType="LocalAnchor" bookmarkLevel="1">                <reportElement x="-170" y="90" width="170" height="30" uuid="7173485a-c0ba-4d49-8a19-3ac6b55cff7f"/>                <textElement verticalAlignment="Middle">                    <font fontName="Times New Roman" size="12" isBold="true"/>                </textElement>                <textFieldExpression>                    <![CDATA["Incomplete key Milestones"]]>                </textFieldExpression>                <hyperlinkAnchorExpression>                    <![CDATA["ikm"]]>                </hyperlinkAnchorExpression>            </textField>            <textField hyperlinkType="LocalAnchor" bookmarkLevel="1">                <reportElement x="-170" y="120" width="170" height="30" uuid="ddf9fea7-bf9a-4f18-a29b-5dee53d2e26c"/>                <textElement verticalAlignment="Middle">                    <font fontName="Times New Roman" size="12" isBold="true"/>                </textElement>                <textFieldExpression>                    <![CDATA["Deployment Forecast"]]>                </textFieldExpression>                <hyperlinkAnchorExpression>                    <![CDATA["df"]]>                </hyperlinkAnchorExpression>            </textField>            <textField hyperlinkType="LocalAnchor" bookmarkLevel="1">                <reportElement x="-170" y="150" width="170" height="30" uuid="c8fbbc99-d593-4296-b02d-abefd3d795c1"/>                <textElement verticalAlignment="Middle">                    <font fontName="Times New Roman" size="12" isBold="true"/>                </textElement>                <textFieldExpression>                    <![CDATA["Unfunded"]]>                </textFieldExpression>                <hyperlinkAnchorExpression>                    <![CDATA["uf"]]>                </hyperlinkAnchorExpression>            </textField>            <textField hyperlinkType="LocalAnchor" bookmarkLevel="1">                <reportElement x="-170" y="180" width="170" height="30" uuid="c8fbbc99-d593-4296-b02d-abefd3d795c1"/>                <textElement verticalAlignment="Middle">                    <font fontName="Times New Roman" size="12" isBold="true"/>                </textElement>                <textFieldExpression>                    <![CDATA["Financials - LT & FY"]]>                </textFieldExpression>                <hyperlinkAnchorExpression>                    <![CDATA["fin"]]>                </hyperlinkAnchorExpression>            </textField>            <textField hyperlinkType="LocalAnchor" bookmarkLevel="1">                <reportElement x="-170" y="210" width="169" height="30" uuid="2c7eb592-f4b4-4f05-95e9-cbfe6882821f"/>                <textElement verticalAlignment="Middle">                    <font fontName="Times New Roman" size="12" isBold="true"/>                </textElement>                <textFieldExpression>                    <![CDATA["Divisional Plan Milestone"]]>                </textFieldExpression>                <hyperlinkAnchorExpression>                    <![CDATA["dpm"]]>                </hyperlinkAnchorExpression>            </textField>            <textField hyperlinkType="LocalAnchor" bookmarkLevel="1">                <reportElement positionType="Float" x="-170" y="270" width="169" height="30" uuid="f498ca0f-2fdb-4e07-af15-2ed48fb26502"/>                <textElement verticalAlignment="Middle">                    <font fontName="Times New Roman" size="12" isBold="true"/>                </textElement>                <textFieldExpression>                    <![CDATA["RiskScore"]]>                </textFieldExpression>                <hyperlinkAnchorExpression>                    <![CDATA["rs"]]>                </hyperlinkAnchorExpression>            </textField>        </band>    </background>    <title>        <band height="32" splitType="Stretch">            <staticText>                <reportElement x="10" y="2" width="781" height="30" uuid="622c8818-d378-4b4b-ae4b-7e37ff18c760"/>                <textElement textAlignment="Center">                    <font fontName="Times New Roman" size="20" isBold="true"/>                </textElement>                <text>                    <![CDATA[PMO METRICS EXTRACT]]>                </text>            </staticText>        </band>    </title>    <detail>        <band height="33">            <subreport runToBottom="true">                <reportElement x="11" y="0" width="59" height="30" uuid="95611b1b-88c8-47d6-a77d-13a9f69316d3"/>                <parametersMapExpression>                    <![CDATA[$P{REPORT_PARAMETERS_MAP}]]>                </parametersMapExpression>                <connectionExpression>                    <![CDATA[$P{REPORT_CONNECTION}]]>                </connectionExpression>                <subreportExpression>                    <![CDATA["ReportDevelopment/PMO/Performance.jasper"]]>                </subreportExpression>            </subreport>            <textField>                <reportElement x="0" y="32" width="100" height="1" uuid="c841c3ff-c3df-4464-8a4c-9ef7d09dffcd">                    <property name="com.jaspersoft.studio.unit.y" value="pixel"/>                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>                </reportElement>                <textFieldExpression>                    <![CDATA["break Text Field"]]>                </textFieldExpression>            </textField>        </band>        <band height="44">            <subreport runToBottom="true">                <reportElement x="11" y="5" width="59" height="38" uuid="f8b8bc34-7a99-46c5-bab9-09381626c1bd"/>                <parametersMapExpression>                    <![CDATA[$P{REPORT_PARAMETERS_MAP}]]>                </parametersMapExpression>                <connectionExpression>                    <![CDATA[$P{REPORT_CONNECTION}]]>                </connectionExpression>                <subreportExpression>                    <![CDATA["ReportDevelopment/PMO/CC_Owners_&_Leads.jasper"]]>                </subreportExpression>            </subreport>            <textField>                <reportElement x="0" y="43" width="100" height="1" uuid="6e9de47b-7f05-4785-8d1f-3457d3d96c1a">                    <property name="com.jaspersoft.studio.unit.y" value="pixel"/>                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>                </reportElement>                <textFieldExpression>                    <![CDATA["break Text Field"]]>                </textFieldExpression>            </textField>        </band>        <band height="51">            <subreport runToBottom="true">                <reportElement x="11" y="0" width="59" height="48" uuid="02a75b6e-cb02-46d7-873f-4a24b462f2a1"/>                <parametersMapExpression>                    <![CDATA[$P{REPORT_PARAMETERS_MAP}]]>                </parametersMapExpression>                <connectionExpression>                    <![CDATA[$P{REPORT_CONNECTION}]]>                </connectionExpression>                <subreportExpression>                    <![CDATA["ReportDevelopment/PMO/Incomplete_Key_Milestones.jasper"]]>                </subreportExpression>            </subreport>            <textField>                <reportElement x="0" y="50" width="100" height="1" uuid="12901493-5c87-40d2-a093-193b20127d0d">                    <property name="com.jaspersoft.studio.unit.y" value="pixel"/>                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>                </reportElement>                <textFieldExpression>                    <![CDATA["break Text Field"]]>                </textFieldExpression>            </textField>        </band>        <band height="35">            <subreport runToBottom="true">                <reportElement x="11" y="4" width="59" height="28" uuid="ac55415d-032e-40a0-8e1f-5ea5ae1f2d8d"/>                <parametersMapExpression>                    <![CDATA[$P{REPORT_PARAMETERS_MAP}]]>                </parametersMapExpression>                <connectionExpression>                    <![CDATA[$P{REPORT_CONNECTION}]]>                </connectionExpression>                <subreportExpression>                    <![CDATA["ReportDevelopment/PMO/Deployment_Forecast.jasper"]]>                </subreportExpression>            </subreport>            <textField>                <reportElement x="0" y="34" width="100" height="1" uuid="a9701cb8-1898-426f-abf3-f503bd2c6be7">                    <property name="com.jaspersoft.studio.unit.y" value="pixel"/>                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>                </reportElement>                <textFieldExpression>                    <![CDATA["break Text Field"]]>                </textFieldExpression>            </textField>        </band>        <band height="30">            <subreport runToBottom="true">                <reportElement x="11" y="0" width="60" height="27" uuid="6d044dc9-ccc1-45dc-8d8d-e85e3ac6146b"/>                <parametersMapExpression>                    <![CDATA[$P{REPORT_PARAMETERS_MAP}]]>                </parametersMapExpression>                <connectionExpression>                    <![CDATA[$P{REPORT_CONNECTION}]]>                </connectionExpression>                <subreportExpression>                    <![CDATA["ReportDevelopment/PMO/Unfunded.jasper"]]>                </subreportExpression>            </subreport>            <textField>                <reportElement x="0" y="29" width="100" height="1" uuid="f0a180e0-0c66-4865-8fe4-13a1e78ba69f">                    <property name="com.jaspersoft.studio.unit.y" value="pixel"/>                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>                </reportElement>                <textFieldExpression>                    <![CDATA["break Text Field"]]>                </textFieldExpression>            </textField>        </band>        <band height="30">            <subreport runToBottom="true">                <reportElement x="11" y="4" width="59" height="25" uuid="c59580ac-a620-48e7-aa27-3c605b7284a3"/>                <parametersMapExpression>                    <![CDATA[$P{REPORT_PARAMETERS_MAP}]]>                </parametersMapExpression>                <connectionExpression>                    <![CDATA[$P{REPORT_CONNECTION}]]>                </connectionExpression>                <subreportExpression>                    <![CDATA["ReportDevelopment/PMO/Financials - LT & FY.jasper"]]>                </subreportExpression>            </subreport>            <textField>                <reportElement x="0" y="29" width="100" height="1" uuid="0894fa3a-1f37-4747-a501-aef8fe997482">                    <property name="com.jaspersoft.studio.unit.y" value="pixel"/>                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>                </reportElement>                <textFieldExpression>                    <![CDATA["break Text Field"]]>                </textFieldExpression>            </textField>        </band>        <band height="31">            <subreport runToBottom="true">                <reportElement x="11" y="0" width="60" height="28" uuid="b4aa0a9a-1024-4fbe-8ed7-0e7feb418c01"/>                <parametersMapExpression>                    <![CDATA[$P{REPORT_PARAMETERS_MAP}]]>                </parametersMapExpression>                <connectionExpression>                    <![CDATA[$P{REPORT_CONNECTION}]]>                </connectionExpression>                <subreportExpression>                    <![CDATA["ReportDevelopment/PMO/Divisional_Plan_Milestone.jasper"]]>                </subreportExpression>            </subreport>            <textField>                <reportElement x="0" y="30" width="100" height="1" uuid="6699d708-6447-4458-bd25-fe064bf990b2">                    <property name="com.jaspersoft.studio.unit.y" value="pixel"/>                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>                </reportElement>                <textFieldExpression>                    <![CDATA["break Text Field"]]>                </textFieldExpression>            </textField>        </band>        <band height="32">            <subreport runToBottom="true">                <reportElement x="11" y="1" width="60" height="28" uuid="9fd474cf-ac38-4de2-a644-ccc9c88fc287"/>                <parametersMapExpression>                    <![CDATA[$P{REPORT_PARAMETERS_MAP}]]>                </parametersMapExpression>                <connectionExpression>                    <![CDATA[$P{REPORT_CONNECTION}]]>                </connectionExpression>                <subreportExpression>                    <![CDATA["ReportDevelopment/PMO/CostCenterFINApprovers.jasper"]]>                </subreportExpression>            </subreport>            <textField>                <reportElement x="0" y="31" width="100" height="1" uuid="a077ff9d-f999-4f95-89ad-d7e909360465">                    <property name="com.jaspersoft.studio.unit.y" value="pixel"/>                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>                </reportElement>                <textFieldExpression>                    <![CDATA["break Text Field"]]>                </textFieldExpression>            </textField>        </band>        <band height="32">            <subreport runToBottom="true">                <reportElement x="11" y="0" width="60" height="30" uuid="e8ae422b-705a-4c48-badd-d955fa7f6d47"/>                <parametersMapExpression>                    <![CDATA[$P{REPORT_PARAMETERS_MAP}]]>                </parametersMapExpression>                <connectionExpression>                    <![CDATA[$P{REPORT_CONNECTION}]]>                </connectionExpression>                <subreportExpression>                    <![CDATA["ReportDevelopment/PMO/Riskss.jasper"]]>                </subreportExpression>            </subreport>            <textField>                <reportElement x="0" y="31" width="100" height="1" uuid="7dbf13dc-74fc-4726-8a13-21d6835312f0">                    <property name="com.jaspersoft.studio.unit.y" value="pixel"/>                    <property name="com.jaspersoft.studio.unit.height" value="pixel"/>                    <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>                </reportElement>                <textFieldExpression>                    <![CDATA["break Text Field"]]>                </textFieldExpression>            </textField>        </band>    </detail></jasperReport>[/code]

mixed cells(1).bmp

mixed cells(1).bmp