What is Apache POI API?

Apache POI is an open-source Java project that provides APIs for working with Microsoft
Office file formats. The project supports Microsoft Office formats based on both
OLE2 Compound Document Format and Office Open XML (OOXML).

Using Apache POI, a Java application can programmatically work with files such as
.xls, .xlsx, .doc, .docx, .ppt,
and .pptx.

For software testers, Apache POI is particularly useful for reading test data from Excel,
creating test reports, validating exported spreadsheet data, and parameterizing automated tests.

Why is Apache POI Used?

Many enterprise applications exchange information using Microsoft Office documents. Instead of
manually opening and editing these files, Java programs can automate those operations using Apache POI.

Common reasons to use Apache POI include:

• Read data from Excel files.
• Create new Excel workbooks.
• Update existing spreadsheets.
• Apply formatting to Excel cells.
• Create formulas and evaluate spreadsheet values.
• Generate automated test reports.
• Read and create Word documents.
• Work with PowerPoint presentations.
• Automate large-scale document-based business processes.

Apache POI Components

Apache POI contains multiple components designed for different Microsoft Office file formats.
Some of the most important components are shown below.

HSSF vs XSSF in Apache POI

One of the most important concepts in Apache POI is the difference between HSSF and
XSSF.

Apache POI documentation identifies HSSF with the older Excel format and XSSF with the Excel 2007+
OOXML format. The shared spreadsheet user model provides common APIs across these formats.

Apache POI Maven Dependency

For modern Excel .xlsx files, the poi-ooxml dependency is commonly used.
The Apache POI download page currently lists version 5.5.1 in Maven Central. Always
verify the official Apache POI release page before starting a new project.

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

For older Excel .xls files, the core poi artifact is commonly required.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.5.1</version>
</dependency>

Apache POI Excel Object Model

When working with Excel through Apache POI, it is important to understand the relationship between
workbook, sheet, row, and cell.

The common spreadsheet user model includes interfaces such as Workbook,
Sheet, Row, and Cell, allowing applications to work with
spreadsheet data using a consistent programming model.

How to Create an Excel File Using Apache POI

The following example creates an Excel workbook, adds a worksheet, writes data to cells, and saves
the file as employee.xlsx.

import java.io.FileOutputStream;
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;

public class CreateExcel {

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

        Workbook workbook = new XSSFWorkbook();

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

        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("Employee ID");
        header.createCell(1).setCellValue("Name");
        header.createCell(2).setCellValue("Department");

        Row data = sheet.createRow(1);
        data.createCell(0).setCellValue(101);
        data.createCell(1).setCellValue("John");
        data.createCell(2).setCellValue("Testing");

        try (FileOutputStream output =
                     new FileOutputStream("employee.xlsx")) {

            workbook.write(output);
        }

        workbook.close();
    }
}

Apache POI’s official spreadsheet guide demonstrates the same basic workflow: create a workbook,
create a sheet, create rows and cells, and write the workbook to an output stream.

How to Read an Excel File Using Apache POI

Reading Excel data is one of the most common Apache POI use cases, especially in
test automation frameworks.

import java.io.File;
import org.apache.poi.ss.usermodel.*;

public class ReadExcel {

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

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

        Sheet sheet = workbook.getSheetAt(0);

        for (Row row : sheet) {
            for (Cell cell : row) {
                System.out.println(cell.toString());
            }
        }

        workbook.close();
    }
}

Apache POI provides DataFormatter when you need the text representation that a user
would typically see in Excel, including formatting for dates and numeric values.

Reading Different Types of Excel Cells

Excel cells can contain strings, numbers, dates, Boolean values, formulas, and other types.
Apache POI provides APIs to determine and retrieve the appropriate value.

switch (cell.getCellType()) {

    case STRING:
        System.out.println(cell.getStringCellValue());
        break;

    case NUMERIC:
        System.out.println(cell.getNumericCellValue());
        break;

    case BOOLEAN:
        System.out.println(cell.getBooleanCellValue());
        break;

    case FORMULA:
        System.out.println(cell.getCellFormula());
        break;

    default:
        System.out.println("Other cell type");
}

How to Write Numbers, Strings and Dates

Apache POI provides overloaded setCellValue() operations for commonly used Java
data types.

Row row = sheet.createRow(0);

row.createCell(0).setCellValue("Testing");
row.createCell(1).setCellValue(100);
row.createCell(2).setCellValue(99.50);

Dates can also be written to cells, typically together with a cell style that specifies the
desired date format.

import java.util.Date;

Cell dateCell = row.createCell(3);
dateCell.setCellValue(new Date());

