Spreadsheet Usermodel Basics

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 in development and has not been released), Java 21 and later, Spring Boot 4.x on Spring Framework 7 and Jakarta EE 11. It is the first hands-on page about Excel: it explains the object model that every other spreadsheet page builds on, the four interfaces Workbook, Sheet, Row and Cell, how to create, open, read, write and close a workbook, and the small helper classes for cell addresses and ranges. The examples use the Bookshelf scenario described in the section landing page. The big picture (components, jars, the three API levels) is in Introduction & Architecture, the Maven and Gradle coordinates in Setup & Dependencies, and the two physical formats in File Formats: OOXML & OLE2.

The model: workbook, sheets, rows and cells

An Excel file is a workbook that contains an ordered list of sheets. A sheet is a sparse grid of rows, and a row is a sparse list of cells. POI mirrors this one to one in the package org.apache.poi.ss.usermodel ("ss" is for spreadsheet), which lives in the poi jar and contains interfaces only for the core types:

Interface Represents Main operations

Workbook

The whole file. Owns the sheets, the cell styles, the fonts, the data formats, the defined names and the shared string table.

createSheet, getSheet, getSheetAt, createCellStyle, createFont, createName, write, close

Sheet

One worksheet (a tab). Also holds sheet-wide settings such as column widths, merged regions, freeze panes and the auto-filter.

createRow, getRow, getLastRowNum, setColumnWidth, addMergedRegion, createFreezePane

Row

One row of a sheet, identified by a zero-based index.

createCell, getCell, getFirstCellNum, getLastCellNum, setHeight

Cell

One cell: a value (or a formula), a type, and a reference to a CellStyle.

setCellValue, getStringCellValue, getNumericCellValue, setCellFormula, getCellType, setCellStyle

CreationHelper

Factory for objects that belong to the workbook but are not created by it directly.

createRichTextString, createFormulaEvaluator, createDataFormat, createHyperlink, createClientAnchor

CellStyle, Font, DataFormat

Formatting objects, shared between cells. Covered in Cell Styles, Fonts & Formats.

setFillForegroundColor, setFont, setDataFormat

Every interface has one implementation per file format, and a third one for streaming writes. Code that is written against the interfaces works for all of them; code that needs a format-specific feature casts or uses the concrete class.

classDiagram class Workbook { <<interface>> createSheet(name) Sheet getSheet(name) Sheet createCellStyle() CellStyle createFont() Font getCreationHelper() CreationHelper write(OutputStream) close() } class Sheet { <<interface>> createRow(index) Row getRow(index) Row getLastRowNum() int autoSizeColumn(index) } class Row { <<interface>> createCell(index, type) Cell getCell(index, policy) Cell getFirstCellNum() short getLastCellNum() short } class Cell { <<interface>> getCellType() CellType setCellValue(value) getAddress() CellAddress setCellStyle(style) } class CellStyle { <<interface>> } class CreationHelper { <<interface>> createRichTextString(text) createFormulaEvaluator() } class HSSFWorkbook class XSSFWorkbook class SXSSFWorkbook Workbook <|.. HSSFWorkbook Workbook <|.. XSSFWorkbook Workbook <|.. SXSSFWorkbook Workbook "1" --> "*" Sheet : contains Workbook "1" --> "*" CellStyle : owns Workbook --> CreationHelper : creates Sheet "1" --> "*" Row : contains Row "1" --> "*" Cell : contains Cell "*" --> "0..1" CellStyle : formatted by

How the interfaces map to the files:

Interface XSSF (.xlsx, OOXML) HSSF (.xls, OLE2)

Workbook

The package part /xl/workbook.xml, plus /xl/styles.xml (styles), /xl/sharedStrings.xml (text) and the relationships between them

The Workbook OLE2 stream: a sequence of BIFF8 records (BOUNDSHEET, XF, FORMAT, SST, NAME …​)

Sheet

One part per sheet, /xl/worksheets/sheetN.xml

The records between a BOF and an EOF record inside the workbook stream

Row

A <row r="2"> element

A ROW record, followed by its cell records

Cell

A <c r="C2" t="s" s="4"> element: r is the address, t the type and s the index of the cell style

One record per cell: NUMBER, LABELSST, BOOLERR, FORMULA, BLANK …​

