Generate/Read an Excel file in Java using Apache POI

In this tutorial, we will find how to generate and read excel files in java using apache poi.

Apache POI is a library which is created to work with Microsoft office formats.

You'd use HSSF if you needed to read or write an Excel file using Java (XLS). You'd use XSSF if you need to read or write an OOXML Excel file using Java (XLSX). The combined SS interface allows you to easily read and write all kinds of Excel files (XLS and XLSX) using Java.

Additionally, there is a specialized SXSSF implementation which allows writing very large Excel (XLSX) files in a memory-optimized way.

In maven we have two separate dependencies for each type. poi artifact id used for .xls files and poi-ooxml used for .xlsx files.

Find this project on Github

https://github.com/javacodenet/excelDemo

pom.xml

	<?xml version="1.0" encoding="UTF-8"?>
	<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.javacodenet</groupId>
	<artifactId>excel-demo</artifactId>
	<version>1.0-SNAPSHOT</version>
	<dependencies>
	<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.16</version>
	</dependency>
	<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.16</version>
	</dependency>
	</dependencies>
	</project>

view raw pom.xml hosted with ❤ by GitHub

ExcelApplication.java

	package com.javacodenet;
	import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
	import java.io.IOException;
	import java.util.ArrayList;
	import java.util.List;
	public class ExcelApplication {
	public static void main(String[] args) throws IOException, InvalidFormatException {
	List<Employee> employees = new ArrayList<Employee>();
	for (int i = 0; i < 10; i++) {
	Employee employee = new Employee("empId" + i, "empName" + i, "dept" + i, 1000 + i);
	employees.add(employee);
	}
	//Create .xlsx file
	ExcelHelper.createExcelSheet(employees, true);
	//Read .xlsx file
	ExcelHelper.readExcelSheet(true);
	//Create .xls file
	ExcelHelper.createExcelSheet(employees, false);
	//Read .xls file
	ExcelHelper.readExcelSheet(false);
	}
	}

view raw ExcelApplication.java hosted with ❤ by GitHub

Employee.java

	package com.javacodenet;
	public class Employee {
	private String employeeId;
	private String employeeName;
	private String department;
	private int salary;
	public Employee(String employeeId, String employeeName, String department, int salary) {
	this.employeeId = employeeId;
	this.employeeName = employeeName;
	this.department = department;
	this.salary = salary;
	}
	public String getEmployeeId() {
	return employeeId;
	}
	public void setEmployeeId(String employeeId) {
	this.employeeId = employeeId;
	}
	public String getEmployeeName() {
	return employeeName;
	}
	public void setEmployeeName(String employeeName) {
	this.employeeName = employeeName;
	}
	public String getDepartment() {
	return department;
	}
	public void setDepartment(String department) {
	this.department = department;
	}
	public int getSalary() {
	return salary;
	}
	public void setSalary(int salary) {
	this.salary = salary;
	}
	}

view raw Employee.java hosted with ❤ by GitHub

