Legacy .xls with HSSF

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

Baseline date: 2026-10-09. This page was written against Apache POI 5.5.1 (the latest release on that date; the 6.0 line is announced but has not been released), Java 21 and later, Spring Boot 4.x on Spring Framework 7 and Jakarta EE 11. It covers HSSF, the POI component for the binary Excel 97-2003 format (.xls). New code should write .xlsx with XSSF or SXSSF, but a server that exchanges files with older systems still meets .xls files: an uploaded catalogue from a partner who has never migrated, an archive of old loan reports, or a legacy integration that refuses anything else. The page explains when the format is still justified, what is specific to HSSF (limits, colours, the record model), how to write HSSF code that survives a later migration, how to read old files and extract their text, and how to re-save them as .xlsx. The shared spreadsheet API is explained in Spreadsheet Usermodel Basics, the physical format in File Formats: OOXML & OLE2, and the large-file read model in Streaming Reads: Event & SAX APIs.

When .xls is still needed

The binary format was replaced by Office Open XML in Excel 2007 and every current Excel, LibreOffice and Google Sheets opens .xlsx. The reasons that remain to produce or accept .xls are few, and each one deserves a check before it is accepted as a requirement.

Situation What to do Why

A partner or a legacy system only reads .xls

Write .xls for that integration only; keep the rest of the application on .xlsx.

Format negotiation is cheap to isolate behind one export method; a global downgrade is not.

Uploaded files may be .xls

Accept both with WorkbookFactory, normalise to the Workbook interface, and store or process a single model.

The import code should not branch on the format after the file is opened.

An archive of old reports must be kept readable

Keep the originals; convert on demand or in a one-off migration job to .xlsx.

The binary format is not going to be read worse tomorrow, but it has no future features.

The workbook needs more than 65,536 rows or 256 columns

Use .xlsx. There is no workaround in .xls.

Excel 97 hard-limits both; see the limits table below.

The file is large and written once per night

Use .xlsx with SXSSF.

HSSF has no streaming writer: the whole workbook lives in memory.

"We always did it" with no consumer to name

Move to .xlsx.

The format is larger on disk for text-heavy data, unreadable as XML for diagnostics and has a lower ceiling in every dimension.

Excel on the desktop can still save .xls, and WorkbookFactory.create still reads it. The Apache POI project has supported HSSF since the first release and keeps fixing it; it is a stable, mature component, not a deprecated one. Choosing .xlsx is a question of limits and features, not of POI dropping .xls.

What HSSF is

HSSF (Horrible Spreadsheet Format) is the POI component that reads and writes the BIFF8 (Binary Interchange File Format, version 8) workbook stored by Excel 97 through 2003. It is in the main poi jar, so unlike XSSF it needs no poi-ooxml jar and no XMLBeans, and it works on a plain poi dependency. On disk an .xls file is an OLE2 compound file (a small file system inside one file, handled by POIFS) whose Workbook stream is a sequence of binary records: BOUNDSHEET for every sheet, ROW, NUMBER, LABELSST and FORMULA for the content, XF for every cell format, FONT and FORMAT for fonts and number formats, and so on. The parts are described in File Formats: OOXML & OLE2.

POI exposes the file at three levels, exactly as for the XML formats:

  • Usermodel (org.apache.poi.hssf.usermodel): HSSFWorkbook, HSSFSheet, HSSFRow, HSSFCell, HSSFCellStyle, HSSFFont. These implement the shared org.apache.poi.ss.usermodel interfaces (Workbook, Sheet, Row, Cell, CellStyle, Font) and are fully read and write.

  • Event model (org.apache.poi.hssf.eventusermodel): HSSFEventFactory, HSSFListener and HSSFRequest deliver every BIFF record to a listener without building the workbook, so a very large .xls can be read with a small, constant amount of memory. This is the HSSF counterpart of the SAX read of XSSF and is explained with XLS2CSVmra in Streaming Reads: Event & SAX APIs.

  • Record level (org.apache.poi.hssf.record): the raw records, used by the event model and by tools that need a feature the usermodel does not expose. Application code rarely touches it.

HSSF has no equivalent of SXSSF. There is no streaming writer for .xls; HSSFWorkbook builds the complete model in memory and write serialises it in one pass. If you need a streamed export, the format has to be .xlsx.

