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
