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