EasyExcel 实现数据的导出

EasyExcel 实现数据的导出

项目环境

jdk17+vue3+element-plus+springSecurity+easyexcel

前端布局

<el-button type="primary" class="btn" @click="batchExportExcel">批量导出(Excel)</el-button>
    <el-button type="success" class="btn" @click="chooseExportExcel">选择导出(Excel)</el-button>

    <el-table :data="customerList" style="width: 10
0%"   @selection-change="handleSelectionChange">
      <el-table-column type="selection" width="50" />
      <el-table-column type="index" label="序号" width="65" />
      <el-table-column property="ownerPO.name" label="负责人" width="120" />
      <el-table-column property="activityPO.name" label="所属活动" />
      <el-table-column label="姓名">
        <template #default="scope">
          <a href="javascript:" @click="view(scope.row.id)">{{ scope.row.cluePO.fullName }}</a>
        </template>
      </el-table-column>
      <el-table-column property="appellationPO.typeValue" label="称呼" />
      <el-table-column property="cluePO.phone" label="手机" width="120" />
      <el-table-column property="cluePO.weixin" label="微信" width="120" />
      <el-table-column property="needLoanPO.typeValue" label="是否贷款" />
      <el-table-column property="intentionStatePO.typeValue" label="意向状态" />
      <el-table-column property="statePO.typeValue" label="线索状态" />
      <el-table-column property="sourcePO.typeValue" label="线索来源" />
      <el-table-column property="intentionProductPO.name" label="意向产品" />
      <el-table-column property="nextContactTime" label="下次联系时间" width="165" />
      <el-table-column label="操作" width="85">
        <template #default="scope">
          <el-button type="primary" @click="view(scope.row.id)">详情</el-button>
        </template>
      </el-table-column>
    </el-table>
    <p>
      <el-pagination background layout="prev, pager, next" :page-size="pageSize" :total="total" @prev-click="page"
        @next-click="page" @current-change="page" />
    </p>
  </div>

前端实现js实现思路：

客户端向服务端发送需要导出的用户id参数（该参数有handleSelectionChange处理）,
后端接受请求后，根据ids查询数据库回写给客户端实现下载功能。

