refermycode.com

Develop your world..

Export the multisheet excel file by using Jasper.

In this blog we are going to learn, how to export multisheet excel file by using Jasper. So we will take an example in which we will print some information in one sheet and some information in another sheet.

Employee NumberEmployee NameEmployee AddressEmployee Salary
1001Adams Springfield 40,000
1002Anderson Clinton 50,000
1003Clark Madison 50,000
1004David Washington 60,000
1005Harris Clinton 50,000
1006Johnson Franklin 60,000
1007Mitchell Washington 50,000
1008Nelson Chester 30,000
1009Robinson Marion 60,000
1010Roberts Greenville 70,000
1011Smith Georgetown 80,000
1012Scott Salem 40,000

In another sheet we will display the information of Department.

Department NumberDepartment NameLocation
101HR Madison
102IT Madison
103Payroll Madison
104Inventory Washington
105Production Washington

We have print this records into 2 different sheets of single excel file.

So let us start with the designing phase,

In iReport we are going to create 2 different design (Sub Reports) for both sheets, one for Employee Information and another for Department Information. Hence we are merging these 2 Sub Report into another report which we are calling here Main Report.

Step 1: Design of First Sub Report.

We need to add the fields which is required to display the information about Employees. Hence we are going to add the necessary field into the Column Header and Detail section as shown in following screenshot.

Blog4_1

Here is the sample code of First 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="mayursreport" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
<property name="ireport.zoom" value="1.464100000000001"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<field name="employeeNumber" class="java.lang.String"/>
<field name="employeeName" class="java.lang.String"/>
<field name="employeeAddress" class="java.lang.String"/>
<field name="employeeSalary" class="java.lang.String"/>
<pageHeader>
<band height="50">
<staticText>
        <reportElement x="120" y="16" width="300" height="20"/>
        <textElement textAlignment="Center">
                <font size="12" isBold="true"/>
        </textElement>
        <text><![CDATA[This is First SubReport Title!]]></text>
</staticText>
</band>
</pageHeader>
<columnHeader>
<band height="30">
<staticText>
        <reportElement x="17" y="5" width="120" height="20"/>
        <textElement>
                <font size="8" isBold="true"/>
        </textElement>
        <text><![CDATA[Employee Number]]></text>
</staticText>
<staticText>
        <reportElement x="150" y="5" width="120" height="20"/>
        <textElement>
                <font size="8" isBold="true"/>
        </textElement>
        <text><![CDATA[Employee Name]]></text>
</staticText>
<staticText>
        <reportElement x="283" y="5" width="120" height="20"/>
        <textElement>
                <font size="8" isBold="true"/>
        </textElement>
        <text><![CDATA[Employee Address]]></text>
</staticText>
<staticText>
        <reportElement x="420" y="5" width="120" height="20"/>
        <textElement>
                <font size="8" isBold="true"/>
        </textElement>
        <text><![CDATA[Employee Salary]]></text>
</staticText>
</band>
</columnHeader>
<detail>
<band height="26">
<textField>
        <reportElement x="17" y="5" width="120" height="20"/>
        <textElement>
                <font size="8"/>
        </textElement>
        <textFieldExpression><![CDATA[$F{employeeNumber}]]></textFieldExpression>
</textField>
<textField>
        <reportElement x="150" y="5" width="120" height="20"/>
        <textElement>
                <font size="8"/>
        </textElement>
        <textFieldExpression><![CDATA[$F{employeeName}]]></textFieldExpression>
</textField>
<textField>
        <reportElement x="283" y="5" width="120" height="20"/>
        <textElement>
                <font size="8"/>
        </textElement>
        <textFieldExpression><![CDATA[$F{employeeAddress}]]></textFieldExpression>
</textField>
<textField>
        <reportElement x="420" y="5" width="120" height="20"/>
        <textElement>
                <font size="8"/>
        </textElement>
        <textFieldExpression><![CDATA[$F{employeeSalary}]]></textFieldExpression>
</textField>
</band>
</detail>
</jasperReport>

Step 2: Design of Second Sub Report.

In this step we will add the fields which is required to display the information about Department. Hence we are going to add the necessary field into the Column Header and Detail section as shown in following screenshot.

Blog4_2

Here is the sample code of 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="mayursreport" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
<property name="ireport.zoom" value="1.464100000000001"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<field name="departmentNumber" class="java.lang.String"/>
<field name="departmentName" class="java.lang.String"/>
<field name="departmentLocation" class="java.lang.String"/>
<pageHeader>
<band height="50">
<staticText>
        <reportElement x="114" y="20" width="300" height="20"/>
        <textElement textAlignment="Center">
                <font size="12" isBold="true"/>
        </textElement>
        <text><![CDATA[This is Second SubReport Title!]]></text>