The model is sparse and zero-based. A row or a cell that was never written does not exist: Sheet.getRow(7) returns null when nothing was written on the eighth row, and Row.getCell(3) returns null for an absent cell. Row and column indices start at 0, so the cell that Excel shows as C2 is row 1, column 2.

Choosing the implementation

Which concrete class you get depends on the format and on how you obtain the workbook.

Class Jar Format Use it for

XSSFWorkbook

poi-ooxml

.xlsx, .xlsm

Creating and editing workbooks in memory. The default choice for new files.

SXSSFWorkbook

poi-ooxml

.xlsx (write only)

Very large exports with a constant memory footprint. See Streaming Writes with SXSSF.

HSSFWorkbook

poi

.xls

Legacy files only. See Legacy .xls with HSSF.

WorkbookFactory

poi (the .xlsx branch needs poi-ooxml at run time)

Either

Opening an existing file when you do not know (or do not care) which format it is.

WorkbookFactory is a static factory, not a workbook. It inspects the first bytes of the input (with FileMagic) and returns an XSSFWorkbook for an OOXML package or an HSSFWorkbook for an OLE2 file. It is declared in poi but the .xlsx branch needs poi-ooxml on the classpath at run time; without it, opening an .xlsx fails with a ClassNotFoundException-style error rather than a clear message, which is one of the problems the setup page addresses.

Creating a workbook directly

Workbook xlsx = new XSSFWorkbook();   // empty .xlsx workbook, no sheets yet
Workbook xls = new HSSFWorkbook();    // empty .xls workbook (legacy)

Source: POI Quick Guide, New Workbook

An empty workbook has no sheet; most operations fail until you call createSheet. Writing a workbook with no sheet produces a file that Excel refuses to open as valid, so always create at least one.

Opening an existing workbook

WorkbookFactory.create is overloaded; the useful forms are:

Signature Behaviour

create(File file)

Opens the file read-write; detects the format; no password.

create(File file, String password)

As above; the password decrypts an encrypted file. An encrypted file opened without a password (or with a wrong one) throws EncryptedDocumentException.

create(File file, String password, boolean readOnly)

readOnly = true opens the package read only. Use it whenever you do not intend to modify the original file.

create(InputStream in), create(InputStream in, String password)

Reads the whole stream. Needed for uploads that arrive as a stream, but costs more memory than a File.

create(boolean xssf)

Creates a new, empty workbook: an XSSFWorkbook when true, an HSSFWorkbook when false.

The difference between File and InputStream matters for .xlsx:

  • With a File, OpenXML4J opens the ZIP file as a random-access archive instead of buffering it. XSSFWorkbook still parses every sheet when it opens, so for large or many-sheet files use XSSFReader with SAX instead.

  • With an InputStream, the ZIP cannot be accessed randomly, so POI first copies the entire stream into memory (or, for very large inputs, into a temporary file, depending on the configuration) before it can read any part. The input stream is not the cheaper option even for a small file; it is simply the only one that exists for data that is not on disk.

  • With a read-write File, closing the workbook can write the changes back to the original file. If you only read, pass readOnly = true and the file can never be modified by accident. This is the rule for every import in this section.

In a web application, store the uploaded multipart file to a temporary file first and open it as a File, as shown in the import pages of this section. It is faster and uses less heap than keeping a copy of the upload in memory.

Creating and writing a workbook

The example creates the first sheet of books-inventory.xlsx from the Bookshelf scenario with text, numbers, a date, a boolean, a rich text value and a formula. It stays on the basics: styles, formats, validation and charts come in the following pages.

package bookshelf;

