Java

说明

  • 使用的是EasyPoi进行导出
  • 行头是动态生成
  • 依据key进行列匹配，进行数据填充

  • 第一列进行纵向动态合并

    工具依赖

    1. <dependency>
    2. <groupId>cn.afterturn</groupId>
    3. <artifactId>easypoi-base</artifactId>
    4. <version>3.2.0</version>
    5. </dependency>
    6. <dependency>
    7. <groupId>cn.afterturn</groupId>
    8. <artifactId>easypoi-annotation</artifactId>
    9. <version>3.2.0</version>
    10. </dependency>
    11. <dependency>
    12. <groupId>cn.afterturn</groupId>
    13. <artifactId>easypoi-web</artifactId>
    14. <version>3.2.0</version>
    15. </dependency>

    实现效果

    变更前样式
    2022-06-29-08-41-23-824140.png
    变更后样式
    2022-06-29-08-41-23-921433.png

    代码解析

    动态生成列头

    1. private List<ExcelExportEntity> dynamicNewAddExcel(Map<String, PlatformStatisParamRespData> paramInfo) {
    2.   //表头的集合，用于添加表头
    3.   List<ExcelExportEntity> entityList = new ArrayList<>();
    5.   //ExcelExportEntity构造参数【第一个是列名头的统计字段,第二个是需要指定的一个key在填充数据的时候是需要根据这个key进行填充值,第三个参数是列宽】
    6.   ExcelExportEntity platformXh = new ExcelExportEntity("统计字段1", "statisKey1", 30);
    7.   //列的合并(纵向列的同名称会进行合并,效果见上图的平台名称的变化)
    8.   platformXh.setMergeVertical(true);
    9.   entityList.add(platformXh);
    11.   ExcelExportEntity statisDateXh = new ExcelExportEntity("统计字段2", "statisKey2", 30);
    12.   entityList.add(statisDateXh);
    14.   //参数信息--[用于动态拼接列头]
    15.   final Iterator<String> iterator = paramInfo.keySet().iterator();
    16.   while (iterator.hasNext()) {
    17.       final String paramKeyStr = iterator.next();
    18.       final String paramNameStr = paramInfo.get(paramKeyStr).getDataName();
    19.       //列头由参数汉字名称,参数key为列key
    20.       entityList.add(new ExcelExportEntity(paramNameStr, paramKeyStr, 30));
    21.   }
    22.   return entityList;
    23. }

    动态填充数据

    1. private List<Map<String, Object>> dynamicListDataByKey(List<PlatformIncomeRespDTO> statisData) {
    2.   //参数类型
    3.   final Set<String> statisParamKey = statisData.get(0).getParamInfo().keySet();
    4.   final List<String> statisDate = statisData.get(0).getStatisDate();
    5.   final int platformNum = statisData.size();
    7.   //最终的数据
    8.   List<Map<String, Object>> datas = new ArrayList<>();
    9.   for (int i = 0; i < platformNum; i++) {
    10.       for (int j = 0; j < statisDate.size(); j++) {
    11.           Map<String, Object> hashMap = new LinkedHashMap<>(10);
    12.           //这个是依据key进行数据的填充,(根据前面填写的statisKey1进行填充数据)
    13.           hashMap.put("statisKey1", statisData.get(i).getPlatformNickName());
    14.           String statisDateStr = statisDate.get(j);
    15.           //这个是依据key进行数据的填充,(根据前面填写的statisKey2进行填充数据)
    16.           hashMap.put("statisKey2", statisDateStr);
    17.           //参数的验证
    18.           for (String paramKey : statisParamKey) {
    19.               for (BiPlatformStatisRespDTO paramData : statisData.get(i).getStatisData().get(j)) {
    20.                   if (paramKey.equals(paramData.getParamKey())) {
    21.                       hashMap.put(paramData.getParamKey(), paramData.getValue() + "(" + paramData.getRateValue() + ")");
    22.                   }
    23.               }
    24.           }
    25.           datas.add(hashMap);
    26.       }
    27.   }
    28.   return datas;
    29. }

    Excel的导出

    1. //statisData就是查询出来的数据
    2. public void downloadPlatformIncomeContrast(List<PlatformIncomeRespDTO> statisData, HttpServletResponse response) {
    3.   if (CollectionUtils.isEmpty(statisData)) {
    4.       return;
    5.   }
    6.   //获取参数信息
    7.   final Map<String, PlatformStatisParamRespData> paramInfo = statisData.get(0).getParamInfo();
    9.   //拼装列头
    10.   List<ExcelExportEntity> colList = this.dynamicNewAddExcel(paramInfo);
    12.   //数据拼装
    13.   List<Map<String, Object>> list = this.dynamicListDataByKey(statisData);
    14.   final String xlsFileName = DateHelper.getNowString(FormatUnit.yyyyMMddHHmmss, true) + ".xls";
    15.   final Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), colList, list);
    17.   //动态合并纵列[mergeMap key列索引(从0开始),value依赖的列,没有传空,startRow 开始行(从零开始)]
    18.   //Map<Integer, int[]> mer = new HashMap<>();
    19.   //mer.put(0, new int[]{});
    20.   //PoiMergeCellUtil.mergeCells(workbook.getSheetAt(0), mer, 1);
    21.   EasypoiUtil.downLoadExcel(xlsFileName, response, workbook);
    22. }

    EasypoiUtil工具类

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

    PlatformIncomeRespDTO ```java @Data @NoArgsConstructor @AllArgsConstructor public class PlatformIncomeRespDTO implements Serializable {

    private static final long serialVersionUID = 1100499105160261425L;

  1. /**
  2.  * 平台别名
  3.  */
  4. private String platformNickName;
  6. /*统计时间*/
  7. private List<String> statisDate;
  9. /*查询参数信息--[用户收入来源统计导出使用]*/
  10. private Map<String, PlatformStatisParamRespData> paramInfo;
  13. /*统计数据*/
  14. private List<List<BiPlatformStatisRespDTO>> statisData;

}

  1. PlatformStatisParamRespData
  2. ```java
  3. @Data
  4. @NoArgsConstructor
  5. @AllArgsConstructor
  6. public class PlatformStatisParamRespData implements Serializable {
  8.     private static final long serialVersionUID = 4263523446154995471L;
  10.     /**
  11.    * 参数名称
  12.    */
  13.     private String dataName;
  15.     /**
  16.    * 参数key
  17.    */
  18.     private String dateKey;
  20.     /**
  21.    * 参数描述
  22.    */
  23.     private String dateDesc;
  25. }

