Apache POI for Excel File Processing

Apache POI is a powerful Java library for working with Microsoft Office documents, including Excel files. It allows you to read, write, and manipulate Excel files in both XLS (Excel 2003 and earlier) and XLSX (Excel 2007 and later) formats. Below is a comprehensive guide to using Apache POI for Excel file processing.

---

Key Features of Apache POI

1. Read and Write Excel Files: Supports both .xls and .xlsx formats.
2. Workbook, Sheet, and Cell Manipulation: Create, modify, and delete workbooks, sheets, and cells.
3. Formatting: Apply styles, fonts, colors, and borders to cells.
4. Formulas: Evaluate and set formulas in cells.
5. Charts: Create and manipulate charts in Excel files.
6. Compatibility: Works with older and newer Excel formats.

---

Setting Up Apache POI

1. Add Dependencies

To use Apache POI in a Maven project, add the following dependencies to your pom.xml:

<dependencies>
    <!-- Core POI library -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.3</version>
    </dependency>

    <!-- POI for OOXML (XLSX) files -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>

    <!-- Optional: For handling OOXML schemas -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>4.1.2</version>
    </dependency>

    <!-- Optional: For handling date and time formatting -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
</dependencies>

2. Import Required Classes

Import the necessary classes in your Java code:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; // For XLSX files
import org.apache.poi.hssf.usermodel.HSSFWorkbook; // For XLS files
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

---

Reading Excel Files

Example: Read an XLSX File

public class ReadExcel {
    public static void main(String[] args) {
        try (FileInputStream file = new FileInputStream("example.xlsx")) {
            Workbook workbook = new XSSFWorkbook(file); // Use HSSFWorkbook for XLS files
            Sheet sheet = workbook.getSheetAt(0); // Get the first sheet

            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;
                        default:
                            System.out.print("UNKNOWN\t");
                    }
                }
                System.out.println();
            }
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

---

Writing Excel Files

Example: Write to an XLSX File

public class WriteExcel {
    public static void main(String[] args) {
        try (Workbook workbook = new XSSFWorkbook()) { // Use HSSFWorkbook for XLS files
            Sheet sheet = workbook.createSheet("Sheet1");

            // Create a row and add cells
            Row row = sheet.createRow(0);
            row.createCell(0).setCellValue("Name");
            row.createCell(1).setCellValue("Age");

            row = sheet.createRow(1);
            row.createCell(0).setCellValue("John Doe");
            row.createCell(1).setCellValue(30);

            // Write the output to a file
            try (FileOutputStream fileOut = new FileOutputStream("output.xlsx")) {
                workbook.write(fileOut);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

---

Formatting Cells

Example: Apply Cell Styles

public class FormatExcel {
    public static void main(String[] args) {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Formatted Sheet");

            // Create a font and a style
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setFontHeightInPoints((short) 14);
            headerFont.setColor(IndexedColors.RED.getIndex());

            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFont(headerFont);

            // Create a header row
            Row headerRow = sheet.createRow(0);
            Cell headerCell = headerRow.createCell(0);
            headerCell.setCellValue("Formatted Header");
            headerCell.setCellStyle(headerStyle);

            // Write the output to a file
            try (FileOutputStream fileOut = new FileOutputStream("formatted.xlsx")) {
                workbook.write(fileOut);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

---

Handling Formulas

Example: Set and Evaluate Formulas

public class FormulaExcel {
    public static void main(String[] args) {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Formula Sheet");

            // Add data
            Row row = sheet.createRow(0);
            row.createCell(0).setCellValue(10);
            row.createCell(1).setCellValue(20);

            // Set a formula
            row.createCell(2).setCellFormula("A1+B1");

            // Evaluate the formula
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            evaluator.evaluateAll();

            // Write the output to a file
            try (FileOutputStream fileOut = new FileOutputStream("formula.xlsx")) {
                workbook.write(fileOut);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

---

Best Practices

1. Use Try-With-Resources: Ensure resources like Workbook and FileInputStream are properly closed.
2. Handle Large Files: Use SXSSFWorkbook for streaming large Excel files to avoid memory issues.
3. Validate Inputs: Check for null values and invalid data when reading Excel files.
4. Use Styles Sparingly: Excessive formatting can increase file size and processing time.

---

Resources

• Official Documentation: Apache POI
• GitHub Repository: Apache POI GitHub
• Tutorials and Examples: Apache POI Tutorial

---

Apache POI is a versatile library for working with Excel files in Java. By mastering its features, you can efficiently read, write, and manipulate Excel files for various use cases.