import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.time.LocalDate;
import java.util.List;

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CreationHelper;
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.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteInventory {

    record Book(String isbn, String title, int copies, double price, LocalDate acquired, boolean available) { }

    public static void main(String[] args) throws IOException {
        List<Book> books = List.of(
                new Book("978-0-13-468599-1", "Effective Java", 4, 45.90, LocalDate.of(2024, 3, 14), true),
                new Book("978-1-61729-994-3", "Spring in Action", 2, 49.99, LocalDate.of(2025, 1, 9), false));

        Path target = Path.of("books-inventory.xlsx");
        try (Workbook workbook = new XSSFWorkbook();
             OutputStream out = Files.newOutputStream(target)) {
            // Sheet names may not contain : \ / ? * [ ] and are limited to 31 characters.
            String sheetName = WorkbookUtil.createSafeSheetName("Books [2026/Q4]: inventory");
            Sheet sheet = workbook.createSheet(sheetName);
            CreationHelper helper = workbook.getCreationHelper();

            Row header = sheet.createRow(0);
            String[] titles = {"ISBN", "Title", "Copies", "Price", "Acquired", "Available"};
            for (int c = 0; c < titles.length; c++) {
                header.createCell(c, CellType.STRING).setCellValue(titles[c]);
            }

            int rowIndex = 1;
            for (Book book : books) {
                Row row = sheet.createRow(rowIndex++);
                row.createCell(0).setCellValue(book.isbn());
                row.createCell(1).setCellValue(helper.createRichTextString(book.title()));
                row.createCell(2).setCellValue(book.copies());
                row.createCell(3).setCellValue(book.price());
                row.createCell(4).setCellValue(book.acquired());   // stored as a number, needs a date style to display
                row.createCell(5).setCellValue(book.available());
            }

            Row total = sheet.createRow(rowIndex);
            total.createCell(0).setCellValue("Total copies");
            total.createCell(2).setCellFormula("SUM(C2:C" + rowIndex + ")");

            workbook.write(out);
        }
        System.out.println("Written " + target.toAbsolutePath());
    }
}

Source: POI Quick Guide, New Workbook and POI Quick Guide, Creating Cells. The example was compiled and run against POI 5.5.1 with Java 21.

What the example shows, step by step:

  1. try (Workbook workbook = ...) closes the workbook at the end. Workbook extends Closeable; closing releases the underlying package, the temporary files POI may have created and the file handle of a workbook opened from a file. Wrap the OutputStream in the same try so that it is closed whatever happens.

  2. WorkbookUtil.createSafeSheetName turns any string into a legal sheet name (see the next section).

  3. createRow(0) and createCell(c, CellType.STRING) create the row and the cell. createCell(c) without a type creates a blank cell whose type is set by the first setCellValue.

  4. The overloads of setCellValue pick the cell type: String and RichTextString give a string cell, double (and the int that widens to it) a numeric cell, boolean a boolean cell, and Date, Calendar, LocalDate and LocalDateTime a numeric cell that holds the Excel serial date.

  5. setCellFormula takes the formula text without the leading =. POI stores the text; it does not calculate a result. See Formula cells below.

  6. workbook.write(out) serialises the whole workbook. Do not depend on write closing or not closing the stream.

Sheet names

A sheet name is validated when the sheet is created. createSheet throws IllegalArgumentException for an invalid name and for a duplicate: the comparison is case-insensitive, so creating "a" after "A" fails with The workbook already contains a sheet named 'a'.

Rule Detail

Length

1 to 31 characters

Forbidden characters

:, \, /, ?, *, [ and ]

Apostrophe

A name may not start or end with a single quote

Reserved name

Excel reserves History; POI’s validation does not reject it, but Excel may refuse the file, so avoid it

Uniqueness

Case-insensitive within the workbook

WorkbookUtil.createSafeSheetName(String) replaces every forbidden character with a blank and truncates the result to 31 characters, so "Books [2026/Q4]: inventory and a very long tail" becomes "Books 2026 Q4 inventory and ". It does not guarantee uniqueness, and it does not trim: when sheet names come from user data (a library name, a report parameter), call it, then make the result unique yourself, for example by appending a counter inside the 31 characters. WorkbookUtil.validateSheetName(String) throws instead of repairing, which is useful for input validation. Never put user-controlled text into a sheet name unchecked: it is also used in formulas and in the names of package parts.

Formula cells

setCellFormula stores the formula only. A workbook created by POI has no cached result in the formula cells, which has two consequences:

  • Excel, LibreOffice and other spreadsheet programs recalculate on open, so people see the values. To force this explicitly, call workbook.setForceFormulaRecalculation(true) (or sheet.setForceFormulaRecalculation(true) for one sheet).

  • A program that reads the file without calculating, for example a script, a preview pane or POI itself, sees an empty cached value. In the reading example below, the total cell is a formula cell whose cached numeric result is 0.0. If you need the value on the server, evaluate the formulas with the FormulaEvaluator (see Formulas & Evaluation).

Opening, reading and closing

The next example opens the file written above, reads it with getRow and getCell, and uses a MissingCellPolicy to decide what a hole in the data means.

package bookshelf;

