Apache POI is a powerful Java library for working with Microsoft Office documents. This comprehensive guide covers Excel export functionality including basic operations, styling, advanced features, and best practices.
Setup and Dependencies
1. Maven Dependencies
<dependencies> <!-- Apache POI Core --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.2.4</version> </dependency> <!-- Apache POI OOXML for .xlsx files --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.2.4</version> </dependency> <!-- Optional: For styled exports --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>5.2.4</version> </dependency> </dependencies>
2. Basic Excel Export Service
@Service
public class ExcelExportService {
private static final Logger logger = LoggerFactory.getLogger(ExcelExportService.class);
/**
* Creates a basic workbook (XSSF for .xlsx, HSSF for .xls)
*/
public Workbook createWorkbook(boolean useXlsx) {
return useXlsx ? new XSSFWorkbook() : new HSSFWorkbook();
}
/**
* Basic data export to Excel
*/
public Workbook exportToExcel(List<List<Object>> data, String sheetName) {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet(sheetName);
int rowNum = 0;
for (List<Object> rowData : data) {
Row row = sheet.createRow(rowNum++);
int colNum = 0;
for (Object field : rowData) {
Cell cell = row.createCell(colNum++);
setCellValue(cell, field);
}
}
return workbook;
}
private void setCellValue(Cell cell, Object value) {
if (value == null) {
cell.setCellValue("");
} else if (value instanceof String) {
cell.setCellValue((String) value);
} else if (value instanceof Number) {
cell.setCellValue(((Number) value).doubleValue());
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
} else if (value instanceof Date) {
cell.setCellValue((Date) value);
} else if (value instanceof LocalDate) {
cell.setCellValue((LocalDate) value);
} else if (value instanceof LocalDateTime) {
cell.setCellValue((LocalDateTime) value);
} else {
cell.setCellValue(value.toString());
}
}
}
Advanced Excel Export with Styling
3. Comprehensive Excel Export with Styling
@Component
public class AdvancedExcelExportService {
private static final String DEFAULT_FONT_NAME = "Arial";
/**
* Export data with headers and styling
*/
public Workbook exportWithStyling(List<String> headers,
List<List<Object>> data,
String sheetName) {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet(sheetName);
// Create header style
CellStyle headerStyle = createHeaderStyle(workbook);
// Create data styles
CellStyle stringStyle = createStringStyle(workbook);
CellStyle numberStyle = createNumberStyle(workbook);
CellStyle dateStyle = createDateStyle(workbook);
CellStyle currencyStyle = createCurrencyStyle(workbook);
// Create header row
createHeaderRow(sheet, headers, headerStyle);
// Create data rows
createDataRows(sheet, data, stringStyle, numberStyle, dateStyle, currencyStyle);
// Auto-size columns
autoSizeColumns(sheet, headers.size());
return workbook;
}
private CellStyle createHeaderStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
// Background color
style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// Font
Font font = workbook.createFont();
font.setFontName(DEFAULT_FONT_NAME);
font.setFontHeightInPoints((short) 11);
font.setBold(true);
font.setColor(IndexedColors.WHITE.getIndex());
style.setFont(font);
// Borders
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
// Alignment
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
private CellStyle createStringStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontName(DEFAULT_FONT_NAME);
font.setFontHeightInPoints((short) 10);
style.setFont(font);
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
return style;
}
private CellStyle createNumberStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontName(DEFAULT_FONT_NAME);
font.setFontHeightInPoints((short) 10);
style.setFont(font);
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.RIGHT);
// Number format
style.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"));
return style;
}
private CellStyle createDateStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontName(DEFAULT_FONT_NAME);
font.setFontHeightInPoints((short) 10);
style.setFont(font);
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
// Date format
style.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd"));
return style;
}
private CellStyle createCurrencyStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontName(DEFAULT_FONT_NAME);
font.setFontHeightInPoints((short) 10);
style.setFont(font);
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.RIGHT);
// Currency format
style.setDataFormat(workbook.createDataFormat().getFormat("$#,##0.00"));
return style;
}
private void createHeaderRow(Sheet sheet, List<String> headers, CellStyle headerStyle) {
Row headerRow = sheet.createRow(0);
for (int i = 0; i < headers.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(headers.get(i));
cell.setCellStyle(headerStyle);
}
}
private void createDataRows(Sheet sheet, List<List<Object>> data,
CellStyle stringStyle, CellStyle numberStyle,
CellStyle dateStyle, CellStyle currencyStyle) {
int rowNum = 1;
for (List<Object> rowData : data) {
Row row = sheet.createRow(rowNum++);
for (int colNum = 0; colNum < rowData.size(); colNum++) {
Cell cell = row.createCell(colNum);
Object value = rowData.get(colNum);
setCellValueWithStyle(cell, value, stringStyle, numberStyle, dateStyle, currencyStyle);
}
}
}
private void setCellValueWithStyle(Cell cell, Object value,
CellStyle stringStyle, CellStyle numberStyle,
CellStyle dateStyle, CellStyle currencyStyle) {
if (value == null) {
cell.setCellValue("");
cell.setCellStyle(stringStyle);
} else if (value instanceof String) {
cell.setCellValue((String) value);
cell.setCellStyle(stringStyle);
} else if (value instanceof Integer || value instanceof Long) {
cell.setCellValue(((Number) value).doubleValue());
cell.setCellStyle(numberStyle);
} else if (value instanceof Double || value instanceof Float) {
cell.setCellValue(((Number) value).doubleValue());
// Use currency style for monetary values
if (isMonetaryValue(value)) {
cell.setCellStyle(currencyStyle);
} else {
cell.setCellStyle(numberStyle);
}
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
cell.setCellStyle(stringStyle);
} else if (value instanceof Date) {
cell.setCellValue((Date) value);
cell.setCellStyle(dateStyle);
} else if (value instanceof LocalDate) {
cell.setCellValue((LocalDate) value);
cell.setCellStyle(dateStyle);
} else if (value instanceof LocalDateTime) {
cell.setCellValue((LocalDateTime) value);
cell.setCellStyle(dateStyle);
} else {
cell.setCellValue(value.toString());
cell.setCellStyle(stringStyle);
}
}
private boolean isMonetaryValue(Object value) {
// Custom logic to determine if value represents money
return value instanceof Double && ((Double) value) != 0.0;
}
private void autoSizeColumns(Sheet sheet, int columnCount) {
for (int i = 0; i < columnCount; i++) {
sheet.autoSizeColumn(i);
}
}
}
Spring Controller for Excel Export
4. REST Controller for Excel Downloads
@RestController
@RequestMapping("/api/excel")
public class ExcelExportController {
@Autowired
private AdvancedExcelExportService excelExportService;
@Autowired
private UserService userService;
@Autowired
private ProductService productService;
/**
* Export users to Excel
*/
@GetMapping("/export-users")
public ResponseEntity<byte[]> exportUsersToExcel() {
try {
List<User> users = userService.getAllUsers();
List<String> headers = Arrays.asList(
"ID", "Username", "Email", "First Name", "Last Name",
"Role", "Created Date", "Active"
);
List<List<Object>> data = convertUsersToData(users);
Workbook workbook = excelExportService.exportWithStyling(headers, data, "Users");
return createExcelResponse(workbook, "users_export.xlsx");
} catch (Exception e) {
logger.error("Error exporting users to Excel", e);
return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build();
}
}
/**
* Export products to Excel with filtering
*/
@GetMapping("/export-products")
public ResponseEntity<byte[]> exportProductsToExcel(
@RequestParam(required = false) String category,
@RequestParam(required = false) Boolean inStock) {
try {
List<Product> products = productService.getProducts(category, inStock);
List<String> headers = Arrays.asList(
"Product ID", "Name", "Category", "Price", "Stock Quantity",
"Description", "Created Date", "Active"
);
List<List<Object>> data = convertProductsToData(products);
Workbook workbook = excelExportService.exportWithStyling(headers, data, "Products");
String filename = "products_export.xlsx";
if (category != null) {
filename = "products_" + category + "_export.xlsx";
}
return createExcelResponse(workbook, filename);
} catch (Exception e) {
logger.error("Error exporting products to Excel", e);
return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build();
}
}
/**
* Custom export with request body
*/
@PostMapping("/custom-export")
public ResponseEntity<byte[]> customExport(@RequestBody ExportRequest request) {
try {
// Process export based on request
List<String> headers = request.getHeaders();
List<List<Object>> data = request.getData();
Workbook workbook = excelExportService.exportWithStyling(
headers, data, request.getSheetName());
return createExcelResponse(workbook, request.getFileName());
} catch (Exception e) {
logger.error("Error in custom export", e);
return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build();
}
}
private List<List<Object>> convertUsersToData(List<User> users) {
List<List<Object>> data = new ArrayList<>();
for (User user : users) {
List<Object> row = new ArrayList<>();
row.add(user.getId());
row.add(user.getUsername());
row.add(user.getEmail());
row.add(user.getFirstName());
row.add(user.getLastName());
row.add(user.getRole());
row.add(user.getCreatedDate());
row.add(user.isActive());
data.add(row);
}
return data;
}
private List<List<Object>> convertProductsToData(List<Product> products) {
List<List<Object>> data = new ArrayList<>();
for (Product product : products) {
List<Object> row = new ArrayList<>();
row.add(product.getId());
row.add(product.getName());
row.add(product.getCategory());
row.add(product.getPrice());
row.add(product.getStockQuantity());
row.add(product.getDescription());
row.add(product.getCreatedDate());
row.add(product.isActive());
data.add(row);
}
return data;
}
private ResponseEntity<byte[]> createExcelResponse(Workbook workbook, String filename)
throws IOException {
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workbook.write(outputStream);
workbook.close();
byte[] bytes = outputStream.toByteArray();
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
headers.setContentDispositionFormData("attachment", filename);
headers.setContentLength(bytes.length);
return new ResponseEntity<>(bytes, headers, HttpStatus.OK);
}
}
// DTO for custom export requests
class ExportRequest {
private List<String> headers;
private List<List<Object>> data;
private String sheetName;
private String fileName;
// Constructors, getters, and setters
public ExportRequest() {}
public ExportRequest(List<String> headers, List<List<Object>> data,
String sheetName, String fileName) {
this.headers = headers;
this.data = data;
this.sheetName = sheetName;
this.fileName = fileName;
}
public List<String> getHeaders() { return headers; }
public void setHeaders(List<String> headers) { this.headers = headers; }
public List<List<Object>> getData() { return data; }
public void setData(List<List<Object>> data) { this.data = data; }
public String getSheetName() { return sheetName; }
public void setSheetName(String sheetName) { this.sheetName = sheetName; }
public String getFileName() { return fileName; }
public void setFileName(String fileName) { this.fileName = fileName; }
}
Advanced Features
5. Excel Export with Multiple Sheets
@Component
public class MultiSheetExcelExportService {
/**
* Export multiple datasets to different sheets
*/
public Workbook exportMultipleSheets(Map<String, SheetData> sheetDataMap) {
Workbook workbook = new XSSFWorkbook();
for (Map.Entry<String, SheetData> entry : sheetDataMap.entrySet()) {
String sheetName = entry.getKey();
SheetData sheetData = entry.getValue();
createSheet(workbook, sheetName, sheetData);
}
return workbook;
}
/**
* Export with summary sheet
*/
public Workbook exportWithSummary(List<ReportData> reportDataList) {
Workbook workbook = new XSSFWorkbook();
// Create summary sheet
createSummarySheet(workbook, reportDataList);
// Create detailed sheets
for (ReportData reportData : reportDataList) {
createDetailedSheet(workbook, reportData);
}
return workbook;
}
private void createSheet(Workbook workbook, String sheetName, SheetData sheetData) {
Sheet sheet = workbook.createSheet(sheetName);
CellStyle headerStyle = createHeaderStyle(workbook);
CellStyle dataStyle = createDataStyle(workbook);
// Create header
Row headerRow = sheet.createRow(0);
for (int i = 0; i < sheetData.getHeaders().size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(sheetData.getHeaders().get(i));
cell.setCellStyle(headerStyle);
}
// Create data rows
int rowNum = 1;
for (List<Object> rowData : sheetData.getData()) {
Row row = sheet.createRow(rowNum++);
for (int colNum = 0; colNum < rowData.size(); colNum++) {
Cell cell = row.createCell(colNum);
setCellValue(cell, rowData.get(colNum));
cell.setCellStyle(dataStyle);
}
}
// Auto-size columns
for (int i = 0; i < sheetData.getHeaders().size(); i++) {
sheet.autoSizeColumn(i);
}
}
private void createSummarySheet(Workbook workbook, List<ReportData> reportDataList) {
Sheet sheet = workbook.createSheet("Summary");
CellStyle headerStyle = createHeaderStyle(workbook);
CellStyle dataStyle = createDataStyle(workbook);
// Create summary headers
String[] summaryHeaders = {"Report Name", "Total Records", "Generated Date"};
Row headerRow = sheet.createRow(0);
for (int i = 0; i < summaryHeaders.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(summaryHeaders[i]);
cell.setCellStyle(headerStyle);
}
// Create summary data
int rowNum = 1;
for (ReportData reportData : reportDataList) {
Row row = sheet.createRow(rowNum++);
row.createCell(0).setCellValue(reportData.getName());
row.createCell(1).setCellValue(reportData.getRecordCount());
row.createCell(2).setCellValue(new Date());
}
// Auto-size columns
for (int i = 0; i < summaryHeaders.length; i++) {
sheet.autoSizeColumn(i);
}
}
private void createDetailedSheet(Workbook workbook, ReportData reportData) {
// Implementation for detailed sheets
Sheet sheet = workbook.createSheet(reportData.getName());
// Add detailed data...
}
private CellStyle createHeaderStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBold(true);
style.setFont(font);
return style;
}
private CellStyle createDataStyle(Workbook workbook) {
return workbook.createCellStyle();
}
private void setCellValue(Cell cell, Object value) {
// Implementation as shown earlier
}
}
class SheetData {
private List<String> headers;
private List<List<Object>> data;
// Constructors, getters, setters
public SheetData(List<String> headers, List<List<Object>> data) {
this.headers = headers;
this.data = data;
}
public List<String> getHeaders() { return headers; }
public List<List<Object>> getData() { return data; }
}
class ReportData {
private String name;
private int recordCount;
private List<List<Object>> data;
// Constructors, getters, setters
}
6. Excel Export with Charts
@Component
public class ExcelChartExportService {
/**
* Export data with chart
*/
public Workbook exportWithChart(List<SalesData> salesData) {
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Sales Report");
// Create data
createSalesData(sheet, salesData);
// Create chart
createChart(workbook, sheet, salesData.size());
return workbook;
}
private void createSalesData(Sheet sheet, List<SalesData> salesData) {
// Create headers
Row headerRow = sheet.createRow(0);
headerRow.createCell(0).setCellValue("Month");
headerRow.createCell(1).setCellValue("Sales");
headerRow.createCell(2).setCellValue("Target");
// Create data rows
int rowNum = 1;
for (SalesData data : salesData) {
Row row = sheet.createRow(rowNum++);
row.createCell(0).setCellValue(data.getMonth());
row.createCell(1).setCellValue(data.getSales());
row.createCell(2).setCellValue(data.getTarget());
}
// Auto-size columns
for (int i = 0; i < 3; i++) {
sheet.autoSizeColumn(i);
}
}
private void createChart(Workbook workbook, Sheet sheet, int dataSize) {
Drawing<?> drawing = sheet.createDrawingPatriarch();
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 0, 10, 15);
Chart chart = drawing.createChart(anchor);
ChartLegend legend = chart.getOrCreateLegend();
legend.setPosition(LegendPosition.BOTTOM);
LineChartData chartData = chart.getChartDataFactory().createLineChartData();
ChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);
ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
ChartDataSource<String> categoryDS = ChartDataSources.fromStringCellRange(
sheet, new CellRangeAddress(1, dataSize, 0, 0));
ChartDataSource<Number> salesDS = ChartDataSources.fromNumericCellRange(
sheet, new CellRangeAddress(1, dataSize, 1, 1));
ChartDataSource<Number> targetDS = ChartDataSources.fromNumericCellRange(
sheet, new CellRangeAddress(1, dataSize, 2, 2));
chartData.addSeries(categoryDS, salesDS).setTitle("Actual Sales");
chartData.addSeries(categoryDS, targetDS).setTitle("Sales Target");
chart.plot(chartData, bottomAxis, leftAxis);
}
}
class SalesData {
private String month;
private double sales;
private double target;
// Constructors, getters, setters
public SalesData(String month, double sales, double target) {
this.month = month;
this.sales = sales;
this.target = target;
}
public String getMonth() { return month; }
public double getSales() { return sales; }
public double getTarget() { return target; }
}
Performance Optimization
7. Large Dataset Export with Streaming
@Component
public class StreamingExcelExportService {
private static final int BATCH_SIZE = 1000;
/**
* Export large datasets using SXSSF for memory efficiency
*/
public Workbook exportLargeDataset(List<List<Object>> data, String sheetName) {
// Use SXSSF for streaming (keeps only a certain number of rows in memory)
Workbook workbook = new SXSSFWorkbook(100); // Keep 100 rows in memory
Sheet sheet = workbook.createSheet(sheetName);
int rowNum = 0;
for (List<Object> rowData : data) {
Row row = sheet.createRow(rowNum++);
int colNum = 0;
for (Object field : rowData) {
Cell cell = row.createCell(colNum++);
setCellValue(cell, field);
}
// Flush every BATCH_SIZE rows to manage memory
if (rowNum % BATCH_SIZE == 0) {
((SXSSFSheet) sheet).flushRows(BATCH_SIZE);
}
}
return workbook;
}
/**
* Export with progress tracking
*/
public Workbook exportWithProgress(List<List<Object>> data,
String sheetName,
ProgressTracker progressTracker) {
Workbook workbook = new SXSSFWorkbook(100);
Sheet sheet = workbook.createSheet(sheetName);
int totalRows = data.size();
int rowNum = 0;
for (List<Object> rowData : data) {
Row row = sheet.createRow(rowNum++);
int colNum = 0;
for (Object field : rowData) {
Cell cell = row.createCell(colNum++);
setCellValue(cell, field);
}
// Update progress
if (progressTracker != null) {
int progress = (int) ((rowNum * 100.0) / totalRows);
progressTracker.updateProgress(progress);
}
if (rowNum % BATCH_SIZE == 0) {
((SXSSFSheet) sheet).flushRows(BATCH_SIZE);
}
}
return workbook;
}
private void setCellValue(Cell cell, Object value) {
// Implementation as shown earlier
}
}
interface ProgressTracker {
void updateProgress(int percentage);
}
Testing Excel Export
8. Unit Tests for Excel Export
@SpringBootTest
public class ExcelExportServiceTest {
@Autowired
private AdvancedExcelExportService excelExportService;
@Test
public void testExcelExport() throws IOException {
// Given
List<String> headers = Arrays.asList("Name", "Age", "Salary");
List<List<Object>> data = Arrays.asList(
Arrays.asList("John Doe", 30, 50000.0),
Arrays.asList("Jane Smith", 25, 60000.0)
);
// When
Workbook workbook = excelExportService.exportWithStyling(headers, data, "Test");
// Then
assertNotNull(workbook);
assertEquals(1, workbook.getNumberOfSheets());
Sheet sheet = workbook.getSheet("Test");
assertNotNull(sheet);
assertEquals(3, sheet.getRow(0).getPhysicalNumberOfCells()); // Header cells
assertEquals(2, sheet.getLastRowNum()); // Data rows (0-based header + 2 data rows)
workbook.close();
}
@Test
public void testLargeExcelExport() throws IOException {
// Given
List<List<Object>> largeData = generateLargeDataset(10000);
// When
Workbook workbook = excelExportService.exportToExcel(largeData, "LargeData");
// Then
assertNotNull(workbook);
Sheet sheet = workbook.getSheet("LargeData");
assertEquals(10000, sheet.getLastRowNum());
workbook.close();
}
private List<List<Object>> generateLargeDataset(int size) {
List<List<Object>> data = new ArrayList<>();
for (int i = 0; i < size; i++) {
data.add(Arrays.asList("User " + i, i, 1000.0 + i));
}
return data;
}
}
Best Practices and Error Handling
9. Error Handling and Utilities
@Component
public class ExcelExportUtils {
private static final Logger logger = LoggerFactory.getLogger(ExcelExportUtils.class);
/**
* Safely export with error handling
*/
public ResponseEntity<byte[]> safeExport(ExportFunction exportFunction, String filename) {
try {
Workbook workbook = exportFunction.export();
return convertToResponse(workbook, filename);
} catch (Exception e) {
logger.error("Error during Excel export: {}", filename, e);
return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR)
.body(("Error generating Excel file: " + e.getMessage()).getBytes());
}
}
/**
* Validate export data
*/
public void validateExportData(List<String> headers, List<List<Object>> data) {
if (headers == null || headers.isEmpty()) {
throw new IllegalArgumentException("Headers cannot be null or empty");
}
if (data == null) {
throw new IllegalArgumentException("Data cannot be null");
}
for (int i = 0; i < data.size(); i++) {
List<Object> row = data.get(i);
if (row.size() != headers.size()) {
throw new IllegalArgumentException(
String.format("Row %d has %d columns, but headers have %d columns",
i, row.size(), headers.size()));
}
}
}
/**
* Sanitize sheet name (Excel has restrictions on sheet names)
*/
public String sanitizeSheetName(String proposedName) {
if (proposedName == null) {
return "Sheet1";
}
// Remove invalid characters
String sanitized = proposedName.replaceAll("[\\\\/*\\[\\]:?]", "");
// Truncate if too long (Excel limit is 31 characters)
if (sanitized.length() > 31) {
sanitized = sanitized.substring(0, 31);
}
// Ensure not empty
if (sanitized.trim().isEmpty()) {
return "Sheet1";
}
return sanitized;
}
private ResponseEntity<byte[]> convertToResponse(Workbook workbook, String filename)
throws IOException {
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workbook.write(outputStream);
workbook.close();
byte[] bytes = outputStream.toByteArray();
HttpHeaders headers = new HttpHeaders();
headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
headers.setContentDispositionFormData("attachment", filename);
headers.setContentLength(bytes.length);
return new ResponseEntity<>(bytes, headers, HttpStatus.OK);
}
}
@FunctionalInterface
interface ExportFunction {
Workbook export() throws Exception;
}
Key Features Summary
- Basic Excel Export: Simple data export to Excel format
- Advanced Styling: Custom fonts, colors, borders, and number formatting
- Multiple Sheets: Export multiple datasets to different sheets
- Charts and Graphics: Create visual representations of data
- Performance Optimization: Streaming for large datasets
- Error Handling: Robust error handling and validation
- REST API Integration: Spring Controller for web exports
- Testing: Comprehensive unit tests
Apache POI provides extensive capabilities for Excel export in Java applications, making it suitable for everything from simple data exports to complex, styled reports with advanced features.