Writing Excel File Using Apache POI Library in Java

Apache POI is and open source Java library allow you to read and write Microsoft Documents such as Excel workbook, Word document, Powerpoint presentation.

In this blog post I will show you how to use Apache POI library to writing an Excel file. Throughout this blog post we will implement an Java application to generate the Excel file as the figure below:

Sample Output Excel File

Overview Apache POI Library

At the time of writing this blog post, the latest stable release is Apache POI 4.0.1.

There are multipe components in Apache POI that support reading and writing different file formats. To write Excel file we will use POI-HSSF or POI-XSSF components:

POI-HSSF component support Excel ‘97 - 2007 (.xls) file format.
POI-XSSF component support Excel 2007 and later version (.xlsx) file format.

When choosing which component to use you should aware that the POI-XSSF will have more memory footprint than POI-HSSF because it process XML based documents (.xlsx) instead of binary file (.xls) in POI-HSSF.

Apache POI Definitions

There are some definitions you need to know when using the library:

Workbook represent an Excel workbook. In this blog post we will discuss about 2 implementations of Workbook are HSSFWorkbook to generate .xls file and XSSFWorkbook to generate .xlsx file.
Sheet represent an Excel worksheet. The object of Sheet will be created by Workbook.
Row represent a row in an Excel spreadsheet.
Cell represent a cell in a row of an Excel spreadsheet.

Adding Apache POI dependencies into your project

Using Gradle

compile group: 'org.apache.poi', name: 'poi', version: '4.0.1'
compile group: 'org.apache.poi', name: 'poi-ooxml', version: '4.0.1'

Using Maven

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>

Or you can download the release from Apache POI home page at: here

Steps to create an Excel file

In order to create Excel file you need these steps:

Create an instance of Workbook.
Create instances of Sheet via Workbook.createSheet(String sheetname) method.
Create instances of Row via Sheet.createRow(int rownum) method.
Create instances of Cell via Row.createCell(int column) mehod.
Write out the workbook to an OutputStream.

Below are sample code that generate a simple Excel file with one sheet and contain only one cell in that sheet.

Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("simplesolution.dev");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("Simple Solution");
try(OutputStream outputStream = new FileOutputStream("sample.xls")) {
    workbook.write(outputStream);
} catch (FileNotFoundException ex) {
    ex.printStackTrace();
} catch (IOException ex) {
    ex.printStackTrace();
}

The output file:

Sample Output Excel File

You can create workbook instance for diferent Excel format. Using HSSFWorkbook to create .xls file

Workbook hssfWorkbook = new HSSFWorkbook();

or using XSSFWorkbook to create .xls file

Workbook xssfWorkbook = new XSSFWorkbook();

To fill color for cell Apache POI support CellStyle interface that allow you setting color and background for cell, below are sample code to color the background of header row:

CellStyle headerCellStyle = workbook.createCellStyle();
headerCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Cell cell = row.createCell(0);
cell.setCellValue("Employee ID");
cell.setCellStyle(headerCellStyle);

Apache POI Cell support different data types, for example in this blog post we will implement an application to save objects of Employee class as below to Excel file:

public class Employee {
    private int employeeId;
    private String firstName;
    private String lastName;
    private Date birthDate;
    private String address;
    private String city;
    private BigDecimal salary;
    // constructors and setters
}

CellStyle also support to setting the format of you data, in order to generate birthDate field of Employee we also setting it data format to “dd/MM/yyyy” by using CellStyle as below:

Cell birthDateCell = row.createCell(3);
birthDateCell.setCellValue(employee.getBirthDate());
CellStyle cellStyle = workbook.createCellStyle();
CreationHelper creationHelper = workbook.getCreationHelper();
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("dd/MM/yyyy"));
birthDateCell.setCellStyle(cellStyle);

And decimal number format for salary

Cell salaryCell = row.createCell(6);
salaryCell.setCellValue(employee.getSalary().doubleValue());
CellStyle salaryCellStyle = workbook.createCellStyle();
salaryCellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0.00"));
salaryCell.setCellStyle(salaryCellStyle);

By default the out put file will be set column width as the same width for all columns. To make the output file more user friendly we need will adjusts the column width to fit the contents using code below:

