apache-poi

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("[email protected]");

    // 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

• Spring Boot
• Java

References

• Apache POI Official Docs