Published on Sat Mar 08 2025 09:15:00 GMT+0000 (Coordinated Universal Time) by Purusothaman Ramanujam

Working with Excel Files Using Apache POI

Introduction

Excel files are ubiquitous in business applications. Whether you’re generating reports, importing data, or creating spreadsheets programmatically, you’ll often need to work with Excel files in your Java applications. Apache POI (Poor Obfuscation Implementation) is the most popular and comprehensive library for handling Microsoft Office documents, including Excel files.

In this blog post, we’ll explore how to read from and write to Excel files using Apache POI, covering both the older .xls format and the modern .xlsx format.

What is Apache POI?

Apache POI is an open-source Java library that provides APIs for reading and writing Microsoft Office file formats, including:

Excel files (.xls and .xlsx)
Word documents (.doc and .docx)
PowerPoint presentations (.ppt and .pptx)
Visio diagrams
Outlook messages

For Excel files, POI provides two main APIs:

HSSF (Horrible Spreadsheet Format) - for .xls files
XSSF (XML Spreadsheet Format) - for .xlsx files

Adding Apache POI to Your Project

Maven Dependencies

<!-- For .xlsx files (recommended) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>

<!-- For .xls files (legacy) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>

Gradle Dependencies

// For .xlsx files (recommended)
implementation 'org.apache.poi:poi-ooxml:5.2.3'

// For .xls files (legacy)
implementation 'org.apache.poi:poi:5.2.3'

Reading Excel Files

Reading .xlsx Files

Let’s start with reading a modern Excel file:

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

public class ExcelReader {
    public static void readExcelFile(String filePath) {
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            // Get the first sheet
            Sheet sheet = workbook.getSheetAt(0);

            // Iterate through rows
            for (Row row : sheet) {
                // Iterate through cells in the row
                for (Cell cell : row) {
                    switch (cell.getCellType()) {
                        case STRING:
                            System.out.print(cell.getStringCellValue() + "\t");
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                System.out.print(cell.getDateCellValue() + "\t");
                            } else {
                                System.out.print(cell.getNumericCellValue() + "\t");
                            }
                            break;
                        case BOOLEAN:
                            System.out.print(cell.getBooleanCellValue() + "\t");
                            break;
                        case FORMULA:
                            System.out.print(cell.getCellFormula() + "\t");
                            break;
                        default:
                            System.out.print("" + "\t");
                    }
                }
                System.out.println(); // New line after each row
            }

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

Reading Specific Cells

Sometimes you need to read specific cells rather than the entire sheet:

public class SpecificCellReader {
    public static void readSpecificCells(String filePath) {
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0);

            // Read cell A1
            Row row = sheet.getRow(0);
            Cell cell = row.getCell(0);
            String value = cell.getStringCellValue();
            System.out.println("A1: " + value);

            // Read cell B2
            row = sheet.getRow(1);
            cell = row.getCell(1);
            double numericValue = cell.getNumericCellValue();
            System.out.println("B2: " + numericValue);

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

Reading with Header Row

Here’s a more practical example that reads data with a header row:

import java.util.ArrayList;
import java.util.List;

public class DataReader {
    public static List<Employee> readEmployeeData(String filePath) {
        List<Employee> employees = new ArrayList<>();

        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0);

            // Skip header row (row 0)
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    Employee employee = new Employee();

                    employee.setId((int) row.getCell(0).getNumericCellValue());
                    employee.setName(row.getCell(1).getStringCellValue());
                    employee.setEmail(row.getCell(2).getStringCellValue());
                    employee.setSalary(row.getCell(3).getNumericCellValue());

                    employees.add(employee);
                }
            }

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

        return employees;
    }
}

class Employee {
    private int id;
    private String name;
    private String email;
    private double salary;

    // Getters and setters
    public int getId() { return id; }
    public void setId(int id) { this.id = id; }

    public String getName() { return name; }
    public void setName(String name) { this.name = name; }

    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }

    public double getSalary() { return salary; }
    public void setSalary(double salary) { this.salary = salary; }
}

Writing Excel Files

Creating a New Excel File

Let’s create a new Excel file with some data:

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

