One excel Sheet for each subreport (No groups in the main report)jaspersoft iReport

export-to-exceljasper-reports

This is really driving me mad.. I read everywhere but still can't understand why mine isn't working

I have a main report( a dummy query returning me only one row) and 2 subreports(each with its own header) found in two different detail band. The second subreport splits into multiple excel sheets!

The main report doesn't contain any groups.. its just a dummy query and has no links with the subreports.

This is what i did in the main report:

Add in Properties : Name="net.sf.jasperreports.export.xls.one.page.per.sheet " with Value="True"
Set Ignore Pagination="True"
Added a Line element and set the Properties Expression: Name="net.sf.jasperreports.export.xls.break.after.row" and Default Value="True"

Then for the second subreport:

Set Ignore Pagination="True"

I just want one excel sheet for each subreports. But the second subreport is being split to multiple sheets… 🙁

Does anybody know what's the problem…?

I also read an issue with ignore pagination and page breaks here:
http://community.jaspersoft.com/ireport-designer/issues/2575

I am using the Jaspersoft iReport Designer 5.0.1

======================================
My jrxml for the main 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="Sample_Report28022013" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" isIgnorePagination="true" uuid="1808a381-4204-4662-9cc0-7d154bcc0890">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <property name="ireport.jasperserver.reportUnit" value="/reports/Test/Subreports"/>
    <property name="ireport.jasperserver.url" value="http://192.168.17.114:8080/jasperserver/services/repository"/>
    <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="True"/>
    <property name="net.sf.jasperreports.export.html.remove.emtpy.space.between.rows" value="True"/>
    <parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA["C:\\Documents and Settings\\TreshmaC\\Desktop\\WCHY\\JasperSoft\\"]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT
     tableliste."ident" AS tableliste_ident
FROM
     "public"."tableliste" tableliste
LIMIT 1]]>
    </queryString>
    <field name="tableliste_ident" class="java.lang.Integer"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="7" splitType="Stretch"/>
    </title>
    <pageHeader>
        <band height="5" splitType="Stretch"/>
    </pageHeader>
    <columnHeader>
        <band height="7" splitType="Stretch"/>
    </columnHeader>
    <detail>
        <band height="97" splitType="Stretch">
            <subreport>
                <reportElement uuid="ea3a0ff6-f82f-43c0-8c7b-3d06e699f2f0" x="0" y="0" width="555" height="75"/>
                <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                <subreportExpression><![CDATA["repo:Sample_Report28022013_subreport1.jrxml"]]></subreportExpression>
            </subreport>
            <break>
                <reportElement uuid="3568174b-2ea9-4973-8e12-adcb7f54685a" x="0" y="83" width="100" height="1"/>
            </break>
        </band>
        <band height="94" splitType="Prevent">
            <subreport>
                <reportElement uuid="b6ab1daa-122a-4a4b-8f60-f9fe2ba78204" x="0" y="13" width="555" height="72">
                    <property name="net.sf.jasperreports.export.xls.break.before.row" value="True"/>
                </reportElement>
                <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                <subreportExpression><![CDATA["repo:Sample_Report28022013_subreport2.jrxml"]]></subreportExpression>
            </subreport>
        </band>
    </detail>
    <columnFooter>
        <band height="7" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="7" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="203" splitType="Stretch">
            <componentElement>
                <reportElement uuid="2651da09-f5ff-45de-91cb-6d9f0a8af724" x="0" y="0" width="555" height="200"/>
                <mp:map xmlns:mp="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd">
                    <mp:latitudeExpression><![CDATA[37.7750f]]></mp:latitudeExpression>
                    <mp:longitudeExpression><![CDATA[-122.4183f]]></mp:longitudeExpression>
                </mp:map>
            </componentElement>
        </band>
    </summary>
</jasperReport>

==================================================
jrxml file for the second subreport:

