Working with Excel in Java: Apache POI and Alternatives

Working with Excel in Java: Library Overview and Code Examples

Despite PythonLib being dedicated to Python, Java remains one of the most in-demand languages in enterprise development, where working with Excel spreadsheets is a daily task. In this article, we will review the main libraries for working with Excel in Java, their capabilities, and provide practical code examples.

Why are Excel libraries needed in Java?

Java does not have built-in tools for reading and writing Excel files. Third-party libraries are used for this purpose, which allow you to:

• Create new Excel workbooks (XLS and XLSX formats).
• Read and edit existing files.
• Format cells, merge them, and set styles.
• Work with formulas, charts, and macros.
• Export data from databases or other sources.

Main Libraries

1. Apache POI

Apache POI is the most popular and powerful library for working with Microsoft Office files in Java. It supports both the old XLS format (HSSF) and the new XLSX format (XSSF).

Advantages:

• Full support for all Excel features: formulas, styles, conditional formatting, charts.
• Active community and regular updates.
• Support for both reading and writing.

Disadvantages:

• High memory consumption when working with large files.
• Complex API for beginners.

2. EasyExcel (Alibaba)

EasyExcel is a lightweight library from Alibaba, created for working with large XLSX files. It uses streaming processing, which significantly reduces memory consumption.

Advantages:

• Minimal memory consumption (up to 100 times less than POI).
• Simple and intuitive API.
• Support for annotations for data mapping.

Disadvantages:

• Does not support the old XLS format.
• Limited formatting support.

3. JExcelApi (jxl)

An old library that only supports the XLS format. Suitable for simple tasks, but has not been updated for a long time and does not work with XLSX.

Code Examples

Example 1: Reading an Excel file with Apache POI

import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.FileInputStream;import java.io.IOException;

public class ReadExcel {    public static void main(String[] args) {        try (FileInputStream fis = new FileInputStream("data.xlsx");             Workbook workbook = new XSSFWorkbook(fis)) {                        Sheet sheet = workbook.getSheetAt(0);            for (Row row : sheet) {                for (Cell cell : row) {                    switch (cell.getCellType()) {                        case STRING:                            System.out.print(cell.getStringCellValue() + "\\t");                            break;                        case NUMERIC:                            System.out.print(cell.getNumericCellValue() + "\\t");                            break;                        case BOOLEAN:                            System.out.print(cell.getBooleanCellValue() + "\\t");                            break;                    }                }                System.out.println();            }        } catch (IOException e) {            e.printStackTrace();        }    }}

Example 2: Writing data to Excel with Apache POI

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

public class WriteExcel {    public static void main(String[] args) {        try (Workbook workbook = new XSSFWor