How to Apply Formatting in Apache POI

Apache POI supports Excel formatting such as fonts, borders, fills, alignment, and number formats.

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Report");

Row row = sheet.createRow(0);
Cell cell = row.createCell(0);

cell.setCellValue("Test Report");

CellStyle style = workbook.createCellStyle();

Font font = workbook.createFont();
font.setBold(true);

style.setFont(font);

cell.setCellStyle(style);

Auto-Size Excel Columns

Apache POI provides autoSizeColumn() for automatically adjusting a column width
based on its contents.

sheet.autoSizeColumn(0);
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(2);

The official Apache POI guide notes that column auto-sizing relies on Java2D and may require
headless Java configuration when running in an environment without a graphical display.

Apache POI Formula Example

Apache POI allows Java applications to place formulas into Excel cells.

Row row = sheet.createRow(0);

row.createCell(0).setCellValue(100);
row.createCell(1).setCellValue(200);

Cell total = row.createCell(2);
total.setCellFormula("A1+B1");

The formula string should use Excel formula syntax and is supplied to
setCellFormula() without placing the leading = character in the
formula string.

Apache POI Formula Evaluation

A formula can be evaluated using FormulaEvaluator.

FormulaEvaluator evaluator =
        workbook.getCreationHelper().createFormulaEvaluator();

evaluator.evaluateFormulaCell(total);

Formula evaluation is useful when a Java program needs to calculate or verify formula results
before saving or consuming spreadsheet data.

Apache POI SXSSF for Large Excel Files

Normal XSSF processing can require significant memory for large workbooks because the workbook
model is maintained in memory. Apache POI provides SXSSF, a streaming extension
of XSSF intended for writing very large spreadsheets with a lower memory footprint.

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

SXSSFWorkbook workbook = new SXSSFWorkbook();

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

for (int i = 0; i < 100000; i++) {
    Row row = sheet.createRow(i);
    row.createCell(0).setCellValue("Record " + i);
}

workbook.close();

SXSSF uses a sliding window for rows, which helps reduce memory pressure, but it also introduces
limitations compared with the full XSSF model.

Apache POI for Selenium Test Automation

One of the most popular applications of Apache POI in software testing is
Excel-based data-driven testing.

For example, a Selenium test can read usernames, passwords, product names, search terms, or
expected results from an Excel spreadsheet.

String username = sheet.getRow(1)
        .getCell(0)
        .getStringCellValue();

String password = sheet.getRow(1)
        .getCell(1)
        .getStringCellValue();

driver.findElement(By.id("username"))
      .sendKeys(username);

driver.findElement(By.id("password"))
      .sendKeys(password);

This approach separates test data from test code and makes it easier to run the same test
scenario with multiple sets of input data.

Apache POI in API Testing

Apache POI is also useful in API testing. For example, an API response can be compared against
expected values stored in an Excel workbook.

Typical workflow:

1. Read expected test data from Excel.
2. Send an API request.
3. Capture the response.
4. Compare actual and expected values.
5. Write the test result back to Excel.

Apache POI for Test Reports

QA teams can use Apache POI to generate Excel-based execution reports containing information
such as test case ID, test description, expected result, actual result, status, execution time,
and defect ID.

Test Case ID | Test Name       | Expected | Actual | Status
------------------------------------------------------------
TC001        | Login Test      | Success  | Success| PASS
TC002        | Search Test     | Results  | Results| PASS
TC003        | Checkout Test   | Success  | Error  | FAIL

The same approach can be integrated into CI/CD pipelines to generate automated test execution
reports after every build.

Apache POI for Word Documents

Apache POI also includes APIs for Microsoft Word documents. HWPF is associated
with older binary Word formats, while XWPF works with newer WordprocessingML
documents such as .docx.

A simple XWPF example is shown below:

import java.io.FileOutputStream;
import org.apache.poi.xwpf.usermodel.XWPFDocument;

public class CreateWord {

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

        XWPFDocument document = new XWPFDocument();

        document.createParagraph()
                .createRun()
                .setText("Hello from Apache POI");

        try (FileOutputStream output =
                     new FileOutputStream("document.docx")) {

            document.write(output);
        }

        document.close();
    }
}

Apache POI’s documentation identifies XWPFDocument as the main entry point for
working with WordprocessingML documents and provides access to paragraphs, pictures, tables,
sections, and related content.

Apache POI for PowerPoint

Apache POI also provides APIs for Microsoft PowerPoint presentations. Modern PowerPoint
presentations can be handled using the XSLF API, while HSLF is associated with older
PowerPoint formats.

