快捷導(dǎo)航

Java使用EasyExcel生成動(dòng)態(tài)表頭和多Sheet數(shù)據(jù)的Excel實(shí)例

更新時(shí)間：2025年04月23日 09:22:23 作者：echola_mendes

這篇文章主要介紹了Java使用EasyExcel生成動(dòng)態(tài)表頭和多Sheet數(shù)據(jù)的Excel實(shí)例,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

Java使用EasyExcel生成動(dòng)態(tài)表頭和多Sheet數(shù)據(jù)的Excel

由于業(yè)務(wù)需求，Excel的表頭需要固定表頭+動(dòng)態(tài)表頭，不同Sheet使用不同的動(dòng)態(tài)表頭進(jìn)行Excel導(dǎo)出，這里還是使用EasyExcel進(jìn)行數(shù)據(jù)導(dǎo)出，對(duì)關(guān)鍵部分代碼會(huì)進(jìn)行釋義:

1、動(dòng)態(tài)表頭+數(shù)據(jù)Excel

 String fileName = "設(shè)備臺(tái)賬" + DateUtil.format(new Date(), DatePattern.PURE_DATE_FORMAT);
    //根據(jù)模版id進(jìn)行分組，不同模板下的數(shù)據(jù)，導(dǎo)出為不同Sheet的數(shù)據(jù)
    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);
          //動(dòng)態(tài)生成多個(gè)Sheet的表頭
          getHeadList(headList, formList);
          List<DeviceLedgerImportVO> deviceList = deviceTemplateMap.get(templateId);
          //動(dòng)態(tài)填充多個(gè)Sheet的數(shù)據(jù)
          getDataList(dataList, deviceList);
          //循環(huán)輸出sheet和表頭以及表格內(nèi)容
          WriteSheet sheet = EasyExcel.writerSheet(i, "Sheet" + (i + 1)).head(headList).build();
          excelWriter.write(dataList, sheet);
      }
      i++;
   }
   excelWriter.finish();

getHeadList 動(dòng)態(tài)生成表頭

  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);
            }
        }
        //動(dòng)態(tài)表頭
        formList.stream().forEach(form -> {
            List<String> filedList = new ArrayList<>();
            filedList.add(form.getLabel());
            headList.add(filedList);
        });
    }

getDataList 動(dòng)態(tài)生出數(shù)據(jù)

   private void getDataList(List<List<String>> dataList, List<DeviceLedgerImportVO> deviceList) {
        //生成Excel中值
        deviceList.stream().forEach(device -> {
            //固定表頭下的數(shù)據(jù)
            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());
            //動(dòng)態(tài)表頭下的數(shù)據(jù)
            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數(shù)據(jù)：

Shee2數(shù)據(jù)：

也可以將Excel導(dǎo)出那部分寫(xiě)到工具類，懶的抽出來(lái)了，也懶得寫(xiě)Demo了，大概邏輯差不多，需要修改headList和dataList也根據(jù)索引導(dǎo)出每個(gè)Sheet的數(shù)據(jù)，在此，提供工具類：

注意：需要修改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();
        //循環(huán)輸出sheet和表頭以及表格內(nèi)容
        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、動(dòng)態(tài)表頭Excel

如果只生成動(dòng)態(tài)表頭模版，不需要數(shù)據(jù)的話，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 = "設(shè)備臺(tái)賬" + DateUtil.format(new Date(), DatePattern.PURE_DATE_FORMAT);
        List<List<String>> resultList = new ArrayList<>();
        getHeadList(resultList, formList);
        EasyExcelUtils.writeDynamicExcel(response, fileName, "設(shè)備臺(tái)賬", 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())
                // 設(shè)置動(dòng)態(tài)頭
                .head(headList)
                .sheet(sheetName)
                .doWrite(dataList);
    }