Vue导出excel的两个常用方式介绍与对比_Javascript

一、需求分析

1. 两个方式

导出excel有两个方式，前端导出和后端导出

前端导出excel：就用 vue+xlsx（npm 包）

后端导出excel：就用 vue+poi（maven 包）

2. 对比分析

前端导出excel 相对来说简单一点，xlsx是前端 npm 包，但是如果数据量大的话，会卡顿，处理时间慢；当数据量多的时候使用后端导出会好一点

后端导出excel 相对来说麻烦一点，但是时间短、速度快；具体操作都放在后端，也节省了前端的操作。用户效果好。

二、方式1：vue+xlsx

1. 安装 xlsx

npm install xlsx file-saver --save

2. xlsx 两个方法比较

这个xlsx 方法一和下面的xlsx 方法二都是使用的 xlsx 模块的方法，只是获取数据的方式和导出excel的方式有点不一样。
相比之下，还是 xlsx 方法一会好一点，可以自定义导出的字段。

3. xlsx 方法一

a. 按钮事件

<el-button size="small" type="primary" @click="exportselect()">导出选中</el-button>
<el-button size="small" type="primary" @click="exportallexcel">导出全部</el-button>

其实上面的二方法可以做成一个方法，但是为了明确好分析，我写成了二个方法。

b. js 方法:导出选中 exportselect()

      exportselect() {
        //  导出选中
        this.$confirm("是否确认导出当前所有参赛人员数据?", "警告", {
          confirmbuttontext: "确定",
          cancelbuttontext: "取消",
          type: "warning",
        }).then((response) => {
          //  this.selectdata 是复选框的 数据列表
          if (this.selectdata.length <= 0) {
            this.$message.info('请选择数据！');
            return false;
          }
          let tabledata = [
            ['序号', '赛区名称', '参赛人', '手机号', '收件地址', "邮箱", "录入时间",  "状态"]//导出表头
          ] // 表格表头
          this.selectdata.foreach((item, index) => {
            let rowdata = []
            //导出内容的字段
            rowdata = [
              index + 1,
              item.matchareaname,
              item.username,
              item.userphone,
              item.receiveaddress,
              item.createtime,
              item.dataflag === 0 ? '待审核': '审核通过',
            ]
            tabledata.push(rowdata)
          })
          let worksheet = xlsx.utils.aoa_to_sheet(tabledata);
          let booknew = xlsx.utils.book_new();
          xlsx.utils.book_append_sheet(booknew, worksheet, '作品名称') // 工作簿名称
          let name = '参赛人员选中' + this.timeformat() + '.xlsx'
          xlsx.writefile(booknew, name) // 保存的文件名
        })
      },
      timeformat() {
        let time = new date();
        let year = time.getfullyear();
        let month = time.getmonth() + 1;
        let date = time.getdate();
        let hours = time.gethours();
        let minutes = time.getminutes();
        let seconds = time.getseconds();
        return year + '-' + this.addzero(month) + '-' + this.addzero(date) + ' ' + this.addzero(hours) + ':' + this.addzero(minutes) + ':' + this.addzero(seconds);
      },
      addzero(num) {
        return num < 10 ? '0' + num : num
      },