data() {
    return {
      //客户列表对象，初始值是空
      customerList: [
        {
          cluePO: {},
          ownerPO: {},
          activityPO: {},
          appellationPO: {},
          needLoanPO: {},
          intentionStatePO: {},
          statePO: {},
          sourcePO: {},
          intentionProductPO: {},
        },
      ],
      //分页时每页显示多少条数据
      pageSize: 10,
      //总共有多少条
      total: 0,
      //定义一个customerId的数组，里面存放勾选的那些客户id，初始值是空
      customerIdArray: [],

batchExportExcel() {
      this.exportExcel(this.customerIdArray);
    },
    chooseExportExcel() {
      this.exportExcel(this.customerIdArray);
    },
    exportExcel(ids) {
      // 发送同步请求  a链接 href img src   iframe src
      // 1. 创建标签
      let ifram = document.createElement("iframe");

      // 2. 指定同步请求
      let token = window.sessionStorage.getItem("token");
      if (token == null || "" == token) {
        token = window.localStorage.getItem("token");
      }
      console.log(token);
      ifram.src ="http://localhost:8089/api/customer/exportExcel?ids=" +
        ids + "&Authorization=" +token;

      document.body.appendChild(ifram);

      ifram.style.display= "none"; // 隐藏标签
    },
    handleSelectionChange(arr) {
      (this.customerIdArray = []),
        arr.forEach((element) => {
          this.customerIdArray.push(element.id);
        });
    },

服务端处理前端请求，回写数据。

 @GetMapping("/api/customer/exportExcel")
    public void exportCustomers(String ids, HttpServletResponse response) throws IOException {
        String fileName = URLEncoder.encode("客户信息列表", "UTF-8");
        response.setHeader("content-disposition", "attachment;filename="+fileName+".xlsx");
        // 2. 获取要导出的数据 并且保存到excel表
        customerService.exportCustomerExcel(response.getOutputStream(),ids);
    }

服务层：

 @Override
    public void exportCustomerExcel(ServletOutputStream outputStream, String ids) {
// 1. 获取数据
        List<TCustomer> tCustomers = customerMapper.selectByIds(ids);

        List<CustomerExcel> customerExcels = new ArrayList<>();

        tCustomers.forEach(tCustomer -> {
            CustomerExcel customerExcel = new CustomerExcel();

            customerExcel.setOwnerName(ObjectUtils.isEmpty(tCustomer.getOwnerPO()) ? "" : tCustomer.getOwnerPO().getName());
            customerExcel.setActivityName(ObjectUtils.isEmpty(tCustomer.getActivityPO()) ? "" : tCustomer.getActivityPO().getName());
            customerExcel.setFullName(tCustomer.getCluePO().getFullName());
            customerExcel.setAppellationName(tCustomer.getAppellationPO().getTypeValue());
            customerExcel.setPhone(tCustomer.getCluePO().getPhone());
            customerExcel.setWeixin(tCustomer.getCluePO().getWeixin());
            customerExcel.setQq(tCustomer.getCluePO().getQq());
            customerExcel.setEmail(tCustomer.getCluePO().getEmail());
            customerExcel.setAge(tCustomer.getCluePO().getAge());
            customerExcel.setJob(tCustomer.getCluePO().getJob());
            customerExcel.setYearIncome(tCustomer.getCluePO().getYearIncome());
            customerExcel.setAddress(tCustomer.getCluePO().getAddress());
            customerExcel.setNeedLoadName(tCustomer.getNeedLoanPO().getTypeValue());
            customerExcel.setProductName(tCustomer.getIntentionProductPO().getName());
            customerExcel.setSourceName(tCustomer.getSourcePO().getTypeValue());
            customerExcel.setDescription(tCustomer.getDescription());
            customerExcel.setNextContactTime(tCustomer.getNextContactTime());
            customerExcels.add(customerExcel);
        });


        // 2. 生成excel 并将数据保存进去
        EasyExcel.write(outputStream, CustomerExcel.class).sheet("客户信息")
                .doWrite(customerExcels);

    }

package com.dqw.domain.result;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

/**
 *  生成客户excel对应的对象
 */
@Data
public class CustomerExcel {
    /**
     * Excel表头的字段如下：
     *
     * 所属人	所属活动	客户姓名	客户称呼	客户手机	客户微信	客户QQ
     * 客户邮箱	客户年龄	客户职业	客户年收入
     * 客户住址	是否贷款	客户产品	客户来源	客户描述	下次联系时间
     */
    @ExcelProperty(value = "所属人")
    private String ownerName;

    @ExcelProperty(value = "所属活动")
    private String activityName;

    @ExcelProperty(value = "客户姓名")
    private String fullName;

    @ExcelProperty(value = "客户称呼")
    private String appellationName;

    @ExcelProperty(value = "客户手机")
    private String phone;

    @ExcelProperty(value = "客户微信")
    private String weixin;

    @ExcelProperty(value = "客户QQ")
    private String qq;

    @ExcelProperty(value = "客户邮箱")
    private String email;

    @ExcelProperty(value = "客户年龄")
    private int age;

    @ExcelProperty(value = "客户职业")
    private String job;

    @ExcelProperty(value = "客户年收入")
    private BigDecimal yearIncome;

    @ExcelProperty(value = "客户住址")
    private String address;

    @ExcelProperty(value = "是否贷款")
    private String needLoadName;

    @ExcelProperty(value = "客户产品")
    private String productName;

    @ExcelProperty(value = "客户来源")
    private String sourceName;

    @ExcelProperty(value = "客户描述")
    private String description;

    @ExcelProperty(value = "下次联系时间")
    private Date nextContactTime;
}

思路总结：

服务端接受客户端请求后处理思路：

1. 设置响应头信息，response.setHeader(“content-disposition”, “attachment;filename=”+fileName+“.xlsx”);

2. 获取要导出的实体类信息

3. 调用EasyExcel.write(输出流对象，处理数据的类的字节码对象).sheet(”表名“).dowrite(信息集合)；

4. 由于是处理同步请求（请求路径需要写全，前端设置的请求拦截器不起作用）而且使用springSecurity安全框架在处理jwt信息校验的时候需要修改一部分代码，认证token从请求路径上获得（get请求）而不是请求头中获取。

    //获取客户端传的jwt
               String token = request.getHeader(Constants.TOKEN_AUTHORIZATION);
               if (token==null){
                   token=request.getParameter("Authorization");
               }
   

以上代码经过实际实践没有错误，仅此记录。