Understanding Workbook Implementations

Apache POI provides distinct classes for handling different Excel versions. For the older binary format (Excel 2003, .xls), the library uses HSSFWorkbook. For the newer XML-based format (Excel 2007 and later, .xlsx), XSSFWorkbook is required. Both classes implement the common Workbook interface, allowing developers to switch between implementations based on the file extension.

// Handling different Excel versions
Workbook workbook;
if (fileName.endsWith(".xls")) {
    workbook = new HSSFWorkbook(new FileInputStream(new File(fileName)));
} else if (fileName.endsWith(".xlsx")) {
    workbook = new XSSFWorkbook(new FileInputStream(new File(fileName)));
}

Project Dependencies

To use these features, include the necessary Maven dependencies. It is crucial that the version numbers for poi and poi-ooxml match to avoid runtime conflicts.

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

Reading Data from Excel

The process of reading an Excel file involves opening a file stream, creating a workbook instance, and iterating through sheets, rows, and cells. The following example demonstrates how to extract data from the first sheet of an .xlsx file and store it in a list of maps, ensuring numeric values are read correctly as strings.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelReader {

    public List> parseExcel(String filePath) {
        List> dataList = new ArrayList<>();
        File excelFile = new File(filePath);

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

            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getPhysicalNumberOfRows();

            // Iterate starting from the second row (index 1) to skip headers
            for (int i = 1; i < rowCount; i++) {
                Row currentRow = sheet.getRow(i);
                if (currentRow == null) continue;

                Map rowMap = new HashMap<>();

                // Read ID (Column 0)
                Cell idCell = currentRow.getCell(0);
                if (idCell != null) {
                    idCell.setCellType(CellType.STRING);
                    rowMap.put("Id", idCell.getStringCellValue());
                }

                // Read Name (Column 1)
                Cell nameCell = currentRow.getCell(1);
                if (nameCell != null) {
                    nameCell.setCellType(CellType.STRING);
                    rowMap.put("Name", nameCell.getStringCellValue());
                }

                dataList.add(rowMap);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return dataList;
    }
}

Writing Data to Excel

Creating an Excel file requires instantiating a new workbook, creating sheets and rows, and defining cell values. Once the data structure is populated in memory, it is written to disk via a FileOutputStream.

import org.apache.poi.ss.usermodel.Cell;
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;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;

public class ExcelWriter {

    public void generateReport(String outputFilePath) {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Report Data");

            // Define Header Row
            Row headerRow = sheet.createRow(0);
            List<String> headers = Arrays.asList("ID", "Value", "Category");
            for (int i = 0; i < headers.size(); i++) {
                headerRow.createCell(i).setCellValue(headers.get(i));
            }

            // Populate Data Rows
            for (int i = 1; i <= 10; i++) {
                Row dataRow = sheet.createRow(i);
                dataRow.createCell(0).setCellValue(i);
                dataRow.createCell(1).setCellValue(i * 100);
                dataRow.createCell(2).setCellValue("Category_" + i);
            }

            // Write to file
            try (FileOutputStream fos = new FileOutputStream(outputFilePath)) {
                workbook.write(fos);
            }
            System.out.println("File created successfully at: " + outputFilePath);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}