Java使用EasyExcel生成动态表头和多Sheet数据的Excel

由于业务需求，Excel的表头需要固定表头+动态表头，不同Sheet使用不同的动态表头进行Excel导出，这里还是使用EasyExcel进行数据导出，对关键部分代码会进行释义:

1、动态表头+数据Excel

 String fileName = "设备台账" + DateUtil.format(new Date(), DatePattern.PURE_DATE_FORMAT);
    //根据模版id进行分组，不同模板下的数据，导出为不同Sheet的数据
    Map<Long, DeviceLedgerTemplateEntity> templateMap = deviceTemplateList.stream().collect(Collectors.toMap(DeviceLedgerTemplateEntity::getLedgerTemplateId, v -> v));
    int i = 0;
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    fileName = URLEncoder.encode(fileName, "UTF-8");
    response.setHeader("Content-disposition", "attachment;filename=" + fileName + ";" + "filename*=utf-8''" + fileName + ".xlsx");
    ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
    for (Map.Entry<Long, List<DeviceLedgerImportVO>> deviceMap : deviceTemplateMap.entrySet()) {
       Long templateId = deviceMap.getKey();
       List<List<String>> headList = new ArrayList<>();
       List<List<String>> dataList = new ArrayList<>();
       if (templateMap.containsKey(templateId)) {
          DeviceLedgerTemplateEntity templateEntity = templateMap.get(templateId);
          List<FormDTO> formList = JSONArray.parseArray(templateEntity.getFormItems(), FormDTO.class);
          //动态生成多个Sheet的表头
          getHeadList(headList, formList);
          List<DeviceLedgerImportVO> deviceList = deviceTemplateMap.get(templateId);
          //动态填充多个Sheet的数据
          getDataList(dataList, deviceList);
          //循环输出sheet和表头以及表格内容
          WriteSheet sheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(headList).build();
          excelWriter.write(dataList, sheet);
      }
      i++;
   }
   excelWriter.finish();

getHeadList 动态生成表头

  private void getHeadList(List<List<String>> headList, List<FormDTO> formList) {
        //生成Excel中表头
        //固定表头
        Field[] fields = DeviceLedgerImportVO.class.getDeclaredFields();
        for (Field field : fields) {
            boolean bool = field.isAnnotationPresent(ExcelProperty.class);
            if (bool) {
                String value = Arrays.stream(field.getAnnotation(ExcelProperty.class).value()).findFirst().get();
                List<String> filedList = new ArrayList<>();
                filedList.add(value);
                headList.add(filedList);
            }
        }
        //动态表头
        formList.stream().forEach(form -> {
            List<String> filedList = new ArrayList<>();
            filedList.add(form.getLabel());
            headList.add(filedList);
        });
    }

getDataList 动态生出数据

   private void getDataList(List<List<String>> dataList, List<DeviceLedgerImportVO> deviceList) {
        //生成Excel中值
        deviceList.stream().forEach(device -> {
            //固定表头下的数据
            List<String> valueList = new ArrayList<>();
            valueList.add(device.getDeviceSn());
            valueList.add(device.getDeviceName());
            valueList.add(device.getDeviceTypeName());
            valueList.add(device.getLocation());
            valueList.add(device.getStatus());
            //动态表头下的数据
            List<FormDTO> formList = JSONArray.parseArray(device.getFormItems(), FormDTO.class);
            if (CollectionUtil.isNotEmpty(formList)) {
                formList.stream().forEach(form -> valueList.add(form.getValue()));
            }
            dataList.add(valueList);
        });
   }

 Sheet1数据：

Shee2数据：

也可以将Excel导出那部分写到工具类，懒的抽出来了，也懒得写Demo了，大概逻辑差不多，需要修改headList和dataList也根据索引导出每个Sheet的数据，在此，提供工具类：

注意：需要修改headList和dataList，不可直接使用上面的headList和dataList方法

  public static void writeDynamicBatchExcel(HttpServletResponse response, String fileName, String sheetName, List<List<String>> headList, List<List<Object>> dataList) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ";" + "filename*=utf-8''" + fileName + ".xlsx");
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        //循环输出sheet和表头以及表格内容
        for (int i = 0; i < dataList.size(); i++) {
            WriteSheet sheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(Collections.singletonList(headList.get(i))).build();
            excelWriter.write(dataList.get(i), sheet);
        }
        excelWriter.finish();
    }

2、动态表头Excel

如果只生成动态表头模版，不需要数据的话，getHeadList()不变，仅供参考

   public void downloadDeviceLedger(DeviceLedgerSearchDTO param, HttpServletResponse response) throws IOException {
        //查询模版
        DeviceLedgerTemplateEntity templateEntity = deviceLedgerTemplateService.queryDeviceLedgerTemplate(param.getLedgerTemplateId());
        List<FormDTO> formList = JSONArray.parseArray(templateEntity.getFormItems(), FormDTO.class);
        String fileName = "设备台账" + DateUtil.format(new Date(), DatePattern.PURE_DATE_FORMAT);
        List<List<String>> resultList = new ArrayList<>();
        getHeadList(resultList, formList);
        EasyExcelUtils.writeDynamicExcel(response, fileName, "设备台账", resultList, Collections.EMPTY_LIST);
    }

EasyExcelUtils.writeDynamicExcel工具类：

  public static void writeDynamicExcel(HttpServletResponse response, String fileName, String sheetName, List<List<String>> headList, List<List<Object>> dataList) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ";" + "filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream())
                // 设置动态头
                .head(headList)
                .sheet(sheetName)
                .doWrite(dataList);
    }

撒花完结❀，嘻嘻……