Dynamic Excel Export with EasyPOI: Generating Column Headers and Data Programmatically

The author, a senior architect, shares a practical tutorial on exporting Excel files using the EasyPOI library, focusing on dynamic generation of column headers and data filling.

Explanation

EasyPOI is used for export.

Column headers are generated dynamically.

Data is filled based on key matching.

The first column is merged vertically.

Personal usage record.

Tool Dependencies

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.2.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.2.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.2.0</version>
</dependency>

Implementation Effect

Before and after screenshots illustrate the merged vertical cells for platform names.

Code Analysis

Dynamic Generation of Column Headers

private List<ExcelExportEntity> dynamicNewAddExcel(Map<String, PlatformStatisParamRespData> paramInfo) {
    // Header collection
    List<ExcelExportEntity> entityList = new ArrayList<>();
    // Fixed columns
    ExcelExportEntity platformXh = new ExcelExportEntity("统计字段1", "statisKey1", 30);
    platformXh.setMergeVertical(true);
    entityList.add(platformXh);
    ExcelExportEntity statisDateXh = new ExcelExportEntity("统计字段2", "statisKey2", 30);
    entityList.add(statisDateXh);
    // Dynamic columns based on paramInfo
    final Iterator<String> iterator = paramInfo.keySet().iterator();
    while (iterator.hasNext()) {
        final String paramKeyStr = iterator.next();
        final String paramNameStr = paramInfo.get(paramKeyStr).getDataName();
        entityList.add(new ExcelExportEntity(paramNameStr, paramKeyStr, 30));
    }
    return entityList;
}

Dynamic Data Filling

private List<Map<String, Object>> dynamicListDataByKey(List<PlatformIncomeRespDTO> statisData) {
    final Set<String> statisParamKey = statisData.get(0).getParamInfo().keySet();
    final List<String> statisDate = statisData.get(0).getStatisDate();
    final int platformNum = statisData.size();
    List<Map<String, Object>> datas = new ArrayList<>();
    for (int i = 0; i < platformNum; i++) {
        for (int j = 0; j < statisDate.size(); j++) {
            Map<String, Object> hashMap = new LinkedHashMap<>(10);
            hashMap.put("statisKey1", statisData.get(i).getPlatformNickName());
            String statisDateStr = statisDate.get(j);
            hashMap.put("statisKey2", statisDateStr);
            for (String paramKey : statisParamKey) {
                for (BiPlatformStatisRespDTO paramData : statisData.get(i).getStatisData().get(j)) {
                    if (paramKey.equals(paramData.getParamKey())) {
                        hashMap.put(paramData.getParamKey(), paramData.getValue() + "(" + paramData.getRateValue() + ")");
                    }
                }
            }
            datas.add(hashMap);
        }
    }
    return datas;
}

Excel Export

public void downloadPlatformIncomeContrast(List<PlatformIncomeRespDTO> statisData, HttpServletResponse response) {
    if (CollectionUtils.isEmpty(statisData)) {
        return;
    }
    final Map<String, PlatformStatisParamRespData> paramInfo = statisData.get(0).getParamInfo();
    List<ExcelExportEntity> colList = this.dynamicNewAddExcel(paramInfo);
    List<Map<String, Object>> list = this.dynamicListDataByKey(statisData);
    final String xlsFileName = DateHelper.getNowString(FormatUnit.yyyyMMddHHmmss, true) + ".xls";
    final Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), colList, list);
    EasypoiUtil.downLoadExcel(xlsFileName, response, workbook);
}

Utility Class for Download

public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
    try {
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        workbook.write(response.getOutputStream());
    } catch (IOException e) {
        throw new RuntimeException(e.getMessage());
    }
}

DTO Definitions

@Data @NoArgsConstructor @AllArgsConstructor
public class PlatformIncomeRespDTO implements Serializable {
    private static final long serialVersionUID = 1100499105160261425L;
    private String platformNickName;
    private List<String> statisDate;
    private Map<String, PlatformStatisParamRespData> paramInfo;
    private List<List<BiPlatformStatisRespDTO>> statisData;
}

@Data @NoArgsConstructor @AllArgsConstructor
public class PlatformStatisParamRespData implements Serializable {
    private static final long serialVersionUID = 4263523446154995471L;
    private String dataName;
    private String dateKey;
    private String dateDesc;
}

@Data @AllArgsConstructor
public class BiPlatformStatisRespDTO implements Serializable {
    private static final long serialVersionUID = 6070471415344415351L;
    @Excel(name = "统计字段", orderNum = "1")
    private String param;
    private String paramKey;
    private String paramDesc;
    private String startDate;
    private String endDate;
    @Excel(name = "统计数据", orderNum = "2")
    private String value;
    private String rateValue;
    private Long id;
    private Integer riseOrFall;
    public BiPlatformStatisRespDTO(String startDate, String paramKey, String value) {
        this.paramKey = paramKey;
        this.startDate = startDate;
        this.value = value;
    }
    public BiPlatformStatisRespDTO() {}
}

Test Case

The test method constructs two PlatformIncomeRespDTO objects with sample data, builds the column list and data list, exports the workbook to a file, and demonstrates the same merging effect as platformXh.setMergeVertical(true).

Additional promotional sections (e.g., QR codes, interview question links) are present but unrelated to the core technical content.