Working with Excel files is a common requirement in enterprise Java applications, whether for reporting, data exchange, or bulk uploads. Apache POI offers a comprehensive API for reading and writing Microsoft Office documents, and for legacy Excel .xls files, the primary class is HSSFWorkbook. In this article, we examine how to create an HSSFWorkbook, convert it to byte streams for storage or download, and then reconstruct it back from those byte streams.
1. What Is HSSFWorkbook?
HSSFWorkbook is the Apache POI representation of an Excel 97–2003 workbook, which corresponds to the .xls file format. It is part of the HSSF (Horrible Spreadsheet Format) API, designed specifically for the older binary Excel format, as opposed to XSSFWorkbook which targets the newer .xlsx XML-based format.
Because .xls files are still widely used in legacy systems, HSSFWorkbook remains relevant. It supports creating sheets, rows, cells, styles, and formulas, and it can both read from and write to streams, making it suitable for web downloads, email attachments, and database or object storage persistence.
2. Setting Up Apache POI
To use HSSFWorkbook, we need to include Apache POI dependencies in our project. In a Spring Boot or standard Maven project, this is done through pom.xml.
<!-- Spring Boot Web -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Apache POI for .xls (HSSF) -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.3.0</version>
</dependency>
<!-- Testing -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
This configuration adds the Apache POI core libraries required for handling .xls files via HSSFWorkbook. Spring Boot dependencies are added for building REST endpoints that can return Excel files as downloads.
3. Creating an HSSFWorkbook in Memory
The first step in working with Excel programmatically is to create a workbook, add sheets, and populate cells. This all occurs in memory before any data is written to disk or sent over the network.
public class WorkbookFactoryUtil {
public static Workbook createSampleWorkbook() {
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("Users");
Row header = sheet.createRow(0);
header.createCell(0).setCellValue("ID");
header.createCell(1).setCellValue("Name");
header.createCell(2).setCellValue("Email");
Row row1 = sheet.createRow(1);
row1.createCell(0).setCellValue(1);
row1.createCell(1).setCellValue("Andrew");
row1.createCell(2).setCellValue("andrew@jcg.com");
Row row2 = sheet.createRow(2);
row2.createCell(0).setCellValue(2);
row2.createCell(1).setCellValue("Thomas");
row2.createCell(2).setCellValue("thomas@.com");
for (int i = 0; i < 3; i++) {
sheet.autoSizeColumn(i);
}
return workbook;
}
}
This code constructs an in-memory .xls workbook using HSSFWorkbook. A sheet named Users is created, followed by a header row and two data rows. Cells are populated using strongly typed setters, and autoSizeColumn is called to improve readability. At this point, the workbook only exists in memory and has not yet been written to any file or stream.
4. Converting HSSFWorkbook to Byte Stream
Excel data is often transferred between services, storage systems, and client applications as raw bytes, and Apache POI supports writing workbooks directly to output streams.
public class WorkbookStreamUtil {
public static byte[] workbookToBytes(Workbook workbook) throws IOException {
try (ByteArrayOutputStream out = new ByteArrayOutputStream()) {
workbook.write(out);
return out.toByteArray();
}
}
}
This utility converts the in-memory workbook into a byte[] by writing it into a ByteArrayOutputStream. This byte array can be returned from a REST endpoint, saved to a database as a BLOB, uploaded to cloud storage, or attached to an email.
5. Converting Byte Stream Back to HSSFWorkbook
Sometimes we receive Excel data from external sources such as file uploads, message queues, or database storage and need to read it back into a workbook for processing.
public class WorkbookReaderUtil {
public static Workbook bytesToWorkbook(byte[] data) throws IOException {
try (ByteArrayInputStream in = new ByteArrayInputStream(data)) {
return new HSSFWorkbook(in);
}
}
}
Here, the byte array is wrapped in a ByteArrayInputStream, which is then passed to the HSSFWorkbook constructor. Apache POI parses the binary .xls format and reconstructs the full workbook structure in memory, including sheets, rows, cells, and styles.
6. Implementing HSSFWorkbook in a Spring Boot Application
A common scenario is generating Excel reports dynamically and returning them as file downloads from REST endpoints. Spring Boot integrates well with Apache POI for this purpose. In this section, we build a minimal Spring Boot application that exposes an endpoint to download an .xls file and another endpoint that reads it back for validation.
Excel Service for Creating and Reading Workbooks
@Service
public class ExcelService {
public byte[] generateExcelFile() throws IOException {
Workbook workbook = WorkbookFactoryUtil.createSampleWorkbook();
return WorkbookStreamUtil.workbookToBytes(workbook);
}
public List<String> readNamesFromExcel(byte[] data) throws IOException {
Workbook workbook = WorkbookReaderUtil.bytesToWorkbook(data);
Sheet sheet = workbook.getSheetAt(0);
List<String> names = new ArrayList<>();
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
names.add(row.getCell(1).getStringCellValue());
}
return names;
}
}
The generateExcelFile method creates a workbook and converts it to bytes, while readNamesFromExcel reconstructs the workbook and extracts the Name column.
REST Controller for Download and Verification
@RestController
@RequestMapping("/api/excel")
public class ExcelController {
private final ExcelService excelService;
public ExcelController(ExcelService excelService) {
this.excelService = excelService;
}
@GetMapping("/download")
public ResponseEntity<byte[]> downloadExcel() throws IOException {
byte[] fileData = excelService.generateExcelFile();
return ResponseEntity.ok()
.header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=users.xls")
.contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
.body(fileData);
}
@PostMapping("/read")
public ResponseEntity<List<String>> readExcel(@RequestBody byte[] fileData) throws IOException {
List<String> names = excelService.readNamesFromExcel(fileData);
return ResponseEntity.ok(names);
}
}
The downloadExcel endpoint, mapped to GET /download, calls the generateExcelFile method of the service to create a byte array representing an .xls Excel file. It then returns this byte array as an HTTP response with the proper headers, including Content-Disposition to prompt a download with the filename users.xls and Content-Type to indicate an Excel file. This allows clients, such as browsers or REST clients, to receive and save the file directly.
The readExcel endpoint, mapped to POST /read, accepts a byte array in the request body, which represents an uploaded Excel file. It calls the service method readNamesFromExcel to extract the names from the workbook and returns them as a JSON array.
Download the Excel File
Open a browser or a REST client like Postman and send a GET request to:
http://localhost:8080/api/excel/download
The browser should prompt you to download a file named users.xls. Open the file in Excel to see a table like this:
7. Conclusion
In this article, we explored how to work with Apache POI HSSFWorkbook to create Excel .xls files, convert them to byte streams, and reconstruct them back for processing. We demonstrated how to set up Apache POI in a Spring Boot project and build REST endpoints for downloading and reading Excel files. This approach enables us to handle Excel data efficiently in web applications and microservices, providing a reliable way to generate reports, exchange data, and automate processing tasks.
8. Download the Source Code
This article covered Apache POI HSSFWorkbook and how to convert a workbook to byte streams and back.
You can download the full source code of this example here: apache poi hssfworkbook workbook byte streams back
