位置：首页-资讯-后端开发

EasyExcel动态头导出

2023-08-23 09:11

短信预约 -IT技能 免费直播动态提醒

前言

这段时间的项目中需要导出动态表格。
根据所选的参数导出对应的字段内容
在这里插入图片描述

下图所示选择下面几个tab页就需要导出对应的表头字段
在这里插入图片描述

下面为具体实现的效果。表头样式可以通过EasyExcel 提供的方法自定义。
在这里插入图片描述

具体实现

主要是通过传入 exportItem 这个条件来决定导出的事项。

下附实现代码

public boolean export(QueryBO queryBo) throws CustomException {    List<Integer> exportItem = queryBo.getExportItem();    List<BankAccountOverviewVO> bankAccountOverviewList =        accountOverviewList(queryBo, new PageInfoVo());    // 动态创建表头    List<List<String>> headList = new ArrayList<>();    // 组装数据    List<List<Object>> dataList = new ArrayList<>();    // 表头合并的逻辑    List<CellRangeAddress> cellRangeAddressList = new ArrayList<>();    // 组装账户信息总览 导出数据    builidAccountOverviewExportData(        exportItem, bankAccountOverviewList, headList, dataList, cellRangeAddressList);    String fileName = "信息总览.xlsx";// 调用EasyExcel 方法输出文件流    try {      HttpServletResponse response = ServletUtils.getResponse();      response.setContentType("application/msexcel;charset=utf-8");      response.setHeader(          "content-disposition", "attachment; filename=" + URLEncoder.encode(fileName, "utf-8"));      OutputStream out = response.getOutputStream();      EasyExcel.write(out)          .head(headList) // 设置表头数据          .registerWriteHandler(new SimpleColumnWidthStyleStrategy(25)) // excel 样式设置          .autoCloseStream(Boolean.TRUE) // 自动关闭文件流          .sheet("信息总览")          .doWrite(dataList); // 数据list      // 开始导出    } catch (Exception e) {      LoggerUtils.error(e.getMessage());      return false;    }    return true;  }

数据组装方法

builidAccountOverviewExportData

主要实现的原理是通过List exportItem 来判断需要导出的tab块。

其中使用了 getApiModelName(OverviewBankBookOcceVO.class); 方法映射对象的字段名进行快捷组装。

private void builidAccountOverviewExportData(      List<Integer> exportItem,      List<BankAccountOverviewVO> bankAccountOverviewList,      List<List<String>> headList,      List<List<Object>> dataList,      List<CellRangeAddress> cellRangeAddressList) {    // --------------表头拼接-------------    // 账户信息    List<String> bankBookHead = new ArrayList<>();    bankBookHead.add("企业主体编码");    bankBookHead.add("企业主体名称");    for (String head : bankBookHead) {      List<String> bankBookHeadTitle = new ArrayList<>();      bankBookHeadTitle.add(head);      headList.add(bankBookHeadTitle);    }// 字段下标！！！！！！！！！！    int index = 13;    if (CollectionUtils.isNotEmpty(exportItem)) {      // 账面发生额      if (exportItem.contains(BankAccountOverviewExportTypeEnum.BANK_BOOK_OCCE.getValue())) {        List<String> apiModelNameList = getApiModelName(OverviewBankBookOcceVO.class);        for (String apiModelName : apiModelNameList) {          List<String> bankBookOccHeadTitle = new ArrayList<>();          bankBookOccHeadTitle.add("账面发生额");          bankBookOccHeadTitle.add(apiModelName);          headList.add(bankBookOccHeadTitle);        }        //      // 需要合并的表头位置  （起始行，结束行，起始列，结束列）        //      int size = apiModelNameList.size();        //      CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, index, index + size -        // 1);        //      cellRangeAddressList.add(cellRangeAddress);        //      index += size;      }      // 发生额调节表      if (exportItem.contains(BankAccountOverviewExportTypeEnum.OCC_ADJUSTMENT.getValue())) {        List<String> apiModelNameList = getApiModelName(OverviewOccAdjustmentVO.class);        for (String apiModelName : apiModelNameList) {          List<String> occAdjustmentInfoHeadTitle = new ArrayList<>();          occAdjustmentInfoHeadTitle.add("发生额调节表");          occAdjustmentInfoHeadTitle.add(apiModelName);          headList.add(occAdjustmentInfoHeadTitle);        }      }      // 网银流水      if (exportItem.contains(BankAccountOverviewExportTypeEnum.EBANK_FLOW.getValue())) {        List<String> apiModelNameList = getApiModelName(OverviewEbankFlowVO.class);        for (String apiModelName : apiModelNameList) {          List<String> ebankFlowHeadTitle = new ArrayList<>();          ebankFlowHeadTitle.add("网银流水");          ebankFlowHeadTitle.add(apiModelName);          headList.add(ebankFlowHeadTitle);        }      }      // 核对余额      if (exportItem.contains(BankAccountOverviewExportTypeEnum.RECONCILE_BALANCE.getValue())) {        List<String> apiModelNameList = getApiModelName(OverviewReconcileBalanceVO.class);        for (String apiModelName : apiModelNameList) {          List<String> reconcileBalancesHeadTitle = new ArrayList<>();          reconcileBalancesHeadTitle.add("核对余额");          reconcileBalancesHeadTitle.add(apiModelName);          headList.add(reconcileBalancesHeadTitle);        }        // 需要合并的表头位置  （起始行，结束行，起始列，结束列）        int size = apiModelNameList.size();        CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, index, index + size - 1);        cellRangeAddressList.add(cellRangeAddress);        index += size;      }      // 双向核对结果      if (exportItem.contains(BankAccountOverviewExportTypeEnum.CHECK_RESULT.getValue())) {        List<String> apiModelNameList = getApiModelName(OverviewCheckResultVO.class);        for (String apiModelName : apiModelNameList) {          List<String> checkResultHeadTitle = new ArrayList<>();          checkResultHeadTitle.add("双向核对结果");          checkResultHeadTitle.add(apiModelName);          headList.add(checkResultHeadTitle);        }      }    }    // ----------------组装数据-------------    for (BankAccountOverviewVO bankAccountOverview : bankAccountOverviewList) {      List<Object> data = new ArrayList<>();      // 账户信息总览      data.add(bankAccountOverview.getCompanyId());      data.add(bankAccountOverview.getCompanyName());      String checkPlanFlag = bankAccountOverview.getCheckPlanFlag();      if (StringUtils.isNotEmpty(checkPlanFlag)) {        checkPlanFlag = checkPlanFlag.replaceAll("0", "是");        checkPlanFlag = checkPlanFlag.replaceAll("1", "否");      }      data.add(checkPlanFlag);      data.add(bankAccountOverview.getNoCheckReason());      // -------动态表格数据组装-----      if (CollectionUtils.isNotEmpty(exportItem)) {        // 账面发生额        if (exportItem.contains(BankAccountOverviewExportTypeEnum.BANK_BOOK_OCCE.getValue())) {          data.add(bankAccountOverview.getBankBookOccInfo().getOpenBalance());          data.add(bankAccountOverview.getBankBookOccInfo().getCurrentIssueAdd());          data.add(bankAccountOverview.getBankBookOccInfo().getCurrentIssueReduce());          data.add(bankAccountOverview.getBankBookOccInfo().getBalance());          data.add(bankAccountOverview.getBankBookOccInfo().getOpenBalanceF());          data.add(bankAccountOverview.getBankBookOccInfo().getCurrentIssueAddF());          data.add(bankAccountOverview.getBankBookOccInfo().getCurrentIssueReduceF());          data.add(bankAccountOverview.getBankBookOccInfo().getBalanceF());        }        // 发生额调节表        if (exportItem.contains(BankAccountOverviewExportTypeEnum.OCC_ADJUSTMENT.getValue())) {          data.add(bankAccountOverview.getOccAdjustmentInfo().getCurrentIssueAdd());          data.add(bankAccountOverview.getOccAdjustmentInfo().getCurrentIssueReduce());        }        // 网银流水        if (exportItem.contains(BankAccountOverviewExportTypeEnum.EBANK_FLOW.getValue())) {          data.add(bankAccountOverview.getEbankFlowInfo().getOpenBalance());          data.add(bankAccountOverview.getEbankFlowInfo().getCurrentIssueAdd());          data.add(bankAccountOverview.getEbankFlowInfo().getCurrentIssueReduce());          data.add(bankAccountOverview.getEbankFlowInfo().getBalance());        }        // 核对余额        if (exportItem.contains(BankAccountOverviewExportTypeEnum.RECONCILE_BALANCE.getValue())) {          data.add(bankAccountOverview.getReconcileBalances().getBalanceVerify());          data.add(bankAccountOverview.getReconcileBalances().getDebitOccFVerify());          data.add(bankAccountOverview.getReconcileBalances().getCreditOccFVerify());          data.add(bankAccountOverview.getReconcileBalances().getNetAmountVerify());        }        // 双向核对结果        if (exportItem.contains(BankAccountOverviewExportTypeEnum.CHECK_RESULT.getValue())) {          data.add(bankAccountOverview.getCheckResult().getDebitOccF());          data.add(bankAccountOverview.getCheckResult().getCreditOccF());          data.add(bankAccountOverview.getCheckResult().getDebitRatio());          data.add(bankAccountOverview.getCheckResult().getCreditRatio());          data.add(bankAccountOverview.getCheckResult().getUntreatedProject());          data.add(bankAccountOverview.getCheckResult().getExceptionProject());        }      }      dataList.add(data);    }  }

getApiModelName 方法代码如下，因为项目引入了 swagger 所以数据接口对象都会使用到 @ApiModelProperty 注解如下图所示。通过反射获取实体类的对应属性的注解名称list。

@Data@ApiModel(value = "vo对象", description = "vo对象")public class OverviewBankBookOcceVO {  @ApiModelProperty(value = "银行账号")  private String bankAccount;  @ApiModelProperty("期初余额(原币)")  private BigDecimal openBalance;}

  private List<String> getApiModelName(Object object) {    Class<T> aClass = (Class<T>) object;    Field[] fields = aClass.getDeclaredFields();    List<String> apiModelName = new ArrayList<>();    for (Field field : fields) {      ApiModelProperty annotation = field.getAnnotation(ApiModelProperty.class);      String value = annotation.value();      if (field.getName().equals("bankAccount")) {        continue;      }      apiModelName.add(value);    }    return apiModelName;  }