vue +element-ui 导出 Excel表格

1 下载相关依赖

1 npm install xlsx@0.17.0 下载固定版本(目前这个可以)
2 npm install xlsx-style 

2 在 src/utils/下新建一个 htmlToExcel.js 文件

一下代码直接放到 htmlToExcel.js 文件

import XLSX from "xlsx";
import XlsxStyle from "xlsx-style";
import { Notification, Message } from 'element-ui';
/**
 * 导出表格方法
 * @param id 需要导出的table的id 
 * @param type 表格类型（默认表头不合并） 默认 normal，合并 merge
 * @param fileName 导出表格的名称 
 * 
**/
const exportExcel = function (id, type, fileName) {
  let loadingText = ".";
  let notif = Notification({
    message: fileName + " " + loadingText,
    duration: 0,
    showClose: false,
    type: 'info'
  });
  let loadingTimer = setInterval(() => {
    if (loadingText == ".") {
      loadingText = ".."
    } else if (loadingText == "..") {
      loadingText = "..."
    } else if (loadingText == "...") {
      loadingText = "...."
    } else if (loadingText == "....") {
      loadingText = "."
    }
    notif.message = fileName + " " + loadingText;
  }, 500)
  let leftArr = [];
  let rightArr = [];
  let dateArr = [];
  let numArr = [];
  let date = new Date();
  let today = date.toLocaleString().split(' ')[0];
  let todayArr = today.split('/');
  todayArr[1] = todayArr[1] < 10 ? '0' + todayArr[1] : todayArr[1];
  todayArr[2] = todayArr[2] < 10 ? '0' + todayArr[2] : todayArr[2];
  today = todayArr.join('');
  var filedata
  if (!id) {
    //id参数不能为空
    Message({
      message: "参数id不能为空！",
    });
  } else {
    var xlsxParam = { raw: true }; //这个保证表格只进行解析 不做运算
    var wb = XLSX.utils.table_to_sheet(
      document.querySelector(id),
      xlsxParam
    ); //id为导出的table的id，通过参数获取
    setExlStyle(wb, [leftArr, rightArr, dateArr, numArr]); //设置excel样式

    if (!type) {
      //type类型不能为空
      Message({
        message: "参数type不能为空！",
      });
    } else if (type == 'normal') {
      // 常规表格执行-无合并单元格
      filedata = sheet2blob(wb);
    } else if (type == 'merge') {
      // 表格有合并单元格时执行-只支持表头合并单元格
      let data = addRangeBorder(wb['!merges'], wb) //合并项添加边框
      filedata = sheet2blob(data)
    }
    // 有无合并单元格都要执行
    openDownloadDialog(filedata, fileName ? fileName + ".xlsx" : today + ".xlsx");
    clearInterval(loadingTimer);
    notif.close();
    Notification({
      type: 'success',
      message: fileName,
    });
  }
};
//将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
function 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 = XlsxStyle.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;
}
//excel命名+下载
function 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);
}
// 设置导出excel样式
function setExlStyle(data, setArr) {
  // 提取参数-第一组数组为左对齐表头name、第二组为右对齐表头name、
  // 第三组为日期类型表头name、第四组为数字类型表头name
  if (!setArr[0]) setArr[0] = []; // 不存在左对齐时为空
  if (!setArr[1]) setArr[1] = []; // 不存在右对齐时为空
  if (!setArr[2]) setArr[2] = []; // 不存在日期格式设置时为空
  if (!setArr[3]) setArr[3] = []; // 不存在数字格式设置时为空
  let leftArr = []//左对齐列数组
  let rightArr = []//右对齐列数组
  let dateArr = []//日期格式数组
  let numArr = []//数字格式数组

  //设置列宽度
  for (var i = 0; i < 50; i++) {
    data["!cols"][i] = { wpx: 180 };
  }
  // https://www.npmjs.com/package/xlsx-style-样式的文档地址
  //设置通用样式
  let styleAll = {
    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" },
    },
  };
  // console.log(Object.keys(data));
  let keys = Object.keys(data);
  let headerKeys = [];
  keys.forEach((ele) => {
    if (ele.indexOf('1') != -1 && ele.length < 3) {
      headerKeys.push(ele)
    }
  })
  let lastKey = headerKeys[headerKeys.length - 1];

  for (const key in data) {
    if (key.indexOf("!") === -1 && data[key].v) {
      // 给指定单元格添加特定样式-如下代码以添加背景色为例
      if (key.split("")[1] === "1" && key.split("").length === 2) {
        var styleS = JSON.stringify(styleAll); //必须要转一下格式，否则styleAll也会跟着styleS一起改变
        styleS = JSON.parse(styleS);
        styleS.fill = {
          fgColor: { rgb: "EEF4FA" }, //设置背景色
        };
        data[key].s = styleS;
        // 根据参数-匹配表头内容-组成左、右对齐、规定格式列字母数组
        let keyVal = data[key].v;
        if (setArr[0].length > 0) { // 左对齐
          setArr[0].forEach((ele) => {
            if (ele == keyVal) {
              leftArr.push(key.split("")[0])
            }
          })
        }
        if (setArr[1].length > 0) { // 右对齐
          setArr[1].forEach((ele) => {
            if (ele == keyVal) {
              rightArr.push(key.split("")[0])
            }
          })
        }
        if (setArr[2].length > 0) { // 日期格式
          setArr[2].forEach((ele) => {
            if (ele == keyVal) {
              dateArr.push(key.split("")[0])
            }
          })
        }
        if (setArr[3].length > 0) { // 数字格式
          setArr[3].forEach((ele) => {
            if (ele == keyVal) {
              numArr.push(key.split("")[0])
            }
          })
        }
      } else {
        //其他单元格设置通用样式
        data[key].s = styleAll;
        let keyLet = key.split('')[0];

        //设置日期格式
        if (dateArr.length > 0) {
          dateArr.forEach((ele) => {
            if (ele == keyLet) {
              //设置指定列数据类型-t表示类型，s是字符串，n是数字,d是日期类型
              data[key].t = 'd'
              data[key].z = 'yyyy-mm-dd HH:mm:ss'
              data[key].s = styleAll;
            }
          })
        }
        //设置数字格式
        if (numArr.length > 0) {
          numArr.forEach((ele) => {
            if (ele == keyLet) {
              //设置指定列数据类型-t表示类型，s是字符串，n是数字,d是日期类型
              data[key].t = 'n'
              data[key].z = '0.00'
              data[key].s = styleAll;
            }
          })
        }
        // 设置左对齐
        if (leftArr.length > 0) {
          let styleL = JSON.stringify(styleAll); //必须要转一下格式，否则styleAll也会跟着styleL一起改变
          styleL = JSON.parse(styleL);
          styleL.alignment = {
            horizontal: "left",
          };
          leftArr.forEach((ele) => {
            if (ele == keyLet) {
              data[key].s = styleL;
            }
          })
        }
        // 设置右对齐
        if (rightArr.length > 0) {
          let styleR = JSON.stringify(styleAll); //必须要转一下格式，否则styleAll也会跟着styleR一起改变
          styleR = JSON.parse(styleR);
          styleR.alignment = {
            horizontal: "right",
          };
          rightArr.forEach((ele) => {
            if (ele == keyLet) {
              data[key].s = styleR;
            }
          })
        }

      }
    } else {
      if (key.indexOf("!") === -1) {
        if (lastKey != key) {
          data[key].s = "";
          data[key].s = styleAll;
        }
      }
    }
  }
}
// 存在合并单元格的表格添加边框-只对表头有效
function 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",
  ];
  range.forEach((item) => {
    let startRowNumber = Number(item.s.c),
      endRowNumber = Number(item.e.c);
    for (let i = startRowNumber + 1; i <= endRowNumber; i++) {
      const name = "A3";
      ws[name] = {
        s: {
          border: {
            top: { style: "thin" },
            left: { style: "thin" },
            bottom: { style: "thin" },
            right: { style: "thin" },
          },
        },
      };
      ws[arr[i] + (Number(item.e.r) + 1)] = {
        s: {
          border: {
            top: { style: "thin" },
            left: { style: "thin" },
            bottom: { style: "thin" },
            right: { style: "thin" },
          },
        },
      };
    }
  });
  // console.log(ws)
  return ws;
}

