Boost Large-Scale Excel Exports in Java with a Multi-Threaded EasyExcel Template

Introduction Common backend projects often need to process massive datasets such as orders, finance, or other business data, requiring operations like export or Excel upload. This article presents a reusable template that lets developers write only the query SQL to achieve low‑latency, high‑efficiency exports.

Tools EasyExcel v3.3.2. Requirements: pagination query, multi‑sheet writing, multithreaded query, replace large offset (SQL‑level, not implemented in this article).

Features Uses Lambda function parameters to decouple logic and provides a generic utility class.

Principle Analysis

The article provides two export methods: multithreaded export and synchronous export.

Multithreaded Export Implementation Utilizes a thread pool to launch multiple threads for segmented queries. It first calculates the total number of pages, then creates Future tasks for each page offset, and finally gathers results via future.get() to write into Excel sheets. Proper thread‑pool sizing is required to avoid excessive thread creation.

Synchronous Export Performs page‑by‑page queries sequentially, writing each page's result to Excel before moving to the next page. This method is simpler but may be slower for very large datasets.

Code Example

/**
 * Asynchronous export of data to Excel file
 * @param outputStream Output stream (file, network)
 * @param queryLambda Query function provided by the user
 * @param clazz Data type
 * @param totalCount Total number of records
 * @param pageSize Page size for queries
 * @param executor Thread pool for executing query tasks
 */
private <T> void asyncExportData(OutputStream outputStream,
                                 Function<Integer, List<T>> queryLambda,
                                 Class<T> clazz,
                                 Integer totalCount,
                                 Integer pageSize,
                                 ExecutorService executor,
                                 String taskName) {
    int totalPages = (totalCount + pageSize - 1) / pageSize;
    int totalSheets = (int) Math.ceil((double) totalCount / SHEET_SIZE);
    log.info("{} async Excel export, total rows:{}, sheets:{}, pages:{}", taskName, totalCount, totalSheets, totalPages);

    List<Future<List<T>>> futureList = new ArrayList<>();
    for (int page = 0; page < totalPages; page++) {
        final int offset = page * pageSize;
        futureList.add(executor.submit(() -> queryLambda.apply(offset)));
    }

    try (ExcelWriter excelWriter = EasyExcel.write(outputStream, clazz).build()) {
        LocalDateTime now = LocalDateTime.now();
        int totalQueriesAllSheet = 0;
        for (int sheetIndex = 0; sheetIndex < totalSheets; sheetIndex++) {
            int startIndexForSheet = sheetIndex * SHEET_SIZE;
            int endIndexForSheet = Math.min(startIndexForSheet + SHEET_SIZE, totalCount);
            int totalQueriesForSheet = (int) Math.ceil((double) (endIndexForSheet - startIndexForSheet) / pageSize);
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetIndex, "Sheet " + (sheetIndex + 1)).build();
            for (int queryIndex = 0; queryIndex < totalQueriesForSheet; queryIndex++) {
                int pageIndex = totalQueriesAllSheet + queryIndex;
                List<T> data = futureList.get(pageIndex).get();
                excelWriter.write(data, writeSheet);
            }
            totalQueriesAllSheet += totalQueriesForSheet;
        }
        LocalDateTime end = LocalDateTime.now();
        log.info("{} async Excel export took:{} ms", taskName, Duration.between(now, end).toMillis());
    } catch (Exception e) {
        log.error("{} async Excel export exception,", taskName, e);
    }
}

/** Synchronous export implementation (similar structure omitted for brevity) */

The full export class ExcelWrTemplet includes constants for sheet size and page size, methods to set download response headers, and wrappers for both synchronous and asynchronous export calls. An auxiliary data class ExcelWrBase<T> holds parameters such as output stream, query lambda, data type, total count, page size, executor, and task name.

Usage example in a Spring controller demonstrates obtaining an ExecutorService, preparing the query lambda with MyBatis PageHelper, building an ExcelWrBase instance, and invoking syncExportData to stream the generated Excel file to the HTTP response.