Implementing a Flexible Excel Export Utility with Apache POI in Java

The article introduces a Java utility class for exporting Excel reports, addressing the need to handle over a hundred different report formats efficiently. It outlines three core features: exporting arbitrary data types, customizable headers, and flexible field formatting.

Usage is demonstrated with a simple three‑step process—setting the data list, defining the header information via ExcelHeaderInfo , and specifying field formats through a Map of ExcelFormat values. The provided export method shows how to instantiate ExcelUtils and stream the workbook to the HTTP response.

Key class members of ExcelUtils are listed, including the data list, header list, and format map. The ExcelHeaderInfo constructor parameters (firstRow, lastRow, firstCol, lastCol, title) are explained, and the ExcelFormat enum (INTEGER, DOUBLE, PERCENT, DATE) is described for type conversion.

@Override
public void export(HttpServletResponse response, String fileName) {
    // 待导出数据
    List
productInfoPos = this.multiThreadListProduct();
    ExcelUtils excelUtils = new ExcelUtils(productInfoPos, getHeaderInfo(), getFormatInfo());
    excelUtils.sendHttpResponse(response, fileName, excelUtils.getWorkbook());
}

private List
getHeaderInfo() {
    return Arrays.asList(
        new ExcelHeaderInfo(1, 1, 0, 0, "id"),
        new ExcelHeaderInfo(1, 1, 1, 1, "商品名称"),
        // ... other header definitions ...
        new ExcelHeaderInfo(1, 1, 13, 13, "记录是否已经删除")
    );
}

private Map
getFormatInfo() {
    Map
format = new HashMap<>();
    format.put("id", ExcelFormat.FORMAT_INTEGER);
    format.put("categoryId", ExcelFormat.FORMAT_INTEGER);
    format.put("price", ExcelFormat.FORMAT_DOUBLE);
    // ... other format mappings ...
    return format;
}

The core export workflow includes three methods: createHeader (uses POI's addMergedRegion for merged cells), transformData (converts a list of objects to a 2‑D string array via reflection and BeanUtils), and createContent (writes cell values with appropriate formatting).

For handling massive data sets, the article describes a multithreaded retrieval strategy using Callable , FutureTask , and a task list to preserve order, followed by aggregation of results.

To avoid HTTP timeout when exporting huge files, an asynchronous approach is suggested: the export request returns immediately with a file identifier, while a background task generates the Excel file. Clients can poll a status endpoint and later download the completed file.

Finally, the article provides the SQL schema for the underlying ttl_product_info table and step‑by‑step instructions to set up the database, run the application, and trigger the export via a browser URL.