Excel cells segmentation problem when exporting sub reports to worksheets

0
  • 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>

harun.prasu's picture
Joined: Oct 16 2015 - 2:39am
Last seen: 1 year 6 days ago

0 Answers:

No answers yet
Feedback