前端做表格导出

发布时间：2023年12月18日

在这里插入图片描述

下面来介绍一下方法

在vue页面里写调用方法

//表头数据格式
column: [
      { key: 'Photo', width: '70', height: '50', colWidth: '100', title: '图片', type: 'image' },
      { key: 'Name', colWidth: '', title: '名称', type: 'text' },
      { key: 'Phone', colWidth: '', title: '手机号', type: 'text' },
      {
        key: 'Gender',
        colWidth: '55',
        title: '性别',
        type: 'select',
        options: [
          {
            Id: '1',
            Name: '男',
          },
          {
            Id: '0',
            Name: '女',
          },
        ],
        align: 'center',
        tag1: 'Id',
        tag2: 'Name',
      },
      { key: 'sfzh', colWidth: '200', title: '身份证号码', type: 'IDCard' },
    ],

table2excel({
        column: column,  //表头内容
        data: data, // 列表数据
        excelName: excelName, // 导出文件名
        captionName: captionName, // 导出表头名
      })

然后在js文件写大致逻辑

import { formatDateTime } from '/@/utils/formatTime';
/* eslint-disable */
let idTmr: any;
const getExplorer = () => {
  let explorer = window.navigator.userAgent;
  //ie
  if (explorer.indexOf("MSIE") >= 0) {
    return 'ie';
  }
  //firefox
  else if (explorer.indexOf("Firefox") >= 0) {
    return 'Firefox';
  }
  //Chrome
  else if (explorer.indexOf("Chrome") >= 0) {
    return 'Chrome';
  }
  //Opera
  else if (explorer.indexOf("Opera") >= 0) {
    return 'Opera';
  }
  //Safari
  else if (explorer.indexOf("Safari") >= 0) {
    return 'Safari';
  }
}
// 判断浏览器是否为IE
const exportToExcel = (data: any, name: any) => {

  // 判断是否为IE
  if (getExplorer() == 'ie') {
    tableToIE(data, name)
  } else {
    tableToNotIE(data, name)
  }
}

const Cleanup = () => {
  window.clearInterval(idTmr);
}

// ie浏览器下执行
const tableToIE = (data: any, name: any) => {
  let curTbl = data;
  let oXL = new ActiveXObject("Excel.Application");

  //创建AX对象excel
  let oWB = oXL.Workbooks.Add();
  //获取workbook对象
  let xlsheet = oWB.Worksheets(1);
  //激活当前sheet
  let sel = document.body.createTextRange();
  sel.moveToElementText(curTbl);
  //把表格中的内容移到TextRange中
  sel.select;
  //全选TextRange中内容
  sel.execCommand("Copy");
  //复制TextRange中内容
  xlsheet.Paste();
  //粘贴到活动的EXCEL中

  oXL.Visible = true;
  //设置excel可见属性

  try {
    let fname = oXL.Application.GetSaveAsFilename("Excel.xls", "Excel Spreadsheets (*.xls), *.xls");
  } catch (e) {
    // print("Nested catch caught " + e);
  } finally {
    oWB.SaveAs(fname);

    oWB.Close(savechanges = false);
    //xls.visible = false;
    oXL.Quit();
    oXL = null;
    // 结束excel进程，退出完成
    window.setInterval("Cleanup();", 1);
    idTmr = window.setInterval("Cleanup();", 1);
  }
}

// 非ie浏览器下执行
const tableToNotIE = (function () {
  // 编码要用utf-8不然默认gbk会出现中文乱码
  const uri = 'data:application/vnd.ms-excel;base64,',
    template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><meta charset="UTF-8"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>';

  const base64 = function (s) {
    return window.btoa(unescape(encodeURIComponent(s)));
  }

  const format = (s: any, c: any) => {
    return s.replace(/{(\w+)}/g,
      (m: any, p: any) => {
        return c[p];
      })
  }

  return (table: any, name: any) => {
    const ctx = {
      worksheet: name,
      table
    }

    const url = uri + base64(format(template, ctx));

    if (navigator.userAgent.indexOf("Firefox") > -1) {
      window.location.href = url
    } else {
      const aLink = document.createElement('a');
      aLink.href = url;
      aLink.download = name || '';
      let 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);
    }
  }
})()

const resolveOptions = (options: any) => {
  if (options.length === 1) {
    return options[0]
  }

  return {
    column: options[0] || [],
    data: options[1] || [],
    excelName: options[2] || '',
    captionName: options[3],
  }
}

// 导出函数
const table2excel = (...options: any) => {
  const typeMap: any = {
    image: getImageHtml, // 图片
    text: getTextHtml, // 文字
    select: getSelectHtml, // 下拉
    time: getTimetHtml, // 时间
    IDCard: getIDCardHtml // 身份证号码
  }

  const {
    column,
    data,
    excelName,
    captionName,
  } = resolveOptions(options)


  function getTextHtml(val: any, options: any) {
    val = val == null ? '' : val
    return `<td style="text-align: center">${val}</td>`
  }

  function getImageHtml(val: any, options: any) {
    options = Object.assign({ width: 40, height: 60 }, options)
    return `<td style="width: ${options.width}px; height: ${options.height}px; text-align: center; vertical-align: middle"><img src="${val}" width=${options.width * .99} height=${options.height * .99} />${val}</td>`
  }
  //这个是下拉菜单，得传入数据源
  function getSelectHtml(val: any, options: any) {
    let name = []
    let filterData = options.options.filter((i: any) => {
      if (options.tag1 && options.tag2) {
        if (i[options.tag1] == val) {
          return i
        }
      } else {
        if (i.key == val) {
          return i
        }
      }
    })
    if (filterData.length) {
      name = filterData.map((i: any) => {
        if (options.tag1 && options.tag2) {
          return i[options.tag2]
        } else {
          return i.value
        }
      }).join()
    }
    return `<td style="text-align: center">${name}</td>`
  }
  function getTimetHtml(val: any, options: any) {
    return `<td style="text-align: center">${formatDateTime(val)}</td>`
  }
  function getIDCardHtml(val: any, options: any) {
    return `<td style="text-align: center;mso-number-format:'\\@'">${val}</td>`
  }
  let caption = captionName ? `<caption style="font-weight:bold">${captionName}</caption>` : '';

  let thead = column.reduce((result: any, item: any) => {
    result += `<th>${item.title}</th>`
    return result
  }, '')

  thead = `<thead><tr>${thead}</tr></thead>`

  let tbody = data.reduce((result: any, row: any) => {
    const temp = column.reduce((tds: any, col: any) => {
      let options: any = {}
      if (col.type === 'image') {
        if (row.width && row.height) {
          options.width = row.width
          options.height = row.height
        } else {
          col.width && (options.width = col.width)
          col.height && (options.height = col.height)
        }
      } else if (col.type === 'select') {
        options = col
      }
      tds += typeMap[col.type || 'text' || 'select' || 'time' || 'IDCard'](row[col.key], options)
      return tds
    }, '')
    result += `<tr>${temp}</tr>`
    return result
  }, '')

  tbody = `<tbody>${tbody}</tbody>`

  const table = caption + thead + tbody

  // 导出表格
  exportToExcel(table, excelName)
}

export default table2excel
文章来源:https://blog.csdn.net/weixin_45289656/article/details/135060878
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
最新文章