526互联

vue导出带样式的excel

发布时间 2023-09-13 11:52:39作者: 刘国微

示例说明
实现导出excel不同块的颜色

template
a标签仅做文件名称调整，默认为隐藏状态；

<template>
  <div>
    <button @click="exportExcel">导出</button>
    <a ref="export_a" />
  </div>
</template>

script

export default {
  name: 'Excel',
  data() {
    return {
      table: [
        { name: '张三', age: 20, sex: '男', amount: 100 },
        { name: '李四', age: 20, sex: '女', amount: -50 },
        { name: '王五', age: 20, sex: '男', amount: 20 },
        { name: '赵六', age: 20, sex: '女', amount: -30 },
        { name: '田七', age: 20, sex: '女', amount: -30 },
        { name: '曹八', age: 20, sex: '女', amount: 60 },
        { name: '刘九', age: 20, sex: '男', amount: -20 },
        { name: '孙十', age: 20, sex: '男', amount: 50 },
        { name: '魏十一', age: 20, sex: '女', amount: 25 }
      ]
    }
  },
  methods: {
    exportExcel() {
      // 列标题
      let table = '<table border="1" style="font-family: SimSun;font-size:16px"><tr><td>姓名</td><td>年龄</td><td>性别</td><td>净资产</td></tr>'
      this.table.forEach(row => {
        var color = ''
        if (row.amount > 0) {
          color = 'green '
        } else {
          color = 'red '
        }
        table += `<tr><td>${row.name}</td><td>${row.age}</td><td>${row.sex}</td><td style="background-color:${color}">${row.amount}</td></tr>`
      })
      table += '</table>'
      // 下载的表格模板数据
      const 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><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>
        <x:Name>sheet名称</x:Name>
        <x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>
        </x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
        </head><body>${table}</body></html>`
      // 下载模板
      this.$refs.export_a.href = `data:application/vnd.ms-excel;base64,${window.btoa(unescape(encodeURIComponent(template)))}`
      this.$refs.export_a.download = `excel名称`
      this.$refs.export_a.click()
    }
  }
}