vue中Table表格转为Excel文件的方法

首先要下载file-saver插件、xlsx插件和xlsx-style插件

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

公用组件封装

/* eslint-disable */
import { saveAs } from 'file-saver'
import XLSX2 from "xlsx"
import XLSX from "xlsx-style"

export function export_table_to_excel(data, title) {
  let sheets = []
  data.forEach(item => {
    let sheetObj = {data: {}, sheetName: 'sheet1'}, s= {};
    s = create_table_to_excel(item.id, item.title, 18, item.obj)
    sheetObj.data = s
    sheetObj.sheetName = item.obj.sheetName
    sheets.push(sheetObj)
  })
  var filedata = sheet2blob(sheets)
  openDownloadDialog(filedata, (title || '表格') + ".xlsx")
}

function textTrim() {

}

// export function export_table_to_excel(tableId, tableName, dataLen, obj) {
function create_table_to_excel(tableId, tableName, dataLen, obj) {
  const realName = !tableName ? 'table' : tableName
  var xlsxParam = { raw: true } // 只读excel,不做计算
  var wb = XLSX2.utils.table_to_sheet(document.querySelector("#" + tableId), xlsxParam)
  if(!wb['!merges']){
    this.$message.warning('无法导出：报表无数据');
    return
  }
  // 设置样式

  // 列宽
  wb['!cols'] = []
  let tableLen = getRelTableLen(wb) // 表的实际行数 (表头 + dataLen)
  const headLen = tableLen - dataLen // 表的表头行数
  for (const key in wb) {
    if(key.indexOf('!') === -1 && wb[key].v){
      let thFlag = false
      for (var i = 1; i <= headLen; i++) {
        if (key.lastIndexOf('' + i) === (key.length - (i + '').length) &&
          key.indexOf(i + '0') < 0 &&
          key.indexOf('0' + i) < 0 &&
          key.indexOf('1' + i) < 0 &&
          key.indexOf('2' + i) < 0 &&
          key.indexOf('3' + i) < 0 &&
          key.indexOf('4' + i) < 0 &&
          key.indexOf('5' + i) < 0 &&
          key.indexOf('6' + i) < 0 &&
          key.indexOf('7' + i) < 0 &&
          key.indexOf('8' + i) < 0 &&
          key.indexOf('9' + i) < 0
        ) {
          thFlag = true
        }
      }
      let fontType = fontChange(wb[key])
      let bgType = bgChange(wb[key])
      wb[key].s = {
        font:{ // 字体设置
          name: fontType.family || '仿宋',
          sz: fontType.size || 14,
          bold: fontType.textBold || false,
          color: {
            rgb: '000000' // 十六进制，不带#
          }
        },
        fill: { // 背景色
          fgColor: {
            rgb: bgType || 'FFFFFF'
          } 
        },
        alignment: { // 文字居中
          horizontal: fontType.horizontal || 'left',
          vertical: 'center',
          wrapText: true,
        },
        border: { // 设置边框
          top: { style: 'thick', color:{ rgb: '000000' } },
          bottom: { style: 'thick', color:{ rgb: '000000' } },
          left: { style: 'thick', color:{ rgb: '000000' } },
          right: { style: 'thick', color:{ rgb: '000000' } }
        },
      }
    }
  }
  // 行高
  wb['!rows'] = [

  ]

  // 打印方向
  wb['!pageSetup'] = {
    scale: obj.scale,
    orientation: 'landscape'
}
  let styles = {
    hs: {
      font:{ // 字体设置
        name: '黑体',
        sz: 26,
      },
      alignment: { // 文字居中
        horizontal: 'center',
        vertical: 'center',
        wrap_text: true
      },
    },
    ts: {
      font:{ // 字体设置
        name: 'SimHei',
        sz: 14,
      },
      alignment: { // 文字居中
        horizontal: 'center',
        vertical: 'center',
        wrap_text: true
      },
    }
  }
  let styles2 = {
    hs: {
      alignment: { // 文字居中
        horizontal: 'right',
        vertical: 'bottom'
      },
    }
  }
  wb['A1'].s = styles.hs;
  wb['A2'].s = styles2.hs;
  for(var i = 0; i<43; i++){
    // const curWpx = i == 0 ? 40 : 120
    wb['!cols'][i] = { wpx: obj.Tdwidth }
    if((i == 17) && obj.name == '1') {
      wb['!cols'][i] = { wpx: 70 }
    }
    
  }
  if(obj.name == '1'){
    excel1(wb)
  } else if(obj.name == '2') {
    excel2(wb)
  } else if(obj.name == '3') {
    excel3(wb)
  }
  console.log('ssssss', wb)
  
  for(var i = 0; i < 43; i++) {
    wb['!rows'][i] = { hpx: obj.TdHeight }
    if(obj.name == '2' && i == 2) {
      wb['!rows'][i] = { hpx: 300 }
    }
  }
  
  var data = addRangeBorder(wb['!merges'], wb) //合并项添加边框
  return data

  // var filedata = sheet2blob(data, obj.title)
  // openDownloadDialog(filedata, realName + ".xlsx")
}

