Performance, Memory & Threading

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; 6.0 is in development and has not been released, so every 6.0 remark is an expectation), Java 21 and later, Spring Boot 4.x on Spring Framework 7 and Jakarta EE 11. The Java classes of this page were compiled against POI 5.5.1 and the timings and allocation figures marked as observed were measured on one developer laptop with JDK 26; they show the shape of each effect, not a benchmark, and the hardware is deliberately not described because the numbers must not be reused as general facts. Measure on your own server with your own files.

Apache POI is a library that favours correctness and completeness over speed, and its default memory use is high: the Spreadsheet How-To lists high memory use for large sheets among the things still to improve. A server that exports reports on request or accepts uploads must therefore make a handful of decisions before the first release: which API to use for which size, how to open a file, how many documents may be open at the same time, and what to clean when a worker thread goes back to a pool. This page collects them in the order in which they usually matter. It does not repeat what other pages explain in depth; it links to them: Streaming Writes with SXSSF, Streaming Reads: Event & SAX APIs, Cell Styles, Fonts & Formats, Formulas & Evaluation and Security & Untrusted Files. The scenario is the Bookshelf of the section landing page: books-inventory.xlsx (small, rich), loans-report.xlsx (large, plain) and books-import.xlsx (uploaded by users).

Choosing an API by file size

POI offers three levels of Excel API, introduced in Introduction & Architecture. They differ in how much of the file is in memory at once, and that is the whole performance story:

API Memory profile What you give up Typical Bookshelf use

Usermodel (XSSFWorkbook, HSSFWorkbook)

The whole document is a tree of objects. Memory grows with the number of cells; the object tree is much larger than the compressed file.

Nothing. Random access to every cell, formulas, charts, pivot tables, auto-size, templates.

books-inventory.xlsx; templates; any file you must modify in place.

SXSSF (SXSSFWorkbook, write only)

A sliding window of rows (100 by default) plus the parts that stay in memory (styles, merged regions, shared strings if enabled). The rest goes to a temporary file.

Reading back or editing a flushed row; some features and auto-size need extra care.

loans-report.xlsx: an export of every loan.

SAX / event API (XSSFReader, XSSFSheetXMLHandler; read only)

One row or even one cell at a time, plus the shared strings table. Memory is nearly independent of the number of rows.

No object model: you receive callbacks and keep only what you need. No random access, no writing.

books-import.xlsx when users may upload very large catalogues.

The official FAQ puts the gain of the event model on the read side at more than a factor of ten in memory compared with the usermodel. POI publishes no memory-per-cell figure, because the cost depends on the content (long distinct strings cost far more than repeated numbers), so the decision should come from an experiment with a fixed heap, not from a rule of thumb. The experiment is described in the section on measuring below.

The decision as a flow:

flowchart TD A["Need to read or to write?"] -->|write| B["Small enough for the heap (measured with your own data)?"] A -->|read| C["Upload larger than the budget or of unknown size?"] B -->|yes| D["XSSFWorkbook full model"] B -->|no| E["Needs random access, charts, pivot tables or a template?"] E -->|yes| F["Split the export, or run it asynchronously with a bigger heap"] E -->|no| G["SXSSFWorkbook window of rows"] C -->|yes| H["SAX event API with XSSFReader and SheetContentsHandler"] C -->|no| I["Need to modify the file?"] I -->|yes| D I -->|no| J["Open from a File with the usermodel, or use the SAX API"]

The same choice as a table of triggers:

Situation Choose Why

Template filled with a few hundred rows, styles, formulas, chart

Usermodel

The model is needed and the memory is small. Streaming would remove features you need.

Export of an unbounded query result (loans-report.xlsx)

SXSSF

Memory is bounded by the window, not by the result size. Disk is bounded by the temporary file.

Export that also needs a pivot table, chart or merged cell per row

Usermodel on a limited export, or SXSSF for the data sheet and a separate small file for the rest

SXSSF keeps merged regions, hyperlinks and comments in memory until write, and flushed rows cannot be read back.

Upload read once from top to bottom (books-import.xlsx)

SAX event API, or usermodel from a File while the size is capped

You rarely need the tree; a capped usermodel read is simpler to write and test.

Edit a large existing workbook

Usermodel with enough heap

Streaming cannot update flushed rows. See Templates & Modifying Workbooks and the section "SXSSF and templates" of Streaming Writes with SXSSF.

Legacy .xls

HSSFWorkbook, or the HSSF event model

The format caps a sheet at 65,536 rows, so the file size is bounded anyway.

A useful pattern is to keep the decision in one place and drive it from a measured budget. The class below decides on the number of cells of an export; the limit is a constructor argument that you set from your own measurements (it is not a POI constant), and it refuses to stream an export that needs random access.

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
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.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/** Picks the workbook implementation for an export from a cell budget measured on the real server. */
public class ExportApiChooser {