BiPlatformStatisRespDTO

  1. @Data
  2. @AllArgsConstructor
  3. public class BiPlatformStatisRespDTO implements Serializable {
  5.     private static final long serialVersionUID = 6070471415344415351L;
  7.     @Excel(name = "统计字段", orderNum = "1")
  8.     private String param;
  10.     /**
  11.     * 参数的key
  12.     */
  13.     private String paramKey;
  15.     /**
  16.     * 参数描述
  17.     */
  18.     private String paramDesc;
  20.     private String startDate;
  22.     private String endDate;
  24.     @Excel(name = "统计数据", orderNum = "2")
  25.     private String value;
  27.     private String rateValue;
  29.     private Long id;
  31.     private Integer riseOrFall;
  33.     public BiPlatformStatisRespDTO(String startDate, String paramKey, String value) {
  34.         this.paramKey = paramKey;
  35.         this.startDate = startDate;
  36.         this.value = value;
  37.     }
  39.     public BiPlatformStatisRespDTO() {
  40.     }
  41. }

测试用例

测试特殊说明

导出的结果有个控制，是在拼装的时候没有填充此数据，不影响整体效果。

测试结果示例

2022-06-29-08-41-23-989731.png

测试数据json示例

  1. [
  2.   {
  3.     "paramInfo": {
  4.       "userCount": {
  5.         "dataName": "用户数", 
  6.         "dateDesc": "用户信息", 
  7.         "dateKey": "userCount"
  8.       }, 
  9.       "friendsCount": {
  10.         "dataName": "好友数", 
  11.         "dateDesc": "好友信息", 
  12.         "dateKey": "friendsCount"
  13.       }
  14.     }, 
  15.     "platformNickName": "aaa", 
  16.     "statisData": [
  17.       [
  18.         {
  19.           "paramKey": "userCount", 
  20.           "startDate": "2019-12-26", 
  21.           "value": "100"
  22.         }, 
  23.         {
  24.           "paramKey": "friendsCount", 
  25.           "startDate": "2019-12-26", 
  26.           "value": "200"
  27.         }
  28.       ], 
  29.       [
  30.         {
  31.           "paramKey": "userCount", 
  32.           "startDate": "2019-12-27", 
  33.           "value": "300"
  34.         }, 
  35.         {
  36.           "paramKey": "friendsCount", 
  37.           "startDate": "2019-12-27", 
  38.           "value": "400"
  39.         }
  40.       ]
  41.     ], 
  42.     "statisDate": [
  43.       "2019-12-26", 
  44.       "2019-12-27"
  45.     ]
  46.   }, 
  47.   {
  48.     "paramInfo": {
  49.       "userCount": {
  50.         "dataName": "用户数", 
  51.         "dateDesc": "用户信息", 
  52.         "dateKey": "userCount"
  53.       }, 
  54.       "friendsCount": {
  55.         "dataName": "好友数", 
  56.         "dateDesc": "好友信息", 
  57.         "dateKey": "friendsCount"
  58.       }
  59.     }, 
  60.     "platformNickName": "bbb", 
  61.     "statisData": [
  62.       [
  63.         {
  64.           "paramKey": "userCount", 
  65.           "startDate": "2019-12-26", 
  66.           "value": "500"
  67.         }, 
  68.         {
  69.           "paramKey": "friendsCount", 
  70.           "startDate": "2019-12-26", 
  71.           "value": "600"
  72.         }
  73.       ], 
  74.       [
  75.         {
  76.           "paramKey": "userCount", 
  77.           "startDate": "2019-12-27", 
  78.           "value": "700"
  79.         }, 
  80.         {
  81.           "paramKey": "friendsCount", 
  82.           "startDate": "2019-12-27", 
  83.           "value": "800"
  84.         }
  85.       ]
  86.     ], 
  87.     "statisDate": [
  88.       "2019-12-26", 
  89.       "2019-12-27"
  90.     ]
  91.   }
  92. ]