export default exportExcel

3 引入 xlsx-style 包的时候会报错 需要改一下依赖包

** 1 在页面中找到这两个文件修改**

node_modules/xlsx/dist、cpexcel.js 文件 代码替换

val cpt = require('./cpt' + 'able')  改为  val cpt = cptable

** node_modules/xlsx-style/dist/xlsx.js文件的 write_ws_xml_data方法替换一下代码**

function write_ws_xml_data(ws, opts, idx, wb) {
                var o = [], r = [], range = safe_decode_range(ws['!ref']), cell, ref, rr = "", cols = [], R, C,rows = ws['!rows'];
                for(C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C);
                for(R = range.s.r; R <= range.e.r; ++R) {
                    r = [];
                    rr = encode_row(R);
                    for(C = range.s.c; C <= range.e.c; ++C) {
                        ref = cols[C] + rr;
                        if(ws[ref] === undefined) continue;
                        if((cell = write_ws_xml_cell(ws[ref], ref, ws, opts, idx, wb)) != null) r.push(cell);
                    }
                    if(r.length > 0){
                  params = ({r:rr});
                  if(rows && rows[R]) {
                    row = rows[R];
                    if(row.hidden) params.hidden = 1;
                    height = -1;
                    if (row.hpx) height = px2pt(row.hpx);
                    else if (row.hpt) height = row.hpt;
                    if (height > -1) { params.ht = height; params.customHeight = 1; }
                    if (row.level) { params.outlineLevel = row.level; }
                  }
                  o[o.length] = (writextag('row', r.join(""), params));
                }
                }
              if(rows) for(; R < rows.length; ++R) {
                if(rows && rows[R]) {
                  params = ({r:R+1});
                  row = rows[R];
                  if(row.hidden) params.hidden = 1;
                  height = -1;
                  if (row.hpx) height = px2pt(row.hpx);
                  else if (row.hpt) height = row.hpt;
                  if (height > -1) { params.ht = height; params.customHeight = 1; }
                  if (row.level) { params.outlineLevel = row.level; }
                  o[o.length] = (writextag('row', "", params));
                }
              }
                return o.join("");
            }

4 在页面中使用