</staticText>
</band>
</pageHeader>
<columnHeader>
<band height="30">
<staticText>
        <reportElement x="17" y="5" width="120" height="20"/>
        <textElement>
                <font size="8" isBold="true"/>
        </textElement>
        <text><![CDATA[Department Number]]></text>
</staticText>
<staticText>
        <reportElement x="150" y="5" width="120" height="20"/>
        <textElement>
                <font size="8" isBold="true"/>
        </textElement>
        <text><![CDATA[Department Name]]></text>
</staticText>
<staticText>
        <reportElement x="283" y="5" width="120" height="20"/>
        <textElement>
                <font size="8" isBold="true"/>
        </textElement>
        <text><![CDATA[Location]]></text>
</staticText>
</band>
</columnHeader>
<detail>
<band height="26">
<textField>
        <reportElement x="17" y="5" width="120" height="20"/>
        <textElement>
                <font size="8"/>
        </textElement>
        <textFieldExpression><![CDATA[$F{departmentNumber}]]></textFieldExpression>
</textField>
<textField>
        <reportElement x="150" y="5" width="120" height="20"/>
        <textElement>
                <font size="8"/>
        </textElement>
        <textFieldExpression><![CDATA[$F{departmentName}]]></textFieldExpression>
</textField>
<textField>
        <reportElement x="283" y="5" width="120" height="20"/>
        <textElement>
                <font size="8"/>
        </textElement>
        <textFieldExpression><![CDATA[$F{departmentLocation}]]></textFieldExpression>
</textField>
</band>
</detail>
</jasperReport>

Step 3: Design of Main Report.

In this step we are going to add this two subreports into our main report. To Separate these two reports we are going to use page break tool which is provided by iReport tool. So add first subreport in fist detail band, then page break in second detail band and finally second subreport in third detail band as shown in following screenshot.

Blog4_3

Here is the sample code of 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="mayursreport" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
<parameter name="SUBREPORT_1" class="net.sf.jasperreports.engine.JasperReport"/>
<parameter name="SUBREPORT_2" class="net.sf.jasperreports.engine.JasperReport"/>
<parameter name="SUBREPORT_DATA_1" class="net.sf.jasperreports.engine.data.JRBeanCollectionDataSource"/>
<parameter name="SUBREPORT_DATA_2" class="net.sf.jasperreports.engine.data.JRBeanCollectionDataSource"/>
<detail>
<band height="26">
<subreport>
        <reportElement mode="Transparent" x="0" y="0" width="555" height="26"/>
        <dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.data.JRBeanCollectionDataSource ($P{SUBREPORT_DATA_1}.getData())]]></dataSourceExpression>
        <subreportExpression class="net.sf.jasperreports.engine.JasperReport"><![CDATA[$P{SUBREPORT_1}]]></subreportExpression>
</subreport>
</band>
<band height="13">
<break>
        <reportElement mode="Opaque" x="0" y="6" width="555" height="1"/>
</break>
</band>
<band height="27">
<subreport>
        <reportElement mode="Transparent" x="0" y="0" width="555" height="26"/>
        <dataSourceExpression><![CDATA[new net.sf.jasperreports.engine.data.JRBeanCollectionDataSource ($P{SUBREPORT_DATA_2}.getData())]]></dataSourceExpression>
        <subreportExpression class="net.sf.jasperreports.engine.JasperReport"><![CDATA[$P{SUBREPORT_2}]]></subreportExpression>
</subreport>
</band>
</detail>
</jasperReport>

In this way, we have finished design phase, now let us move to the Java Code.

We need to create two separate POJO classes to store the information for Employee and Department, hence we have created distinct classes for Employee and Department in same package.

Here is sample code for Employee class.

package com.refermycode.mypackage;
/*
* @author Mayur
*/

public class Employee {
String employeeNumber = "";
String employeeName = "";
String employeeAddress = "";
String employeeSalary = "";
public String getEmployeeAddress() {
return employeeAddress;
}
public void setEmployeeAddress(String employeeAddress) {
this.employeeAddress = employeeAddress;
}
public String getEmployeeName() {
return employeeName;
}
public void setEmployeeName(String employeeName) {
this.employeeName = employeeName;
}
public String getEmployeeNumber() {
return employeeNumber;
}
public void setEmployeeNumber(String employeeNumber) {
this.employeeNumber = employeeNumber;
}
public String getEmployeeSalary() {
return employeeSalary;
}
public void setEmployeeSalary(String employeeSalary) {
this.employeeSalary = employeeSalary;
}
}

