Apache POI Tutorial: Working with Excel Files in Java

Apache POI is a popular Java library that allows developers to create, modify, and display Excel files (.xls and .xlsx formats). In this tutorial, we will explore how to use Apache POI to perform common tasks such as reading, writing, and formatting Excel files.

Table of Contents

1. Introduction to Apache POI
2. Setting up Apache POI
3. Reading Excel Files
4. Writing to Excel Files
5. Formatting Excel Files
6. Conclusion

1. Introduction to Apache POI

Apache POI stands for “Poor Obfuscation Implementation” and is a project by Apache Software Foundation. It provides libraries for working with Microsoft Office formats, including Excel. With Apache POI, you can create new Excel files, read existing ones, and modify them.

2. Setting up Apache POI

Before we start, make sure you have Apache POI added to your project’s dependencies. If you are using Maven, add the following to your pom.xml:

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

If you are using Gradle, add this to your build.gradle:

implementation group: 'org.apache.poi', name: 'poi', version: '5.2.7'

3. Reading Excel Files

To read an existing Excel file, you can use XSSFWorkbook for .xlsx files and HSSFWorkbook for .xls files. Here’s a simple example to read an Excel file:

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.InputStream;

public class ExcelReader {

    public static void main(String[] args) {
        try {
            InputStream inputStream = new FileInputStream("example.xlsx");
            Workbook workbook = WorkbookFactory.create(inputStream);
            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;
                        default:
                            System.out.print("NA\t");
                    }
                }
                System.out.println();
            }

            workbook.close();
            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

4. Writing to Excel Files

To create a new Excel file or modify an existing one, you can use XSSFWorkbook for .xlsx files and HSSFWorkbook for .xls files. Here’s an example to create a new Excel file and write data to it:

import org.apache.poi.ss.usermodel.*;

import java.io.FileOutputStream;

public class ExcelWriter {

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

            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("Name");
            headerRow.createCell(1).setCellValue("Age");
            headerRow.createCell(2).setCellValue("City");

            Row row1 = sheet.createRow(1);
            row1.createCell(0).setCellValue("John Doe");
            row1.createCell(1).setCellValue(30);
            row1.createCell(2).setCellValue("New York");

            Row row2 = sheet.createRow(2);
            row2.createCell(0).setCellValue("Jane Smith");
            row2.createCell(1).setCellValue(25);
            row2.createCell(2).setCellValue("Los Angeles");

            FileOutputStream outputStream = new FileOutputStream("output.xlsx");
            workbook.write(outputStream);

            workbook.close();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

5. Formatting Excel Files

You can format cells, rows, and columns in Excel using Apache POI. Here’s an example to format cells:

import org.apache.poi.ss.usermodel.*;

import java.io.FileOutputStream;

public class ExcelFormatter {

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

            CellStyle style = workbook.createCellStyle();
            style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            Row headerRow = sheet.createRow(0);
            Cell headerCell1 = headerRow.createCell(0);
            headerCell1.setCellValue("Name");
            headerCell1.setCellStyle(style);

            Cell headerCell2 = headerRow.createCell(1);
            headerCell2.setCellValue("Age");
            headerCell2.setCellStyle(style);

            Cell headerCell3 = headerRow.createCell(2);
            headerCell3.setCellValue("City");
            headerCell3.setCellStyle(style);

            // Create data rows...

            FileOutputStream outputStream = new FileOutputStream("formatted.xlsx");
            workbook.write(outputStream);

            workbook.close();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

6. Conclusion

Apache POI is a powerful library for working with Excel files in Java. In this tutorial, we covered basic operations such as reading, writing, and formatting Excel files. There is much more you can do with Apache POI, including handling formulas, charts, and more complex Excel features. Check out the Apache POI documentation for detailed information and examples.