    /** Largest export (rows x columns) that the usermodel API may build in memory; set it from your own measurements. */
    private final long maxUsermodelCells;

    public ExportApiChooser(long maxUsermodelCells) {
        this.maxUsermodelCells = maxUsermodelCells;
    }

    public Workbook create(long rows, int columns, boolean needsRandomAccess) {
        if (needsRandomAccess && rows * columns > maxUsermodelCells) {
            throw new IllegalArgumentException("Too large for random access: split the export or relax the layout");
        }
        return rows * columns <= maxUsermodelCells || needsRandomAccess
                ? new XSSFWorkbook()          // full model: formulas, auto-size, pivot tables, charts
                : new SXSSFWorkbook(100);     // sliding window of 100 rows
    }

    public void export(List<String[]> data, OutputStream out) throws IOException {
        int columns = data.isEmpty() ? 0 : data.get(0).length;
        try (Workbook workbook = create(data.size(), columns, false)) {
            Sheet sheet = workbook.createSheet("Loans");
            int r = 0;
            for (String[] line : data) {
                Row row = sheet.createRow(r++);
                for (int c = 0; c < line.length; c++) {
                    row.createCell(c).setCellValue(line[c]);
                }
            }
            workbook.write(out);
        }
    }
}

Source: Spreadsheet How-To, SXSSF (Streaming Usermodel API) and Spreadsheet How-To, event API.

File versus InputStream

The most effective one-line change for reading is to give POI a File instead of an InputStream. The Javadoc of XSSFWorkbook is explicit: the InputStream constructor buffers the whole stream into memory before opening the package, needs more memory than a File, and opening from a file has the lower memory footprint. The Javadoc of POIFSFileSystem says the same for .xls and .doc files. With a file POI reads zip entries on demand from disk; with a stream it must have the whole archive in hand first.

You have Do Notes

A File or Path (local file, temp file)

new XSSFWorkbook(file) or WorkbookFactory.create(file, null, true)

close() closes the underlying package and the file handle. The last argument true opens it read-only.

A MultipartFile or request InputStream

Copy it to a private temporary file, then open the file

One extra disk write, one copy of the bytes less in the heap. Delete the file in a finally.

A stream that cannot be copied (for example a decrypted stream)

new XSSFWorkbook(stream), after raising the temp-file threshold (below)

The stream constructors close the stream by default; the overload with a closeStream flag exists since POI 5.2.5.

When a stream cannot be avoided, ZipInputStreamZipEntrySource.setThresholdBytesForTempFiles(int) (since POI 5.1.0) tells POI to keep decompressed zip entries larger than the threshold in temporary files instead of the heap. The default is -1, meaning no temp files, and then an entry above 2 GB after decompression fails. The Javadoc recommends something like 50,000,000 bytes. setEncryptTempFiles(true) encrypts those temporary files. Both are static, so set them once at start-up; the second example of the temp-file section below shows the calls.

The helper below is the pattern for the Bookshelf import: the upload goes to a directory you control, POI reads it from there, and the file is removed whatever happens. The size cap and the other upload checks belong before this step; they are in Security & Untrusted Files.

import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.StandardCopyOption;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class UploadedWorkbook {

    /** Copies the upload to a private directory first, then lets POI read the package from the file. */
    public static int countRows(InputStream upload, Path uploadDir) throws IOException {
        Path file = Files.createTempFile(uploadDir, "books-import-", ".xlsx");
        try {
            Files.copy(upload, file, StandardCopyOption.REPLACE_EXISTING);
            // readOnly = true: the package is opened read-only and nothing can be saved back to the same file
            try (Workbook workbook = WorkbookFactory.create(file.toFile(), null, true)) {
                Sheet sheet = workbook.getSheetAt(0);
                return sheet.getPhysicalNumberOfRows();
            }
        } finally {
            Files.deleteIfExists(file);
        }
    }
}

A measurement of the effect (observed, one run on one machine): a books-import.xlsx of 100,000 rows by three columns (a 1.97 MB file) was opened and its last row number read with each constructor, and the allocation of the thread was read with com.sun.management.ThreadMXBean.getThreadAllocatedBytes. The File constructor allocated about 450 MB and the InputStream constructor about 515 MB, in two consecutive rounds each. The difference is the buffered copy of the archive; the bulk of both numbers is the object model, which is why a large upload belongs to the SAX API and not to a better constructor. Allocation is not retention, so read it as "relative work", not as heap in use.

Source: XSSFWorkbook Javadoc, POIFSFileSystem Javadoc and ZipInputStreamZipEntrySource Javadoc

The logging slowdown