Here is sample code for Department.

package com.refermycode.mypackage;

/*
* @author Maddy
*/

public class Department {
String departmentNumber = "";
String departmentName = "";
String departmentLocation = "";
public String getDepartmentNumber() {
return departmentNumber;
}
public void setDepartmentNumber(String departmentNumber) {
this.departmentNumber = departmentNumber;
}
public String getDepartmentName() {
return departmentName;
}
public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
}
public String getDepartmentLocation() {
return departmentLocation;
}
public void setDepartmentLocation(String departmentLocation) {
this.departmentLocation = departmentLocation;
}
}

Now let us write a another class which we will use to store the data and export it into the Excel file.

Here is sample code for JasperBlog4.java which include the main method in it.

package com.refermycode.mypackage;

import java.io.*;
import java.util.AbstractList;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import net.sf.jasperreports.engine.*;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import net.sf.jasperreports.engine.design.JasperDesign;
import net.sf.jasperreports.engine.export.JRXlsExporter;
import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
import net.sf.jasperreports.engine.xml.JRXmlLoader;

/*
* @author Mayur
*/

public class JasperBlog4 {

/* Method to add Employee Information */
public static List addEmployeeInfo() {
List employeeList = new ArrayList();

Employee employee = new Employee();
employee.setEmployeeNumber("1001");
employee.setEmployeeName("Adams");
employee.setEmployeeAddress("Springfield");
employee.setEmployeeSalary("40,000");
employeeList.add(employee);

employee = new Employee();
employee.setEmployeeNumber("1002");
employee.setEmployeeName("Anderson");
employee.setEmployeeAddress("Clinton");
employee.setEmployeeSalary("50,000");
employeeList.add(employee);

employee = new Employee();
employee.setEmployeeNumber("1004");
employee.setEmployeeName("Clark");
employee.setEmployeeAddress("Madison");
employee.setEmployeeSalary("50,000");
employeeList.add(employee);

employee = new Employee();
employee.setEmployeeNumber("1004");
employee.setEmployeeName("David");
employee.setEmployeeAddress("Washington");
employee.setEmployeeSalary("60,000");
employeeList.add(employee);

employee = new Employee();
employee.setEmployeeNumber("1005");
employee.setEmployeeName("Harris");
employee.setEmployeeAddress("Clinton");
employee.setEmployeeSalary("50,000");
employeeList.add(employee);

employee = new Employee();
employee.setEmployeeNumber("1006");
employee.setEmployeeName("Johnson");
employee.setEmployeeAddress("Franklin");
employee.setEmployeeSalary("60,000");
employeeList.add(employee);

employee = new Employee();
employee.setEmployeeNumber("1007");
employee.setEmployeeName("Mitchell");
employee.setEmployeeAddress("Washington");
employee.setEmployeeSalary("50,000");
employeeList.add(employee);

employee = new Employee();
employee.setEmployeeNumber("1008");
employee.setEmployeeName("Nelson");
employee.setEmployeeAddress("Chester");
employee.setEmployeeSalary("30,000");
employeeList.add(employee);

employee = new Employee();
employee.setEmployeeNumber("1009");
employee.setEmployeeName("Robinson");
employee.setEmployeeAddress("Marion");
employee.setEmployeeSalary("60,000");
employeeList.add(employee);

employee = new Employee();
employee.setEmployeeNumber("1010");
employee.setEmployeeName("Roberts");
employee.setEmployeeAddress("Greenville");
employee.setEmployeeSalary("70,000");
employeeList.add(employee);

employee = new Employee();
employee.setEmployeeNumber("1011");
employee.setEmployeeName("Smith");
employee.setEmployeeAddress("Georgetown");
employee.setEmployeeSalary("80,000");
employeeList.add(employee);

employee = new Employee();
employee.setEmployeeNumber("1012");
employee.setEmployeeName("Scott");
employee.setEmployeeAddress("Salem");
employee.setEmployeeSalary("40,000");
employeeList.add(employee);

return employeeList;
}

/* Method to add Department Information */
public static List addDepartmentInfo() {
List departmentList = new ArrayList();

Department department = new Department();
department.setDepartmentNumber("101");
department.setDepartmentName("HR");
department.setDepartmentLocation("Madison");
departmentList.add(department);

department = new Department();
department.setDepartmentNumber("102");
department.setDepartmentName("IT");
department.setDepartmentLocation("Madison");
departmentList.add(department);

department = new Department();
department.setDepartmentNumber("103");
department.setDepartmentName("Payroll");
department.setDepartmentLocation("Madison");
departmentList.add(department);

department = new Department();
department.setDepartmentNumber("104");
department.setDepartmentName("Inventory");
department.setDepartmentLocation("Washington");
departmentList.add(department);

department = new Department();
department.setDepartmentNumber("105");
department.setDepartmentName("Production");
department.setDepartmentLocation("Washington");
departmentList.add(department);

return departmentList;
}

public static void main(String[] args) throws FileNotFoundException, IOException {
try {
/* Hashmap to store all Parameters required for Export Operation */
HashMap parameters = new HashMap();

/* Loading and Compiling First Subreport */
InputStream inputStreamSubReport1 = new FileInputStream("C:/JasperBlog4/src/com/refermycode/mypackage/SubReport1.jrxml");
JasperDesign jasperDesignSubReport1 = JRXmlLoader.load(inputStreamSubReport1);
JasperReport jasperReportSubReport1 = JasperCompileManager.compileReport(jasperDesignSubReport1);

/* Loading and Compiling Second Subreport */
InputStream inputStreamSubReport2 = new FileInputStream("C:/JasperBlog4/src/com/refermycode/mypackage/SubReport2.jrxml");
JasperDesign jasperDesignSubReport2 = JRXmlLoader.load(inputStreamSubReport2);
JasperReport jasperReportSubReport2 = JasperCompileManager.compileReport(jasperDesignSubReport2);

/* Loading and Compiling Main Report */
InputStream inputStream = new FileInputStream("C:/JasperBlog4/src/com/refermycode/mypackage/MainReport.jrxml");
JasperDesign jasperDesign = JRXmlLoader.load(inputStream);
JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);

List employeeList = addEmployeeInfo();
List departmentList = addDepartmentInfo();

/* Putting the Subreport and Data into Parameters */
parameters.put("SUBREPORT_1", jasperReportSubReport1);
parameters.put("SUBREPORT_2", jasperReportSubReport2);
parameters.put("SUBREPORT_DATA_1", new JRBeanCollectionDataSource(employeeList));
parameters.put("SUBREPORT_DATA_2", new JRBeanCollectionDataSource(departmentList));

/* Putting some Dummy Data for Main Report,
* if you not pass this dummy data then result will be blank sheet.
* and if you send multiple times then it will print same data multiple times.*/

ArrayList mainReportsList = new ArrayList&lt;&gt;();
mainReportsList.add("DummyValue");

JRBeanCollectionDataSource beanColDataSource = new JRBeanCollectionDataSource(mainReportsList);
JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, beanColDataSource);

/*If you want to print the data in PDF format then you can add this line into code.
JasperExportManager.exportReportToPdfFile(jasperPrint, "C:/test_jasper.pdf");
*/

ByteArrayOutputStream outputByteArray = new ByteArrayOutputStream();
OutputStream outputfile = new FileOutputStream(new File("C:/test_jasper.xls"));

JRXlsExporter xlsExporter = new JRXlsExporter();
xlsExporter.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
xlsExporter.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, outputByteArray);

/* This is Properties of Excel file which is used for configuration setting. */
xlsExporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.TRUE);
xlsExporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
xlsExporter.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.FALSE);
xlsExporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);

xlsExporter.exportReport();
outputfile.write(outputByteArray.toByteArray());

} catch (JRException ex) {
Logger.getLogger(JasperBlog4.class.getName()).log(Level.SEVERE, null, ex);
}
}
}

To export this file we need additional library called as poi-X.X.jar  which can be found in following path of installation folder of iReport.

“iReport-X.X.X\ireport\modules\ext”.

Include this into classpath of project. If you forget to include this library in Project then it will throw following exception while execution of Project.

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/poi/ss/usermodel/RichTextString

If you have follow all the above steps then run your project and you will get following excel sheet with two sheets as shown in following screenshot.

Blog4_4

In short when we need to display our data on multiple sheets then we need to add below 2 things into ireport design.

  1. Page Break Component to create multiple pages.
  2. Set following property “net.sf.jasperreports.export.xls.one.page.per.sheet” to TRUE.

In this way we can export the multisheet excel file by using Jasper.

Please don’t forget to comment your feedback for this post. 🙂