Apache POI HSSFWorkbook: Workbook to Byte Streams and Back

Apache POI HSSFWorkbook: Workbook to Byte Streams and Back

Apache POI’s HSSFWorkbook class provides a powerful way to create and manipulate Excel files in Java. This tutorial demonstrates converting a workbook to a byte stream and reconstructing it from bytes, crucial for dynamic Excel generation in Spring Boot applications. The method avoids disk I/O, enhancing performance and scalability.

Working with Excel files is a common task in enterprise Java applications, but directly managing file I/O can introduce performance bottlenecks and complexity. Converting to and from byte streams allows for in-memory processing, which is significantly faster and more manageable, particularly in microservice architectures.

Key Insights

• POI Version 5.3.0: The tutorial utilizes version 5.3.0 of the Apache POI library.
• ByteArrayOutputStream: This class efficiently handles the conversion of a workbook to a byte array in memory.
• Spring Boot Integration: Demonstrates seamless integration with Spring Boot for building REST endpoints that serve Excel files.

Working Example

public class ExcelCreator {
public static HSSFWorkbook createSampleWorkbook() {
HSSFWorkbook workbook = new HSSFWorkbook();
final String SHEET_NAME = "Employees";
final String[] COLUMN_HEADERS = { "ID", "Name", "Department" };
Object[][] data = {
{ 101, "John Doe", "Finance" },
{ 102, "Jane Smith", "HR" },
{ 103, "Michael Clark", "IT" }
};
Sheet sheet = workbook.createSheet(SHEET_NAME);
HSSFFont font = workbook.createFont();
font.setBold(true);
HSSFCellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFont(font);
Row header = sheet.createRow(0);
for (int i = 0; i < COLUMN_HEADERS.length; i++) {
Cell cell = header.createCell(i);
cell.setCellValue(COLUMN_HEADERS[i]);
cell.setCellStyle(headerStyle);
}
int rowNum = 1;
for (Object[] rowData : data) {
Row row = sheet.createRow(rowNum++);
for (int i = 0; i < rowData.length; i++) {
Cell cell = row.createCell(i);
Object value = rowData[i];
if (value instanceof Integer) {
cell.setCellValue(((Integer) value).doubleValue());
} else if (value instanceof Double) {
cell.setCellValue((Double) value);
} else if (value != null) {
cell.setCellValue(value.toString());
}
}
}
for (int i = 0; i < COLUMN_HEADERS.length; i++) {
sheet.autoSizeColumn(i);
}
return workbook;
}
}

public class ExcelConverter {
public static byte[] convertWorkbookToBytes(HSSFWorkbook workbook) throws IOException {
try (ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
workbook.write(baos);
return baos.toByteArray();
}
}
}

public static HSSFWorkbook convertBytesToWorkbook(byte[] excelBytes) throws IOException {
try (ByteArrayInputStream bais = new ByteArrayInputStream(excelBytes)) {
return new HSSFWorkbook(bais);
}
}

Practical Applications

• Reporting Services: Generating dynamic Excel reports based on real-time data, such as a financial reporting system creating daily sales summaries.
• Pitfall: Directly writing to disk for large Excel files can lead to performance issues and potential file locking conflicts; using byte streams mitigates this risk.

References:

• https://www.baeldung.com/apache-poi-hssfworkbook-workbook-byte-streams-back