The POI FAQ answers the question "why is reading so slow" with logging: verbose logging, it says, "will reduce performance by a factor of at least 100", and the logging page of the project says that levels noisier than WARN are only for debugging and must never be enabled on a production system. Since POI 5.1.0 POI logs through the Log4j 2 API only (see Setup & Dependencies for the bridge), so the cost depends on what is behind the API: a Logback or Log4j configuration that writes DEBUG records to the console in a container is expensive, and with no implementation on the classpath Log4j falls back to a minimal console logger.

The rule is simple and cheap to enforce: pin the POI loggers at WARN in production, whatever the root level is. With Spring Boot, which routes the Log4j API to Logback through log4j-to-slf4j:

logging.level.org.apache.poi=WARN
logging.level.org.apache.xmlbeans=WARN

With a native Log4j 2 configuration the equivalent is a Logger element for org.apache.poi with level="WARN", as in the official logging page.

An honest note on the size of the effect. The FAQ number is the project’s statement, and it was written for the old logger. A probe on the same machine as the rest of this page, with POI 5.5.1, Log4j Core 2.25.2 and a TRACE level on org.apache.poi, did not reproduce a slowdown of that size: opening a 20,000-row workbook and evaluating 20,000 formulas took about 250 ms to 300 ms with a WARN configuration (file appender), a TRACE configuration with a file appender (11.8 MB of log written, mostly by the write phase of the setup) and a TRACE configuration with a console appender. The probe logs mostly while writing and little while reading, so it proves one thing: the penalty depends on the amount of logging that your workload triggers and on the appender (synchronous console output in a slow container log driver is the worst case). Treat the factor as an upper-end warning, keep the level at WARN, and look at the log volume (grep -c on a sample) before concluding anything about your own service.

Source: Apache POI FAQ, performance and logging and Apache POI, logging framework

Style and font reuse

Every CellStyle and Font created through the workbook is stored in the workbook and written to styles.xml. A .xlsx can hold at most 64,000 cell styles, and each new one costs memory and time. The classic mistake is creating a style per cell inside the row loop. The measured effect (observed, 20,000 date cells written with an XSSFWorkbook):

Strategy Styles in the workbook Time for the loop

One shared CellStyle, applied to every cell

2

85 ms

createCellStyle() per cell

20,002

353 ms

The time difference is small here and grows with the size of the style table, but the real damage is the limit: a loop that creates a style per cell fails after 64,000 cells with IllegalStateException: The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook (observed at style number 64,001). An export of the loans-report.xlsx size reaches it in a single sheet.

The fix is to create every style and font once, per workbook, before the first row, and hand them to the row writer. A small holder class makes the rule visible in code review. Never share it between workbooks: a style belongs to the workbook that created it.

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Workbook;

/** Creates every style and font of one workbook once, before the first row is written. */
public final class BookshelfStyles {

    public final CellStyle header;
    public final CellStyle date;
    public final CellStyle money;

    public BookshelfStyles(Workbook workbook) {
        CreationHelper helper = workbook.getCreationHelper();

        Font bold = workbook.createFont();
        bold.setBold(true);

        header = workbook.createCellStyle();
        header.setFont(bold);
        header.setBorderBottom(BorderStyle.THIN);

        date = workbook.createCellStyle();
        date.setDataFormat(helper.createDataFormat().getFormat("yyyy-mm-dd"));

        money = workbook.createCellStyle();
        money.setDataFormat(helper.createDataFormat().getFormat("#,##0.00"));
    }
}

The same rule applies to fonts (workbook.createFont() per cell is the same bug) and to data formats, where DataFormat.getFormat(String) should likewise be resolved once per format, not per cell. The page Cell Styles, Fonts & Formats explains the style cache pattern and the style-sharing figure in detail.

Source: Spreadsheet Quick Guide, Creating date cells and Workbook.createCellStyle Javadoc

The cost of autoSizeColumn

Sheet.autoSizeColumn measures the text of every cell of the column with font metrics. The Javadoc warns that it can be slow on large sheets and should normally be called once per column, after the last row. It also ignores merged cells unless you pass useMergedCells. The danger is calling it inside the row loop, which turns a linear export into a quadratic one. Observed with a XSSFWorkbook and five text columns:

Workload Time

500 rows, autoSizeColumn for the five columns once, at the end

144 ms

5,000 rows, once at the end

913 ms

500 rows, autoSizeColumn for the five columns after every row

23,003 ms

Tips that follow from the numbers:

  • Call it once per column, after the last row, and only for the columns that need it (ISBN, title, member). Numbers and dates have predictable widths: use setColumnWidth with a fixed value, which costs nothing.

  • Run with -Djava.awt.headless=true and make sure the container has a font; the text measurement uses java.awt. A minimal image without fonts fails or measures wrongly.

  • In SXSSF flushed rows are gone, so the columns must be tracked before the rows are written (trackColumnForAutoSizing), and tracking itself has a cost per written cell. Track only the columns you need and call untrackAllColumnsForAutoSizing afterwards. See Streaming Writes with SXSSF.

