Comparison of HSSFWorkbook, XSSFWorkbook, and SXSSFWorkbook in Apache POI

1. Overview

Apache POI is an open-source library that allows us to work programmatically with Microsoft Office documents, including Excel. Apache POI has three different classes that can be used to create workbooks: HSSFWorkbook, XSSFWorkbook, and SXSSFWorkbook.

In this tutorial, we’ll compare the functionality of these three classes and conduct some evaluations to help us choose the best option for our particular use case.

2. Creating an Excel File

Before comparing them, let’s do a quick review on how to generate an Excel file using Apache POI. We’ll need the Apache POI and POI OOXML schema dependencies in the pom.xml:

<dependency> 
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId> 
    <version>5.3.0</version> 
</dependency> 
<dependency> 
    <groupId>org.apache.poi</groupId> 
    <artifactId>poi-ooxml</artifactId> 
    <version>5.3.0</version> 
</dependency>

To create an instance of Workbook, we need to call the default constructor of our target Workbook class. After that, we can write our content to the Workbook and then write the Workbook into the target OutputStream.

In the following example, we create a Workbook with XSSFWorkbook and write some content to the test.xlsx file:

try (Workbook workbook = new XSSFWorkbook();
  OutputStream outputStream = new BufferedOutputStream(new FileOutputStream("test.xlsx"))) {
    Sheet sheet = workbook.createSheet("test");
    sheet.createRow(0).createCell(0).setCellValue("test content");
    workbook.write(outputStream);
}

It’s simple to replace the Workbook class because we only need to change the instance of XSSFWorkbook to whatever Workbook class we want to use, and after that, everything is just the same: creating sheets, rows, and columns in the Excel file.

Another thing to remember is that HSSFWorkbook is used for the old Excel format, so the output file needs to have the .xls extension. The XSSFWorkbook and the SXSSFWorkbook are used for the newer Excel format, which provides an output file with the .xlsx extension.

3. General Comparisons

The three Workbook classes available in Apache POI are HSSFWorkbook, XSSFWorkbook, and SXSSFWorkbook. They share similarities yet offer distinct functionalities catering to different file formats and use cases. Here’s a quick summary of their main characteristics:

In short, HSSFWorkbook produces Excel files in the older .xls format. The XSSFWorkbook and SXSSFWorkbook create files in the XML-based .xlsx format used by Excel 2007 and later.

Both HSSFWorbook and XSSFWorkbook are non-streaming workbooks that keep all rows of data in memory, whereas SXSSFWorkbook is a streaming workbook that only retains a certain number of rows in memory. Hence, it’s much more memory-efficient if the dataset is huge.

4. Unsupported Functions in SXSSFWorkbook

A streaming workbook flushes rows when the number of rows in the window reaches a threshold called row access window size. The default value is 100, but we can change it with the constructor. For example, let’s see how to create a workbook with a window size of 50:

Workbook workbook = new SXSSFWorkbook(50);

Due to this streaming behavior, accessing all of the row information simultaneously is impossible, which can sometimes lead to the invocation failure of some Apache POI functions. In the following subsections, we’ll examine which functions are not supported in the SXSSFWorkbook:

4.1. Test Setup

Let’s set up some tests to verify the functionalities that do not work in the streaming workbook. We’ll create an instance of SXSSFWorkbook with two data rows and one column in the sheet.

For our demonstration, we’ll explicitly set the window size to 1. This would be considerably larger in reality:

@BeforeEach
void setup() {
    workbook = new SXSSFWorkbook(1);
    sheet = workbook.createSheet("Test Sheet");
    sheet.createRow(0).createCell(0).setCellValue(5);
    sheet.createRow(1).createCell(0).setCellValue(15);
}

4.2. Auto-Size Column

The auto-size column function automatically sets the width of the column to the longest cell value in that column so that the data becomes fully visible without manual resizing. This should fail during streaming, as it does not know the row widths:

@Test
void whenAutoSizeColumnOnSXSSFWorkbook_thenThrowsIllegalStateException() {
    assertThrows(IllegalStateException.class, () -> sheet.autoSizeColumn(0));
}

4.3. Clone Sheet

The clone sheet function creates a copy of an existing sheet within the Workbook. This operation isn’t supported in streaming because it doesn’t keep all data from the sheet in memory:

@Test
void whenCloneSheetOnSXSSFWorkbook_thenThrowsIllegalStateException() {
    assertThrows(IllegalStateException.class, () -> workbook.cloneSheet(0));
}

4.4. Get Row

Now, let’s attempt to get a row that has already been flushed. It’ll return null from the sheet:

