easypoi导出excel嵌套动态列

• T1 - 目标效果

  col1	col2		col3
  	col2-1	col2-2
  col1-v1	col2-1-v1	col2-2-v1	col3-v1
  col1-v2	col2-1-v2	col2-2-v2	col3-v2

• T2 - 依赖

  <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-base</artifactId>
      <version>4.1.0</version>
  </dependency>
  

• T3 - 例子

  • 范例代码

      /**
       * @author Wilson
       * @date 2019/8/26
       **/
      public class DynamicPoiTest {
          public static void main(String[] args) throws IOException {
              List<ExcelExportEntity> entity = new ArrayList<>();
              entity.add(buildExcelEntity("序号", "order", 0));
              entity.add(buildExcelEntity("性别", "sex", 1));
              // 设置了group的若不设置排序默认会派到最后
              entity.add(buildExcelEntityByGroup("姓名", "students.name", "学生", 2));
              entity.add(buildExcelEntityByGroup("性别", "students.sex", "学生", 2));
              entity.add(buildExcelEntity("班级", "class", 3));
              List<Map<String, Object>> list = new ArrayList<>();
              IntStream.range(0, 10).forEach(i -> {
                  Map<String, Object> params = new HashMap<>();
                  params.put("order", "" + i);
                  params.put("sex", "sex" + i);
                  params.put("class", "class-" + i);
                  params.put("students.sex", "man-" + i);
                  params.put("students.name", "name-" + i);
                  list.add(params);
              });
              Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("测试", "测试"), entity, list);
              try (FileOutputStream fos = new FileOutputStream("E:/IntelliJ/workspace/netty-demo/ExcelExportForMap.xls")) {
                  workbook.write(fos);
              }
          }
    
          private static ExcelExportEntity buildExcelEntity(String name, Object key, int orderNo) {
              ExcelExportEntity excelEntity = new ExcelExportEntity(name, key);
              excelEntity.setOrderNum(orderNo);
              return excelEntity;
          }
    
          /**
           * 在group列下添加子列
           *
           * @param name
           * @param key
           * @param group
           * @return
           */
          private static ExcelExportEntity buildExcelEntityByGroup(String name, Object key, String group, int orderNo) {
              ExcelExportEntity excelEntity = new ExcelExportEntity(name, key);
              excelEntity.setGroupName(group);
              excelEntity.setOrderNum(orderNo);
              return excelEntity;
          }
      }
    

  • 导出结果

    

• T4 - 注

  • easypoi虽然在BaseExportService.createCell时虽然会嵌套遍历设置的setList()中的list，但遍历数据映射的值时会直接转成Collection,而Map并非Collection的子类(即下源码中obj.get()后获得的然是Map)，所以报类型转换错误，即上例中list里的每个map.value中都不能再包含Map(百度时发现其他人的博客直接放Map，转成其Demo的版本后发现依旧是不可行的)。遍历ExcelExportEntity中的list时其value转换源码(ExportCommonService)如下：

    /**
     * @param entity  当前列
     * @param obj     每一行excel的数据列映射，如上第一行为[order:0,sex:sex0.....]
     **/
    public Collection<?> getListCellValue(ExcelExportEntity entity, Object obj) throws Exception {
        Object value;
        if (obj instanceof Map) {
            value = ((Map<?, ?>) obj).get(entity.getKey());
        } else {
            value = (Collection<?>) entity.getMethod().invoke(obj, new Object[]{});
        }
        return (Collection<?>) value;
    }
    

  • 目前个人只发现easypoi可通过groupName设置父列名嵌套导出，即只能嵌套1层，即是groupName与ExportExcelEntity创建时的name是相同的也依然会重新创建一列而不会覆盖，但也满足目前所需