import java.io.IOException;
import java.util.List;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class AutoSizeOnce {

    /** Usermodel: write all rows first, then fit the few text columns once. */
    static void usermodel(List<String[]> rows) throws IOException {
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Books");
            int r = 0;
            for (String[] line : rows) {
                Row row = sheet.createRow(r++);
                for (int c = 0; c < line.length; c++) row.createCell(c).setCellValue(line[c]);
            }
            sheet.autoSizeColumn(0);               // ISBN
            sheet.autoSizeColumn(1);               // title
            sheet.setColumnWidth(2, 12 * 256);     // numbers and dates: a fixed width costs nothing
        }
    }

    /** SXSSF: only tracked columns are measured, and tracking must start before the rows are written. */
    static void streaming(List<String[]> rows) throws IOException {
        try (SXSSFWorkbook workbook = new SXSSFWorkbook(100)) {
            SXSSFSheet sheet = workbook.createSheet("Loans");
            sheet.trackColumnForAutoSizing(0);
            sheet.trackColumnForAutoSizing(1);
            int r = 0;
            for (String[] line : rows) {
                Row row = sheet.createRow(r++);
                for (int c = 0; c < line.length; c++) row.createCell(c).setCellValue(line[c]);
            }
            sheet.autoSizeColumn(0);
            sheet.autoSizeColumn(1);
            sheet.untrackAllColumnsForAutoSizing();
        }
    }

    public static void main(String[] args) throws IOException {
        System.setProperty("java.awt.headless", "true");
        List<String[]> rows = List.<String[]>of(new String[] {"978-84-376-0494-7", "Don Quijote", "12.50"});
        usermodel(rows);
        streaming(rows);
        System.out.println("ok");
    }
}

Source: Sheet.autoSizeColumn Javadoc and SXSSFSheet.trackColumnForAutoSizing Javadoc

Evaluator caching

A FormulaEvaluator caches the result of every formula cell it evaluates, so a cell that fifty other formulas depend on is calculated once. That cache lives in the evaluator, which is why the evaluator should be created once per workbook and reused: workbook.getCreationHelper().createFormulaEvaluator() inside a loop throws the cache away every time. Observed with a workbook of 1,500 rows in which column C holds SUM($B$1:Bn) over a column of formulas, run twice in the same JVM:

Strategy Round 1 Round 2

One evaluator for the workbook

312 ms

200 ms

A new evaluator for every cell

2,097 ms

1,728 ms

 
import java.io.IOException;
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.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class EvaluatorReuse {

    public static void main(String[] args) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Loans");
            for (int r = 0; r < 1000; r++) {
                Row row = sheet.createRow(r);
                row.createCell(0).setCellValue(r);
                row.createCell(1).setCellFormula("SUM($A$1:A" + (r + 1) + ")");
            }
            // One evaluator for the whole workbook: it caches every result it computes
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            for (Row row : sheet) {
                evaluator.evaluateFormulaCell(row.getCell(1));
            }
            System.out.println(sheet.getRow(999).getCell(1).getNumericCellValue()); // 499500.0
        }
    }
}

The other half of the story is staleness: the cache does not notice that you changed an input cell. After changing cells or formulas, call notifyUpdateCell, notifySetFormula or clearAllCachedResultValues, as shown in Formulas & Evaluation. Two further points for servers:

  • Evaluating is a CPU cost at export time. If readers open the file in Excel, which calculates on load, you can skip evaluateAll and save the cost; do it only when downstream readers (a preview, a script using POI) need cached values.

  • An evaluator is bound to one workbook and is not thread safe, like everything else in POI (next section).

Source: Spreadsheet, formula evaluation and FormulaEvaluator Javadoc

One document per thread

The FAQ answers the threading question in two sentences: handling different document objects in different threads works; accessing the same document from several threads does not, and even touching different sheets of one workbook concurrently can cause errors that are very hard to track. Treat Workbook, Sheet, Row, Cell, CellStyle, FormulaEvaluator, XWPFDocument and DataFormatter as confined to the thread (or virtual thread) that created them. The source of POIXMLTypeLoader carries a comment that shows why a "just make it faster" switch is not available: the XMLBeans option setUnsynchronized() is present but commented out, because enabling it caused unexplained NullPointerExceptions and other problems when properties were read from separate workbooks in several threads. The SSPerformanceTest class has an --unsynchronized-xmlbeans flag that sets the option for an experiment; do not enable it in production.