@Test
void whenGetRowOnSXSSFWorkbook_thenReturnNull() {
    Row row = sheet.getRow(0);
    assertThat(row).isNull();
}

However, in the case of a window size of 2, it will return the instance of the row because it hasn’t been flushed yet.

4.5. Formula Evaluation

Formula evaluation refers to the re-computation of the formula result present in Excel cells. Similar to previous cases, evaluation may happen only when rows aren’t flushed:

@Test
void whenEvaluateFormulaCellOnSXSSFWorkbook_thenThrowsIllegalStateException() {
    Cell formulaCell = sheet.createRow(sheet.getLastRowNum()).createCell(0);
    formulaCell.setCellFormula("SUM(A1:B1)");

    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    assertThrows(SXSSFFormulaEvaluator.RowFlushedException.class,
      () -> evaluator.evaluateFormulaCell(formulaCell));
}

4.6. Shift Columns

Finally, let’s look into shift columns, which shift existing columns left or right in a sheet. It’s expected to fail in the streaming workbook, as shifting a column obviously requires the whole data of the sheet to be in memory:

@Test
void whenShiftColumnsOnSXSSFWorkbook_thenThrowsUnsupportedOperationException() {
    assertThrows(UnsupportedOperationException.class, () -> sheet.shiftColumns(0, 2, 1));
}

5. Evaluations

We’ve discussed the functional differences between the three Workbook classes. Now, it’s time to perform experiments to compare execution time and memory consumption. These are the basic metrics for the Workbook class choice regarding our requirements.

For our comparison, we’ll adopt JMH (Java Microbenchmark Harness) for benchmarking. Let’s add the JMH dependencies to the pom.xml. Both Core and Annotation Processors can be found in Maven Central:

<dependency>
    <groupId>org.openjdk.jmh</groupId>
    <artifactId>jmh-core</artifactId>
    <version>1.37</version>
</dependency>
<dependency>
    <groupId>org.openjdk.jmh</groupId>
    <artifactId>jmh-generator-annprocess</artifactId>
    <version>1.37</version>
</dependency>

5.1. Experiments Setup

In our experiments, let’s create a sheet via the chosen Workbook class and write a specified number of rows into an Excel file, each containing 256 columns with identical text across all columns:

Sheet sheet = workbook.createSheet();
for (int n=0;n<iterations;n++) {
    Row row = sheet.createRow(sheet.getLastRowNum()+1);
    for (int c=0;c<256;c++) {
        Cell cell = row.createCell(c);
        cell.setCellValue("abcdefghijklmnopqrstuvwxyz");
    }
}

We’ll execute the test for various quantities of rows: 2,500, 5,000, 10,000, 20,000, and 40,000 rows. Three classes of Workbook will be tested based on execution time and memory consumption. We’ll conduct each set of experiments three times and take their average as the results.

5.2. Execution Time

Let’s look at the execution time (in milliseconds) each workbook class took to write a certain number of rows into an Excel file:

Among the three classes, the HSSFWorkbook is always faster compared to the XSSFWorkbook and the SXSSFWorkbook. The XSSFWorkbook shows the highest execution time, which is around 30 times slower than the HSSFWorkbook. The SXSSFWorkbook class provides a compromise between the two.

The reason for such results could be that the binary .xls format is less complex to handle. It’s evident that the XML-based .xlsx format requires more processing, and such slowdown will be more significant with larger datasets. 

5.3. Memory Consumption

Let’s review the memory consumption (in megabytes) for each workbook class when writing the same number of rows:

For both the HSSFWorkbook and the XSSFWorkbook, the memory consumption grows with the number of rows. That’s because these Workbook classes store all data in memory. However, the XSSFWorkbook grows significantly more than the HSSFWorkbook.

SXSSFWorkbook is the clear winner in terms of memory efficiency. Its memory consumption remains almost constant, regardless of the number of rows, at around 210MB.

This is due to its streaming behavior, where only a small portion of rows is kept in memory at any given time. This makes SXSSFWorkbook ideal for handling large datasets without running out of memory.

6. Conclusion

The HSSFWorkbook, XSSFWorkbook, and SXSSFWorkbook have different use cases:

• HSSFWorkbook is the fastest but is limited to the old .xls format and small datasets.
• XSSFWorkbook supports most of the Excel features in the .xlsx format. However, this is highly memory-consuming.
• SXSSFWorkbook excels with big data sets and does so using very little memory, via its streaming capabilities. However, some functionalities are missing compared to the XSSFWorkbook.

In general, we can choose SXSSFWorkbook for large files, XSSFWorkbook for complete features, and HSSFWorkbook for compatibility with older Excel formats.