Limits of the .xls format

The limits are properties of BIFF8, not of POI. The SpreadsheetVersion.EXCEL97 constant exposes them, so code that must work for both formats can read them instead of hard-coding the numbers. The XSSF limits are in SpreadsheetVersion.EXCEL2007.

Limit .xls (BIFF8, EXCEL97) .xlsx (EXCEL2007) Consequence for a report

Rows per sheet

65,536

1,048,576

A catalogue or loans export over 65,535 data rows does not fit; split sheets or use .xlsx.

Columns per sheet

256 (A to IV)

16,384 (A to XFD)

Wide pivots of years or branches fail to fit.

Distinct cell formats (styles)

4,000

64,000

One style per cell is the usual cause of reaching the limit; see the style-reuse rule below.

Characters in a cell

32,767

32,767

Equal in both formats; the display shows only about the first 1,000 characters.

Formula length

1,000 characters (text); 30 arguments per function; 7 nesting levels

8,192 characters; 255 arguments; 64 levels

Generated formulas that are long or deeply nested may exceed the .xls limits.

Sheet name length

31 characters

31 characters

The same in both; the characters \ / ? * [ ] : are forbidden.

Colours

Fixed indexed palette of 56 colours that can be replaced (HSSFPalette)

True 24-bit RGB per style and per font

Brand colours need palette slots in .xls; there are only 56 of them in the whole workbook.

Conditional formatting

Rules per range are limited (the original format allowed three) and only a subset of the XML features exist

Many rules per range, data bars, colour scales, icon sets

Rich conditional formatting belongs to .xlsx.

Streaming writer

none

SXSSF

Memory is proportional to the workbook size.

Encryption

Biff8EncryptionKey (RC4 variants)

Agile and Standard encryption

.xls encryption is weaker; see the security pages of the section.

Writing past the limits does not always fail at the call that exceeds them. HSSFSheet.createRow(65536) throws IllegalArgumentException, but a workbook with too many distinct styles fails with an IllegalStateException only when createCellStyle is called once the workbook holds 4,030 XF records. A new workbook already has 21, so in a test on 5.5.1 the 4,010th user style was the one that failed. A generator that creates one style per cell (the typical beginner mistake) will work for a small report and break for a large one. Create each style once and reuse it, as shown in Cell Styles, Fonts & Formats.

The limits can be checked at runtime through the enum, which also gives a safe way to choose the output format from the size of the data:

import org.apache.poi.ss.SpreadsheetVersion;

public final class WorkbookFormatChooser {

    private WorkbookFormatChooser() {
    }

    /** Returns true when the data fits an .xls workbook (one header row plus the data rows). */
    public static boolean fitsLegacyXls(int dataRows, int columns) {
        SpreadsheetVersion xls = SpreadsheetVersion.EXCEL97;
        return dataRows + 1 <= xls.getMaxRows() && columns <= xls.getMaxColumns();
    }
}

Source: SpreadsheetVersion Javadoc and Spreadsheet limitations.

Writing an .xls: the Bookshelf catalogue

The following class writes the Bookshelf catalogue as books-inventory-legacy.xls for a partner system that cannot read .xlsx. It checks the row limit before writing, builds the header style with a corporate colour by replacing a palette slot, reuses one style per kind of cell, applies number and date formats, freezes the header row and auto-sizes the columns. Note that the code is almost identical to the XSSF version; only the construction of the workbook, the colours and the class imports differ.

package bookshelf.legacy;

import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.time.LocalDate;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Row;

public class LegacyCatalogueExport {

    record Book(String isbn, String title, double price, LocalDate published) { }

    public static void main(String[] args) throws IOException {
        List<Book> books = List.of(
                new Book("978-0-13-468599-1", "Effective Java", 44.95, LocalDate.of(2018, 1, 6)),
                new Book("978-1-61729-461-9", "Spring in Action", 49.99, LocalDate.of(2022, 12, 20)));
        write(books, Path.of("books-inventory-legacy.xls"));
    }