What this means for the Bookshelf server:

  • One request, one workbook: create it, fill it, write it and close it in the same method (try-with-resources). Nothing about a workbook is stored in a field of a singleton service.

  • What you may share between threads is data that is immutable on your side: the list of rows, the configuration, the template bytes. If a template is read from disk for every export, keep the bytes in memory and open a new document from a ByteArrayInputStream per request.

  • To parallelise a very large export, give each worker its own workbook (for example one month per worker), then combine the files, or deliver a ZIP. Do not write different sheets of one workbook from different threads.

  • With virtual threads each task has its own thread, so thread-locals disappear with the thread; the pool-related advice in the next section matters for platform thread pools. See Virtual Threads.

  • Cap the number of exports that run at once. The memory of a server is spent by concurrent exports, and a gate turns an unbounded spike into a short wait or a 429 Too Many Requests with Retry-After, the same answer as the job queue of Exporting Reports from a Server. The class below is the minimum; the resilience patterns are in Bulkhead.

import java.io.IOException;
import java.io.OutputStream;
import java.util.concurrent.Semaphore;
import java.util.concurrent.TimeUnit;

/** Caps the number of exports that run at the same time, so the heap and the temp disk can be sized. */
public class ExportGate {

    public interface Export {
        void writeTo(OutputStream out) throws IOException;
    }

    private final Semaphore permits;

    public ExportGate(int maxConcurrentExports) {
        this.permits = new Semaphore(maxConcurrentExports, true);
    }

    /** Returns false when the server is busy, so the controller can answer 429 with Retry-After. */
    public boolean run(Export export, OutputStream out) throws IOException, InterruptedException {
        if (!permits.tryAcquire(2, TimeUnit.SECONDS)) {
            return false;
        }
        try {
            export.writeTo(out);
            return true;
        } finally {
            permits.release();
        }
    }
}

Source: Apache POI FAQ, multithreading and POIXMLTypeLoader Javadoc

ThreadLocalUtil.clearAllThreadLocals in servlet containers

POI keeps some state in ThreadLocal fields. In a container that reuses its threads (Tomcat, Jetty, a Spring ThreadPoolTaskExecutor) that state outlives the request, and when the web application is undeployed the thread still references classes of the old class loader. Tomcat then reports a thread-local leak, and a redeploy can leak the class loader. The FAQ describes the remedy: since POI 5.2.4 the utility org.apache.poi.util.ThreadLocalUtil provides clearAllThreadLocals(), which clears the objects held in thread-local variables and avoids those warnings.

The Javadoc of the method in 5.5.1 gives the precise contract: it clears the thread-locals of the current thread, to clean out a thread before it goes back to a thread pool or to a web container such as Tomcat, and it adds that org.apache.xmlbeans.ThreadLocalUtil#clearAllThreadLocals() should be called as well, because XMLBeans keeps its own thread-locals. From the source of 5.5.1 the cleaners that POI registers cover these state holders:

Class Thread-local state it clears

org.apache.poi.util.TempFile

The per-thread TempFileCreationStrategy set with setThreadLocalTempFileCreationStrategy (since POI 5.5.0).

org.apache.poi.hssf.record.crypto.Biff8EncryptionKey

The password for .xls encryption (see Encryption, Signing & Protection).

org.apache.poi.util.LocaleUtil

The user locale and time zone set for the thread.

org.apache.poi.ss.usermodel.DateUtil

The last date-format check, cached to avoid re-checking the same format (see enableThreadLocalCache).

org.apache.poi.hssf.usermodel.HSSFCellStyle

The last format records and data-format string looked up by the HSSF cell style.

org.apache.poi.extractor.ExtractorFactory

The per-thread preference for event-based extractors.

org.apache.poi.sl.draw.DrawFactory

The default draw factory of the slide renderer.

Three consequences follow. First, a cleaner is registered when its class is initialised, so the call cleans the classes that the thread has used. Second, the call must run on the worker thread: calling it from a shutdown hook of the application cleans only the thread that calls it. Third, the registry ThreadLocalUtil keeps is a plain static list, so call it from the same classloader that loaded POI (inside the web application, not from the container).

Two places cover almost every container. A servlet Filter cleans the request thread after every request that may have used POI; the filter is registered with @WebFilter, in web.xml or, in Spring Boot, as a FilterRegistrationBean:

import jakarta.servlet.Filter;
import jakarta.servlet.FilterChain;
import jakarta.servlet.ServletException;
import jakarta.servlet.ServletRequest;
import jakarta.servlet.ServletResponse;
import java.io.IOException;
import org.apache.poi.util.ThreadLocalUtil;

/** Clears the thread-locals that POI and XMLBeans left on the request thread. */
public class PoiThreadCleanupFilter implements Filter {

    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
            throws IOException, ServletException {
        try {
            chain.doFilter(request, response);
        } finally {
            ThreadLocalUtil.clearAllThreadLocals();                          // POI: poi jar and modules that registered cleaners
            org.apache.xmlbeans.ThreadLocalUtil.clearAllThreadLocals();      // XMLBeans: its own caches
        }
    }
}