This makes Apache POI useful for automated generation of presentations, reports, charts,
slides, and other Office-based deliverables.

Important Apache POI Classes

Advantages of Apache POI API

• Pure Java: Office file processing can be integrated directly into Java applications.
• Open source: Apache POI is an Apache Software Foundation project.
• Excel support: Supports both traditional and OOXML Excel formats.
• Document automation: Can automate Word and PowerPoint processing as well.
• Testing integration: Works well with Selenium, TestNG, JUnit, and other Java-based automation frameworks.
• Formatting: Provides APIs for fonts, styles, borders, formulas, hyperlinks, validation, and more.
• Streaming support: SXSSF can help when generating very large spreadsheets.

Limitations of Apache POI

Apache POI is powerful, but it is important to understand its limitations before using it for
very large or highly complex Office documents.

• Large workbooks can consume significant memory when using the standard user model.
• Streaming APIs have limitations compared with the full in-memory model.
• Not every advanced Microsoft Office feature has identical support across all formats.
• Complex Office documents may require deeper knowledge of the underlying file format.
• Careful resource management is required when processing many files.

Apache POI’s documentation specifically discusses memory considerations for large spreadsheets
and recommends streaming approaches such as SXSSF where appropriate.

Apache POI Best Practices

1. Use poi-ooxml for modern .xlsx and other OOXML-based Office work.
2. Close workbooks and streams using try-with-resources whenever practical.
3. Use SXSSF when generating very large Excel files.
4. Avoid creating unnecessary cell styles repeatedly in large workbooks.
5. Validate Excel input before using it in automated tests.
6. Separate Excel utility code from business and test logic.
7. Keep test data files organized and version controlled appropriately.
8. Handle formulas, dates, blank cells, and unexpected cell types explicitly.

Apache POI Utility Class Example

In an automation framework, it is a good practice to create a reusable Excel utility rather
than repeating workbook and cell-handling code in every test.

public class ExcelUtil {

    private Workbook workbook;
    private Sheet sheet;

    public ExcelUtil(String filePath, String sheetName)
            throws Exception {

        workbook = WorkbookFactory.create(
                new java.io.File(filePath));

        sheet = workbook.getSheet(sheetName);
    }

    public String getCellValue(int row, int column) {

        return sheet.getRow(row)
                    .getCell(column)
                    .toString();
    }

    public int getRowCount() {
        return sheet.getLastRowNum() + 1;
    }

    public void close() throws Exception {
        workbook.close();
    }
}

A reusable utility makes Excel handling easier to maintain and allows the same functionality to
be shared across Selenium, API, database, and other automation tests.

Apache POI vs Manual Excel Processing

Apache POI Use Cases

Apache POI can be used in many real-world applications, including:

• Data-driven Selenium automation.
• Excel-based test case execution.
• Automated regression test reports.
• API response validation.
• Export and import validation.
• Excel report generation.
• Employee and financial report processing.
• Document generation systems.
• Business data migration tools.
• Office document automation.

Frequently Asked Questions About Apache POI

What is Apache POI in Java?

Apache POI is an open-source Java API for reading, creating, modifying, and writing Microsoft
Office documents, including Excel, Word, and PowerPoint files.

Is Apache POI free?

Yes. Apache POI is an open-source project under the Apache Software Foundation.

Can Apache POI read XLSX files?

Yes. Apache POI provides XSSF APIs for working with Excel OOXML .xlsx files.

Can Apache POI read XLS files?

Yes. Apache POI provides HSSF APIs for older Excel .xls files.

Can Apache POI be used with Selenium?

Yes. Apache POI is commonly integrated into Java-based Selenium automation frameworks for
reading test data and generating Excel reports.

Which is better, HSSF or XSSF?

The choice depends on the file format. Use HSSF for older .xls files and XSSF for
modern .xlsx files.

What is SXSSF?

SXSSF is Apache POI’s streaming extension of XSSF designed to help generate very large
spreadsheets with reduced memory usage, subject to its streaming limitations.

Conclusion

Apache POI API is an important Java technology for automating Microsoft Office
documents. For software engineers and QA professionals, it provides a practical way to work with
Excel files, test data, reports, Word documents, and PowerPoint presentations directly from Java.

For software testing and Selenium automation, Apache POI is especially valuable
for implementing data-driven testing, managing test data, validating exported Excel reports,
and creating automated execution results.

Once you understand the core Apache POI objects such as Workbook, Sheet,
Row, and Cell, you can build reusable Excel utilities and integrate
Office-document automation into larger Java applications and test frameworks.