Create Excel POI Java

Create Excel POI Java

Apache POI is open source java library for reading and writing file in various Microsoft office format such as Excel, Microsoft Word, PowerPoint. Here we will see how to create Excel sheet using Apahce POI. As you might have heard of HSSF and XSSF to read and write spreadsheets and want to know what is the difference between these:

HSSF use to create XLS sheet while XSSF support to create XLSX sheet which is Microsoft new format. Below is and example of both:

Workbook workbook = new HSSFWorkbook();
FileOutputStream fileOutputStream = new FileOutputStream("JavaHonk.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();


Workbook workbook = new XSSFWorkbook();
FileOutputStream fileOutputStream = new FileOutputStream("JavaHonk.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();

• In this example I will show you how to use XSSFWorkbook to create xlsx sheet as both feature are same only extensions are different. Create maven project name: XLSGeneratorUsingPOI below is project structure:

• To use Apache POI you need its jar as mentioned in pom.xml file. I have added log4j jars as well for logging purpose but you don’t need to generate XLSX sheet.

<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>com.javahonk.report</groupId>
  <artifactId>XLSGeneratorUsingPOI</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  
    <properties>
		<org.apache.log4j.version>2.1</org.apache.log4j.version>
		<org.apache.poi.version>3.11</org.apache.poi.version>
	</properties>
	
	<dependencies>	
 
		<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>
		
		<!-- 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>
		
	</dependencies>
	
</project>

• XLSDataModel.java: Java POJO to store data:

package com.javahonk.report.model;

public class XLSDataModel {
	
	private String firstName; 
	private String lastName; 
	private String location;
	
	public XLSDataModel(String firstName, String lastName, String location) {
		super();
		this.firstName = firstName;
		this.lastName = lastName;
		this.location = location;
	}
	public String getFirstName() {
		return firstName;
	}
	public String getLastName() {
		return lastName;
	}
	public String getLocation() {
		return location;
	}
	
}

• ExcelGenerator.java: I have made this class as abstract and kept all logic to generate sheet here so that if you have requirement to generate multiple sheets then It will be easy to just extends this class:

package com.javahonk.report.poi;

import java.awt.Color;
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;

abstract class ExcelGenerator<T> {
	
	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);
		}
	}
	
	protected void processWorkSheet(XSSFSheet sheet, List<T> entries) {
		int exCount = 0;
		int lastCellNum = 0;
		
		for(T entry : entries){
			XSSFRow row = sheet.createRow(++exCount);	
			List<String> elements = getColumnElements(entry);
			writeRecord(row , elements);
			lastCellNum =  row.getLastCellNum();
		}
		
		for(int i=0; i < lastCellNum; i++){
			sheet.autoSizeColumn(i);
		}
	}
	
	protected 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));
		}		
	}
	
	abstract protected List<String> getColumnElements(T model);
}

• Now to I have created child class: ExcelGeneratorChild which extends ExcelGenerator.java to create XLSX sheet.

package com.javahonk.report.poi;

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;

import com.wfs.otc.report.model.XLSDataModel;

public class ExcelGeneratorChild extends ExcelGenerator<XLSDataModel>{
	
	private static final Logger logger = LogManager.getLogger(ExcelGeneratorChild.class.getName());

	public File createXLS(
			String reportName,
			List<XLSDataModel> modelData){
		
		File file = null;
		
		try {
			
			XSSFWorkbook workbook = new XSSFWorkbook();
			XSSFSheet xSSFSheet = workbook.createSheet("Sheet 1");
			XSSFSheet xSSFSheet2 = workbook.createSheet("Sheet 2");
						
			String[]  reportFields = getReportFields();

			setUpWorkSheets(workbook,xSSFSheet, reportFields);
			setUpWorkSheets(workbook,xSSFSheet2, reportFields);
			
			List<XLSDataModel> sheetData = new ArrayList<XLSDataModel>();
			for(XLSDataModel expiryEvent : modelData){
				sheetData.add(expiryEvent);				
			}
			
			processWorkSheet(xSSFSheet, sheetData);	
			processWorkSheet(xSSFSheet2, sheetData);	
			
			FileOutputStream out = new FileOutputStream("C:\\LAS\\JavaHonk\\"+reportName+".xlsx");
			workbook.write(out);
			out.close();
			
			file = new  File("C:\\LAS\\JavaHonk\\"+reportName+".xlsx");
			
		} 
		catch (Exception e) {
			
			logger.error("Error generating excel report:\n", e);
		}
	
		return file;
	}
	
	@Override
	protected List<String> getColumnElements(XLSDataModel expiryEvent) {
		List<String> elements = new ArrayList<String>();
		
		elements.add(expiryEvent.getFirstName());
		elements.add(expiryEvent.getLastName());
		elements.add(expiryEvent.getLocation());
		
		return elements;
	}
	
	public static String[] getReportFields(){
		
		String[] fields = new String[3];
		fields[0] = "First Name";
		fields[1] = "Last Name";
		fields[2] = "Location";		
		
		return fields;
	}
}

• log4j2.xml: This is not needed. If you want to include logger as well in your application you could use it:

<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="INFO" shutdownHook="disable">

	<Properties>
		<Property name="envrionment.target">DEV</Property>
	</Properties>

	<Properties>
		<Property name="logging.dir">./</Property>
	</Properties>

	<Appenders>
		<Console name="Console" target="SYSTEM_OUT">
			<PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
		</Console>

		<RollingFile name="RollingFile"
			fileName="logs/rolling-file.log"	filePattern="${sys:logging.dir}/logs/rolling-file-%d{yyyy-MM-dd}-%i.log">
			<PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n" />
			<!-- TODO:Change to time based policy -->
			<Policies>
				<TimeBasedTriggeringPolicy interval="1"	modulate="true" />
				<SizeBasedTriggeringPolicy size="100 MB" />
			</Policies>
			<DefaultRolloverStrategy max="4" />
		</RollingFile>
	</Appenders>

	<Loggers>
		<Root level="info">
			<AppenderRef ref="Console" />
			<!-- <AppenderRef ref="file" /> -->
			<AppenderRef ref="RollingFile" />
		</Root>
	</Loggers>
</Configuration>

• That’s it. Finally java main class CreateXLSSheet.java to generate XLSX sheet:

package com.javahonk.report;

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

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import com.wfs.otc.report.model.XLSDataModel;
import com.wfs.otc.report.poi.ExcelGeneratorChild;



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

	public static void main(String[] args) {
		
		ExcelGeneratorChild xlsReportGenerator = new ExcelGeneratorChild();
		
		List<XLSDataModel> dataModel = new ArrayList<XLSDataModel>();
		dataModel.add(new XLSDataModel("Java", "Honk", "NY"));
		dataModel.add(new XLSDataModel("Java2", "Honk2", "NY"));
		dataModel.add(new XLSDataModel("Java2", "Honk2", "NY"));
		dataModel.add(new XLSDataModel("Java2", "Honk2", "NY"));
		
		File file = xlsReportGenerator.createXLS("JavaHonk", dataModel);
		
		logger.info("XLS got generated. Path is {}",file.getAbsoluteFile());

	}

}

• Run main class CreateXLSSheet.java as java application. You will see out put on console that XLSX sheet got generated:

• If you want more depth details please visit Apache POI documentation here on its site