Background work runs on pools you create. A ThreadPoolExecutor subclass cleans in afterExecute, which the executor calls on the worker thread after each task. The example below also shows the one-workbook-per-task rule: eight exports run on four workers and nothing is shared.

import java.util.concurrent.*;
import org.apache.poi.util.ThreadLocalUtil;

/** A worker pool for Bookshelf exports that cleans each worker thread after every task. */
public class CleaningExecutor extends ThreadPoolExecutor {

    public CleaningExecutor(int threads) {
        super(threads, threads, 0L, TimeUnit.MILLISECONDS, new LinkedBlockingQueue<>(100),
                new ThreadPoolExecutor.CallerRunsPolicy());
    }

    @Override
    protected void afterExecute(Runnable task, Throwable error) {
        // afterExecute runs on the worker thread that ran the task, which is the thread to clean
        ThreadLocalUtil.clearAllThreadLocals();
        org.apache.xmlbeans.ThreadLocalUtil.clearAllThreadLocals();
        super.afterExecute(task, error);
    }
}
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Future;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class OneDocumentPerThread {

    /** Each task builds, writes and closes its own workbook: nothing is shared between threads. */
    static Path export(int month) throws IOException {
        Path out = Files.createTempFile("loans-" + month + "-", ".xlsx");
        try (SXSSFWorkbook workbook = new SXSSFWorkbook(100);
             var stream = Files.newOutputStream(out)) {
            Sheet sheet = workbook.createSheet("Loans");
            for (int r = 0; r < 5_000; r++) {
                Row row = sheet.createRow(r);
                row.createCell(0).setCellValue("loan-" + month + "-" + r);
            }
            workbook.write(stream);
        }
        return out;
    }

    public static void main(String[] args) throws Exception {
        CleaningExecutor pool = new CleaningExecutor(4);
        List<Future<Path>> results = new ArrayList<>();
        for (int month = 1; month <= 8; month++) {
            final int m = month;
            results.add(pool.submit(() -> export(m)));
        }
        for (Future<Path> f : results) {
            Path p = f.get();
            System.out.println(p.getFileName() + " " + Files.size(p) + " bytes");
            Files.delete(p);
        }
        pool.shutdown();
    }
}

The output is one line per export (observed):

loans-1-2639838793003793069.xlsx 41687 bytes
loans-2-4732096277473806964.xlsx 41687 bytes
...
loans-8-2120040645442865413.xlsx 41687 bytes

The call is cheap, so clean after every task rather than trying to detect whether POI was used. It does not replace closing the documents: a Workbook that is not closed keeps a file handle and, for SXSSF, a temporary file.

Source: Apache POI FAQ, ThreadLocalUtil and ThreadLocalUtil Javadoc

Temp-file strategies

POI creates temporary files in three situations: SXSSF flushing rows (poi-sxssf-sheet*.xml), the zip-entry temp files of the InputStream route above, and a few other internals. All of them go through org.apache.poi.util.TempFile, which asks a TempFileCreationStrategy. The default, DefaultTempFileCreationStrategy, uses the directory poifiles below java.io.tmpdir, creates it on demand and, unless the JVM property poi.delete.tmp.files.on.exit is set, does not register the files for deletion on exit. The Javadoc warns that this property can cause an OutOfMemoryError through the frequent use of File.deleteOnExit. Hence the cleanup is close(), as described in Streaming Writes with SXSSF.

Strategy API When to use it

JVM wide directory

TempFile.setTempFileCreationStrategy(new DefaultTempFileCreationStrategy(dir))

One dedicated, size-limited, permission-restricted volume for the whole application. Set it once at start-up.

Per thread, for good

TempFile.setThreadLocalTempFileCreationStrategy(strategy), since POI 5.5.0

A pool of workers that each own a directory. null resets the thread. Cleared by ThreadLocalUtil.

Scoped to one task

TempFile.withStrategy(strategy, supplier), since POI 5.5.0

Exports and imports that need different directories (for example uploads on an encrypted volume, exports on a fast disk). The previous strategy is restored when the task ends, normally or with an exception.

Zip entries of an InputStream

ZipInputStreamZipEntrySource.setThresholdBytesForTempFiles(int) and setEncryptTempFiles(boolean)

Streams that you cannot turn into a file. Both settings are static.

The example uses all three levels. It creates two directories, installs the global strategy, runs a task with its own strategy and verifies that the task wrote its SXSSF temp file into the task directory.

