Mastering High‑Performance Excel Export in Java: A Complete Guide

Preface

Our company project required exporting more than one hundred reports, so an efficient and reusable solution was essential.

Implemented Features

Export any type of data

Customizable table header

Customizable field format

Usage Example

Set the data list, header information, and field format, then call the export method to stream an Excel file to the client.

@Override
public void export(HttpServletResponse response, String fileName) {
    // data to export
    List<TtlProductInfoPo> productInfoPos = this.multiThreadListProduct();
    ExcelUtils excelUtils = new ExcelUtils(productInfoPos, getHeaderInfo(), getFormatInfo());
    excelUtils.sendHttpResponse(response, fileName, excelUtils.getWorkbook());
}

private List<ExcelHeaderInfo> getHeaderInfo() {
    return Arrays.asList(
        new ExcelHeaderInfo(1, 1, 0, 0, "id"),
        new ExcelHeaderInfo(1, 1, 1, 1, "商品名称"),
        new ExcelHeaderInfo(0, 0, 2, 3, "分类"),
        new ExcelHeaderInfo(1, 1, 2, 2, "类型ID"),
        new ExcelHeaderInfo(1, 1, 3, 3, "分类名称"),
        new ExcelHeaderInfo(0, 0, 4, 5, "品牌"),
        new ExcelHeaderInfo(1, 1, 4, 4, "品牌ID"),
        new ExcelHeaderInfo(1, 1, 5, 5, "品牌名称"),
        new ExcelHeaderInfo(0, 0, 6, 7, "商店"),
        new ExcelHeaderInfo(1, 1, 6, 6, "商店ID"),
        new ExcelHeaderInfo(1, 1, 7, 7, "商店名称"),
        new ExcelHeaderInfo(1, 1, 8, 8, "价格"),
        new ExcelHeaderInfo(1, 1, 9, 9, "库存"),
        new ExcelHeaderInfo(1, 1, 10, 10, "销量"),
        new ExcelHeaderInfo(1, 1, 11, 11, "插入时间"),
        new ExcelHeaderInfo(1, 1, 12, 12, "更新时间"),
        new ExcelHeaderInfo(1, 1, 13, 13, "记录是否已经删除")
    );
}

private Map<String, ExcelFormat> getFormatInfo() {
    Map<String, ExcelFormat> format = new HashMap<>();
    format.put("id", ExcelFormat.FORMAT_INTEGER);
    format.put("categoryId", ExcelFormat.FORMAT_INTEGER);
    format.put("branchId", ExcelFormat.FORMAT_INTEGER);
    format.put("shopId", ExcelFormat.FORMAT_INTEGER);
    format.put("price", ExcelFormat.FORMAT_DOUBLE);
    format.put("stock", ExcelFormat.FORMAT_INTEGER);
    format.put("salesNum", ExcelFormat.FORMAT_INTEGER);
    format.put("isDel", ExcelFormat.FORMAT_INTEGER);
    return format;
}

Implementation Effect

Source Code Analysis

We focus on the ExcelUtils class, which contains three key member variables.

Member Variables

list

Stores the data to be exported.

excelHeaderInfos

Holds a list of ExcelHeaderInfo objects that define header positions and titles. Constructor:

ExcelHeaderInfo(int firstRow, int lastRow, int firstCol, int lastCol, String title)

. firstRow: starting row of the header lastRow: ending row of the header firstCol: starting column of the header lastCol: ending column of the header title: header name

formatInfo

Maps field names to ExcelFormat enums for type conversion (INTEGER, DOUBLE, PERCENT, DATE). Default format is string.

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. Create Header

The method iterates over excelHeaderInfos , merges cells using Sheet.addMergedRegion , creates the first row and column cells, sets their values and styles, and adjusts column width.

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

2. Transform Data

Converts the original list of objects into a two‑dimensional string array using reflection and BeanUtils.getProperty , enabling uniform handling of various data types.

private String[][] transformData() {
    int dataSize = this.list.size();
    String[][] datas = new String[dataSize][];
    Field[] fields = list.get(0).getClass().getDeclaredFields();
    List<String> 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. Create Content

Writes the transformed data into the sheet, applying formatting based on formatInfo . Supports DOUBLE, INTEGER, PERCENT, and DATE formats.

private void createContent(Row row, CellStyle style, String[][] content, int i, Field[] fields) {
    List<String> 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]);
        }
    }
}

Additional Topics

1. Multithreaded Data Query

Using Callable and FutureTask to fetch data in parallel while preserving order by storing tasks in a list and retrieving results sequentially.

List<FutureTask<List<TtlProductInfoPo>>> tasks = new ArrayList<>();
FutureTask<List<TtlProductInfoPo>> task = new FutureTask<>(new listThread(map));
new Thread(task).start();
tasks.add(task);
for (FutureTask<List<TtlProductInfoPo>> t : tasks) {
    productInfoPos.addAll(t.get());
}

2. Solving Interface Timeout

Exporting massive data may cause the HTTP request to time out. The solution is to make the export asynchronous with Spring’s @Async annotation, returning a token immediately and providing separate endpoints to check status and download the generated file.

Implement an async export API that starts the export job and returns a unique token.

Provide a status API that the client polls to know when the file is ready.

Provide a download API that streams the file once it is completed.

SQL Schema

CREATE TABLE `ttl_product_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '记录唯一标识',
  `product_name` varchar(50) NOT NULL COMMENT '商品名称',
  `category_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '类型ID',
  `category_name` varchar(50) NOT NULL COMMENT '冗余分类名称-避免跨表join',
  `branch_id` bigint(20) NOT NULL COMMENT '品牌ID',
  `branch_name` varchar(50) NOT NULL COMMENT '冗余品牌名称-避免跨表join',
  `shop_id` bigint(20) NOT NULL COMMENT '商品ID',
  `shop_name` varchar(50) NOT NULL COMMENT '冗余商店名称-避免跨表join',
  `price` decimal(10,2) NOT NULL COMMENT '商品当前价格-属于热点数据，而且价格变化需要记录，需要价格详情表',
  `stock` int(11) NOT NULL COMMENT '库存-热点数据',
  `sales_num` int(11) NOT NULL COMMENT '销量',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '插入时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_del` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '记录是否已经删除',
  PRIMARY KEY (`id`),
  KEY `idx_shop_category_salesnum` (`shop_id`,`category_id`,`sales_num`),
  KEY `idx_category_branch_price` (`category_id`,`branch_id`,`price`),
  KEY `idx_productname` (`product_name`)
) ENGINE=InnoDB AUTO_INCREMENT=15000001 DEFAULT CHARSET=utf8 COMMENT='商品信息表';

How to Use

Create the table and insert data.

Run the application.

Visit http://localhost:8080/api/excelUtils/export to download the generated Excel file.

Source Code URL

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