import java.io.File;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class ReadInventory {

    public static void main(String[] args) throws IOException {
        File file = new File("books-inventory.xlsx");
        // password null: unencrypted; readOnly true: the file is never written back.
        try (Workbook workbook = WorkbookFactory.create(file, null, true)) {
            System.out.println("Format: " + workbook.getClass().getSimpleName()
                    + ", sheets: " + workbook.getNumberOfSheets());
            Sheet sheet = workbook.getSheetAt(0);
            System.out.println("Rows " + sheet.getFirstRowNum() + ".." + sheet.getLastRowNum());

            for (int r = sheet.getFirstRowNum() + 1; r <= sheet.getLastRowNum(); r++) {
                Row row = sheet.getRow(r);                 // null if the row was never written
                if (row == null) {
                    continue;
                }
                // CREATE_NULL_AS_BLANK never returns null (but it adds a blank cell to the sheet!)
                // RETURN_BLANK_AS_NULL treats blank cells and missing cells the same way.
                Cell isbn = row.getCell(0, MissingCellPolicy.RETURN_BLANK_AS_NULL);
                Cell copies = row.getCell(2, MissingCellPolicy.RETURN_BLANK_AS_NULL);
                Cell acquired = row.getCell(4, MissingCellPolicy.RETURN_BLANK_AS_NULL);
                System.out.printf("row %d: isbn=%s copies=%s acquired=%s%n", r + 1,
                        isbn == null ? "-" : isbn.getStringCellValue(),
                        copies == null ? "-" : describe(copies),
                        acquired == null ? "-" : describe(acquired));
            }
        }
    }

    static String describe(Cell cell) {
        // For a formula cell, look at the type of the cached result.
        CellType type = cell.getCellType() == CellType.FORMULA ? cell.getCachedFormulaResultType() : cell.getCellType();
        return switch (type) {
            case STRING -> cell.getStringCellValue();
            case NUMERIC -> DateUtil.isCellDateFormatted(cell)
                    ? cell.getLocalDateTimeCellValue().toLocalDate().toString()
                    : Double.toString(cell.getNumericCellValue());
            case BOOLEAN -> Boolean.toString(cell.getBooleanCellValue());
            case ERROR -> "#ERR" + cell.getErrorCellValue();
            case BLANK, _NONE, FORMULA -> "";
        };
    }
}

Output for the file written above:

Format: XSSFWorkbook, sheets: 1
Rows 0..3
row 2: isbn=978-0-13-468599-1 copies=4.0 acquired=45365.0
row 3: isbn=978-1-61729-994-3 copies=2.0 acquired=45666.0
row 4: isbn=Total copies copies=0.0 acquired=-

Source: POI Quick Guide, Cell contents and the Javadoc of WorkbookFactory and Row.MissingCellPolicy.

Two things in the output are deliberate lessons:

  • acquired=45365.0: the date was written as a plain number because the cell has no date style, so DateUtil.isCellDateFormatted is false. Excel shows 45365 too. A date is a number plus a date format; the format is part of the cell style, which is the subject of Dates, Numbers & DataFormatter.

  • copies=0.0 on the totals row: the cell is a formula that POI never evaluated, so the cached result is the default 0.

Cell types

Cell.getCellType() returns the CellType enum. The old integer constants (HSSFCell.CELL_TYPE_STRING …​) were deprecated in 3.15 and removed in POI 4.0; the transitional getCellTypeEnum() was deprecated in 4.0 and removed in 5.0. Do not use code that still has them.

CellType Meaning Getter that is valid

NUMERIC

A number. Also every date and time, which Excel stores as a number of days since 1900.

getNumericCellValue, getDateCellValue, getLocalDateTimeCellValue

STRING

Text, up to 32,767 characters.

getStringCellValue, getRichStringCellValue

BOOLEAN

true or false.

getBooleanCellValue

FORMULA

A formula. The result type is a second, separate type.

getCellFormula; for the result getCachedFormulaResultType() then the getter of that type

BLANK

An existing cell with no value (it may have a style).

none

ERROR

An error value such as #DIV/0! or #N/A.

getErrorCellValue (a byte code, see FormulaError)

_NONE

Internal marker for "no type"; it never occurs in a cell read from a file. The leading underscore is intentional.

none