    static void write(List<Book> books, Path target) throws IOException {
        int maxRows = SpreadsheetVersion.EXCEL97.getMaxRows();
        if (books.size() + 1 > maxRows) {
            throw new IllegalArgumentException("A .xls sheet holds at most " + maxRows
                    + " rows; export to .xlsx instead");
        }
        try (HSSFWorkbook workbook = new HSSFWorkbook()) {
            CreationHelper helper = workbook.getCreationHelper();

            // Overwrite the palette slot of "lavender" (a slot that may be replaced) with the corporate blue.
            HSSFPalette palette = workbook.getCustomPalette();
            palette.setColorAtIndex(HSSFColor.HSSFColorPredefined.LAVENDER.getIndex(),
                    (byte) 0x1F, (byte) 0x4E, (byte) 0x79);
            HSSFColor headerColor = palette.findColor((byte) 0x1F, (byte) 0x4E, (byte) 0x79);

            HSSFFont headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());

            HSSFCellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFont(headerFont);
            headerStyle.setFillForegroundColor(headerColor.getIndex());
            headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            CellStyle priceStyle = workbook.createCellStyle();
            priceStyle.setDataFormat(helper.createDataFormat().getFormat("#,##0.00"));
            CellStyle dateStyle = workbook.createCellStyle();
            dateStyle.setDataFormat(helper.createDataFormat().getFormat("yyyy-mm-dd"));

            HSSFSheet sheet = workbook.createSheet("Catalogue");
            Row header = sheet.createRow(0);
            String[] titles = {"ISBN", "Title", "Price", "Published"};
            for (int c = 0; c < titles.length; c++) {
                var cell = header.createCell(c);
                cell.setCellValue(titles[c]);
                cell.setCellStyle(headerStyle);
            }
            int r = 1;
            for (Book book : books) {
                Row row = sheet.createRow(r++);
                row.createCell(0).setCellValue(book.isbn());
                row.createCell(1).setCellValue(book.title());
                var price = row.createCell(2);
                price.setCellValue(book.price());
                price.setCellStyle(priceStyle);
                var published = row.createCell(3);
                published.setCellValue(book.published());
                published.setCellStyle(dateStyle);
            }
            sheet.createFreezePane(0, 1);
            for (int c = 0; c < titles.length; c++) {
                sheet.autoSizeColumn(c);
            }
            try (var out = Files.newOutputStream(target)) {
                workbook.write(out);
            }
        }
    }
}

Source: POI Quick Guide, New Workbook, Fills and colors and the HSSFPalette Javadoc.

The notes on this example, in the order they appear:

  • HSSFWorkbook is a Closeable; try-with-resources releases it. The no-argument constructor creates an empty workbook; new HSSFWorkbook(POIFSFileSystem) reads one.

  • SpreadsheetVersion.EXCEL97.getMaxRows() is 65,536 (rows 0 to 65,535), so the check is books.size() + 1, the header plus the data.

  • The font and style are created once and shared by every header cell. Creating a new style for each cell would use up the 4,000 styles quickly.

  • The date value is written with setCellValue(LocalDate); the number stored is an Excel serial date and only the yyyy-mm-dd format makes it look like a date, as explained in Dates, Numbers & DataFormatter.

  • autoSizeColumn is expensive because it measures every cell with the AWT font metrics; call it once per column after the data has been written, and see the performance notes for headless servers.

Colours: HSSFPalette and HSSFColor

A .xls file does not store RGB values in a cell format. The format stores an index into a colour table of 64 entries: indices 0 to 7 are reserved fixed colours (black, white, red, green, blue, yellow, magenta, cyan), and indices 8 to 63 are the palette of 56 colours that a workbook can redefine. The workbook ships with a default palette (the well-known Excel 97 colours); IndexedColors and HSSFColor.HSSFColorPredefined are enums over this default palette, and constants such as LIGHT_BLUE or GREY_25_PERCENT give the index.

HSSFPalette (obtained from HSSFWorkbook.getCustomPalette()) lets you read and change the table:

Method Effect

getColor(int index)

Returns the HSSFColor at a slot of the palette.

findColor(byte red, byte green, byte blue)

Finds a slot that already has exactly this colour, or null.

findSimilarColor(byte, byte, byte)

Returns the slot whose colour is closest to the requested one. Use it when you do not want to change the palette.

addColor(byte, byte, byte)

Takes the first slot that has not been customised and sets it; throws RuntimeException when all 56 are used.

setColorAtIndex(short, byte, byte, byte)

