Generating Excel files whose column headers are detremined at runtime requires a structured approach. This walkthrough shows how to assemble a data-oriented export pipeline using EasyExcel, covering metadata-driven headers, data mapping, custom column widths, and multi-sheet output.

Defining Column Metadata

A lightweight entity captures the internal key and the user‑facing column name:

public class ColumnDefinition {
    private final String fieldKey;
    private final String displayName;

    public ColumnDefinition(String fieldKey, String displayName) {
        this.fieldKey = fieldKey;
        this.displayName = displayName;
    }
    // getters...
}

Encoding a sample set of headers:

List<ColumnDefinition> definitions = Arrays.asList(
    new ColumnDefinition("personName", "Full Name"),
    new ColumnDefinition("idCard", "ID Number"),
    new ColumnDefinition("region", "Administrative Region"),
    new ColumnDefinition("ethnicity", "Ethnic Group"),
    new ColumnDefinition("residentialAddress", "Address"),
    new ColumnDefinition("mobile", "Phone"),
    new ColumnDefinition("amount", "Subsidy (CNY)"),
    new ColumnDefinition("bankType", "Bank"),
    new ColumnDefinition("accountHolder", "Account Name"),
    new ColumnDefinition("accountNumber", "Bank Account"),
    new ColumnDefinition("remarks", "Notes")
);

Preparing Data

Source data usually arrives as a list of maps where keys match the display names. Convert domain objects in to this shape before feeding the exporter:

List<Map<String, String>> flatData = regionList.stream().map(record -> {
    Map<String, String> row = new HashMap<>();
    row.put("Full Name", record.getPersonName());
    row.put("ID Number", record.getIdCard());
    return row;
}).collect(Collectors.toList());

Assembling the Workbook

Configuration for each sheet is bundled into a structure that carries the sheet name, metadata, and15 data payload:

Map<String, Object> sheetConfig = new HashMap<>();
sheetConfig.put("sheetName", "Sheet1");
sheetConfig.put("columnDefs", definitions);
sheetConfig.put("rows", flatData);
List<Map<String, Object>> allSheets = Collections.singletonList(sheetConfig);

Invoking the utility:

try {
    ExcelExportUtil.generateDynamicWorkbook(response, "export_template.xlsx", allSheets);
} catch (Exception ex) {
    ex.printStackTrace();
    throw new RuntimeException("Export failed. Contact admin.");
}

Custom Column Width Strategy

To prevent truncated cells, implement a column width handler that adjusts widths based on header length:

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import java.util.List;

public class AdaptiveColumnWidthStrategy extends AbstractColumnWidthStyleStrategy {
    @Override
    protected void setColumnWidth(WriteSheetHolder sheetHolder,
                                  List<WriteCellData<?>> cellDataList,
                                  Cell cell,
                                  Head head,
                                  Integer relativeRowIndex,
                                  Boolean isHeader) {
        if (Boolean.TRUE.equals(isHeader)) {
            String headerText = cell.getStringCellValue();
            int textLength = headerText.length();
            int width = Math.max(textLength * 5, 20);
            if (width > 255) {
                width = 255;
            }
            if ("Instructions".equals(headerText)) {
                sheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), 150 * 255);
            } else {
                sheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), width * 256);
            }
        }
    }
}

Core Export Utility (Multi‑Sheet)

The method below processes each sheet configuration, builds the header structure, and streams all data to the client:

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.springframework.util.CollectionUtils;
import org.apache.commons.lang3.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.stream.Collectors;

public class ExcelExportUtil {

    public static void generateDynamicWorkbook(HttpServletResponse response,
                                               String fileName,
                                               List<Map<String, Object>> sheetConfigs) throws Exception {
        // Set up response headers for .xlsx download
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Transfer-Encoding", "binary");
        response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
        response.setHeader("Pragma", "public");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8");
        String encodedFileName = new String(fileName.getBytes(StandardCharsets.UTF_8), "ISO-8859-1");
        response.setHeader("Content-Disposition", "attachment;filename=" + encodedFileName);

        // Define content style with text wrapping
        WriteCellStyle wrappedCellStyle = new WriteCellStyle();
        wrappedCellStyle.setWrapped(true);
        HorizontalCellStyleStrategy styleStrategy = new HorizontalCellStyleStrategy(wrappedCellStyle, wrappedCellStyle);

        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();

        try {
            for (int s = 0; s < sheetConfigs.size(); s++) {
                Map<String, Object> config = sheetConfigs.get(s);
                String sheetName = (String) config.get("sheetName");
                @SuppressWarnings("unchecked")
                List<Map<String, String>> rows = (List<Map<String, String>>) config.get("rows");
                @SuppressWarnings("unchecked")
                List<ColumnDefinition> colDefs = (List<ColumnDefinition>) config.get("columnDefs");

                // Build header lists for EasyExcel
                List<List<String>> headers = colDefs.stream()
                        .map(def -> Collections.singletonList(def.getDisplayName()))
                        .collect(Collectors.toList());

                // Build body data aligned with the column definitions
                List<List<Object>> body = new ArrayList<>();
                if (!CollectionUtils.isEmpty(rows)) {
                    for (Map<String, String> row : rows) {
                        List<Object> line = new ArrayList<>();
                        for (ColumnDefinition def : colDefs) {
                            String value = row.get(def.getDisplayName());
                            line.add(StringUtils.isNotBlank(value) ? value : "");
                        }
                        body.add(line);
                    }
                }

                WriteSheet writeSheet = EasyExcel.writerSheet(sheetName)
                        .head(headers)
                        .registerWriteHandler(new AdaptiveColumnWidthStrategy())
                        .registerWriteHandler(styleStrategy)
                        .registerConverter(new LongStringConverter())
                        .build();
                excelWriter.write(body, writeSheet);
            }
            excelWriter.finish();
        } catch (Exception e) {
            throw new RuntimeException("Workbook generation error", e);
        } finally {
            try {
                response.flushBuffer();
            } catch (IOException ignored) {}
        }
    }
}

The approach decouples header configuration from1925 data800 retrieval, making the export layer reusable across different report types.3 The custom column width strategy ensures readable column sizing, and the multi‑sheet loop supports complex multi‑tab workbooks.