easyExcel 4.x 动态表头动态数据

场景

后台管理系统在查询的时候可以筛选选择显示的列，在导出的时候期望也能导出的时候保持一致，这个时候就不能使用对象的方式了，对象是固定顺序，如果使用动态的 需要是用 List<List<Object>> 方式 这里只是简单的demo 其实可以封装一个组件来使用

方案

maven

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>4.0.3</version>
</dependency>
<!-- 如果引入了这个 需要版本大于 2.16.1 -->
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <!-- 这个版本可以继续往上升 ，但是不要低于这个 -->
    <version>2.16.1</version>
</dependency>
<!-- 最大支持版本 5.2.5 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.5</version>
</dependency>

代码

/**
 * 下载excel
 *
 * @param request
 * @return
 */
@RequestMapping("/downloadExcel")
public void downloadExcel(@Validated @RequestBody PageRequest<ReportDayOrderRequest> request,
                          HttpServletResponse httpServletResponse) throws Exception {
    logPayOrderService.downloadExcel(request, httpServletResponse);
}

public void downloadExcel(PageRequest<ReportDayOrderRequest> request, HttpServletResponse httpServletResponse) throws Exception {
    String fileName = "/download/excel/" + "充值汇总-" + System.currentTimeMillis();
    // 将数据用户信息导出成Excel文件并以流的形式返回
    httpServletResponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    // 设置字符集
    httpServletResponse.setCharacterEncoding("utf-8");
    // 设置文件名，并且进行编码
    fileName = URLEncoder.encode(fileName, "UTF-8");//.replaceAll("\+", "%20");
    // 告诉浏览器将以下载的方式处理响应，而不是在浏览器中直接打开(attachment表示下载、filename*=utf-8''" + fileName + ".xlsx"表示下载的文件名)
    httpServletResponse.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

    // 自定义表头
    List<List<String>> headers = getDynameicHeader(getByValue(request.getData().getGroupType()).name());

    request.setPageSize(1000);

    Long total = PageUtil.selectPageCount(request, page -> {
        LogStatQueryV2DTO logStatQueryDTO = ReportUtil.getLogStatQueryV2DTO(request.getData());
        logStatQueryDTO.setMerchantCode(request.getData().getMerchantCode());
        logPayOrderCrudService.dynamicGroupBy(page, logStatQueryDTO);
    });
    long totalPages = PageUtils.totalPages(total, request.getPageSize());


    WriteSheet writeSheet = EasyExcel.writerSheet("sheet 名称").head(headers).build();
    try (ExcelWriter excelWriter = EasyExcel.write(httpServletResponse.getOutputStream()).build()) {
        for (int i = 1; i <= totalPages; i++) {
            request.setPageNum(i);
            PageResult<LogDayPayStatDTO> objectPageResult = PageUtil.selectPage(request, page -> {
                LogStatQueryV2DTO logStatQueryDTO = ReportUtil.getLogStatQueryV2DTO(request.getData());
                logStatQueryDTO.setMerchantCode(request.getData().getMerchantCode());
                logPayOrderCrudService.dynamicGroupBy(page, logStatQueryDTO);
            });
            List<List<Object>> dynamicData = getDynamicData(getByValue(request.getData().getGroupType()), objectPageResult.getList());
            // 动态数据
            excelWriter.write(dynamicData, writeSheet);
        }
    }
}

动态表头代码

/**
 * 获取动态表头
 *
 * @param groupType
 * @return
 */
private List<List<String>> getDynameicHeader(String groupType) {
    List<List<String>> headers = Lists.newArrayList();
    boolean isTimeIncluded = groupType.startsWith("TIME_");
    boolean isGameIncluded = groupType.contains("GAME");
    boolean isChannelIncluded = groupType.contains("CHANNEL");
    boolean isSubGameIncluded = groupType.contains("SUB_GAME");

    if (isTimeIncluded) {
        headers.add(Collections.singletonList("时间"));
    }
    if (isChannelIncluded) {
        headers.add(Collections.singletonList("渠道"));
    }
    if ((isGameIncluded && !isSubGameIncluded) || TIME_GAME_CHANNEL_SUB_GAME_GROUP.name().equals(groupType)) {
        headers.add(Collections.singletonList("游戏"));
    }
    if (isSubGameIncluded) {
        headers.add(Collections.singletonList("子游戏ID"));
    }


    headers.addAll(Lists.newArrayList(
            Collections.singletonList("总金额"),
            Collections.singletonList("订单数"),
            Collections.singletonList("账号数"),
            Collections.singletonList("设备数")
    ));

    return headers;
}

动态数据

private List<List<Object>> getDynamicData(List<LogDayPayStatDTO> logDayPayStatDTOList) {
         // 自定义字段 需要和表头顺序一致

        // 添加公共字段
        row.add(logDayPayStatDTO.getAmount());
        row.add(logDayPayStatDTO.getOrderNum());
        row.add(logDayPayStatDTO.getNum());
        row.add(logDayPayStatDTO.getDeviceNum());

        dataList.add(row);
    }
    return dataList;
}