Replaces the colour of one slot. Every cell that uses the slot changes appearance, including cells you did not create.

The practical rules are:

  • Decide the brand colours once, per workbook, and write them to the palette before creating any style.

  • Colours are bytes: the byte literal (byte) 0xFF is -1 in Java. When you convert a hexadecimal colour such as 1F4E79, cast each channel with (byte).

  • A palette change is saved in the workbook (the PALETTE record), so it is also seen by the next person who opens the file. Replace slots that the report does not otherwise use.

  • There is no 24-bit colour in a .xls. If a colour must be exact (a logo colour, an accessibility contrast), .xlsx is the safer format; XSSF stores XSSFColor with an RGB triple, and the ss interfaces have no common way to set an arbitrary colour (see the next section).

Source: POI Quick Guide, Fills and colors and HSSFColor Javadoc.

Writing code that survives a migration

A codebase that already contains HSSFWorkbook rarely stays on .xls for ever. The cheapest way to keep a later move to XSSF cheap is to code against the interfaces of org.apache.poi.ss.usermodel, which POI’s own documentation recommends as the conversion path (the Converting HSSF usermodel code to SS usermodel page). The table shows the replacement for the commonly met HSSF types.

HSSF-specific code Interface-based replacement Notes

HSSFWorkbook, XSSFWorkbook as a field or parameter type

Workbook

Create with new HSSFWorkbook() or new XSSFWorkbook() in exactly one place (a factory).

HSSFSheet, HSSFRow, HSSFCell

Sheet, Row, Cell

Same methods; the CellType enum is shared.

HSSFCellStyle, HSSFFont

CellStyle, Font

workbook.createCellStyle() and workbook.createFont() return the right implementation.

HSSFDataFormat

DataFormat from workbook.getCreationHelper().createDataFormat()

The format string is the same; the built-in indexes are the same for the first 50.

HSSFColor.GREY_25_PERCENT.index

IndexedColors.GREY_25_PERCENT.getIndex()

IndexedColors is available in ss.usermodel and works in both formats. For an RGB colour you need the XSSF class.

HSSFPalette

none

Branch on the format (if (workbook instanceof XSSFWorkbook)) or keep colour code in a format-specific helper.

HSSFPatriarch (drawing)

Drawing<?> from sheet.createDrawingPatriarch()

Creating a patriarch for a sheet that already has shapes erases them; the same rule as in XSSF.

HSSFFormulaEvaluator

workbook.getCreationHelper().createFormulaEvaluator()

Returns a FormulaEvaluator that works for both formats.

HSSFWorkbook(InputStream), HSSFWorkbook(POIFSFileSystem)

WorkbookFactory.create(File) or WorkbookFactory.create(InputStream)

Detects .xls versus .xlsx from the content, not from the extension.

HSSFDateUtil

DateUtil

HSSFDateUtil was removed and does not exist in POI 5.x; use DateUtil in ss.usermodel.

A few differences remain even with the interfaces: the colour model (above), the limits, and the font-height methods, where Font.getFontHeightInPoints() is the portable way to ask. Run the tests of a report for .xls and .xlsx output with a parameterised JUnit test that builds a Workbook through a Supplier<Workbook>, and the differences surface immediately.

Reading old files

A server that accepts uploads should not decide on the extension, because users rename files. WorkbookFactory.create looks at the first bytes, which for an OLE2 file are the signature D0 CF 11 E0 A1 B1 1A E1, and returns an HSSFWorkbook; for a ZIP-based file it returns an XSSFWorkbook. The code that follows works on the Workbook interface alone and therefore reads both generations.

package bookshelf.legacy;

import java.io.File;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class CatalogueSniffer {

    public static void main(String[] args) throws IOException {
        File upload = new File("books-import.xls");
        // WorkbookFactory.create(file, password, readOnly): readOnly = true avoids a rewrite of the file
        try (Workbook workbook = WorkbookFactory.create(upload, null, true)) {
            boolean legacy = workbook instanceof HSSFWorkbook;
            System.out.println("Format: " + (legacy ? "xls (HSSF)" : "xlsx (XSSF)"));

            DataFormatter formatter = new DataFormatter();
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                StringBuilder line = new StringBuilder();
                for (var cell : row) {
                    line.append(formatter.formatCellValue(cell)).append('\t');
                }
                System.out.println(line);
            }
        }
    }
}

