vue导出element表格，xlsx和xlsx-style生成xlsx文件并修改样式

1.下载依赖

npm install xlsx --save
npm install file-saver --save
npm install xlsx-style --save

2.先修改xlsx-style的源码，一旦引入xlsx-style则会报错

xlsx-style使用中常见问题及解决办法：

xlsx-style使用中常见问题及解决办法-CSDN博客

在\node_modules\xlsx-style\dist\cpexcel.js 807行 的 var cpt = require(’./cpt’ + ‘able’); 改成 var cpt = cptable;
但是如果每次打包之前去修改包里的代码很麻烦所以我去node_modules包里找到了这个文件

把这个xlsx.full.min.js复制一份出来，粘贴到项目内，我是放到了public的index里面如图：

3.在页面使用，这里是使用element表格，用id绑定后，再用dom获取该节点，然后导出数据添加样式

<template>
  <div>
    <el-table :id="'mytable'" :data="tableData" style="width: 100%">
      <el-table-column prop="date" label="日期" width="150"> </el-table-column>
      <el-table-column label="配送信息">
        <el-table-column prop="name" label="姓名" width="120">
        </el-table-column>
        <el-table-column label="地址">
          <el-table-column prop="province" label="省份" width="120">
          </el-table-column>
          <el-table-column prop="city" label="市区" width="120">
          </el-table-column>
          <el-table-column prop="address" label="地址" width="300">
          </el-table-column>
          <el-table-column prop="zip" label="邮编" width="120">
          </el-table-column>
        </el-table-column>
      </el-table-column>
    </el-table>
    <button @click="setExport2Excel">按钮</button>
  </div>
</template>

<script>
import { utils } from "xlsx";
export default {
  data() {
    return {
      tableData: [
        {
          date: "2016-05-03",
          name: "王小虎",
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-02",
          name: "王小虎",
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-04",
          name: "王小虎",
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-01",
          name: "王小虎",
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-08",
          name: "王小虎",
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-06",
          name: "王小虎",
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
        {
          date: "2016-05-07",
          name: "王小虎",
          province: "上海",
          city: "普陀区",
          address: "上海市普陀区金沙江路 1518 弄",
          zip: 200333,
        },
      ],
    };
  },
  methods: {
    setExport2Excel() {
      var xlsxParam = { raw: true }; //这个保证表格只进行解析 不做运算
      var wb = utils.table_to_sheet(
        document.querySelector("#mytable"),
        xlsxParam
      ); 
      for (var i = 0; i < 11; i++) {
        wb["!cols"][i] = { wpx: 130 };
      }
      // 样式的文档地址
      // https://www.npmjs.com/package/xlsx-style
      for (const key in wb) {
        if (key.indexOf("!") === -1 && wb[key].v) {
          wb[key].s = {
            font: {
              //字体设置
              sz: 13,
              bold: false,
              color: {
                rgb: "000000", //十六进制，不带#
              },
            },
            alignment: {
              //文字居中
              horizontal: "center",
              vertical: "center",
              wrap_text: true,
            },
            border: {
              // 设置边框
              top: { style: "thin" },
              bottom: { style: "thin" },
              left: { style: "thin" },
              right: { style: "thin" },
            },
          };
        }
      }
      var data = this.addRangeBorder(wb["!merges"], wb) //合并项添加边框
      var filedata = this.sheet2blob(data)
      this.openDownloadDialog(filedata,this.todayTimeString + "-xxx报表.xlsx")
    },
    //为合并项添加边框
    addRangeBorder(range, ws) {
      let arr = [
        "A",
        "B",
        "C",
        "D",
        "E",
        "F",
        "G",
        "H",
        "I",
        "J",
        "K",
        "L",
        "M",
        "N",
        "O",
        "P",
        "Q",
        "R",
        "S",
        "T",
        "U",
        "V",
        "W",
        "X",
        "Y",
        "Z",
      ];
      if (range) {
        range.forEach((item) => {
          let startColNumber = Number(item.s.r),
            endColNumber = Number(item.e.r);
          let startRowNumber = Number(item.s.c),
            endRowNumber = Number(item.e.c);
          const test = ws[arr[startRowNumber] + (startColNumber + 1)];
          for (let col = startColNumber; col <= endColNumber; col++) {
            for (let row = startRowNumber; row <= endRowNumber; row++) {
              ws[arr[row] + (col + 1)] = test;
            }
          }
        });
      }
      return ws;
    },
    //将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
    sheet2blob(sheet, sheetName) {
      sheetName = sheetName || "sheet1";
      var workbook = {
        SheetNames: [sheetName],
        Sheets: {},
      };
      workbook.Sheets[sheetName] = sheet; // 生成excel的配置项

      var wopts = {
        bookType: "xlsx", // 要生成的文件类型
        bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
        type: "binary",
      };
      var wbout = XLSX.write(workbook, wopts);
      var blob = new Blob([s2ab(wbout)], {
        type: "application/octet-stream",
      }); // 字符串转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;
      }
      return blob;
    },
    openDownloadDialog(url, saveName) {
      if (typeof url == "object" && url instanceof Blob) {
        url = URL.createObjectURL(url); // 创建blob地址
      }
      var aLink = document.createElement("a");
      aLink.href = url;
      aLink.download = saveName || ""; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
      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);
    },
  },
};
</script>