测试用例代码

  1. public class Simple {
  3.   /**
  4.    * @Description: 拼接表头
  5.    * @Param: paramInfo :表头信息
  6.    * @return: java.util.List<cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity>
  7.    */
  8.   private static List<ExcelExportEntity> dynamicNewAddExcel(Map<String, PlatformStatisParamRespData> paramInfo) {
  9.     //表头的集合，用于添加表头
  10.     List<ExcelExportEntity> entityList = new ArrayList<>();
  12.     //ExcelExportEntity构造参数【第一个是列名头的统计字段,第二个是需要指定的一个key在填充数据的时候是需要根据这个key进行填充值,第三个参数是列宽】
  13.     ExcelExportEntity platformXh = new ExcelExportEntity("统计字段1", "statisKey1", 30);
  14.     //列的合并(纵向列的同名称会进行合并,效果见上图的平台名称的变化)
  15.     platformXh.setMergeVertical(true);
  16.     entityList.add(platformXh);
  18.     ExcelExportEntity statisDateXh = new ExcelExportEntity("统计字段2", "statisKey2", 30);
  19.     entityList.add(statisDateXh);
  21.     //参数信息--[用于动态拼接列头]
  22.     final Iterator<String> iterator = paramInfo.keySet().iterator();
  23.     while (iterator.hasNext()) {
  24.       final String paramKeyStr = iterator.next();
  25.       final String paramNameStr = paramInfo.get(paramKeyStr).getDataName();
  26.       //列头由参数汉字名称,参数key为列key
  27.       entityList.add(new ExcelExportEntity(paramNameStr, paramKeyStr, 30));
  28.     }
  29.     return entityList;
  30.   }
  33.   /**
  34.    * @Description: 拼接数据
  35.    * @Param: statisData :拼接数据
  36.    */
  37.   private static List<Map<String, Object>> dynamicListDataByKey(List<PlatformIncomeRespDTO> statisData) {
  38.     //参数类型
  39.     final Set<String> statisParamKey = statisData.get(0).getParamInfo().keySet();
  40.     final List<String> statisDate = statisData.get(0).getStatisDate();
  41.     final int platformNum = statisData.size();
  43.     //最终的数据
  44.     List<Map<String, Object>> datas = new ArrayList<>();
  45.     for (int i = 0; i < platformNum; i++) {
  46.       for (int j = 0; j < statisDate.size(); j++) {
  47.         Map<String, Object> hashMap = new LinkedHashMap<>(10);
  48.         //这个是依据key进行数据的填充,(根据前面填写的statisKey1进行填充数据)
  49.         hashMap.put("statisKey1", statisData.get(i).getPlatformNickName());
  50.         String statisDateStr = statisDate.get(j);
  51.         //这个是依据key进行数据的填充,(根据前面填写的statisKey2进行填充  数据)
  52.         hashMap.put("statisKey2", statisDateStr);
  53.         //参数的验证
  54.         for (String paramKey : statisParamKey) {
  55.           for (BiPlatformStatisRespDTO paramData : statisData.get(i).getStatisData().get(j)) {
  56.             if (paramKey.equals(paramData.getParamKey())) {
  57.               hashMap.put(paramData.getParamKey(), paramData.getValue() + "(" + paramData.getRateValue() + ")");
  58.             }
  59.           }
  60.         }
  61.         datas.add(hashMap);
  62.       }
  63.     }
  64.     return datas;
  65.   }
  68.   @Test
  69.   public void Administrator_84_20191226095523() throws IOException {
  70.     System.out.println("欢迎使用单元测试方法【Administrator_84()_20191226095523】");
  71.     System.out.println("此方法测试描述：【】");
  72.     //拼装第一个数据---------------------------------------------------------------------
  73.     final PlatformIncomeRespDTO platformIncomeRespDTO1 = new PlatformIncomeRespDTO();
  74.     platformIncomeRespDTO1.setPlatformNickName("aaa");
  75.     //拼装时间维度
  76.     platformIncomeRespDTO1.setStatisDate(Lists.newArrayList("2019-12-26","2019-12-27"));
  77.     //拼装头信息
  78.     Map<String, PlatformStatisParamRespData> paramInfo1=new HashMap<>();
  79.     paramInfo1.put("userCount", new PlatformStatisParamRespData("用户数","userCount","用户信息"));
  80.     paramInfo1.put("friendsCount", new PlatformStatisParamRespData("好友数","friendsCount","好友信息"));
  81.     platformIncomeRespDTO1.setParamInfo(paramInfo1);
  82.     //拼装数据
  83.     final ArrayList<List<BiPlatformStatisRespDTO>> data1 = Lists.newArrayList();
  84.     data1.add(Lists.newArrayList(new BiPlatformStatisRespDTO("2019-12-26","userCount","100"),new BiPlatformStatisRespDTO("2019-12-26","friendsCount","200")));
  85.     data1.add(Lists.newArrayList(new BiPlatformStatisRespDTO("2019-12-27","userCount","300"),new BiPlatformStatisRespDTO("2019-12-27","friendsCount","400")));
  86.     platformIncomeRespDTO1.setStatisData(data1);
  89.     //拼装第二个数据---------------------------------------------------------------------
  90.     final PlatformIncomeRespDTO platformIncomeRespDTO2 = new PlatformIncomeRespDTO();
  91.     platformIncomeRespDTO2.setPlatformNickName("bbb");
  92.     //拼装时间维度
  93.     platformIncomeRespDTO2.setStatisDate(Lists.newArrayList("2019-12-26","2019-12-27"));
  94.     //拼装头信息
  95.     Map<String, PlatformStatisParamRespData> paramInfo2=new HashMap<>();
  96.     paramInfo2.put("userCount", new PlatformStatisParamRespData("用户数","userCount","用户信息"));
  97.     paramInfo2.put("friendsCount", new PlatformStatisParamRespData("好友数","friendsCount","好友信息"));
  98.     platformIncomeRespDTO2.setParamInfo(paramInfo2);
  100.     //拼装数据
  101.     final ArrayList<List<BiPlatformStatisRespDTO>> data2 = Lists.newArrayList();
  102.     data2.add(Lists.newArrayList(new BiPlatformStatisRespDTO("2019-12-26","userCount","500"),new BiPlatformStatisRespDTO("2019-12-26","friendsCount","600")));
  103.     data2.add(Lists.newArrayList(new BiPlatformStatisRespDTO("2019-12-27","userCount","700"),new BiPlatformStatisRespDTO("2019-12-27","friendsCount","800")));
  104.     platformIncomeRespDTO2.setStatisData(data2);
  106.     final ArrayList<PlatformIncomeRespDTO> platformIncomeRespDTOS = Lists.newArrayList(platformIncomeRespDTO1, platformIncomeRespDTO2);
  107.     System.out.println(JSONObject.toJSONString(platformIncomeRespDTOS));
  109.     //拼装列头
  110.     List<ExcelExportEntity> colList = dynamicNewAddExcel(paramInfo2);
  112.     //数据拼装
  113.     List<Map<String, Object>> list = dynamicListDataByKey(platformIncomeRespDTOS);
  115.     //文件名称
  116.     final Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), colList, list);
  118.     //此功能与【拼装列头】中的 platformXh.setMergeVertical(true);功能效果一样,可直接使用 platformXh.setMergeVertical(true);进行纵向合并
  119.     //动态合并纵列[mergeMap key列索引(从0开始),value依赖的列,没有传空,startRow 开始行(从零开始)]
  120.     //Map<Integer, int[]> mer = new HashMap<>();
  121.     //mer.put(0, new int[]{});
  122.     //PoiMergeCellUtil.mergeCells(workbook.getSheetAt(0), mer, 1);
  124.     final FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\1.xls");
  125.     //导出excel
  126.     downLoadExcel(null, fileOutputStream, workbook);
  127.   }
  132.   /**
  133.    * @Description: 下载文件
  134.    * @Param: fileName
  135.    * @Param outputStream
  136.    * @Param workbook
  137.    * @return: void
  138.    */
  139.   public static void downLoadExcel(String fileName, FileOutputStream outputStream, Workbook workbook)
  140.       throws IOException {
  141.     try {
  142.       workbook.write(outputStream);
  143.     } catch (IOException e) {
  144.       throw new RuntimeException(e.getMessage());
  145.     } finally {
  146.       outputStream.close();
  147.     }
  148.   }
  149. }