Apache POI - Quick Reference

When NOT to Use This Skill

CSV files - Use OpenCSV, Apache Commons CSV, or Files.lines() for CSV
PDF generation - Use Apache PDFBox or iText instead
Large Excel files (> 100k rows) - Use SXSSFWorkbook or consider CSV export
Excel formulas/macros - POI has limited macro support; consider VBA alternatives
Real-time Excel editing - POI is for generation/parsing, not collaborative editing

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: apache-poi for comprehensive API documentation, advanced formatting, and formula handling.

Pattern Essenziali

Maven

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

Create Excel

try (Workbook workbook = new XSSFWorkbook()) {
    Sheet sheet = workbook.createSheet("Data");

    // Header
    Row header = sheet.createRow(0);
    header.createCell(0).setCellValue("Name");
    header.createCell(1).setCellValue("Email");

    // Data
    Row row = sheet.createRow(1);
    row.createCell(0).setCellValue("John");
    row.createCell(1).setCellValue("john@email.com");

    // Auto-size
    sheet.autoSizeColumn(0);
    sheet.autoSizeColumn(1);

    // Write
    workbook.write(new FileOutputStream("output.xlsx"));
}

Export Service

@Service
public class ExportService {
    public byte[] exportToExcel(List<User> users) throws IOException {
        try (Workbook wb = new XSSFWorkbook();
             ByteArrayOutputStream out = new ByteArrayOutputStream()) {
            Sheet sheet = wb.createSheet("Users");
            // ... populate rows
            wb.write(out);
            return out.toByteArray();
        }
    }
}

REST Download Endpoint

@GetMapping("/export")
public ResponseEntity<byte[]> export() throws IOException {
    byte[] data = exportService.exportToExcel(users);
    return ResponseEntity.ok()
        .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=data.xlsx")
        .contentType(MediaType.APPLICATION_OCTET_STREAM)
        .body(data);
}

Cell Styles

CellStyle headerStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);
headerStyle.setFont(font);
cell.setCellStyle(headerStyle);

Anti-Patterns

Anti-Pattern	Why It's Wrong	Correct Approach
Not closing workbook	Memory leak, file locks	Use try-with-resources: `try (Workbook wb = new XSSFWorkbook())`
Creating styles in loops	Workbook has max 64,000 styles limit	Create styles once, reuse across cells
Using `XSSFWorkbook` for large files	OutOfMemoryError for > 100k rows	Use `SXSSFWorkbook` for streaming writes
Reading entire file into memory	Memory exhaustion on large files	Use event-based parsing (SAX) for reading
Not setting cell types	Data interpreted incorrectly	Explicitly set cell type: `cell.setCellType(CellType.NUMERIC)`
Using `autoSizeColumn()` for every column	Very slow, especially with many rows	Manually set widths or use sparingly
Hardcoding column indices	Brittle, breaks on column changes	Use constants or column name maps
Generating Excel for large datasets	Excel has 1M row limit, slow	Consider CSV or database export instead

Quick Troubleshooting

Issue	Diagnosis	Solution
`OutOfMemoryError` on large Excel	Using `XSSFWorkbook`, all data in memory	Switch to `SXSSFWorkbook` for streaming
`IllegalStateException: Cannot get a STRING value from a NUMERIC cell`	Wrong cell type getter	Check type with `cell.getCellType()` before reading
File corrupted after generation	Workbook not closed properly	Use try-with-resources or explicitly call `wb.close()`
Styles not applying	Exceeding 64,000 style limit	Reuse CellStyle objects, don't create in loops
Numbers displayed as text in Excel	Cell type not set	Use `cell.setCellType(CellType.NUMERIC)` and set value as number
Formula not calculating	Formula mode not set	Use `cell.setCellFormula("SUM(A1:A10)")` or force recalc
Very slow generation	`autoSizeColumn()` on large sheets	Remove or call only on critical columns
Date formatting incorrect	Default format not desired	Create date CellStyle with custom format

Related Skills

References

Apache POI Official Docs