Apache POI HSSFWorkbook: Workbook to Byte Streams and Back

1. Overview

Working with Excel files is one of the most common tasks in enterprise Java applications, from reports to analytics exports. The Apache POI library makes it incredibly simple to read and write Excel files right from Java code.

In this tutorial, let’s explore how to use HSSFWorkbook in a Spring Boot project by creating an Excel workbook from scratch: converting it into a byte stream, reconstructing it from bytes, and finally, serving it via a REST endpoint. By the end, we’ll have a clear understanding of how Apache POI integrates seamlessly with Spring Boot for dynamic Excel generation.

2. Setting Up Apache POI

Before writing any code, we’ll include the necessary dependencies in our Spring Boot project for Maven:

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.3.0</version>
    </dependency>
</dependencies>

The spring-boot-starter-web dependency provides Spring MVC, REST capabilities, and embedded Tomcat, which is essential for exposing download endpoints. Apache POI (org.apache.poi) handles Excel file creation, modification, and conversion to bytes.

2.1. Creating an HSSFWorkbook from Scratch

Now that our setup is complete, let’s create a simple Excel workbook to store the data:

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;
    }
}

Let’s examine the utility function, createSampleWorkbook(). We begin by initializing a new HSSFWorkbook, which is the core object representing the Excel file in the .xls format. We define our sample employee data, column headers, and then immediately create the “Employees” sheet. For better readability, we then define a bold font style and apply it to a cell style, ensuring our column headings stand out.

The primary function of this method is to generate an HSSFWorkbook object, ready for output, with predefined employee data. Following the header, we iterate through the sample data array, creating new rows and cells as needed.

Finally, we auto-size all columns based on their content length using sheet.autoSizeColumn(). This step automatically adjusts the column width to fit the content, which ensures the final spreadsheet looks clean and professional before we return the completed HSSFWorkbook object.

2.2. Converting HSSFWorkbook to Byte Stream

Now that we can create a workbook, let’s convert it into bytes. This is extremely useful when storing files in a database, sending them as REST responses, or caching in memory:

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

The workbook is written into a ByteArrayOutputStream, which temporarily stores it in memory. We then extract a byte array using toByteArray().

This conversion is the foundation for file transmission, which is a lightweight and memory-efficient approach, especially for RESTful applications that serve dynamic content. By using bytes, we keep Excel processing in memory, eliminating the need for temporary file storage.

2.3. Converting Byte Stream Back to HSSFWorkbook

Once a workbook is in byte form, reconstructing it is straightforward using ByteArrayInputStream:

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

This snippet reverses the earlier process. The byte array becomes an input stream, which the HSSFWorkbook constructor reads to rebuild the Excel workbook in memory. It’s particularly helpful when reading Excel data stored in a database, cached object, or received from another service.

3. Practical Use Case in a Spring Boot Application

Let’s integrate everything into a working REST endpoint.

3.1. Converting HSSFWorkbook to Byte Stream

This endpoint will generate the Excel workbook on the fly and serve it as a downloadable .xls file:

@RestController
public class ExcelController {
    @GetMapping("/download-excel")
    public ResponseEntity<byte[]> downloadExcel() {
        try {
            HSSFWorkbook workbook = ExcelCreator.createSampleWorkbook();

            try (ByteArrayOutputStream baos = new ByteArrayOutputStream()) {
                workbook.write(baos);
                byte[] bytes = baos.toByteArray();

                return ResponseEntity.ok()
                  .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=employee_data.xls")
                  .contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
                  .body(bytes);
            }
        } catch (IOException e) {
            System.err.println("Error generating or writing Excel workbook: " + e.getMessage());
            return ResponseEntity.internalServerError()
                     .build();
        }
    }
}

This Spring Boot controller, marked with @RestController, defines a single endpoint, /download-excel, that handles HTTP GET requests. We initiate the process by calling ExcelCreator.createSampleWorkbook(), which generates the raw Excel data structure in memory. The primary purpose of this controller is to intercept a GET request and serve a dynamically generated Excel file directly to the client’s browser without ever touching the server’s file system.

Once we’ve the workbook, we use a ByteArrayOutputStream to stream the workbook’s content into a byte array. This approach is highly efficient and scalable, as it avoids disk I/O, making it ideal for microservices that generate reports dynamically.

Finally, we construct the ResponseEntity. We set the HTTP status to 200 OK and configure two essential headers: Content-Disposition is set to attachment with a filename of employee_data.xls, which triggers the browser’s download dialog. We also specify the Content-Type as application/vnd.ms-excel, letting the client know the exact format of the incoming data stream.

If any step of the process fails (e.g., an IOException), we catch it, log the error, and return a 500 Internal Server Error response.

3.2. Converting Byte Stream Back to HSSFWorkbook

Sometimes, Excel files are uploaded as byte arrays (for instance, in multipart form). Here’s how we could handle that, too:

@PostMapping("/upload-excel")
public ResponseEntity<String> uploadExcel(@RequestParam("file") MultipartFile file) {
    if (file.isEmpty()) {
        return ResponseEntity.badRequest()
                 .body("File is empty. Please upload a file.");
    }
    try {
        try (HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(0);
            return ResponseEntity.ok("Sheet '" + sheet.getSheetName() + "' uploaded successfully!");
        }
    } catch (IOException e) {
        System.err.println("Error processing uploaded Excel file: " + e.getMessage());
        return ResponseEntity.internalServerError()
                 .body("Failed to process the Excel file.");
    } catch (Exception e) {
        System.err.println("An unexpected error occurred during file upload: " + e.getMessage());
        return ResponseEntity.internalServerError()
                 .body("An unexpected error occurred.");
    }
}

This controller method, mapped to the /upload-excel endpoint via a POST request, is designed to accept and process an Excel file submitted as a MultipartFile.

We start with essential validation by checking if the uploaded file is empty; if it is, we return a 400 Bad Request status immediately, preventing further processing. Once the file is validated, this method helps us to accept an uploaded Excel file, convert its byte stream into a readable Apache POI HSSFWorkbook object, and acknowledge the successful parsing.

We use the file.getInputStream() to feed the raw file data directly into the HSSFWorkbook constructor. If this conversion is successful, we grab the name of the first sheet (getSheetAt(0)) and return a 200 OK status with a confirmation message, letting the client know the file was properly parsed and recognized. The method employs structured exception handling to provide informative feedback.

We specifically catch the IOException that Apache POI would likely throw if the uploaded data stream does not represent a valid Excel file format, returning a generic “Failed to process the Excel file” message. A final catch block handles any unexpected or system-level exceptions, ensuring we return a reliable 500 Internal Server Error instead of crashing the process.

4. Conclusion

In this tutorial, we explored how HSSFWorkbook can be effectively used within a Spring Boot application to create, convert, and serve Excel files in memory. Starting from a blank workbook, we learned to serialize it into bytes, deserialize it back, and expose it through a REST API. Handling Excel in this byte-based manner simplifies integration with cloud systems, APIs, and databases without dealing with physical files.

Apache POI continues to be a reliable choice for such tasks, and with Spring Boot’s simplicity, Excel file generation can become a seamless part of enterprise applications.