Source: WorkbookFactory Javadoc and POI Quick Guide, Reading and rewriting workbooks.

Three habits make the legacy path safe and cheap:

  • Open a file, not a stream, when you have a file. WorkbookFactory.create(File, String, boolean) with readOnly set to true reads from the file directly. With an InputStream POI has to buffer the whole content.

  • Detect, then validate. FileMagic.valueOf(InputStream) tells OLE2 from OOXML. The same API can reject a text file that was renamed books.xls before it reaches POI.

  • Remember that encrypted .xls files need the key set first. For an RC4-protected .xls, set Biff8EncryptionKey.setCurrentUserPassword(password) on the reading thread before opening the file and clear it afterwards in a finally block; the key is stored in a thread local and leaks to the next request in a servlet container that reuses threads. Passwords come from configuration, never from literals. See the encryption page of the section (Encryption, Signing & Protection) for the full pattern.

The usermodel stays in memory for the life of the object. HSSFWorkbook reads the whole Workbook stream and its records; a rule of thumb is an order of magnitude more heap than the file size, because every record becomes an object. For a .xls upload of several hundred megabytes, which exists in practice for exports from old ERP systems, use the event model instead of the usermodel. It is described in Streaming Reads: Event & SAX APIs.

Extracting text with ExcelExtractor

org.apache.poi.hssf.extractor.ExcelExtractor produces a plain text rendering of a .xls workbook: one block per sheet, one line per row, cells separated by tabs. It is useful for search indexing, for a quick preview of an upload and for diffs in tests. For .xlsx, the counterparts are XSSFExcelExtractor and XSSFEventBasedExcelExtractor; to treat all formats with the same code use ExtractorFactory or Apache Tika (see the text extraction page of the section).

package bookshelf.legacy;

import java.io.File;
import java.io.IOException;
import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class LegacyTextExtractor {

    public static void main(String[] args) throws IOException {
        try (POIFSFileSystem fs = new POIFSFileSystem(new File("books-inventory-legacy.xls"), true);
             ExcelExtractor extractor = new ExcelExtractor(fs)) {
            extractor.setIncludeSheetNames(true);
            extractor.setFormulasNotResults(false);
            extractor.setIncludeCellComments(true);
            extractor.setIncludeBlankCells(false);
            extractor.setIncludeHeadersFooters(false);
            System.out.println(extractor.getText());
        }
    }
}

With the catalogue written earlier the output is (tabs between the columns; decimal separator depends on the JVM locale):

Catalogue
ISBN	Title	Price	Published
978-0-13-468599-1	Effective Java	44.95	2018-01-06
978-1-61729-461-9	Spring in Action	49.99	2022-12-20

Source: ExcelExtractor Javadoc and POI text extraction.

Option Effect

setIncludeSheetNames(boolean)

Print the sheet name before its rows (default true).

setFormulasNotResults(boolean)

true prints formula text such as SUM(C2:C3); false (default) prints the cached value saved in the file.

setIncludeCellComments(boolean)

Append the comment text of cells (default false).

setIncludeBlankCells(boolean)

Emit an empty column for a blank cell so columns stay aligned (default false).

setIncludeHeadersFooters(boolean)

Include the print header and footer text (default true).

The extractor is read-only, builds an HSSFWorkbook internally, and therefore has the same memory cost as the usermodel. It reads the cached result of a formula; it does not recalculate.

Re-saving an old .xls as .xlsx

There are two ways to open the old workbook in the new format, and neither one is a one-line conversion in POI.

Rewrite the file with Excel or LibreOffice. For a one-off migration of an archive, the headless conversion of LibreOffice (soffice --headless --convert-to xlsx) preserves more of the original features (charts, conditional formats, pivot tables) than any POI code can, and the result is a normal .xlsx that POI reads. POI itself does not convert between formats and does not call out to an external office suite.

Copy the content with the shared API. When the workbook is simple (values, formulas, basic styles, widths, merged regions), a copy through the ss.usermodel interfaces works. The class below reads any workbook that WorkbookFactory can open and writes an XSSFWorkbook. It copies the style attributes the Bookshelf catalogue needs and caches the converted style by the index of the source style so that the 64,000-style limit of the target is not approached.