Calling the wrong getter is a classic server bug. getStringCellValue() on a numeric cell throws IllegalStateException: Cannot get a STRING value from a NUMERIC cell, and getNumericCellValue() on a text cell throws too. The only forgiving combination is a blank cell, which returns an empty string. User-supplied workbooks mix types in a column constantly (an ISBN typed as a number, a price typed as text), so an import must switch on the cell type, or use DataFormatter to read everything as the text the user sees. Both strategies are in Dates, Numbers & DataFormatter.

How to change a cell’s type:

  • Call setCellValue with a value of the new kind. The type follows the value.

  • Call setBlank() to empty a cell. This is the replacement for the deprecated setCellType(CellType.BLANK).

  • Cell.setCellType(CellType) is deprecated since POI 4.x (it converted the content in surprising ways); it is expected to disappear in the 6.0 clean-up. Choose the type when you create the cell (createCell(index, type)) and let setCellValue do the rest.

  • getCellType() of a formula cell is FORMULA, not the type of its result. Use getCachedFormulaResultType(), as describe does above.

Missing cells and MissingCellPolicy

There are three states for a position in a row: the cell does not exist, the cell exists and is BLANK, or it has a value. Spreadsheets produced by people have all three in the same column. The MissingCellPolicy argument of Row.getCell(int, MissingCellPolicy) tells POI how to report the first two:

Policy Cell does not exist Cell is BLANK Notes

RETURN_NULL_AND_BLANK (the default of getCell(int))

null

The blank cell

Keeps the distinction. A blank cell may still carry a style.

RETURN_BLANK_AS_NULL

null

null

The simplest for imports: "no value" is always null.

CREATE_NULL_AS_BLANK

A new blank cell is added to the row and returned

The blank cell

Never returns null, but it modifies the row. The workbook gets bigger and, when you write it, it contains the empty cells.

In an import, use RETURN_BLANK_AS_NULL and map null to "no value". Use CREATE_NULL_AS_BLANK only when you are about to write into the cell anyway (for example, to apply a style to a column of a template).

Rows and the length of a sheet

Method Behaviour and trap

Sheet.getFirstRowNum(), Sheet.getLastRowNum()

Zero-based indices of the first and last row that exists. Both return -1 when the sheet has no rows, so an empty sheet and a sheet with only the first row (0) are told apart directly.

Sheet.getPhysicalNumberOfRows()

The number of rows that exist (not the index of the last one). A sheet with rows 0 and 1000 has 2.

Row.getFirstCellNum()

Zero-based index of the first cell, or -1 for a row without cells.

Row.getLastCellNum()

The index of the last cell plus one (so it equals the "width" of the row), or -1 for an empty row. Use < with it, not <=. This is the opposite convention to getLastRowNum().

Row.getPhysicalNumberOfCells()

The number of cells that exist.

Sheet.createRow(int)

If a row already exists at that index, it is replaced by a new empty row, and its cells are lost. Fetch it first with getRow and create only when it is null.

Row.createCell(int)

The same for a cell: an existing cell at that index is replaced.

The "create only if absent" idiom, repeated everywhere in report code:

Row row = Objects.requireNonNullElseGet(sheet.getRow(index), () -> sheet.createRow(index));
Cell cell = Objects.requireNonNullElseGet(row.getCell(column), () -> row.createCell(column));

Source: Sheet Javadoc and Row Javadoc.

Iterating and addressing

There are two ways to walk a sheet. Sheet implements Iterable<Row> and Row implements Iterable<Cell>: the iteration visits only rows and cells that exist, in ascending order, and silently skips the gaps. The index loop of the reading example visits every position, so gaps surface as null. Choose by what a gap means:

Style Good for Watch out

Iterators (for (Row row : sheet))

Dumping all content, searching, transforming whatever is there

The cell’s column is lost unless you read cell.getColumnIndex(); a skipped empty cell shifts positional logic by one

Index loops with getRow / getCell

Tabular data with known columns (the Bookshelf import: ISBN is always column 0)

You must handle null rows and cells

The second example iterates over the whole workbook and shows the three address helpers of org.apache.poi.ss.util.

package bookshelf;

import java.io.File;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
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.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;

public class Iterate {