ExcelHelper.java

	package com.javacodenet;
	import org.apache.poi.hssf.usermodel.HSSFWorkbook;
	import org.apache.poi.ss.usermodel.Cell;
	import org.apache.poi.ss.usermodel.CellStyle;
	import org.apache.poi.ss.usermodel.DateUtil;
	import org.apache.poi.ss.usermodel.Font;
	import org.apache.poi.ss.usermodel.IndexedColors;
	import org.apache.poi.ss.usermodel.Row;
	import org.apache.poi.ss.usermodel.Sheet;
	import org.apache.poi.ss.usermodel.Workbook;
	import org.apache.poi.xssf.usermodel.XSSFWorkbook;
	import java.io.File;
	import java.io.FileInputStream;
	import java.io.FileNotFoundException;
	import java.io.FileOutputStream;
	import java.io.IOException;
	import java.util.List;
	public class ExcelHelper {
	private static final String XLSX_FILE_NAME = "tmp\\employees.xlsx";
	private static final String XLS_FILE_NAME = "tmp\\employees.xls";
	public static void createExcelSheet(List<Employee> employees, boolean isXlsx) {
	System.out.println(isXlsx ? "Creating .xlsx file" : "Creating .xls file");
	//Create workbook, XSSFWorkbook for xlsx files and HSSFWorkbook for xls files
	Workbook workbook = isXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
	//Create Worksheet
	Sheet employeeDetailsSheet = workbook.createSheet("Employee details");
	//Create Header ROw
	Row rowHeader = employeeDetailsSheet.createRow(0);
	CellStyle boldCellStyle = getBoldCellStyle(workbook);
	createHeaderCell(rowHeader, 0, boldCellStyle, "EmployeeId");
	createHeaderCell(rowHeader, 1, boldCellStyle, "EmployeeName");
	createHeaderCell(rowHeader, 2, boldCellStyle, "Department");
	createHeaderCell(rowHeader, 3, boldCellStyle, "Salary");
	//Create data cells
	int rowNum = 1;
	for (Employee employee : employees) {
	Row row = employeeDetailsSheet.createRow(rowNum++);
	Cell empId = row.createCell(0);
	empId.setCellValue(employee.getEmployeeId());
	Cell empName = row.createCell(1);
	empName.setCellValue(employee.getEmployeeName());
	Cell dept = row.createCell(2);
	dept.setCellValue(employee.getDepartment());
	Cell salary = row.createCell(3);
	salary.setCellValue(employee.getSalary());
	}
	//create file and directory if not present
	File f = new File(isXlsx ? XLSX_FILE_NAME : XLS_FILE_NAME);
	if (!f.exists()) {
	//If directories are not available then create it
	File parent_directory = f.getParentFile();
	if (parent_directory != null) {
	parent_directory.mkdirs();
	}
	try {
	f.createNewFile();
	} catch (IOException e) {
	e.printStackTrace();
	}
	}
	//write to the created file
	try {
	FileOutputStream out = new FileOutputStream(f, false);
	workbook.write(out);
	out.close();
	} catch (FileNotFoundException e) {
	e.printStackTrace();
	} catch (IOException e) {
	e.printStackTrace();
	}
	System.out.println("Done");
	}
	private static void createHeaderCell(Row rowHeader, int i, CellStyle boldCellStyle, String employeeId) {
	Cell empIdHeader = rowHeader.createCell(i);
	empIdHeader.setCellStyle(boldCellStyle);
	empIdHeader.setCellValue(employeeId);
	}
	private static CellStyle getBoldCellStyle(Workbook workbook) {
	CellStyle cellStyle = workbook.createCellStyle();
	Font font = getBoldFont(workbook);
	cellStyle.setFont(font);
	return cellStyle;
	}
	private static Font getBoldFont(Workbook workbook) {
	Font font = workbook.createFont();
	font.setFontHeightInPoints((short) 11);
	font.setFontName("Arial");
	font.setColor(IndexedColors.GREEN.getIndex());
	font.setBold(true);
	font.setItalic(true);
	return font;
	}
	public static void readExcelSheet(boolean isXlsx) {
	try {
	FileInputStream excelFile = new FileInputStream(new File(isXlsx ? XLSX_FILE_NAME : XLS_FILE_NAME));
	Workbook workbook = isXlsx ? new XSSFWorkbook(excelFile) : new HSSFWorkbook(excelFile);
	Sheet workSheet = workbook.getSheetAt(0);
	for (Row currentRow : workSheet) {
	for (Cell currentCell : currentRow) {
	printCellContents(currentCell);
	}
	System.out.println();
	}
	} catch (FileNotFoundException e) {
	e.printStackTrace();
	} catch (IOException e) {
	e.printStackTrace();
	}
	}
	private static void printCellContents(Cell currentCell) {
	switch (currentCell.getCellTypeEnum()) {
	case STRING:
	System.out.print(currentCell.getRichStringCellValue().getString() + "|");
	break;
	case NUMERIC:
	if (DateUtil.isCellDateFormatted(currentCell)) {
	System.out.print(currentCell.getDateCellValue() + "|");
	} else {
	System.out.print(currentCell.getNumericCellValue() + "|");
	}
	break;
	case BOOLEAN:
	System.out.print(currentCell.getBooleanCellValue() + "|");
	break;
	case FORMULA:
	System.out.print(currentCell.getCellFormula() + "|");
	break;
	case BLANK:
	System.out.print("");
	break;
	default:
	System.out.println();
	}
	}
	}

view raw ExcelHelper.java hosted with ❤ by GitHub

Output: Generated Excel file

Output: Data read from an Excel file