前端原生下载excel表格

ABC123220

已于 2022-09-18 23:24:18 修改

阅读量795

点赞数 1

文章标签：前端 javascript java

于 2022-09-18 14:28:13 首次发布

本文链接：https://blog.csdn.net/ABC123220/article/details/126916787

版权

方式一

// html 部分--------
  <button @click="downloadExcel()">下载excel</button>

// js 代码----------
  const download = (data, name) => {
    let a = document.createElement('a')
    const result = data.map(v => {
      return Object.values(v).join('\t')
    }).join('\n')
    console.log(result )

    a.href = URL.createObjectURL(new Blob([result]))
    a.setAttribute('download', name)
    a.click()
  }

  const downloadExcel = () => {
    download([
      { id: 'ID', name: '姓名', age: '年龄' }, // 表头
      { id: 1, name: '张三', age: '18' },
      { id: 2, name: '李四', age: '19' },
      { id: 3, name: '王五', age: '20' },
    ], '用户信息.xls')
  }

控制台打印log（result）

方式二：下载csv表格数据

后台返回数据：

const tableData = [
    {id:1,name: "张三",age:18,sex:'男'},
    {id:2,name: "李四",age:19,sex:'男'},
    {id:3,name: "王五",age:20,sex:'女'},
    {id:4,name: "赵六",age:21,sex:'男'},
]

功能

// html 部分--------
  <button @click="downloadExcel()">下载excel</button>

// js 代码----------

function downloadExcel() {
  if (tableData.length > 0) {
    const headerMap = {  // 表头中英文映射，同时可以限定下载那些列的设局
      name: '姓名',
      age: '年龄',
      sex: '性别',
    }
    const str = commonArrToString(tableData, headerMap) + '\n'
    createLink(str, '用户信息')
  }
}


// ----------------
const createLink = (str, name) => {
  const uri = 'data:text/csv;charset=utf-8,\ufeff' + encodeURIComponent(str)
  // 通过创建a标签实现
  const link = document.createElement('a')
  link.href = uri
  // 对下载的文件命名
  link.download = `${name}.csv`
  link.click()
}

// ----------------
commonArrToString = (tableData, headerMap) => {
  let str = ''
  const columns = Object.keys(headerMap)
  str += Object.values(headerMap).join(',') + '\n' // 替换英文

  // 增加\t为了不让表格显示科学计数法或者其他格式
  for (let i = 0; i < tableData.length; i++) {
    for (let j = 0; j < columns.length; j++) {
      let fliterColumn = tableData[i][columns[j]] + ''
      str += `${fliterColumn + '\t'},`
    }
    str += '\n'
  }
  return str
}

方式三：可定制的excel导出

// html 部分--------
  <button @click="downloadExcel()">下载excel</button>

// js 代码----------
  /*
    可定制的excel导出
    1. 字体颜色和背景颜色
    2.宽度设置
    3.单元格合并
    4.支持html元素
  */ 
  function downloadExcel() {
    JSONToExcelConvertor([
      { id: 1, name: { value: '<i>张三</i>', color: 'red' }, age: 22 },
      { id: 2, name: { value: '李四', backgroundColor: 'red' }, age: 22 },
      { id: 3, name: {value:'王五',width:100}, age: 22 },
      { id: 4, name: {value:'赵六',rowspan:2}, age: 22 },
      { id: 5, age: 22 },
    ], '用户信息', [
      'ID', '姓名', '年龄'
    ])
  }
  function JSONToExcelConvertor(JSONData, FileName, ShowLabel) {
    const arrData = typeof JSONData !== 'object' ? JSON.parse(JSONData) : JSONData

    let excel = '<table>'
    var row = '<tr>'
    for (var i = 0, l = ShowLabel.length; i < l; i++) {
      if (typeof ShowLabel[i] === 'object') {
        row += `<th ${ShowLabel[i].width ? `width=${ShowLabel[i].width}` : ''}>` + ShowLabel[i].value + '</th>'
      } else {
        row += '<th>' + ShowLabel[i] + '</th>'
      }
    }
    excel += row + '</tr>'

    for (var i = 0; i < arrData.length; i++) {
      var row = '<tr>'
      let labelIndex = 0
      for (const index in arrData[i]) {
        const value = arrData[i][index] === '.' ? '' : arrData[i][index]
        if (typeof value === 'object') {
          row +=
            `<td style="text-align:center;background-color:${value.backgroundColor};color:${value.color}" ${value.rowspan ? `rowspan=${value.rowspan}` : ''
            } ${value.colspan ? `colspan=${value.colspan}` : ''} ${value.width ? `width=${value.width}` : ''} >` +
            value.value +
            '</td>'
        } else {
          row += '<td style="text-align:center">' + value + '</td>'
        }
        labelIndex++
      }
      excel += row + '</tr>'
    }

    excel += '</table>'

    let excelFile =
      "<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'>"
    excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">'
    excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel'
    excelFile += '; charset=UTF-8">'
    excelFile += '<head>'
    excelFile += '<!--[if gte mso 9]>'
    excelFile += '<xml>'
    excelFile += '<x:ExcelWorkbook>'
    excelFile += '<x:ExcelWorksheets>'
    excelFile += '<x:ExcelWorksheet>'
    excelFile += '<x:Name>'
    excelFile += '{worksheet}'
    excelFile += '</x:Name>'
    excelFile += '<x:WorksheetOptions>'
    excelFile += '<x:DisplayGridlines/>'
    excelFile += '</x:WorksheetOptions>'
    excelFile += '</x:ExcelWorksheet>'
    excelFile += '</x:ExcelWorksheets>'
    excelFile += '</x:ExcelWorkbook>'
    excelFile += '</xml>'
    excelFile += '<![endif]-->'
    excelFile += '</head>'
    excelFile += '<body>'
    excelFile += excel
    excelFile += '</body>'
    excelFile += '</html>'

    const uri = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(excelFile)

    const link = document.createElement('a')
    link.href = uri

    link.style = 'visibility:hidden'
    link.download = FileName + '.xls'

    document.body.appendChild(link)
    link.click()
    document.body.removeChild(link)
  }