import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.util.Arrays;
import org.apache.poi.openxml4j.util.ZipInputStreamZipEntrySource;
import org.apache.poi.util.DefaultTempFileCreationStrategy;
import org.apache.poi.util.TempFile;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class TempFileStrategies {

    public static void main(String[] args) throws IOException {
        File exportDir = Files.createTempDirectory("bookshelf-exports").toFile();

        // 1. Application wide, once at start-up
        TempFile.setTempFileCreationStrategy(new DefaultTempFileCreationStrategy(exportDir));

        // 2. Scoped to one task and one thread (since POI 5.5.0); the previous strategy is restored afterwards
        File uploadDir = Files.createTempDirectory("bookshelf-uploads").toFile();
        String listing = TempFile.withStrategy(new DefaultTempFileCreationStrategy(uploadDir), () -> {
            try (SXSSFWorkbook workbook = new SXSSFWorkbook(10)) {
                workbook.createSheet("Loans").createRow(0).createCell(0).setCellValue("ok");
                return Arrays.toString(uploadDir.list());
            } catch (IOException e) {
                throw new IllegalStateException(e);
            }
        });
        System.out.println("scoped task used its own directory: " + listing.contains("poi-sxssf-sheet"));

        // 3. Uploads opened from an InputStream: keep big zip entries on disk instead of in the heap
        ZipInputStreamZipEntrySource.setThresholdBytesForTempFiles(50_000_000);
        ZipInputStreamZipEntrySource.setEncryptTempFiles(true);
        System.out.println("threshold: " + ZipInputStreamZipEntrySource.getThresholdBytesForTempFiles());
    }
}
scoped task used its own directory: true
threshold: 50000000

Operational rules for the directory: put it on a volume sized for the number of concurrent exports multiplied by the uncompressed size of a sheet (which is several times the finished .xlsx); on Kubernetes use an emptyDir volume with a sizeLimit when you can and remember that a tmpfs or medium: Memory volume counts against the memory limit; clean the directory at start-up because a killed JVM never runs close(); and restrict its permissions, since the files hold the report data in clear text. The tuning of setCompressTempFiles is a CPU-for-disk trade covered in the SXSSF page.

Source: TempFile Javadoc and DefaultTempFileCreationStrategy Javadoc

Measuring with SSPerformanceTest

The FAQ points at one class for measuring file generation, SSPerformanceTest in the examples package. It is org.apache.poi.examples.ss.SSPerformanceTest in the poi-examples artifact, and it creates a workbook of the chosen implementation with the given number of rows and columns (numbers, dates, percentages, styled headers and a merged title), optionally saving it. Its usage text in 5.5.1 is:

usage: java SSPerformanceTest HSSF|XSSF|SXSSF rows cols saveFile (0|1)? [--unsynchronized-xmlbeans] [--with-warmup-run]

The FAQ adds a yardstick of its own: if you cannot write 50,000 rows by 50 columns within a few seconds, the problem is with your environment, not with POI. Run it from a throwaway project that depends on poi-examples, with the heap you intend to give the application:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-examples</artifactId>
    <version>5.5.1</version>
</dependency>
mvn -q dependency:build-classpath -Dmdep.outputFile=cp.txt
java -Xmx256m -cp "$(cat cp.txt)" org.apache.poi.examples.ss.SSPerformanceTest SXSSF 50000 50 0 --with-warmup-run
java -Xmx256m -cp "$(cat cp.txt)" org.apache.poi.examples.ss.SSPerformanceTest XSSF 50000 50 0

Observed results on the machine of this page (an illustration of the method; the figures say nothing about your hardware): SXSSF with a warm-up run reported "Elapsed 0.73 seconds before save" for the measured run (1.42 seconds for the warm-up), and XSSF with the same arguments and -Xmx256m ended with java.lang.OutOfMemoryError: Java heap space. That is the experiment the choice table above recommends: the same workload, one fixed heap, two implementations. Notes on reading it:

  • --with-warmup-run runs the workload twice and reports the second; without it the first run includes JIT warm-up and class loading. Compare like with like.

  • saveFile set to 1 adds the cost of write and the disk, which is the cost your users feel; use 0 to isolate the generation.

  • The workload is synthetic. For the real export, wrap your own service method in the same harness: fixed -Xmx, warm-up run, then the measured run, and record peak memory with GC logging (-Xlog:gc* writes the heap after each collection) or with Java Flight Recorder.

  • A heap dump on failure (-XX:+HeapDumpOnOutOfMemoryError) is the cheapest evidence in production; open it and look at which classes hold the memory before changing anything.

  • The tool is a throwaway: keep it out of the application’s runtime dependencies.

Source: Apache POI FAQ, memory and performance and SSPerformanceTest in poi-examples

Container memory sizing

A container limit bounds the whole process, not only the Java heap. For a service that uses POI, budget these items:

Item How to think about it

Java heap

Set it explicitly. Without -Xmx the JVM derives the maximum heap from the container limit as a percentage; on the JDK used for this page the default MaxRAMPercentage is 25 (checked with -XX:+PrintFlagsFinal), which is a value you should not leave to chance for an export service. Choose the percentage (or an explicit -Xmx) from the budget in this table.

Peak heap of one operation

The measured peak of the largest export or import, taken with the experiment above and the heap sized for the implementation you chose. For SXSSF it is the window plus the parts that stay in memory; for usermodel imports it is the object model of the biggest allowed upload.