    public static void main(String[] args) throws IOException {
        try (Workbook workbook = WorkbookFactory.create(new File("books-inventory.xlsx"))) {
            Sheet sheet = workbook.getSheetAt(0);

            // Iteration visits only rows and cells that exist, in ascending order; gaps are skipped.
            for (Row row : sheet) {
                for (Cell cell : row) {
                    CellAddress address = cell.getAddress();       // e.g. C2
                    System.out.println(address.formatAsString() + " -> " + cell.getCellType());
                }
            }

            CellReference ref = new CellReference("Sheet1!$D$3");
            System.out.println(ref.getRow() + "," + ref.getCol() + " absolute row=" + ref.isRowAbsolute());
            System.out.println(CellReference.convertNumToColString(27));                 // AB
            System.out.println(CellReference.convertColStringToIndex("AB"));             // 27

            CellRangeAddress range = CellRangeAddress.valueOf("A1:F3");
            System.out.println(range.getNumberOfCells() + " cells, " + range.formatAsString());
            System.out.println(range.isInRange(2, 5));                                   // row 2, col 5
        }
    }
}

Output (shortened):

A1 -> STRING
B1 -> STRING
...
C4 -> FORMULA
2,3 absolute row=true
AB
27
18 cells, A1:F3
true

Source: POI Quick Guide, Iterate over rows and cells and the Javadoc of CellReference, CellAddress and CellRangeAddress.

The three address classes

Class What it models Typical use

CellAddress

A row and a column. Parses and formats "C2". Immutable-style value type, Comparable, usable as a map key. Cell.getAddress() returns one.

Error reports ("row 17, cell D17"), annotating the exact cell, comments and hyperlinks

CellReference

A cell reference as it appears in a formula: optional sheet name, absolute markers ($D$3), plus conversion helpers between column letters and indices.

Building and parsing formula text, converting 27 to AB

CellRangeAddress

A rectangular range A1:F3: first and last row, first and last column.

Merged regions, auto-filters, data validation ranges, print areas, named ranges

Remember that all of them are zero-based internally (CellAddress("C2") is row 1, column 2) while the text form is one-based, as in Excel. When you build an error message for a user, add 1 to the row index, or use formatAsString(), which already does.

Limits of the format

The limits belong to the file format, not to POI, but POI enforces them and throws when you exceed them. They are exposed by the SpreadsheetVersion enum, so code can adapt to the format of the workbook:

Limit (SpreadsheetVersion getter) EXCEL97 (.xls) EXCEL2007 (.xlsx)

Rows (getMaxRows)

65,536

1,048,576

Columns (getMaxColumns)

256

16,384

Characters in one cell (getMaxTextLength)

32,767

32,767

Cell styles in the workbook (getMaxCellStyles)

4,000

64,000

Sheet name length

31

31

How to apply this on the server:

SpreadsheetVersion version = workbook instanceof HSSFWorkbook
        ? SpreadsheetVersion.EXCEL97
        : SpreadsheetVersion.EXCEL2007;
if (rowIndex >= version.getMaxRows()) {
    throw new IllegalStateException("The export has more rows than this format supports");
}

Source: SpreadsheetVersion Javadoc and POI Spreadsheet Limitations.

A few more practical limits of the usermodel, from the POI documentation and from experience:

  • Text longer than 32,767 characters is rejected (IllegalArgumentException from setCellValue). Truncate or split long descriptions before writing them.

  • The number of cell styles is capped (see the table). Creating one style per cell exhausts it quickly; create styles once and reuse them, as explained in Cell Styles, Fonts & Formats.

  • Numbers are stored as IEEE 754 doubles, like in Excel. An integer above 253 (a 19-digit identifier) or a BigDecimal with many digits loses precision. Write identifiers such as ISBNs and phone numbers as strings, which also keeps their leading zeros.

  • Excel displays at most 15 significant digits of a number whatever the stored value is.

What it costs: memory and performance

The usermodel is convenient and expensive. Everything is kept in memory as a tree of objects:

Aspect Behaviour

Heap

For .xlsx, XSSF keeps the XMLBeans representation of every sheet that you touch, plus the shared string table and the styles. As a rule of thumb the heap needed is a multiple of the size of the uncompressed XML, which is itself many times larger than the zipped file. The POI documentation gives no fixed factor because it depends on the content; measure with your own data (see the performance page of this section).

Reading

WorkbookFactory.create(File, null, true) avoids buffering the ZIP in memory, but every sheet is still parsed when the workbook opens. For large or many-sheet files use XSSFReader and SAX (the streaming reads page).

Writing

write serialises everything again. There is no incremental output: that is what SXSSF is for.

Shared strings

Text cells are stored once in the workbook’s string table. Writing thousands of identical strings (status names, ISBN prefixes) is cheap; thousands of unique long strings are not.

