Vue導(dǎo)出excel的兩個(gè)常用方式介紹與對(duì)比

更新時(shí)間：2025年01月28日 08:53:11 作者：java馮堅(jiān)持

這篇文章主要為大家詳細(xì)介紹了Vue導(dǎo)出excel的兩個(gè)常用方式,分別為前端vue+XLSX導(dǎo)出excel和vue+后端POI?導(dǎo)出excel,感興趣的小伙伴可以了解下

一、需求分析

1. 兩個(gè)方式

導(dǎo)出excel有兩個(gè)方式，前端導(dǎo)出和后端導(dǎo)出

前端導(dǎo)出excel：就用 vue+XLSX（npm 包）

后端導(dǎo)出excel：就用 vue+POI（maven 包）

2. 對(duì)比分析

前端導(dǎo)出excel 相對(duì)來說簡單一點(diǎn)，XLSX是前端 npm 包，但是如果數(shù)據(jù)量大的話，會(huì)卡頓，處理時(shí)間慢；當(dāng)數(shù)據(jù)量多的時(shí)候使用后端導(dǎo)出會(huì)好一點(diǎn)

后端導(dǎo)出excel 相對(duì)來說麻煩一點(diǎn)，但是時(shí)間短、速度快；具體操作都放在后端，也節(jié)省了前端的操作。用戶效果好。

二、方式1：vue+XLSX

1. 安裝 XLSX

npm install xlsx file-saver --save

2. XLSX 兩個(gè)方法比較

這個(gè)XLSX 方法一和下面的XLSX 方法二都是使用的 XLSX 模塊的方法，只是獲取數(shù)據(jù)的方式和導(dǎo)出excel的方式有點(diǎn)不一樣。
相比之下，還是 XLSX 方法一會(huì)好一點(diǎn)，可以自定義導(dǎo)出的字段。

3. XLSX 方法一

a. 按鈕事件

<el-button size="small" type="primary" @click="exportSelect()">導(dǎo)出選中</el-button>
<el-button size="small" type="primary" @click="exportAllExcel">導(dǎo)出全部</el-button>

其實(shí) 上面的二方法可以做成一個(gè)方法，但是為了明確好分析，我寫成了二個(gè)方法。

b. js 方法:導(dǎo)出選中 exportSelect()

      exportSelect() {
        //  導(dǎo)出選中
        this.$confirm("是否確認(rèn)導(dǎo)出當(dāng)前所有參賽人員數(shù)據(jù)?", "警告", {
          confirmButtonText: "確定",
          cancelButtonText: "取消",
          type: "warning",
        }).then((response) => {
          //  this.selectdata 是復(fù)選框的 數(shù)據(jù)列表
          if (this.selectdata.length <= 0) {
            this.$message.info('請(qǐng)選擇數(shù)據(jù)！');
            return false;
          }
          let tableData = [
            ['序號(hào)', '賽區(qū)名稱', '參賽人', '手機(jī)號(hào)', '收件地址', "郵箱", "錄入時(shí)間",  "狀態(tài)"]//導(dǎo)出表頭
          ] // 表格表頭
          this.selectdata.forEach((item, index) => {
            let rowData = []
            //導(dǎo)出內(nèi)容的字段
            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 方法:導(dǎo)出全部 exportAllExcel

      // 導(dǎo)出 所有
      exportAllExcel() {
        this.$confirm("是否確認(rèn)導(dǎo)出全部參賽人員數(shù)據(jù)?", "警告", {
          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("系統(tǒng)問題，請(qǐng)稍后重試！")
          })
      },
      exportList(arr){
        let tableData = [
          ['序號(hào)', '賽區(qū)名稱', '參賽人', '手機(jī)號(hào)', '收件地址', "郵箱", "錄入時(shí)間",  "審核狀態(tài)", "是否發(fā)送豆子"]//導(dǎo)出表頭
        ] // 表格表頭
        arr.forEach((item, index) => {
          let rowData = []
          //導(dǎo)出內(nèi)容的字段
          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">導(dǎo)出當(dāng)前頁</el-button>

b. js 方法:導(dǎo)出當(dāng)前頁 exportExcel

這里是

// 導(dǎo)出當(dāng)前頁
      exportExcel() {
        this.$confirm("是否確認(rèn)導(dǎo)出當(dāng)前所有參賽人員數(shù)據(jù)?", "警告", {
          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"}),
                    "參賽人員數(shù)據(jù).xlsx"
            );
          } catch (e) {
            if (typeof console !== "undefined") console.log(e, wbout);
          }
        });
      },

二、方式2：vue+POI

這個(gè)方式也就是后端生成excel，與前端沒有多大的關(guān)系，后端寫好的 excel就直接write到 response里面了。
先直接放上前端代碼。

1. 前端代碼

a、按鈕事件

<el-button size="small" type="primary" @click="exportAllExcel">導(dǎo)出全部</el-button>

b、網(wǎng)絡(luò)請(qǐng)求封裝

// 導(dǎo)出全部
export function exportExcelForMatchUser(data) {
  return fetch({
    url: '/xfx/matchUser/web/exportExcelForMatchUser',
    method: 'post',
    timeout: '120000',
    responseType: 'blob',
    data
  });
}

c、js方法：導(dǎo)出全部 exportAllExcel

      // 導(dǎo)出 所有
      exportAllExcel() {
        this.$confirm("是否確認(rèn)導(dǎo)出全部參賽人員數(shù)據(jù)?", "警告", {
          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); // 創(chuàng)建下載的鏈接
              downloadElement.href = href;
              downloadElement.download = data; // 下載后文件名
              document.body.appendChild(downloadElement);
              downloadElement.click(); // 點(diǎn)擊下載
              document.body.removeChild(downloadElement); // 下載完成移除元素
              window.URL.revokeObjectURL(href); // 釋放掉blob對(duì)象
            }
          }).catch(err => {
            console.log(err)
            this.loading = false;
            this.$message.warning("對(duì)不起，下載失敗");
          });
        })
      },

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>

???????        <!--這個(gè)不屬于 poi ，就是一個(gè)工具類-->
        <!-- 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 導(dǎo)出全部
     * @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);
            // 導(dǎo)出 代碼
            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);
    }

重點(diǎn)就是其中的五行：

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 = "序號(hào)", align = 1, sort = 1)
    private Integer id;

    private Long matchMainId;
    private Long userId;

    @ExcelField(title = "是否發(fā)送豆子（2：發(fā)送，1：未發(fā)送）", 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 = "手機(jī)號(hào)", 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 = "賽區(qū)名稱", align = 1, sort = 8)
    private String matchAreaName;

    @ExcelField(title = "備注", align = 1, sort = 9)
    private String remark;

    private Integer createUserId;
    private String createUserName;

    @ExcelField(title = "創(chuàng)建時(shí)間", 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、其余的工具類