使⽤easypoi 导出excel 实现动态列
使⽤easypoi 导出excel 实现动态列说明
使⽤的是easypoi进⾏导出
⾏头是动态⽣成
依据key进⾏列匹配，进⾏数据填充
第⼀列进⾏纵向动态合并
⾃⼰的⼀个使⽤，记录⼀下
⼯具依赖
实现效果<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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
变更前样式
变更后样式
代码解析
动态⽣成列头
动态填充数据  private List<ExcelExportEntity> dynamicNewAddExcel(Map<String, PlatformStatisParamRespData> paramInfo) {  //表头的集合，⽤于添加表头    List<ExcelExportEntity> entityList = new ArrayList<>(); //ExcelExportEntity 构造参数【第⼀个是列名头的统计字段，第⼆个是需要指定的⼀个key 在填充数据的时候是需要根据这个key 进⾏填充值，第三个参数是列宽】    ExcelExportEntity platformXh = new ExcelExportEntity("统计字段1", "statisKey1", 30);    //列的合并(纵向列的同名称会进⾏合并，效果见上图的平台名称的变化)    platformXh.setMerge
Vertical(true);    entityList.add(platformXh);    ExcelExportEntity statisDateXh = new ExcelExportEntity("统计字段2", "statisKey2", 30);    entityList.add(statisDateXh);    //参数信息--[⽤于动态拼接列头]    final Iterator<String> iterator = paramInfo.keySet().iterator();    while (iterator.hasNext()) {      final String paramKeyStr = ();      final String paramNameStr = (paramKeyStr).getDataName();      //列头由参数汉字名称，参数key 为列key      entityList.add(new ExcelExportEntity(paramNameStr, paramKeyStr, 30));    }    return entityList;  }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
excel 的导出
EasypoiUtil⼯具类private List<Map<String, Object>> dynamicListDataByKey(List<PlatformIncomeRespDTO> statisData) {    //参数类型    final Set<String> statisParamKey = (0).getParamInfo().keySet();    final List<String> statisDate = (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);        //这个是依据key 进⾏数据的填充,(根据前⾯填写的statisKey1进⾏填充数据)        hashMap.put("statisKey1", (i).getPlatformNickName());        String statisDateStr = statis
<(j);        //这个是依据key 进⾏数据的填充,(根据前⾯填写的statisKey2进⾏填充数据)        hashMap.put("statisKey2", statisDateStr);        //参数的验证        for (String paramKey : statisParamKey) {          for (BiPlatformStatisRespDTO paramData : (i).getStatisData().get(j)) {            if (paramKey.ParamKey())) {              hashMap.ParamKey(), Value() + "(" + RateValue() + ")");            }          }        }        datas.add(hashMap);      }    }    return datas;  }
1
2
3
4
5
6
7
8
9
10
11
12
13
1415
16
17
18
19
20
21
22
param name23
24
25
26
27
28
29//statisData 就是我们查询出来的数据public void downloadPlatformIncomeContrast(List<PlatformIncomeRespDTO> statisData, HttpServletResponse response) {    if (CollectionUtils.isEmpty(statisData)) {      return;    }    //获取参数信息    final Map<Stri
ng, PlatformStatisParamRespData> paramInfo = (0).getParamInfo();    //拼装列头    List<ExcelExportEntity> colList = this.dynamicNewAddExcel(paramInfo);    //数据拼装    List<Map<String, Object>> list = this.dynamicListDataByKey(statisData);    final String xlsFileName = MMddHHmmss, true) + ".xls";    final Workbook workbook = portExcel(new ExportParams(), colList, list);    //动态合并纵列[mergeMap key 列索引(从0开始),value 依赖的列，没有传空,startRow 开始⾏(从零开始)]    //Map<Integer, int[]> mer = new HashMap<>();    //mer.put(0, new int[]{});    //SheetAt(0), mer, 1);    EasypoiUtil.downLoadExcel(xlsFileName, response, workbook);  }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
PlatformIncomeRespDTO PlatformStatisParamRespData 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=" + de(fileName, "UTF-8"));      workbook.OutputStream());    } catch (IOException e) {      throw new Message());    }  }
1
2
3
4
5
6
7
8
9
10
11@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;}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
BiPlatformStatisRespDTO @Data @NoArgsConstructor @AllArgsConstructor public class PlatformStatisParamRespData implements Serializable {  private static final long serialVersionUID = 4263523446154995471L;  /**  * 参数名称  */  private String dataName;  /**  * 参数key    */  private String dateKey;  /**  * 参数描述  */  private String dateDesc;}
1234567891011121314151617181920212223

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。