Concurrency

Peak heap of one operation multiplied by the number of operations allowed at the same time (the gate above), plus the baseline of the application with no exports running.

Metaspace, code cache, thread stacks

XMLBeans and the OOXML classes are large; add a fixed margin for class metadata and for each thread’s stack. Do not leave MaxMetaspaceSize unlimited without a container limit behind it.

Native and direct memory

Zip inflaters, AWT font handling for autoSizeColumn and image work allocate outside the heap. Reserve a margin and measure it with Native Memory Tracking (-XX:NativeMemoryTracking=summary).

Temporary files

Disk, unless the directory is a tmpfs, in which case it is memory. See the temp-file section.

Safety margin

The container limit minus everything above must stay positive at the peak of the largest allowed workload.

A starting point for a Kubernetes deployment of the Bookshelf service follows. The numbers are placeholders to be replaced by your measurements, not recommendations: the point is the relationship between the heap setting, the memory limit and the temp-file volume.

env:
  - name: JAVA_TOOL_OPTIONS
    value: >-
      -XX:MaxRAMPercentage=70
      -XX:+ExitOnOutOfMemoryError
      -XX:+HeapDumpOnOutOfMemoryError
      -XX:HeapDumpPath=/dumps
      -Djava.awt.headless=true
resources:
  requests:
    memory: "<measured steady state plus peak of one export>"
  limits:
    memory: "<requests plus margin for native memory>"
volumeMounts:
  - name: poi-temp
    mountPath: /var/poi-temp
volumes:
  - name: poi-temp
    emptyDir:
      sizeLimit: "<concurrent exports times uncompressed sheet size>"

Combine this with the concurrency gate, a maximum upload size in the web layer, and the limits of the security page, so that the worst case is something you have measured. For the surrounding practices see Kubernetes resource management, Java and Spring Boot images and Metrics & Observability (heap, GC and temp-directory metrics are worth alerting on). Logging configuration of the application is in Logging in Spring Boot.

Source: Apache POI FAQ, memory and Spreadsheet How-To, SXSSF memory notes

Pitfalls

Symptom Likely cause and fix

OutOfMemoryError on a large export

XSSFWorkbook used for an unbounded result. Move to SXSSFWorkbook, keep its default inline strings for free text, and cap the concurrency.

OutOfMemoryError on a large upload

InputStream constructor or usermodel on a hostile or huge file. Copy to a temp file, cap the size, or use the SAX API.

An export that takes minutes

autoSizeColumn inside the row loop, a new evaluator per cell, or a new style per cell. Move each out of the loop.

IllegalStateException about 64000 styles

A CellStyle created per cell. Create styles once per workbook.

Slow reads and a growing log volume

Debug logging of org.apache.poi enabled. Pin it at WARN.

Tomcat reports a ThreadLocal leak on undeploy

POI or XMLBeans state left on pooled threads. Call both clearAllThreadLocals methods on the worker thread.

Random exceptions under load

One workbook, style or evaluator shared by several threads. Confine every document to one thread.

/tmp or the volume is full

Workbooks not closed, a JVM killed during exports, or a tmpfs that counts as memory. Close in try-with-resources and clean the directory at start-up.

IllegalStateException from autoSizeColumn in SXSSF

The column was not tracked before the rows were written. Track it first.

What POI does not provide

  • No memory estimator: there is no API that predicts the heap for a file, so the measurement is yours.

  • No built-in concurrency control or back-pressure: limiting simultaneous exports and uploads is the application’s job.

  • No thread-safe document: the FAQ says so, and the XMLBeans synchronisation option is not a way around it.

  • No automatic cleanup of thread-locals: the container integration is the explicit clearAllThreadLocals call.

  • No .xlsx or .docx conversion to PDF, and no incremental write of Word documents; XWPF is a full in-memory model, so large Word documents need the heap, not a streaming API. See Word (XWPF) Basics.

Changes between versions

Version Change relevant to performance, memory and threading

4.x

Logging through POILogger, disabled by default and selected with a system property; the FAQ factor-of-100 remark was written in that era.

5.1.0

Logging moves to the Log4j 2 API only. ZipInputStreamZipEntrySource.setThresholdBytesForTempFiles and SXSSFWorkbook.writeAvoidingTempFiles (experimental) appear.

5.2.4

ThreadLocalUtil.clearAllThreadLocals() is available, according to the FAQ.

5.2.5

XSSFWorkbook(InputStream, boolean closeStream).

5.5.0

TempFile.setThreadLocalTempFileCreationStrategy and TempFile.withStrategy.

6.0 (in development)

Not released on 2026-10-09. Nothing here depends on it; re-read the release notes before upgrading, because the removal of deprecated APIs can affect code that uses the older entry points.

Related pages

References