c. js 方法:导出全部 exportallexcel

      // 导出 所有
      exportallexcel() {
        this.$confirm("是否确认导出全部参赛人员数据?", "警告", {
          confirmbuttontext: "确定",
          cancelbuttontext: "取消",
          type: "warning",
        }).then(response => {

          // 获取用户列表
          let arr = [];
          let parameter = {
            pageno: 1,
            pagesize: 1000
          }
          getmatchuserinfolist(parameter).then(res => {
            this.loading = false;
            if (res.data.code != "1") {
              this.$message({
                type: 'info',
                message: res.data.message
              })
            } else {
              arr = res.data.data;
              console.log('----------:', json.stringify(arr))
              this.exportlist(arr);
            }
          }).catch(err => {
            this.$message.warning("系统问题，请稍后重试！")
          })
      },
      exportlist(arr){
        let tabledata = [
          ['序号', '赛区名称', '参赛人', '手机号', '收件地址', "邮箱", "录入时间",  "审核状态", "是否发送豆子"]//导出表头
        ] // 表格表头
        arr.foreach((item, index) => {
          let rowdata = []
          //导出内容的字段
          rowdata = [
            index + 1,
            item.matchareaname,
            item.username,
            item.userphone,
            item.receiveaddress,
            item.email,
            item.createtime,
            item.dataflag === 0 ? '待审核': '审核通过',
            item.sendflag === 1 ? '否': '是',
          ]
          tabledata.push(rowdata)
        })
        let worksheet = xlsx.utils.aoa_to_sheet(tabledata);
        let booknew = xlsx.utils.book_new();
        xlsx.utils.book_append_sheet(booknew, worksheet, '作品名称') // 工作簿名称
        let name = '全部参赛人员' + this.timeformat() + '.xlsx'
        xlsx.writefile(booknew, name) // 保存的文件名
      },

4. xlsx 方法二

a. 按钮事件

<el-button size="small" type="primary" @click="exportexcel">导出当前页</el-button>

b. js 方法:导出当前页 exportexcel

这里是

// 导出当前页
      exportexcel() {
        this.$confirm("是否确认导出当前所有参赛人员数据?", "警告", {
          confirmbuttontext: "确定",
          cancelbuttontext: "取消",
          type: "warning",
        }).then((response) => {
          const wb = xlsx.utils.table_to_book(
                  document.queryselector("#el-table")
          );
          const wbout = xlsx.write(wb, {
            booktype: "xlsx",
            booksst: true,
            type: "array",
          });
          try {
            filesaver.saveas(
                    new blob([wbout], {type: "application/octet-stream"}),
                    "参赛人员数据.xlsx"
            );
          } catch (e) {
            if (typeof console !== "undefined") console.log(e, wbout);
          }
        });
      },

二、方式2：vue+poi

这个方式也就是后端生成excel，与前端没有多大的关系，后端写好的 excel就直接write到 response里面了。
先直接放上前端代码。

1. 前端代码

a、按钮事件

<el-button size="small" type="primary" @click="exportallexcel">导出全部</el-button>

b、网络请求封装

// 导出全部
export function exportexcelformatchuser(data) {
  return fetch({
    url: '/xfx/matchuser/web/exportexcelformatchuser',
    method: 'post',
    timeout: '120000',
    responsetype: 'blob',
    data
  });
}

c、js方法：导出全部 exportallexcel

      // 导出 所有
      exportallexcel() {
        this.$confirm("是否确认导出全部参赛人员数据?", "警告", {
          confirmbuttontext: "确定",
          cancelbuttontext: "取消",
          type: "warning",
        }).then(response => {
          exportexcelformatchuser().then(response => {
            const data = "参赛人员web.xlsx";
            console.log('1111111111111111111111111', json.stringify(response))
            let blob = new blob([response.data], {type: "application/vnd.openxmlformats-officedocument.wordprocessingml.document;charset=utf-8"});
            console.log('333333333333333333333333', json.stringify(blob))
            // for ie
            if (window.navigator && window.navigator.mssaveoropenblob) {
              window.navigator.mssaveoropenblob(blob, data);
            } else {
              console.log('chrome go here ')
              let downloadelement = document.createelement('a');
              let href = window.url.createobjecturl(blob); // 创建下载的链接
              downloadelement.href = href;
              downloadelement.download = data; // 下载后文件名
              document.body.appendchild(downloadelement);
              downloadelement.click(); // 点击下载
              document.body.removechild(downloadelement); // 下载完成移除元素
              window.url.revokeobjecturl(href); // 释放掉blob对象
            }
          }).catch(err => {
            console.log(err)
            this.loading = false;
            this.$message.warning("对不起，下载失败");
          });
        })
      },

2. 后端代码（important）

a、maven 依赖

        <!-- poi -->
        <dependency>
            <groupid>org.apache.poi</groupid>
            <artifactid>poi</artifactid>
            <version>3.9</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupid>org.apache.poi</groupid>
            <artifactid>poi-ooxml</artifactid>
            <version>3.9</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
        <dependency>
            <groupid>org.apache.poi</groupid>
            <artifactid>poi-scratchpad</artifactid>
            <version>3.14</version>
        </dependency>

        <!--这个不属于 poi ，就是一个工具类-->
        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupid>org.apache.commons</groupid>
            <artifactid>commons-lang3</artifactid>
            <version>3.3.2</version>
        </dependency>

a、controller 控制器层

    /**
     * @return com.zheng.ucenter.common.constant.ucenterresult
     * @author fengfanli
     * @description //todo 导出全部
     * @date 17:40 2021/5/17
     * @param [param, request]
     **/
    @requestmapping(value = "/web/exportexcelformatchuser", method = requestmethod.post)
    public ucenterresult exportexcelformatchuser(httpservletresponse response) {

        try {
            matchusermodel model = new matchusermodel();
            model.setstart(0);
            model.setpagesize(10000);
            list<matchusermodel> allmatchusermodels = matchuserservice.getallmatchusermodels(model);
            // 导出 代码
            list<matchuserresp> result = new arraylist<>(allmatchusermodels.size());
            for (matchusermodel matchusermodel : allmatchusermodels) {
                matchuserresp resp = new matchuserresp();
                beanutils.copyproperties(matchusermodel, resp);
                resp.setcreatetime(datehander.datetostr1(matchusermodel.getcreatetime()));
                result.add(resp);
            }
            if (result.size()!=0){
                exportexcel exportexcel = new exportexcel("参赛人员", matchuserresp.class, 1);
                exportexcel.setdatalist(result);
                string filename = "match_user_" + datehander.datetostrd(new date()) + (new random().nextint(100 - 10) + 10) + ".xlsx";
                exportexcel.write(response, filename);
            }
            return new ucenterresult(ucenterresultconstant.success);
        } catch (exception e) {
            logger.error("matchusercontroller exportexcelformatchuser error:", e);
        }
        return new ucenterresult(ucenterresultconstant.failed);
    }

重点就是其中的五行：

b、pojo类matchuserresp类

这里使用到了自定义的注解类

import java.io.serializable;
import java.util.date;

/**
 * @classname matchuserresp
 * @description todo
 * @author admin
 * @date 2021/5/14 15:36
 * @version 1.0
 */
public class matchuserresp implements serializable {
    @excelfield(title = "序号", align = 1, sort = 1)
    private integer id;

    private long matchmainid;
    private long userid;

    @excelfield(title = "是否发送豆子（2：发送，1：未发送）", align = 1, sort = 2)
    private long sendflag;

    @excelfield(title = "比赛名称", align = 1, sort = 3)
    private string matchname;

    @excelfield(title = "用户名", align = 1, sort = 4)
    private string username;

    @excelfield(title = "手机号", align = 1, sort = 5)
    private string userphone;

    private string userwxhead;

    @excelfield(title = "收件地址", align = 1, sort = 6)
    private string receiveaddress;

    @excelfield(title = "邮箱", align = 1, sort = 7)
    private string email;

    private long matchareacodeid;

    @excelfield(title = "赛区名称", align = 1, sort = 8)
    private string matchareaname;

    @excelfield(title = "备注", align = 1, sort = 9)
    private string remark;

    private integer createuserid;
    private string createusername;

    @excelfield(title = "创建时间", align = 1, sort = 10)
    private string createtime;

    private integer dataflag;
    private integer useflag;
    private string timestamp;


    public long getsendflag() {
        return sendflag;
    }

    public void setsendflag(long sendflag) {
        this.sendflag = sendflag;
    }

    public long getuserid() {
        return userid;
    }

    public void setuserid(long userid) {
        this.userid = userid;
    }

    public integer getid() {
        return id;
    }

    public void setid(integer id) {
        this.id = id;
    }

    public long getmatchmainid() {
        return matchmainid;
    }

    public void setmatchmainid(long matchmainid) {
        this.matchmainid = matchmainid;
    }

    public string getmatchname() {
        return matchname;
    }

    public void setmatchname(string matchname) {
        this.matchname = matchname;
    }

    public string getusername() {
        return username;
    }

    public void setusername(string username) {
        this.username = username;
    }

    public string getuserphone() {
        return userphone;
    }

    public void setuserphone(string userphone) {
        this.userphone = userphone;
    }

    public string getuserwxhead() {
        return userwxhead;
    }

    public void setuserwxhead(string userwxhead) {
        this.userwxhead = userwxhead;
    }

    public string getreceiveaddress() {
        return receiveaddress;
    }

    public void setreceiveaddress(string receiveaddress) {
        this.receiveaddress = receiveaddress;
    }

    public string getemail() {
        return email;
    }

    public void setemail(string email) {
        this.email = email;
    }

    public long getmatchareacodeid() {
        return matchareacodeid;
    }

    public void setmatchareacodeid(long matchareacodeid) {
        this.matchareacodeid = matchareacodeid;
    }

    public string getmatchareaname() {
        return matchareaname;
    }

    public void setmatchareaname(string matchareaname) {
        this.matchareaname = matchareaname;
    }

    public string getremark() {
        return remark;
    }

    public void setremark(string remark) {
        this.remark = remark;
    }

    public integer getcreateuserid() {
        return createuserid;
    }

    public void setcreateuserid(integer createuserid) {
        this.createuserid = createuserid;
    }

    public string getcreateusername() {
        return createusername;
    }

    public void setcreateusername(string createusername) {
        this.createusername = createusername;
    }

    public string getcreatetime() {
        return createtime;
    }

    public void setcreatetime(string createtime) {
        this.createtime = createtime;
    }

    public integer getdataflag() {
        return dataflag;
    }

    public void setdataflag(integer dataflag) {
        this.dataflag = dataflag;
    }

    public integer getuseflag() {
        return useflag;
    }

    public void setuseflag(integer useflag) {
        this.useflag = useflag;
    }

    public string gettimestamp() {
        return timestamp;
    }

    public void settimestamp(string timestamp) {
        this.timestamp = timestamp;
    }
}

c、其余的工具类