function excel1(wb) {
  for(var i = 0; i<43; i++){
    if(i == 0 || i == 9 || i == 16) {
      wb['!cols'][i] = { wpx: 30 }
    }
    if(i == 10) {
      wb['!cols'][i] = { wpx: 65 }
    }
    if(i == 17 || i == 1) {
      wb['!cols'][i] = { wpx: 90 }
    }
  }
}

function excel2(wb) {
  for(var i = 0; i<43; i++){
    if(i == 0 || i == 3 || i==4 || i == 15 || i == 20 || i == 21 || i == 27 || i == 28 || i == 29 || i==33) {
      wb['!cols'][i] = { wpx: 30 }
    }
    if(i == 5 || i == 6 || i == 7 || i == 9 || i == 10 || i == 11 || i == 12 || i == 14 || i == 16 || i == 17 || i == 18 || i == 22 || i == 23 || i == 24
      || i == 25 || i==30 || i==31 || i==32 || i==33 || i == 34 || i == 35 || i == 36 || i == 37 || i == 38 || i == 39 || i == 41) {
      wb['!cols'][i] = { wpx: 60 }
    }
    if(i == 42 || i == 13) {
      wb['!cols'][i] = { wpx: 70 }
    }
  }
}

function excel3(wb) {
  for(var i = 0; i<43; i++){
    if(i == 0) {
      wb['!cols'][i] = { wpx: 25 }
    }
    if(i == 1) {
      wb['!cols'][i] = { wpx: 45 }
    }
    if(i == 2) {
      wb['!cols'][i] = { wpx: 245 }
    }
  }
}

function fontChange(val) {
  // console.log('类别==', val)
  let name = val.v, fontObj = {size: 14, family: '仿宋',horizontal: 'center',textBold: true}
  if(name == '类别' || name == '住宿信息' || name == '供餐信息' || name == '食品、食品原材料供应及安全监测信息' || name == '基本信息' || name == '动态信息' || name == '序号'
  || name == '东城区'|| name == '西城区'|| name == '朝阳区'|| name == '海淀区'|| name == '丰台区'|| name == '石景山区'|| name == '门头沟区'|| name == '房山区'|| name == '通州区'
  || name == '顺义区'|| name == '昌平区'|| name == '大兴区'|| name == '平谷区'|| name == '怀柔区'|| name == '密云区'|| name == '延庆区'|| name == '经开区'|| name == '合计') {
    return fontObj
  } else {
    return ''
  }
}

function bgChange(val) {
  let name = val.v, color1 = 'EBF1DE', color2 = 'C4D79B'
  if(name == '基本信息') {
    return color1
  } else if(name == '动态信息') {
    return color2
  } else {
    return ''
  }
}

// 获取表实际的行数
function getRelTableLen(wb) {
  const str = wb['!ref'].split(':')[1]
  const arr = [...str]
  const numArr = arr.filter(item => !Number.isNaN(Number.parseInt(item))).join('')
  return Number.parseInt(numArr)
}

//为合并项添加边框
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","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK",
            "AL","AM","AN","AO","AP","AQ","AR"];
  console.log('range===', range)
  if(!range) return
  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下载
function sheet2blob(sheet, sheetName) {
  // sheetName = sheetName || 'sheet1';
  // var workbook = {
  //   SheetNames: [sheetName],
  //   Sheets: {}
  // };
  // workbook.Sheets[sheetName] = sheet; // 生成excel的配置项
  
  var workbook = {
    SheetNames: [],
    Sheets: {}
  };
  sheet.forEach((item,i) => {
    workbook.SheetNames.push(item.sheetName)
    workbook.Sheets[item.sheetName] = item.data
  })

  var wopts = {
    bookType: 'xlsx', // 要生成的文件类型
    bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
    type: 'binary',
    border: 'none'
  };
  
  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;
}

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);
}