EasyExcel动态头导出

心已赠人 2024-03-22 10:07 48阅读 0赞

#### 前言 ####

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

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

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

--------------------

#### 具体实现 ####

主要是通过 传入 `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<Integer> 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;
    }

/**
       * @description 获取对象中 api 注解的名称
       * @params [object]
       * @return
       */
      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;
      }

--------------------

**本文由 SoGeek\_Studio 发布，有任何问题请留言评论，欢迎指正。**

[4284beaafa3b449fb39073376a5e2801.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/5649ee02e9c64a3da22ea335794d0f03.png
[cdae3a2a8520453bb4d69f4bf05dba16.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/855bc5a50a724e5a85d75c0998d71afb.png
[f14d1906c08848e2bf09117d5c8bf3dc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/155a7db620d54ec3b3d6fe03e413b8f0.png