<?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="Sample_Report28022013_subreport2" language="groovy" pageWidth="555" pageHeight="802" columnWidth="555" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" isIgnorePagination="true" uuid="5a86bc51-7425-4897-b74c-0dcf7016c666">
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <property name="ireport.jasperserver.reportUnit" value="/reports/Test/Subreports"/>
    <property name="ireport.jasperserver.url" value="http://192.168.17.114:8080/jasperserver/services/repository"/>
    <property name="net.sf.jasperreports.export.html.remove.emtpy.space.between.rows" value="True"/>
    <queryString>
        <![CDATA[SELECT
     ad_dcr."id_client" AS ad_dcr_id_client,
     ad_dcr."detail_obj_dem" AS ad_dcr_detail_obj_dem,
     ad_dcr."cre_mnt_octr" AS ad_dcr_cre_mnt_octr,
     ad_dcr."cre_mnt_deb" AS ad_dcr_cre_mnt_deb,
     ad_cli."id_client" AS ad_cli_id_client,
     ad_cli."num_port" AS ad_cli_num_port,
     ad_cli."pp_prenom" AS ad_cli_pp_prenom,
     ad_cli."pays" AS ad_cli_pays,
     ad_cli."email" AS ad_cli_email,
     ad_cli."pp_nom" AS ad_cli_pp_nom
FROM
     "public"."ad_cli" ad_cli LEFT JOIN "public"."ad_dcr" ad_dcr ON ad_cli."id_client" = ad_dcr."id_client"
ORDER BY ad_cli."id_client"]]>
    </queryString>
    <field name="ad_dcr_id_client" class="java.lang.Integer"/>
    <field name="ad_dcr_detail_obj_dem" class="java.lang.String"/>
    <field name="ad_dcr_cre_mnt_octr" class="java.math.BigDecimal"/>
    <field name="ad_dcr_cre_mnt_deb" class="java.math.BigDecimal"/>
    <field name="ad_cli_id_client" class="java.lang.Integer"/>
    <field name="ad_cli_num_port" class="java.lang.String"/>
    <field name="ad_cli_pp_prenom" class="java.lang.String"/>
    <field name="ad_cli_pays" class="java.lang.Integer"/>
    <field name="ad_cli_email" class="java.lang.String"/>
    <field name="ad_cli_pp_nom" class="java.lang.String"/>
    <background>
        <band splitType="Stretch"/>
    </background>
    <title>
        <band height="23" splitType="Stretch"/>
    </title>
    <pageHeader>
        <band height="43" splitType="Stretch">
            <staticText>
                <reportElement uuid="a071e7c5-1a11-497b-9eee-6b243a947965" x="378" y="0" width="177" height="39"/>
                <textElement textAlignment="Right">
                    <font fontName="Castellar" size="26"/>
                </textElement>
                <text><![CDATA[DCR ]]></text>
            </staticText>
        </band>
    </pageHeader>
    <columnHeader>
        <band height="22" splitType="Stretch">
            <frame>
                <reportElement uuid="12e8ca58-2196-4a8a-9137-c5104e63a485" mode="Opaque" x="0" y="0" width="555" height="20" backcolor="#006666"/>
            </frame>
            <staticText>
                <reportElement uuid="a1b68cab-e347-4a8f-87f8-01064a78854f" x="12" y="0" width="100" height="20" forecolor="#FFFFFF"/>
                <textElement>
                    <font isBold="true"/>
                </textElement>
                <text><![CDATA[Client ID]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="8a1870d5-2682-45a1-bf8b-31a613028daf" x="312" y="0" width="143" height="20" forecolor="#FFFFFF"/>
                <textElement>
                    <font isBold="true"/>
                </textElement>
                <text><![CDATA[Achat]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="df37206c-95bf-4d03-8add-3fc7ca1d07a9" x="455" y="0" width="100" height="20" forecolor="#FFFFFF"/>
                <textElement textAlignment="Center">
                    <font isBold="true"/>
                </textElement>
                <text><![CDATA[Montant]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="40e2dded-de55-4692-8e8d-2e94127dd28e" x="112" y="0" width="100" height="20" forecolor="#FFFFFF"/>
                <textElement>
                    <font isBold="true"/>
                </textElement>
                <text><![CDATA[Nom]]></text>
            </staticText>
            <staticText>
                <reportElement uuid="45abf217-95a0-4ef4-963b-45c2915e5723" x="212" y="0" width="100" height="20" forecolor="#FFFFFF"/>
                <textElement>
                    <font isBold="true"/>
                </textElement>
                <text><![CDATA[Pays]]></text>
            </staticText>
        </band>
    </columnHeader>
    <detail>
        <band height="26" splitType="Stretch">
            <textField>
                <reportElement uuid="7f43f0d2-fcc2-45b3-865e-02d643954c1c" x="12" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{ad_dcr_id_client}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement uuid="c14584b6-c516-423a-ac17-bc4ffeb7d74c" x="312" y="0" width="143" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{ad_dcr_detail_obj_dem}]]></textFieldExpression>
            </textField>
            <textField pattern="#,##0.00">
                <reportElement uuid="88cb8587-2e82-4ea6-9c1d-f53767ffb93c" x="455" y="0" width="100" height="20"/>
                <textElement textAlignment="Center"/>
                <textFieldExpression><![CDATA[$F{ad_dcr_cre_mnt_deb}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement uuid="f4feb876-fbb5-4b7b-b9fb-f24528dfb762" x="112" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{ad_cli_pp_nom}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement uuid="5fed5609-a23b-4cbe-a4b2-a0ebf36e3cd5" x="212" y="0" width="100" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA[$F{ad_cli_pays}]]></textFieldExpression>
            </textField>
            <line>
                <reportElement uuid="6eb43b44-1a3f-4b05-be37-4caeb35e5feb" x="0" y="20" width="555" height="1"/>
            </line>
        </band>
    </detail>
    <columnFooter>
        <band height="7" splitType="Stretch"/>
    </columnFooter>
    <pageFooter>
        <band height="8" splitType="Stretch"/>
    </pageFooter>
    <summary>
        <band height="7" splitType="Stretch"/>
    </summary>
</jasperReport>

Best Answer

I think You can just set the size of a page in main and sub reports to some very large value, for example 999999 and insert "page break" from pallete between sub-reports. This way You should get each subreport in different page, but second one should not span across multiple pages.

Related Solutions

How to show each subreport in a separate Excel sheet

You can solve this issue with help of net.sf.jasperreports.export.xls.break.after.row property.

You can set this net.sf.jasperreports.export.xls.break.after.row property for the "sheets break" element in report (for example, line or staticText element).

In my sample below I've put line element to the Group Footer band for generating new sheet in Excel for a new group.

Here is my sample, master report:

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport ..>
    <parameter name="SUBREPORT_DIR" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA["path_to_subreport"]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT id, addressId FROM document ORDER BY addressId]]>
    </queryString>
    <field name="ID" class="java.lang.Integer"/>
    <field name="ADDRESSID" class="java.lang.Integer"/>
    <group name="addressGroup">
        <groupExpression><![CDATA[$F{ADDRESSID}]]></groupExpression>
        <groupHeader>
            <band height="53">
                <subreport>
                    <reportElement x="189" y="0" width="200" height="31"/>
                    <subreportParameter name="addressId">
                        <subreportParameterExpression><![CDATA[$F{ADDRESSID}]]></subreportParameterExpression>
                    </subreportParameter>
                    <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                    <subreportExpression><![CDATA[$P{SUBREPORT_DIR} + "subreport_new_sheet_for_excel.jasper"]]></subreportExpression>
                </subreport>
                <staticText>
                    <reportElement x="0" y="33" width="100" height="20"/>
                    <textElement textAlignment="Center" verticalAlignment="Middle">
                        <font isItalic="true"/>
                    </textElement>
                    <text><![CDATA[ID]]></text>
                </staticText>
            </band>
        </groupHeader>
        <groupFooter>
            <band height="9">
                <line>
                    <reportElement x="1" y="5" width="100" height="1">
                        <property name="net.sf.jasperreports.export.xls.break.after.row" value="true"/>
                    </reportElement>
                    <graphicElement>
                        <pen lineWidth="0.0"/>
                    </graphicElement>
                </line>
            </band>
        </groupFooter>
    </group>
    <detail>
        <band height="20" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="100" height="20"/>
                <box>
                    <pen lineWidth="1.0"/>
                </box>
                <textElement/>
                <textFieldExpression><![CDATA[$F{ID}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

My subreport:

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport ...>
    <parameter name="addressId" class="java.lang.Integer" isForPrompting="false"/>
    <queryString>
        <![CDATA[SELECT city, street FROM address WHERE id = $P{addressId}]]>
    </queryString>
    <field name="CITY" class="java.lang.String"/>
    <field name="STREET" class="java.lang.String"/>
    <detail>
        <band height="20" splitType="Stretch">
            <textField>
                <reportElement x="0" y="0" width="258" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA["City: " + $F{CITY}]]></textFieldExpression>
            </textField>
            <textField>
                <reportElement x="258" y="0" width="297" height="20"/>
                <textElement/>
                <textFieldExpression><![CDATA["Street: " + $F{STREET}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
</jasperReport>

The result will be:

The generated report in MS Excel, case of using net.sf.jasperreports.export.xls.break.after.row

The result without using of "break" element (I removed the line with net.sf.jasperreports.export.xls.break.after.row property) will be:

The generated report in MS Excel

You can find more about info in Advanced Excel Features post.

Javascript – How does promise.all work

Promise.all takes an array (or any iterable) of promises and fulfills when all of them fulfill or rejects when one of them rejects. I think it's easier to understand if we implement it and understand why we need it.

A common use case might be to wait for the window to load and for the server to return data in order to run some code:

// a function that returns a promise for when the document is ready.
function windowReady(){
    return new Promise(function(resolve){
         window.addEventListener('DOMContentLoaded', resolve);
    }); 
}

// function that returns a promise for some data
function getData(){
    return fetch("/").then(function(r){ return r.json() });
}

Now, we want both of them to execute at the same time and then get the result. There are two items here but there could have easily been 5 things to wait for, or 100. So we use Promise.all:

Promise.all([windowReady(), getData()]).then(function(results){
     // results[1] is the data, it's all in an array.
});

Let's see how we can implement it:

function all(iterable){ // take an iterable 
  // `all` returns a promise.  
  return new Promise(function(resolve, reject){ 
    let counter = 0; // start with 0 things to wait for
    let results = [], i = 0;
    for(let p of iterable){
        let current = i;
        counter++; // increase the counter 
        Promise.resolve(p).then(function(res){ // treat p as a promise, when it is ready: 
          results[i] = res; // keep the current result
          if(counter === 0) resolve(results) // we're done
        }, reject); // we reject on ANY error
       i++; // progress counter for results array
    }
  });
}

Or, in even more ES6ness:

let all = iterable => new Promise((resolve, reject) => { 
  let arr = [...iterable], c = arr.length, results = [];
  arr.map(Promise.resolve, Promise).
      map((p, i) => p.then(v => { 
        r[i] = v;
        if(--c === 0) resolve(r);
      } , reject));
});

Best Answer

Related Solutions

How to show each subreport in a separate Excel sheet

Javascript – How does promise.all work

Related Topic