Generate XLS report Multiple Sheet Apache XSSF

Generate XLS report Multiple Sheet Apache XSSF

In previous tutorials you have many ways to generate report in XLS format. In this example you will see how to generate XLS report using Apache POI-XSSF. You could directly copy this project and include any of your XLS report generation requirement.

Tools needed:

• Any latest version of Eclipse
• JDK 1.8
• Apache XSSF related jars (Dependencies already included in project)
• Maven (It comes with eclipse)

Maven project name: ApacheXSSFReportJava:

• Project dependencies:pom.xml:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>ApacheXSSFReportJava</groupId>
  <artifactId>ApacheXSSFReportJava</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>ApacheXSSFReportJava</name>
  <description>ApacheXSSFReportJava</description>
  
  <properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

		<maven.compiler.source>1.8</maven.compiler.source>
		<maven.compiler.target>1.8</maven.compiler.target>
		
		<org.springframework.version>4.1.5.RELEASE</org.springframework.version>
		<org.apache.log4j.version>2.1</org.apache.log4j.version>
		<org.apache.poi.version>3.11</org.apache.poi.version>	
		
	</properties>

	<dependencies>	

		<!-- Spring -->
		<!-- Core utilities used by other modules. Define this if you use Spring 
			Utility APIs (org.springframework.core.*/org.springframework.util.*) -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Expression Language (depends on spring-core) Define this if you use 
			Spring Expression APIs (org.springframework.expression.*) -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-expression</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Bean Factory and JavaBeans utilities (depends on spring-core) Define 
			this if you use Spring Bean APIs (org.springframework.beans.*) -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-beans</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Aspect Oriented Programming (AOP) Framework (depends on spring-core, 
			spring-beans) Define this if you use Spring AOP APIs (org.springframework.aop.*) -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-aop</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Application Context (depends on spring-core, spring-expression, spring-aop, 
			spring-beans) This is the central artifact for Spring's Dependency Injection 
			Container and is generally always defined -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Various Application Context utilities, including EhCache, JavaMail, 
			Quartz, and Freemarker integration Define this if you need any of these integrations -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context-support</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- JDBC Data Access Library (depends on spring-core, spring-beans, spring-context, 
			spring-tx) Define this if you use Spring's JdbcTemplate API (org.springframework.jdbc.*) -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Object-to-Relation-Mapping (ORM) integration with Hibernate, JPA, 
			and iBatis. (depends on spring-core, spring-beans, spring-context, spring-tx) 
			Define this if you need ORM (org.springframework.orm.*) -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-orm</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Web application development utilities applicable to both Servlet and 
			Portlet Environments (depends on spring-core, spring-beans, spring-context) 
			Define this if you use Spring MVC, or wish to use Struts, JSF, or another 
			web framework with Spring (org.springframework.web.*) -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-web</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Support for testing Spring applications with tools such as JUnit and 
			TestNG This artifact is generally always defined with a 'test' scope for 
			the integration testing framework and unit testing stubs -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>${org.springframework.version}</version>
			<scope>test</scope>
		</dependency>
		
		<!-- Spring Aspects -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-aspects</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>
		
		<!-- Spring Tx -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-tx</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>

		<!-- Spring JMS -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jms</artifactId>
			<version>${org.springframework.version}</version>
		</dependency>		
		
		<!-- Log4j -->
        <dependency>
        	<groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>${org.apache.log4j.version}</version>
		</dependency>
        
        <dependency>
        	<groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>${org.apache.log4j.version}</version>
		</dependency>
		
		<!-- Apache POI jars -->
		
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>${org.apache.poi.version}</version>
		</dependency>
		<dependency>
    		<groupId>org.apache.poi</groupId>
    		<artifactId>poi-ooxml</artifactId>
    		<version>${org.apache.poi.version}</version>
		</dependency>

	</dependencies>
		
</project>

• Spring context file: common-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:jdbc="http://www.springframework.org/schema/jdbc"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:util="http://www.springframework.org/schema/util"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
		http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.1.xsd
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.1.xsd">

    <context:annotation-config />
    <context:component-scan base-package="com.javahonk" />

    <util:list id="propertiesList" value-type="java.lang.String" >
        <value>JavaHonk.properties</value>        
    </util:list>

    <bean id="propertiesBean" class="org.springframework.beans.factory.config.PropertiesFactoryBean" >
        <property name="locations" ref="propertiesList" />
        <property name="ignoreResourceNotFound" value="true" />
    </bean>
    
    <bean id="JavaHonkReport" class="com.javahonk.JavaHonkReport"/>

</beans>

• Model to load dummry data into xls sheet:

package com.javahonk;

public class JavaHonkReportModel {
	
	private String firstName;
	private String lastName;
	private String city;
	private String phone;
	private String state;
	private String zip;
	
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public String getCity() {
		return city;
	}
	public void setCity(String city) {
		this.city = city;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public String getState() {
		return state;
	}
	public void setState(String state) {
		this.state = state;
	}
	public String getZip() {
		return zip;
	}
	public void setZip(String zip) {
		this.zip = zip;
	}
	@Override
	public String toString() {
		return "DSPSGalaxyReconModel [firstName=" + firstName + ", lastName="
				+ lastName + ", city=" + city + ", phone=" + phone + ", state="
				+ state + ", zip=" + zip + "]";
	}
	

}

• Report filed class which represents XLS column names: JavaHonkReportFields.java

package com.javahonk;

public class JavaHonkReportFields {