package bookshelf.legacy;

import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/** Copies values, formulas, basic styles, widths and merged regions from an .xls into a new .xlsx. */
public class XlsToXlsxConverter {

    public static void main(String[] args) throws IOException {
        convert(new File("books-inventory-legacy.xls"), Path.of("books-inventory.xlsx"));
    }

    static void convert(File source, Path target) throws IOException {
        try (Workbook in = WorkbookFactory.create(source, null, true);
             Workbook out = new XSSFWorkbook()) {
            Map<Short, CellStyle> styles = new HashMap<>();
            for (int s = 0; s < in.getNumberOfSheets(); s++) {
                Sheet from = in.getSheetAt(s);
                Sheet to = out.createSheet(from.getSheetName());
                for (Row fromRow : from) {
                    Row toRow = to.createRow(fromRow.getRowNum());
                    toRow.setHeight(fromRow.getHeight());
                    for (Cell fromCell : fromRow) {
                        Cell toCell = toRow.createCell(fromCell.getColumnIndex());
                        copyCell(fromCell, toCell, in, out, styles);
                    }
                }
                for (CellRangeAddress region : from.getMergedRegions()) {
                    to.addMergedRegion(region);
                }
                int lastColumn = from.getRow(from.getFirstRowNum()).getLastCellNum();
                for (int c = 0; c < lastColumn; c++) {
                    to.setColumnWidth(c, from.getColumnWidth(c));
                }
            }
            try (var stream = Files.newOutputStream(target)) {
                out.write(stream);
            }
        }
    }

    private static void copyCell(Cell from, Cell to, Workbook in, Workbook out, Map<Short, CellStyle> styles) {
        CellStyle fromStyle = from.getCellStyle();
        to.setCellStyle(styles.computeIfAbsent(fromStyle.getIndex(), k -> convertStyle(fromStyle, in, out)));
        switch (from.getCellType()) {
            case STRING -> to.setCellValue(from.getStringCellValue());
            case BOOLEAN -> to.setCellValue(from.getBooleanCellValue());
            case FORMULA -> to.setCellFormula(from.getCellFormula());
            case NUMERIC -> {
                if (DateUtil.isCellDateFormatted(from)) {
                    to.setCellValue(from.getDateCellValue());
                } else {
                    to.setCellValue(from.getNumericCellValue());
                }
            }
            case ERROR -> to.setCellErrorValue(from.getErrorCellValue());
            case BLANK, _NONE -> to.setBlank();
        }
    }

    private static CellStyle convertStyle(CellStyle from, Workbook in, Workbook out) {
        CellStyle to = out.createCellStyle();
        to.setDataFormat(out.getCreationHelper().createDataFormat().getFormat(from.getDataFormatString()));
        to.setAlignment(from.getAlignment());
        to.setVerticalAlignment(from.getVerticalAlignment());
        to.setWrapText(from.getWrapText());
        Font fromFont = in.getFontAt(from.getFontIndex());
        Font toFont = out.createFont();
        toFont.setFontName(fromFont.getFontName());
        toFont.setFontHeightInPoints(fromFont.getFontHeightInPoints());
        toFont.setBold(fromFont.getBold());
        toFont.setItalic(fromFont.getItalic());
        to.setFont(toFont);
        return to;
    }
}

Source: POI Quick Guide, Reading and rewriting workbooks, the CellStyle Javadoc and the Converting HSSF usermodel code to SS usermodel guide.

What the copy deliberately does and does not do:

Feature Copied by the example Comment

Values (text, numbers, booleans, errors)

yes

Dates are recognised with DateUtil.isCellDateFormatted and written as dates.

Formulas

yes, as text

The cached result is not copied; the target recalculates on open. Call FormulaEvaluator.evaluateAll() on the new workbook only if consumers read it without Excel.

Number formats, alignment, wrap, font name, size, bold, italic

yes

Extend convertStyle for borders, fill colours and font colour.

Fill and font colours

no

CellStyle.cloneStyleFrom throws between HSSFCellStyle and XSSFCellStyle; a colour copy needs the palette to be resolved to RGB and set with XSSFColor. Add it when the report needs it.

Column widths, merged regions, row heights

yes

Widths are in units of 1/256 of a character in both formats.