sheet.autoSizeColumn(0);
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(2);
sheet.autoSizeColumn(3);
sheet.autoSizeColumn(4);
sheet.autoSizeColumn(5);
sheet.autoSizeColumn(6);

Below is the full source code of application:

package simplesolution.dev;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;

public class UsingApachePOIWithExcelSample {

    public static void main(String... args) {
        createSimpleFile();

        Workbook hssfWorkbook = new HSSFWorkbook();
        createXLSFile(hssfWorkbook, "employees.xls");

        Workbook xssfWorkbook = new XSSFWorkbook();
        createXLSFile(xssfWorkbook, "employees.xlsx");
    }

    private static void createSimpleFile() {
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("simplesolution.dev");
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("Simple Solution");
        try(OutputStream outputStream = new FileOutputStream("sample.xls")) {
            workbook.write(outputStream);
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

    private static void createXLSFile(Workbook workbook, String fileName) {
        Sheet sheet = workbook.createSheet("Employee List");
        createHeaderRow(workbook, sheet);

        Employee employee = new Employee(
                2303,
                "Buffet",
                "Jimmy",
                1975, 3, 4,
                "The Alamo",
                "San Antonio",
                new BigDecimal(75324.7634));
        createEmployeeRow(workbook, sheet, employee, 1);

        employee = new Employee(
                2304,
                "Cartman",
                "Eric",
                1973, 11, 8,
                "Empire State Building",
                "New York",
                new BigDecimal(59000.0256));
        createEmployeeRow(workbook, sheet, employee, 2);

        employee = new Employee(
                2307,
                "Jefferson",
                "George",
                1980, 12, 10,
                "Mockingbird Lane",
                "Fargo",
                new BigDecimal(159000.342));
        createEmployeeRow(workbook, sheet, employee, 3);
        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        sheet.autoSizeColumn(3);
        sheet.autoSizeColumn(4);
        sheet.autoSizeColumn(5);
        sheet.autoSizeColumn(6);
        try(OutputStream outputStream = new FileOutputStream(fileName)) {
            workbook.write(outputStream);
        } catch (FileNotFoundException ex) {
            ex.printStackTrace();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }

    private static void createHeaderRow(Workbook workbook, Sheet sheet) {
        Row row = sheet.createRow(0);
        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
        headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Cell cell = row.createCell(0);
        cell.setCellValue("Employee ID");
        cell.setCellStyle(headerCellStyle);

        cell = row.createCell(1);
        cell.setCellValue("First Name");
        cell.setCellStyle(headerCellStyle);

        cell = row.createCell(2);
        cell.setCellValue("Last Name");
        cell.setCellStyle(headerCellStyle);

        cell = row.createCell(3);
        cell.setCellValue("Birth Date");
        cell.setCellStyle(headerCellStyle);

        cell = row.createCell(4);
        cell.setCellValue("Address");
        cell.setCellStyle(headerCellStyle);

        cell = row.createCell(5);
        cell.setCellValue("City");
        cell.setCellStyle(headerCellStyle);

        cell = row.createCell(6);
        cell.setCellValue("Salary");
        cell.setCellStyle(headerCellStyle);
    }

    private static void createEmployeeRow(Workbook workbook, Sheet sheet, Employee employee, int rowIndex) {
        Row row = sheet.createRow(rowIndex);
        row.createCell(0).setCellValue(employee.getEmployeeId());
        row.createCell(1).setCellValue(employee.getFirstName());
        row.createCell(2).setCellValue(employee.getLastName());

        Cell birthDateCell = row.createCell(3);
        birthDateCell.setCellValue(employee.getBirthDate());
        CellStyle cellStyle = workbook.createCellStyle();
        CreationHelper creationHelper = workbook.getCreationHelper();
        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("dd/MM/yyyy"));
        birthDateCell.setCellStyle(cellStyle);

        row.createCell(4).setCellValue(employee.getAddress());
        row.createCell(5).setCellValue(employee.getCity());
        Cell salaryCell = row.createCell(6);
        salaryCell.setCellValue(employee.getSalary().doubleValue());
        CellStyle salaryCellStyle = workbook.createCellStyle();
        salaryCellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("#,##0.00"));
        salaryCell.setCellStyle(salaryCellStyle);
    }

}