纯前端导出xlsx文件

纯前端导出xlsx文件

1.utile文件中新建downloadXlsx.js

import xlsx from "xlsx"  //版本 "xlsx": "^0.14.5"
import xlsxStyle from "xlsx-style" // "xlsx-style": "^0.8.13"
 
export function downloadXlsx (fileName = '', dataTable = [], colsWpxList = [], titleObj = {}, titleStyleObj = {}, mergesList = []) {
  //生成表格
  var sheet = xlsx.utils.json_to_sheet(dataTable);
  var tmpdata = xlsx.utils.sheet_add_json(sheet, dataTable, { origin: "A2" });
  // 设置每个单元格宽度
  tmpdata["!cols"] = colsWpxList
  //给A1单元格赋值
  tmpdata["A1"] = titleObj
  // 设置A1标题字体样式
  tmpdata["A1"].s = titleStyleObj
  //单元格合并
  tmpdata["!merges"] = mergesList
  openDownloadDialog(sheet2blob(tmpdata), `${fileName}.xlsx`);
}
 
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;
}
 
function openDownloadDialog(url, saveName) {
  let href = URL.createObjectURL(url)
  let a = document.createElement('a');
  a.href = href;
  a.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
  document.body.appendChild(a)
  a.click()
  document.body.removeChild(a)
  setTimeout(() => {
    URL.revokeObjectURL(url)
  }, 200);
}

2.使用

timeSlotResult (result) {
      const loading = this.$loading({
        lock: true,
        text: 'Loading',
        spinner: 'el-icon-loading',
        background: 'rgba(0, 0, 0, 0.7)'
      })
      if (result) {
        this.exportTrackData // 导出的table数据
        let fieldKeys = ['currTime', 'lineName', 'lineType', 'busName', 'drvName', 'busStateDesc', 'busSpeed', 'currMile', 'staName']
        let fieldLabels = ['时间', '线路', '上下行', '车牌号/自编号', '司机', '状态', '速度(km/h)', '里程(km)', '位置']
        let fileName = `${this.exportTrackParams.busName}在${this.exportTrackParams.startTime}至${this.exportTrackParams.endTime}轨迹回放数据统计`
        let dataTable = []
        for (let i in this.exportTrackData) {
          let obj = {
            '时间': this.exportTrackData[i].currTime ? this.exportTrackData[i].currTime : '',
            '线路': this.exportTrackData[i].lineName ? this.exportTrackData[i].lineName : '',
            '上下行': this.exportTrackData[i].lineType ?  this.exportTrackData[i].lineType : '',
            '车牌号/自编号': this.exportTrackData[i].busName ? this.exportTrackData[i].busName : '',
            '司机': this.exportTrackData[i].drvName ? this.exportTrackData[i].drvName : '',
            '状态': this.exportTrackData[i].busStateDesc ? this.exportTrackData[i].busStateDesc : '',
            '速度(km/h)': this.exportTrackData[i].busSpeed ? this.exportTrackData[i].busSpeed : '',
            '里程(km)': this.exportTrackData[i].currMile ? this.exportTrackData[i].currMile : '',
            '位置': this.exportTrackData[i].staName ? this.exportTrackData[i].staName : '',
          }
          dataTable.push(obj);
        }
        let colsWpxList = [
          { wpx: 150 }, 
          { wpx: 120 }, 
          { wpx: 120 }, 
          { wpx: 150 }, 
          { wpx: 120 }, 
          { wpx: 120 }, 
          { wpx: 120 }, 
          { wpx: 120 },
          { wpx: 120 },
        ]
        let titleObj = {
          t: "s",
          v: fileName
        }
        let titleStyleObj = {
          font: {
            name: '宋体',
            sz: 20,
            bold: true,
          },
          alignment: {
            horizontal: "center",
            vertical: "center",
            wrap_text: true
          },
        }
        let mergesList = [
          {
            //合并单元格 index都从0开始
            s: { // start 开始
              c: 0, //开始列
              r: 0 //开始行
            },
            e: { // end 结束
              c: fieldKeys.length - 1, //结束列
              r: 0 //结束行
            }
          }
        ]
        downloadXlsx (fileName, dataTable, colsWpxList, titleObj, titleStyleObj, mergesList)
      }
      loading.close()
    }

注意: 需要在webpack配置 ① 或者vue.config.js中配置 ②

①
externals: {
‘./cptable’: ‘var cptable’
}
②
configureWebpack: {
externals: [{
‘./cptable’: ‘var cptable’
}]
},