Images, charts, comments, data validation, conditional formats, defined names

no

Each has its own API. Use the headless conversion of an office suite when these matter.

Macros (VBA)

no

An .xlsx cannot hold macros; they would need .xlsm, which POI can read but not author.

The example creates the converted styles lazily and caches them in a map keyed by the source style index. It reads the source with readOnly = true so that the original file is never modified. Compiled against POI 5.5.1, it converts the catalogue of the previous section and produces a valid books-inventory.xlsx.

Memory and performance

Aspect HSSF Practical advice

Read

HSSFWorkbook loads and decodes every record; a 50 MB .xls typically needs several hundred megabytes of heap

Measure with your own files. For anything larger than a few tens of megabytes, use HSSFEventFactory.

Write

The whole model stays in memory until write

Keep exports within the 65,536-row limit anyway; for bigger ones use .xlsx with SXSSF.

Styles

Each createCellStyle adds an XF record, limited to 4,000

Cache the styles; do not create them inside the row loop.

Fonts

Each createFont adds a FONT record; the count is limited

Create one font per look and share it.

autoSizeColumn

Measures text with the AWT font metrics

On a headless server run with -Djava.awt.headless=true and make sure fonts exist in the container.

InputStream open

POI buffers the stream before parsing

Pass a File when one is available.

Pitfalls

  • Treating the extension as the format. An uploaded .xls file may be an .xlsx or an HTML table that Excel accepts. Detect by content with WorkbookFactory or FileMagic, and handle NotOLE2FileException and OLE2NotOfficeXmlFileException as an invalid upload (a 4xx, not a 500).

  • Writing HSSF-typed code in new modules. Prefer Workbook, Sheet, Row and Cell in method signatures. Only the code that creates the workbook should name the HSSF classes.

  • Exceeding the row limit unnoticed. The failure appears deep inside the export loop. A guard like WorkbookFormatChooser.fitsLegacyXls fails early and clearly instead.

  • Mixed date systems. A workbook can use the 1904 date system. Copy dates as Date or LocalDateTime values, as the converter does, and never copy raw serial numbers between workbooks.

  • Reading and writing the same stream. Reading with new HSSFWorkbook(fs) and then writing back to the same POIFSFileSystem file needs an explicit output; opening a file read-write and saving to it in place risks truncation if the write fails. Write to a temporary file and move it.

  • Leaving the encryption key on the thread. Clear Biff8EncryptionKey after reading or writing an encrypted file.

  • Expecting macros to survive. A rewrite does not carry VBA. If a .xls template with macros must be filled, test that the output keeps what you need before shipping, and consider keeping macros out of server-side templates.

What POI does not provide for .xls

  • No streaming writer: the whole workbook is held in memory (use .xlsx and SXSSF for large exports).

  • No conversion function between .xls and .xlsx; the copy above is hand-written and partial.

  • No arbitrary RGB colours: the format has 56 replaceable palette slots.

  • No full-fidelity round trip for features that HSSF does not model (some charts, macros, embedded objects); unknown records are generally preserved when the same HSSFWorkbook is read and written, but this is not a guarantee.

  • No rendering and no conversion to PDF or HTML, as for every spreadsheet component of POI.

  • No password cracking and no recovery of lost passwords.

Changes between POI 4.x, 5.x and 6.0

Version What changed for HSSF users

3.x to 4.0

The cell type constants became the CellType enum; Cell.getCellTypeEnum() was an intermediate step and was then removed. HSSFDateUtil was deprecated in favour of DateUtil (it is gone in 5.x). The project switched to semantic versioning.

5.0 to 5.5

Continuous hardening of the record parser (limits on record lengths through IOUtils.setByteArrayMaxOverride and related settings), logging moved to the log4j-api API only (5.1.0), improved handling of malformed files. The ss.usermodel interfaces gained methods, so code against the interfaces gets the benefits for both formats.

6.0 (planned, not released as of 2026-10-09)

The project announces JDK 17 as the minimum, removal of deprecated APIs, and stricter zip-bomb and path-traversal limits. Code that compiles without deprecation warnings on 5.5.1 and uses the ss.usermodel interfaces (DateUtil included, since HSSFDateUtil is already gone) is the best preparation. Re-check the change log when 6.0 ships.

References