Apache POI Excel Workbook in Java: Create and Manage Excel Sheets

INTRODUCTION

Working with Excel files is a common requirement in Java applications. Whether you need to generate reports, export database records, create invoices, or process spreadsheet data, manually creating Excel files is not always practical.

Apache POI is a popular Java library that allows applications to work with Microsoft Office document formats, including Excel workbooks.

In this article, we'll focus on the Excel Workbook functionality of Apache POI and learn how to create a workbook, add worksheets, write data, and save the resulting Excel file.

What Is Apache POI?

Apache POI is a Java library used for reading, creating, and modifying Microsoft Office documents.

For Excel files, Apache POI provides APIs for working with workbooks, worksheets, rows, cells, formatting, formulas, and other spreadsheet features.

The main workbook implementations include:

HSSFWorkbook — used for the older .xls Excel format.
XSSFWorkbook — used for the modern .xlsx Excel format.

Apache POI's API describes XSSFWorkbook as a high-level representation of an Excel SpreadsheetML workbook and a top-level object for creating sheets and other workbook content.

What Is an Excel Workbook?

An Excel workbook is essentially an Excel file that can contain one or more worksheets.

For example:

Workbook
│
├── Sheet1
├── Sheet2
└── Sheet3

Each worksheet can contain rows and columns of data.

In Apache POI, the Workbook interface represents this overall Excel document.

For example:

Workbook workbook = new XSSFWorkbook();

This creates a new workbook suitable for the .xlsx format.

HSSFWorkbook vs XSSFWorkbook

Apache POI provides different classes for different Excel formats.

HSSFWorkbook

HSSFWorkbook works with the older Excel .xls format.

Workbook workbook = new HSSFWorkbook();
XSSFWorkbook

XSSFWorkbook works with the modern .xlsx format.

Workbook workbook = new XSSFWorkbook();

For new applications targeting modern Excel files, XSSFWorkbook is commonly the appropriate choice.

Apache POI's API identifies HSSFWorkbook as a high-level workbook representation for the older HSSF format, while XSSFWorkbook represents SpreadsheetML workbooks.

Creating an Excel Workbook

Let's start with a simple Java program that creates a new .xlsx workbook.

import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;

public class CreateWorkbook {

public static void main(String[] args) {

    try (Workbook workbook = new XSSFWorkbook();
         FileOutputStream output =
                 new FileOutputStream("example.xlsx")) {

        workbook.write(output);

        System.out.println("Workbook created successfully.");

    } catch (IOException e) {
        e.printStackTrace();
    }
}

}

The important line is:

Workbook workbook = new XSSFWorkbook();

It creates a new Excel workbook in memory.

The write() method then saves the workbook to an output stream.

Using try-with-resources is helpful because workbook implementations implement Closeable, allowing resources to be closed automatically.

Creating a Worksheet

Creating a workbook alone does not provide the data structure you normally see as an Excel sheet. You can create worksheets using createSheet().

Workbook workbook = new XSSFWorkbook();

Sheet sheet = workbook.createSheet("Employees");

Here, "Employees" becomes the name of the worksheet.

The TPointTech example similarly uses createSheet() to create independent sheets within a workbook.

Creating Multiple Sheets

A single workbook can contain multiple worksheets.

For example:

Workbook workbook = new XSSFWorkbook();

Sheet employeeSheet =
workbook.createSheet("Employees");

Sheet departmentSheet =
workbook.createSheet("Departments");

Sheet salarySheet =
workbook.createSheet("Salary");

The workbook now contains three separate sheets:

example.xlsx
│
├── Employees
├── Departments
└── Salary

This can be useful when generating reports that contain different categories of information.

Naming Rules for Excel Sheets

When creating a worksheet, its name must follow Excel's naming restrictions.

For example, sheet names cannot exceed 31 characters and cannot contain certain characters such as:

:
\
/
?
*
[
]

These restrictions are also noted in the TPointTech example.

Therefore, this is a valid name:

workbook.createSheet("Employee Data");

But a name containing prohibited characters should be avoided.

Creating Rows and Cells

After creating a worksheet, you can add rows and cells.

Sheet sheet = workbook.createSheet("Employees");

Row row = sheet.createRow(0);

Cell cell = row.createCell(0);

cell.setCellValue("Employee Name");

The indexes start from zero.

So:

0 → first row/column
1 → second row/column
2 → third row/column

For example:

Row row = sheet.createRow(1);

row.createCell(0).setCellValue("Ravi");
row.createCell(1).setCellValue("Java Developer");

This creates data in the second row.

Reading an Existing Workbook

Apache POI can also be used to open an existing workbook.

For example, the WorkbookFactory API can create an appropriate workbook implementation from a file. The POI API documents WorkbookFactory.create(File) for creating a workbook from an existing readable file.

A simple example is:

import java.io.File;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ReadWorkbook {

public static void main(String[] args)
        throws Exception {

    Workbook workbook =
            WorkbookFactory.create(
                    new File("employees.xlsx"));

    System.out.println(
            "Workbook opened successfully.");

    workbook.close();
}

}

Important Points to Remember

When working with Apache POI workbooks, keep these points in mind:

Use XSSFWorkbook for modern .xlsx workbooks.
Use HSSFWorkbook for the older .xls format.
Use createSheet() to add worksheets.
Sheet names must follow Excel's naming restrictions.
Use createRow() to create rows.
Use createCell() to create cells.
Call write() to save the workbook.
Close the workbook and related resources after use.

Conclusion

Apache POI Excel Workbook functionality makes it possible to create and manage Excel files directly from Java applications.

The Workbook interface provides the foundation for working with Excel documents, while classes such as XSSFWorkbook and HSSFWorkbook handle different Excel formats. You can create worksheets with createSheet(), add rows and cells, insert data, and finally write the workbook to an Excel file.

Once these basics are understood, you can move on to more advanced Apache POI features such as cell formatting, formulas, merged cells, borders, fonts, images, charts, and reading existing Excel files.