vue2 elementui封装导出按钮功能 导出列表数据 excel

1.首先安装xlsx

2.建一个js文件 命名为exportExcel.js

import XLSX from "xlsx";

/**
 * @name: 导出excel
 * @param {导出文件名} fileName
 * @param {需要导出的sheet页} sheet
 * @return {*}
 */
function exportExcel(fileName, sheet) {
  // 创建一个新的空的workbook
  var wb = XLSX.utils.book_new();
  XLSX.utils.book_append_sheet(wb, sheet, "sheet1");
  const workbookBlob = workbook2blob(wb);
  openDownloadDialog(workbookBlob, fileName);
}

function workbook2blob(workbook) {
  // 生成excel的配置项
  var wopts = {
    // 要生成的文件类型
    bookType: "xlsx",
    // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
    bookSST: false,
    type: "binary",
  };
  var wbout = XLSX.write(workbook, wopts);
  // 将字符串转ArrayBuffer
  function s2ab(s) {
    var buf = new ArrayBuffer(s.length);
    var view = new Uint8Array(buf);
    for (var i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
    return buf;
  }
  let buf = s2ab(wbout);
  var blob = new Blob([buf], {
    type: "application/octet-stream",
  });
  return blob;
}
// 将blob对象 创建bloburl,然后用a标签实现弹出下载框
function openDownloadDialog(blob, fileName) {
  if (typeof blob === "object" && blob instanceof Blob) {
    blob = URL.createObjectURL(blob); // 创建blob地址
  }
  var aLink = document.createElement("a");
  aLink.href = blob;
  // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，有时候 file:///模式下不会生效
  aLink.download = fileName || "";
  var event;
  if (window.MouseEvent) event = new MouseEvent("click");
  //   移动端
  else {
    event = document.createEvent("MouseEvents");
    event.initMouseEvent(
      "click",
      true,
      false,
      window,
      0,
      0,
      0,
      0,
      0,
      false,
      false,
      false,
      false,
      0,
      null
    );
  }
  aLink.dispatchEvent(event);
}
export default exportExcel;

3.新建一个ExportTable.vue文件

<template>
  <div @click="handleDownload" style="display: inline-block">
    <slot
      ><el-button type="primary" size="small"
        ><i class="el-icon-download"></i> 导出</el-button
      ></slot
    >
  </div>
</template>
<script>
import XLSX from "xlsx";
import exportExcel from "./exportExcel.js";
export default {
  name: "ExportTable",
  data() {
    return {
      letterList: [],
    };
  },
  mounted() {
    this.getLetterList();
  },
  methods: {
    getLetterList() {
      const letterList = [];
      for (let i = 0; i < 26; i++) {
        const char = String.fromCharCode(65 + i);
        letterList.push(char);
      }
      this.letterList = letterList;
    },
    handleDownload() {
      // 通过回调函数去获取下载的相关参数
      this.$emit("download", ({ list, fileName, headerDisplay = {} }) => {
        const tableList = Array.isArray(list) ? list : [];
        const newData = [headerDisplay, ...tableList]; //添加标题行
        const header = Object.keys(headerDisplay);
        const sheet = XLSX.utils.json_to_sheet(newData, {
          header: header,
          skipHeader: true, //不需要标题行（该标题行是指变量名那一行）
        });
        const realMaxColLength = Object.keys(headerDisplay).length; // 不能直接使用header的长度去获取，因为header最后会补全数据
        const berforeMaxCol = sheet["!ref"]
          .split(":")[1]
          .replace(/[^A-Z]/gi, ""); // 找出原渲染范围最大的列
        let afterMaxCol = this.letterList[realMaxColLength - 1];
        if (realMaxColLength > 26) {
          const a = Math.floor(realMaxColLength / 26); // 商
          const b = realMaxColLength % 26; // 余数
          afterMaxCol = `${this.letterList[a - 1]}${this.letterList[b - 1]}`; // 超过26列时，由列由两个字母组成
        }
        sheet["!ref"] = sheet["!ref"].replace(berforeMaxCol, afterMaxCol); // 修改excel的渲染范围
        // 设置标题行样式
        for (let c = 0; c < realMaxColLength; c++) {
          const cell = `${this.letterList[c]}1`;
          if (!sheet[cell]) {
            sheet[cell] = {};
          }
          sheet[cell].s = {
            font: {
              bold: true,
            },
            alignment: {
              vertical: "center",
            },
          };
        }
        const nowDate = new Date().toLocaleDateString().split("/").join("-");
        exportExcel(`${fileName}_${nowDate}.xlsx`, sheet);
      });
    },
  },
};
</script>

4.使用案例

<template>
  <div>
    <ExportTable @download="getDownloadList"></ExportTable>
  </div>
</template>
<script>

import ExportTable from "@/components/ExportTable.vue";
export default {
  name: "developerList",
  components: { ExportTable },
  data() {
    return {
      columns: 
        {
          id: "ID",
          account: "账号",
          name: "姓名",
          age: "年龄",
        },
    };
  },

  methods: {
    async getDownloadList(callback) {
      let headerDisplay = this.columns;
      let list=[{id:1,account:"123",name:'赵华',age:25},{id:2,account:"456",name:'张华',age:26}]
      callback({
        fileName: "导出表名",
        headerDisplay,
        list,
      });
  },
  },
};
</script>
<style lang="scss" scoped>
/deep/ .el-select{
  width: 100%;
}
</style>