autoSizeColumn

Needs font metrics (AWT) and measures every cell of the column: slow for big sheets, and it can fail on a headless server without fonts installed. Call it once per column, after the data is written, or set fixed widths with setColumnWidth (units of 1/256 of a character).

Rules of thumb for the usermodel: it is a good choice up to some tens of thousands of rows, it is the only API that supports random access, styles, formulas, charts and templates together, and it is a poor choice for a million-row export or for reading a huge upload. For those, the two streaming pages replace it: Streaming Writes with SXSSF and Streaming Reads: Event & SAX APIs.

Pitfalls and security

Pitfall What to do

One workbook, many threads

Workbook and its sheets, rows and cells are not thread safe. Use one workbook per thread or per request. A CellStyle created for one workbook cannot be applied in another.

Sharing styles across workbooks

A CellStyle or Font belongs to the workbook that created it. Applying it to a cell of another workbook fails with an IllegalArgumentException.

Not closing the workbook

Leaks a file handle and, on SXSSF, temporary files. Always use try-with-resources.

Opening read-write by accident

WorkbookFactory.create(file) opens read-write; use the three-argument form with readOnly = true for imports.

NullPointerException on getCell(...)

The cell is absent. Use a MissingCellPolicy and check for null.

IllegalStateException on a getter

The getter does not match the cell type. Switch on getCellType().

Date shown as a number

The cell has no date format. Set a CellStyle with a data format (next pages).

Formulas without values

POI does not calculate on write. Use setForceFormulaRecalculation, or evaluate.

Leading zeros lost

The value was numeric. Write identifiers as strings.

Untrusted workbook

A user-supplied .xlsx is a ZIP of XML and can be a zip bomb or an XML attack. Limits such as ZipSecureFile.setMinInflateRatio apply, and the section has a page about validating uploads before parsing them. In the usermodel basics, always open uploads read only, from a temporary file, with a size limit enforced before POI is called.

Formula injection in exports

A text value that starts with =, +, - or @ is dangerous in a spreadsheet opened by a person. Always write user data with setCellValue(String) (a text cell), never with setCellFormula. The security page of this section covers the rest.

What changed across versions

Version Change relevant to the usermodel basics

3.x

Integer cell types (CELL_TYPE_STRING …​), XSSFWorkbook(InputStream) as the standard way to open a file, and getCellTypeEnum() as a bridge towards the enum.

4.0

The integer constants (deprecated in 3.15) were removed in favour of CellType; getCellType() returns the enum and getCellTypeEnum() is deprecated (removed in 5.0). Java 8 became the minimum. POI follows semantic versioning from this release: an API is deprecated in one major release and removed in the next.

4.x and 5.x

LocalDate and LocalDateTime overloads of setCellValue and getLocalDateTimeCellValue are available since 4.1; Cell.setBlank() replaces setCellType(BLANK); setCellType(CellType) is deprecated; the jars are re-split into poi-ooxml-lite and poi-ooxml-full (5.0). Logging is log4j-api only (5.1.0).

5.5.x

The latest line on 2026-10-09 (5.5.1): needs Java 8 or later and receives critical and security fixes. .xlsb (binary OOXML) files can be read only through the event-style XSSFBReader (since 3.16), not as a usermodel workbook; 5.5.0 added a new XLSB reading API on top of it.

6.0 (planned, not released)

The POI site announces JDK 17 as the minimum, the removal of APIs deprecated in the 5.x line (so setCellType(CellType) and similar should be removed), and stricter zip-bomb and path-traversal limits. Code that compiles without deprecation warnings on 5.5.1 is the best preparation. Re-check the change log when 6.0 ships.

What POI does not provide here

  • No calculation of formula results when writing. The evaluator exists, but it is a separate step, and it supports most but not all Excel functions.

  • No automatic type conversion. POI never turns the text "12" into a number or a date for you.

  • No object mapping. There is no @Column annotation or bean binding in POI. Mapping a row to a Java record, as the Bookshelf import does, is code you write (or a library such as the community Spring Batch Excel reader, covered later in the section).

  • No rendering. Column widths, row heights and styles are stored, but POI never lays out the sheet or converts it to PDF. Converting a workbook to PDF needs another tool.

  • No Excel user interface. VBA macros are neither run nor created, and there are no macro-enabled outputs from the usermodel.

References