	public static String[] getJavaHonkReportFields(){
		
		String[] fields = new String[6];
		fields[0] = "First Name";
		fields[1] = "Last Name";
		fields[2] = "City";
		fields[3] = "Phone";
		fields[4] = "State";
		fields[5] = "Zip";
		
		return fields;
	}
	
}

• Main XLS generator class: JavaHonkExcelReportGenerator.java

package com.javahonk;

import java.awt.Color;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class JavaHonkExcelReportGenerator {
	
	protected void setUpWorkSheets(
			XSSFWorkbook workbook, 
			XSSFSheet sheet, 
			String[] field) {
		
		XSSFRow row = sheet.createRow(0);
		
		XSSFCellStyle style = workbook.createCellStyle();
		XSSFFont font = workbook.createFont();
		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
		font.setColor(new XSSFColor(Color.WHITE));
	    style.setFont(font);
	    style.setFillForegroundColor(new XSSFColor(new byte[]{(byte)79,(byte)129,(byte)189}));
	    style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);


		int len = field.length;
		
		for(int i = 0; i < len; i++) {
			XSSFCell genericCell = row.createCell(i);
			genericCell.setCellValue(field[i]);
		}
		
		for(int k = 0; k <row.getLastCellNum(); k++){
			row.getCell(k).setCellStyle(style);
		}
	}
	
	private void writeRecord(XSSFRow row, List<String> elements){
		int len = elements.size();
		
		for (int i = 0; i < len; i++) {
			XSSFCell genericCell = row.createCell(i);
			genericCell.setCellValue(elements.get(i));
		}		 
	}
	
	public void processDSPSGalaxyWorkSheet(XSSFSheet sheet, List<JavaHonkReportModel> entries) {
		int exCount = 0;
		int lastCellNum = 0;
		
		for(JavaHonkReportModel entry : entries){
			XSSFRow row = sheet.createRow(++exCount);	
			List<String> elements = getDSPSGalaxyColumnElements(entry);
			writeRecord(row , elements);
			lastCellNum =  row.getLastCellNum();
		}
		
		for(int i=0; i < lastCellNum; i++){
			sheet.autoSizeColumn(i);
		}
	}
	
	private List<String> getDSPSGalaxyColumnElements(JavaHonkReportModel model){
		
		List<String> elements = new ArrayList<String>();
		
		elements.add(model.getFirstName());
		elements.add(model.getLastName());
		elements.add(model.getCity());
		elements.add(model.getPhone());
		elements.add(model.getState());
		elements.add(model.getZip());
		
		return elements;
		
	}
	
}

• Report generator class which extends JavaHonkExcelReportGenerator to put logic to pull to data and generate XLS sheet:

package com.javahonk;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class JavaHonkReport extends JavaHonkExcelReportGenerator{
	
	private static final Logger logger = LogManager.getLogger(JavaHonkReport.class.getName());

	public void generateReport() {

		logger.info("Sending Galaxy DSPS recon report...");

		createDSPSGalaxySheet("JavaHonk Report");

		logger.info("Java Honk report sent successfully...");

	}
    
    public File createDSPSGalaxySheet(String reportName) {
		
		File file = null;
		
		try {
			
			XSSFWorkbook workbook = new XSSFWorkbook();
			XSSFSheet javahonkSheet1 = workbook.createSheet("JAVAHONK_DATA1");
			XSSFSheet javahonkSheet2 = workbook.createSheet("JAVAHONK_DATA2");
			XSSFSheet javahonkSheet3 = workbook.createSheet("JAVAHONK_DATA3");

			setUpWorkSheets(workbook,javahonkSheet1, JavaHonkReportFields.getJavaHonkReportFields());
			setUpWorkSheets(workbook,javahonkSheet2, JavaHonkReportFields.getJavaHonkReportFields());
			setUpWorkSheets(workbook,javahonkSheet3, JavaHonkReportFields.getJavaHonkReportFields());			
			
			List<JavaHonkReportModel> javaHonkData1 = new ArrayList<JavaHonkReportModel>();
			
			for (int i = 0; i < 10; i++) {
				
				JavaHonkReportModel javaHonkReportModel = new JavaHonkReportModel();
				javaHonkReportModel.setFirstName("firstName");
				javaHonkReportModel.setLastName("lastName");
				javaHonkReportModel.setCity("city");
				javaHonkReportModel.setPhone("phone");
				javaHonkReportModel.setState("state");
				javaHonkReportModel.setZip("zip");
				javaHonkData1.add(javaHonkReportModel);
			}
			
			processDSPSGalaxyWorkSheet(javahonkSheet1, javaHonkData1);
			processDSPSGalaxyWorkSheet(javahonkSheet2, javaHonkData1);
			processDSPSGalaxyWorkSheet(javahonkSheet3, javaHonkData1);
						
			FileOutputStream out = new FileOutputStream(reportName+".xlsx");
			workbook.write(out);
			out.close();
			file = new  File(reportName+".xlsx");
			
		} 
		catch (Exception e) {
			
			logger.error("Error generating excel report:\n", e);
		}
	
		return file;
	}    
    
}

• JavaHonkMainApp.java class which load Spring context and use class to generate the report:

package com.javahonk;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class JavaHonkMainApp {

    private static final Logger logger = LogManager.getLogger(JavaHonkMainApp.class);

    public static void main(String[] args) {

        logger.info("Starting JavaHonkMainApp...");

        ApplicationContext context = new ClassPathXmlApplicationContext("common-context.xml");
        
        context.getBean(JavaHonkReport.class).generateReport();
        
        registerShutdownHook(context);
    }

    private static void registerShutdownHook(ApplicationContext context) {
        Runtime.getRuntime().addShutdownHook(new Thread(() -> {
            logger.info("JavaHonkMainApp Exiting.");
            ((AbstractApplicationContext) context).close();
        }));
    }
}

• Now to generate the report right click JavaHonkMainApp –> Run As –> Java application in eclipse you will see report generated in current project directory (Please refresh your project)

• Generated report:

• Download project: apachexssfreportjava
• Reference: Apache POI XSSF