vue导出表格/内容到EXCEL

npm install xlsx
npm install xlsx-js-style

导入使用

import * as XLSX from 'xlsx'
import * as XLSXSTYLE from 'xlsx-js-style'

一、直接导出table表格

<BasicTable ref="tableRef" @register="registerTable">
       <template #toolbar>
        <a-button
          preIcon="ant-design:arrow-down-outlined"
          v-auth="'fqzlyxtz_export'"
          @click="exportlDataFun"
        >
          导出数据
        </a-button>
      </template>
    </BasicTable>

const tableDom = tableRef.value?.$el;
      const clonedElement = tableDom.cloneNode(true);
      if (clonedElement) {
        // 删除一些用不到的行和列
        const antTableTbody = clonedElement.querySelector('.ant-table-tbody').firstElementChild;
        const theadChildNodes = clonedElement.querySelector('.ant-table-header thead').firstElementChild.childNodes[2];
        antTableTbody.parentNode.removeChild(antTableTbody);
        theadChildNodes.parentNode.removeChild(theadChildNodes);
        let wb = XLSX.utils.table_to_book(clonedElement,{sheet:'Sheet1',raw:true,cellStyles: true});
        //边框样式
        let borderStyle = {
          top: {
            style: "thin",
            color: {
              rgb: "000000"
            }
          },
          bottom: {
            style: "thin",
            color: {
              rgb: "000000"
            }
          },
          left: {
            style: "thin",
            color: {
              rgb: "000000"
            }
          },
          right: {
            style: "thin",
            color: {
              rgb: "000000"
            }
          }
        }
        let alignmentStyle = {
          vertical: 'center', // 垂直居中
          horizontal: 'center', // 水平居中
          wrapText: 1,//自动换行
        }
        // 列宽
        wb.Sheets.Sheet1['!cols'] = [
            { wch: 10 },
            { wch: 20 },
            { wch: 20 },
            { wch: 10 },
            { wch: 10 }, // 压差
            { wch: 15 },
            { wch: 15 },
            { wch: 15 },
            { wch: 15 }, // VOCs出口浓度
            { wch: 10 },
            { wch: 10 },
            { wch: 15 },
            { wch: 15 },
            { wch: 15 },
            { wch: 15 },
            { wch: 20 },
            { wch: 20 },
            { wch: 20 },
            { wch: 25 },
            { wch: 30 },
        ]
        // 每行高
        wb.Sheets.Sheet1['!rows'] = [
            { hpx: 25 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
            { hpx: 20 },
        ]
        for(let key in wb.Sheets.Sheet1){
          if(!key.includes('!')){
            if(key != 'A2'){
              wb.Sheets.Sheet1[key]['s'] = {border: borderStyle,alignment:alignmentStyle};
            }else{
              wb.Sheets.Sheet1[key]['s'] = {border: borderStyle};
            }
            
          }
        }
        wb.Sheets.Sheet1 = addRangeBorder( wb.Sheets.Sheet1['!merges'], wb.Sheets.Sheet1)
        XLSXSTYLE.writeFile(wb, '导出文件.xlsx');
      }

二、导出json到表格

// 准备表格数据，每个索引就是一行
  const data:any = [
    ['原辅材料台账'],
    ["序号","所属公司", "材料名称", "材料成分", "含量", "供货厂家","月初库存量","采购时间","采购量（吨/次）","使用量（吨/月）","回收量（吨/月）","回收方式","废弃量（吨/月）","库存量（吨/月）","创建时间"],
  ];
  // 获取要导出的数据
  const { records } = await getData(params.value);
  for(let i = 0;i<records.length;i++){
    const item = records[i];
    const data = await getCompanyById({id:item.companyId});
    item['companyName'] = data.companyName;
  }
    // 把数据push到data数组中
  records.forEach((item, index) => {
    data.push([
        index+1,
        item.companyName, 
        item.vocsMateralName, 
        item.materalIngredients, 
        item.vocsVolume +' %',
        item.supplier,
        item.monthBeginInventory+' 吨',
        item.procureTime,
        item.procureVolume + ' 吨/次',
        item.useVolume + ' 吨/月',
        item.recoveryVolume + ' 吨/月',
        item.recoveryMode,
        item.discardVolume + ' 吨/月',
        item.inventoryVolume + ' 吨/月',
        item.createTime
        ]);
    });
    // 创建工作簿和工作表
    const wb = XLSX.utils.book_new();
    const worksheet = XLSX.utils.aoa_to_sheet(data);
    XLSX.utils.book_append_sheet(wb, worksheet, 'Sheet1');
      //边框样式
      let borderStyle = {
        top: {
          style: "thin",
          color: {
            rgb: "000000"
          }
        },
        bottom: {
          style: "thin",
          color: {
            rgb: "000000"
          }
        },
        left: {
          style: "thin",
          color: {
            rgb: "000000"
          }
        },
        right: {
          style: "thin",
          color: {
            rgb: "000000"
          }
        }
      }
      let alignmentStyle = {
        vertical: 'center', // 垂直居中
        horizontal: 'center', // 水平居中
        wrapText: 1,//自动换行
      }
      // 列宽
      wb.Sheets.Sheet1['!cols'] = [
          { wch: 10 },
          { wch: 30 },
          { wch: 20 },
          { wch: 20 },
          { wch: 15 },
          { wch: 15 },
          { wch: 20 },
          { wch: 15 },
          { wch: 25 },
          { wch: 25 },
          { wch: 20},
          { wch: 20 },
          { wch: 20 },
          { wch: 20 },
          { wch: 20 },
          { wch: 20 },
          { wch: 20 },
          { wch: 20 },
          { wch: 25 },
          { wch: 30 },
      ]
      // 每行高
      wb.Sheets.Sheet1['!rows'] = [
          { hpx: 25 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
          { hpx: 20 },
      ]
      for(let key in wb.Sheets.Sheet1){
        if(!key.includes('!')){
          wb.Sheets.Sheet1[key]['s'] = {border: borderStyle,alignment:alignmentStyle};
        }
      }
      // 设置合并行/列，对象e：代表合并结束，s：代表合并开始，r:行号，c列号
      wb.Sheets.Sheet1['!merges'] = [{e: {r: 0, c: 14},s: {r: 0, c: 0}}]
      wb.Sheets.Sheet1 = addRangeBorder( wb.Sheets.Sheet1['!merges'], wb.Sheets.Sheet1)
      XLSXSTYLE.writeFile(wb, '导出文件.xlsx');

备注：合并的行/列，直接加边框加不上，需要补充缺失的行\列：

function addRangeBorder (range, ws) {
  let cols = ["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 style = {
      s: {
        border: {
          top: { style: 'thin' },
          left: { style: 'thin' },
          bottom: { style: 'thin' },
          right: { style: 'thin' }
        }
      }
    }
    // 处理合并行
    for (let i = item.s.c; i <= item.e.c; i++) {
      ws[`${cols[i]}${Number(item.e.r) + 1}`] = ws[`${cols[i]}${Number(item.e.r) + 1}`] || {t:'s',v:'',...style}
      // 处理合并列
      for (let k = item.s.r + 2; k <= item.e.r + 1; k++) {
        ws[cols[i] + k] = ws[cols[k] + item.e.r] || {t:'s',v:'',...style}
      }
    }
  })
  return ws;
}