public class ExcelWriter {
    public static void createExcelFile(String filePath) {
        try (Workbook workbook = new XSSFWorkbook();
             FileOutputStream fos = new FileOutputStream(filePath)) {

            // Create a new sheet
            Sheet sheet = workbook.createSheet("Employees");

            // Create header row
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("ID");
            headerRow.createCell(1).setCellValue("Name");
            headerRow.createCell(2).setCellValue("Email");
            headerRow.createCell(3).setCellValue("Salary");

            // Create data rows
            Object[][] data = {
                {1, "John Doe", "john@example.com", 50000.0},
                {2, "Jane Smith", "jane@example.com", 60000.0},
                {3, "Bob Johnson", "bob@example.com", 55000.0}
            };

            int rowNum = 1;
            for (Object[] rowData : data) {
                Row row = sheet.createRow(rowNum++);
                int colNum = 0;
                for (Object field : rowData) {
                    Cell cell = row.createCell(colNum++);
                    if (field instanceof String) {
                        cell.setCellValue((String) field);
                    } else if (field instanceof Integer) {
                        cell.setCellValue((Integer) field);
                    } else if (field instanceof Double) {
                        cell.setCellValue((Double) field);
                    }
                }
            }

            // Auto-size columns
            for (int i = 0; i < 4; i++) {
                sheet.autoSizeColumn(i);
            }

            // Write to file
            workbook.write(fos);
            System.out.println("Excel file created successfully!");

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

Adding Formatting

Let’s enhance our Excel file with some formatting:

public class FormattedExcelWriter {
    public static void createFormattedExcel(String filePath) {
        try (Workbook workbook = new XSSFWorkbook();
             FileOutputStream fos = new FileOutputStream(filePath)) {

            Sheet sheet = workbook.createSheet("Formatted Data");

            // Create styles
            CellStyle headerStyle = workbook.createCellStyle();
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setColor(IndexedColors.WHITE.getIndex());
            headerStyle.setFont(headerFont);
            headerStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            CellStyle currencyStyle = workbook.createCellStyle();
            DataFormat format = workbook.createDataFormat();
            currencyStyle.setDataFormat(format.getFormat("$#,##0.00"));

            // Create header row
            Row headerRow = sheet.createRow(0);
            String[] headers = {"ID", "Name", "Email", "Salary"};
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
            }

            // Create data rows
            Object[][] data = {
                {1, "John Doe", "john@example.com", 50000.0},
                {2, "Jane Smith", "jane@example.com", 60000.0},
                {3, "Bob Johnson", "bob@example.com", 55000.0}
            };

            int rowNum = 1;
            for (Object[] rowData : data) {
                Row row = sheet.createRow(rowNum++);
                for (int i = 0; i < rowData.length; i++) {
                    Cell cell = row.createCell(i);
                    if (i == 3) { // Salary column
                        cell.setCellValue((Double) rowData[i]);
                        cell.setCellStyle(currencyStyle);
                    } else if (rowData[i] instanceof String) {
                        cell.setCellValue((String) rowData[i]);
                    } else if (rowData[i] instanceof Integer) {
                        cell.setCellValue((Integer) rowData[i]);
                    }
                }
            }

            // Auto-size columns
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }

            workbook.write(fos);
            System.out.println("Formatted Excel file created successfully!");

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

Working with Multiple Sheets

Excel files can contain multiple sheets. Here’s how to work with them:

public class MultiSheetExample {
    public static void createMultiSheetWorkbook(String filePath) {
        try (Workbook workbook = new XSSFWorkbook();
             FileOutputStream fos = new FileOutputStream(filePath)) {

            // Create first sheet
            Sheet employeesSheet = workbook.createSheet("Employees");
            createEmployeeData(employeesSheet);

            // Create second sheet
            Sheet departmentsSheet = workbook.createSheet("Departments");
            createDepartmentData(departmentsSheet);

            // Create third sheet with summary
            Sheet summarySheet = workbook.createSheet("Summary");
            createSummaryData(summarySheet);

            workbook.write(fos);
            System.out.println("Multi-sheet workbook created successfully!");

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

    private static void createEmployeeData(Sheet sheet) {
        // Implementation for employee data
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("ID");
        headerRow.createCell(1).setCellValue("Name");
        headerRow.createCell(2).setCellValue("Department");

        // Add some sample data
        Row dataRow = sheet.createRow(1);
        dataRow.createCell(0).setCellValue(1);
        dataRow.createCell(1).setCellValue("John Doe");
        dataRow.createCell(2).setCellValue("Engineering");
    }

    private static void createDepartmentData(Sheet sheet) {
        // Implementation for department data
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("Department ID");
        headerRow.createCell(1).setCellValue("Department Name");
        headerRow.createCell(2).setCellValue("Manager");

        // Add some sample data
        Row dataRow = sheet.createRow(1);
        dataRow.createCell(0).setCellValue(1);
        dataRow.createCell(1).setCellValue("Engineering");
        dataRow.createCell(2).setCellValue("Jane Smith");
    }

    private static void createSummaryData(Sheet sheet) {
        // Implementation for summary data
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("Total Employees");
        headerRow.createCell(1).setCellValue("Total Departments");

        Row dataRow = sheet.createRow(1);
        dataRow.createCell(0).setCellValue(150);
        dataRow.createCell(1).setCellValue(8);
    }
}

Handling Different Data Types

Excel supports various data types. Here’s how to handle them:

public class DataTypeExample {
    public static void createDataTypesExample(String filePath) {
        try (Workbook workbook = new XSSFWorkbook();
             FileOutputStream fos = new FileOutputStream(filePath)) {

            Sheet sheet = workbook.createSheet("Data Types");

            // Create styles for different data types
            CellStyle dateStyle = workbook.createCellStyle();
            DataFormat dateFormat = workbook.createDataFormat();
            dateStyle.setDataFormat(dateFormat.getFormat("dd/mm/yyyy"));

            CellStyle percentageStyle = workbook.createCellStyle();
            DataFormat percentageFormat = workbook.createDataFormat();
            percentageStyle.setDataFormat(percentageFormat.getFormat("0.00%"));

            // Header row
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("String");
            headerRow.createCell(1).setCellValue("Number");
            headerRow.createCell(2).setCellValue("Date");
            headerRow.createCell(3).setCellValue("Boolean");
            headerRow.createCell(4).setCellValue("Percentage");
            headerRow.createCell(5).setCellValue("Formula");

            // Data row
            Row dataRow = sheet.createRow(1);

            // String
            dataRow.createCell(0).setCellValue("Sample Text");

            // Number
            dataRow.createCell(1).setCellValue(1234.56);

            // Date
            Cell dateCell = dataRow.createCell(2);
            dateCell.setCellValue(new Date());
            dateCell.setCellStyle(dateStyle);

            // Boolean
            dataRow.createCell(3).setCellValue(true);

            // Percentage
            Cell percentageCell = dataRow.createCell(4);
            percentageCell.setCellValue(0.75);
            percentageCell.setCellStyle(percentageStyle);

            // Formula
            Cell formulaCell = dataRow.createCell(5);
            formulaCell.setCellFormula("B2*D2");

            // Auto-size columns
            for (int i = 0; i < 6; i++) {
                sheet.autoSizeColumn(i);
            }

            workbook.write(fos);
            System.out.println("Data types example created successfully!");

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

Best Practices

Always Close Resources: Use try-with-resources to ensure workbooks and streams are properly closed.
Choose the Right Format: Use .xlsx for new files (better performance, smaller file size) and .xls only for legacy compatibility.
Handle Large Files: For large Excel files, consider using the streaming API (SXSSF) to reduce memory usage.
Error Handling: Always wrap POI operations in try-catch blocks and handle exceptions appropriately.
Cell Types: Be careful when reading cells - always check the cell type before accessing its value.
Memory Management: For large files, read/write in chunks rather than loading everything into memory.

Common Pitfalls

Memory Issues: Large Excel files can consume significant memory. Use streaming APIs for files with thousands of rows.
Cell Type Mismatch: Trying to read a numeric cell as a string or vice versa can cause exceptions.
File Format Confusion: Using the wrong API (HSSF vs XSSF) for the file format can cause issues.
Resource Leaks: Not closing workbooks and streams can lead to memory leaks.

Conclusion

Apache POI is a powerful library for working with Excel files in Java. Whether you’re reading existing spreadsheets, creating new ones, or manipulating data, POI provides all the tools you need.

In this post, we covered the basics of reading and writing Excel files, handling different data types, formatting, and working with multiple sheets. As you become more comfortable with these concepts, you can explore advanced features like charts, pivot tables, and the streaming API for large files.

Stay tuned for more advanced POI topics in future posts!