Implementing a Flexible Excel Export Utility in Java with Apache POI

The article introduces a common business need: exporting hundreds of reports efficiently. It presents a Java utility class built on Apache POI (version 4.0.1) that supports exporting arbitrary data types, customizable headers, and field formatting.

Key Features

Export any type of data

Customizable table headers

Customizable field formats (integer, double, percent, date)

Usage Example

To use the utility, set the data list, header information, and format map, then call the export method, which writes an Excel workbook to the HTTP response.

@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 headers omitted for brevity ...
        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 formats ...
    return format;
}

Source Code Analysis

The core class ExcelUtils contains three main member variables: a list of data, a list of ExcelHeaderInfo objects, and a map of field formats.

private List list;
private List
excelHeaderInfos;
private Map
formatInfo;

ExcelHeaderInfo stores header coordinates and title, while ExcelFormat is an enum defining possible cell formats (INTEGER, DOUBLE, PERCENT, DATE).

public enum ExcelFormat {
    FORMAT_INTEGER("INTEGER"),
    FORMAT_DOUBLE("DOUBLE"),
    FORMAT_PERCENT("PERCENT"),
    FORMAT_DATE("DATE");
    private String value;
    ExcelFormat(String value) { this.value = value; }
    public String getValue() { return value; }
}

Core Methods

1. createHeader – Initializes merged cells based on ExcelHeaderInfo and sets header titles.

private void createHeader(Sheet sheet, CellStyle style) {
    for (ExcelHeaderInfo excelHeaderInfo : excelHeaderInfos) {
        // merge cells if needed
        if ((excelHeaderInfo.getLastRow() - excelHeaderInfo.getFirstRow()) != 0 ||
            (excelHeaderInfo.getLastCol() - excelHeaderInfo.getFirstCol()) != 0) {
            sheet.addMergedRegion(new CellRangeAddress(
                excelHeaderInfo.getFirstRow(), excelHeaderInfo.getLastRow(),
                excelHeaderInfo.getFirstCol(), excelHeaderInfo.getLastCol()));
        }
        Row row = sheet.getRow(excelHeaderInfo.getFirstRow());
        Cell cell = row.createCell(excelHeaderInfo.getFirstCol());
        cell.setCellValue(excelHeaderInfo.getTitle());
        cell.setCellStyle(style);
        sheet.setColumnWidth(excelHeaderInfo.getFirstCol(), sheet.getColumnWidth(excelHeaderInfo.getFirstCol()) * 17 / 12);
    }
}

2. transformData – Converts the list of objects into a two‑dimensional String array using reflection and BeanUtils .

private String[][] transformData() {
    int dataSize = this.list.size();
    String[][] datas = new String[dataSize][];
    Field[] fields = list.get(0).getClass().getDeclaredFields();
    List
columnNames = this.getBeanProperty(fields);
    for (int i = 0; i < dataSize; i++) {
        datas[i] = new String[fields.length];
        for (int j = 0; j < fields.length; j++) {
            try {
                datas[i][j] = BeanUtils.getProperty(list.get(i), columnNames.get(j));
            } catch (Exception e) {
                LOGGER.error("获取对象属性值失败");
                e.printStackTrace();
            }
        }
    }
    return datas;
}

3. createContent – Writes the transformed data into the sheet, applying the appropriate format based on the formatInfo map.

private void createContent(Row row, CellStyle style, String[][] content, int i, Field[] fields) {
    List
columnNames = getBeanProperty(fields);
    for (int j = 0; j < columnNames.size(); j++) {
        if (formatInfo == null) {
            row.createCell(j).setCellValue(content[i][j]);
            continue;
        }
        if (formatInfo.containsKey(columnNames.get(j))) {
            switch (formatInfo.get(columnNames.get(j)).getValue()) {
                case "DOUBLE":
                    row.createCell(j).setCellValue(Double.parseDouble(content[i][j]));
                    break;
                case "INTEGER":
                    row.createCell(j).setCellValue(Integer.parseInt(content[i][j]));
                    break;
                case "PERCENT":
                    style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(style);
                    cell.setCellValue(Double.parseDouble(content[i][j]));
                    break;
                case "DATE":
                    row.createCell(j).setCellValue(this.parseDate(content[i][j]));
                    break;
            }
        } else {
            row.createCell(j).setCellValue(content[i][j]);
        }
    }
}

Multi‑Threaded Data Retrieval

To handle massive data sets, the article demonstrates splitting the query into multiple threads using Callable and FutureTask , preserving order by storing tasks in a list and retrieving results sequentially.

List
>> tasks = new ArrayList<>();
FutureTask
> task = new FutureTask<>(new listThread(map));
log.info("开始查询第{}条开始的{}条记录", i * THREAD_MAX_ROW, THREAD_MAX_ROW);
new Thread(task).start();
tasks.add(task);

for (FutureTask
> task : tasks) {
    try {
        productInfoPos.addAll(task.get());
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Asynchronous Export to Avoid Interface Timeout

The article suggests using Spring's @Async annotation to decouple the long‑running export process from the HTTP request, returning a token for the client to poll the export status and later download the generated file.

Full Source and Deployment Instructions

GitHub repository: https://github.com/dearKundy/excel-utils

SQL script to create the ttl_product_info table is provided, followed by steps to run the application and access the export endpoint at http://localhost:8080/api/excelUtils/export .

The article concludes with a call for readers to like, comment, and follow the author.