vue实现导出excel模板

使用 vue-json-excel 插件

安装 vue-json-excel 插件

npm install vue-json-excel --save

在 main.js 中引入并注册组件

import Vue from 'vue'
import JsonExcel from 'vue-json-excel'

Vue.component('downloadExcel', JsonExcel)

在组件中使用

<download-excel
  :data="json_data"
  :fields="json_fields"
  name="filename.xls">
  <button>导出 Excel</button>
</download-excel>

export default {
  data() {
    return {
      json_fields: {
        '姓名': 'name',
        '年龄': 'age',
        '地址': 'address'
      },
      json_data: [
        {
          name: '张三',
          age: 25,
          address: '北京'
        },
        {
          name: '李四',
          age: 30,
          address: '上海'
        }
      ]
    }
  }
}

使用 xlsx 和 file-saver 库

安装所需依赖

npm install xlsx file-saver --save

创建导出方法

import * as XLSX from 'xlsx'
import FileSaver from 'file-saver'

export default {
  methods: {
    exportExcel() {
      const data = [
        ['姓名', '年龄', '地址'],
        ['张三', 25, '北京'],
        ['李四', 30, '上海']
      ]

      const ws = XLSX.utils.aoa_to_sheet(data)
      const wb = XLSX.utils.book_new()
      XLSX.utils.book_append_sheet(wb, ws, 'Sheet1')

      const wbout = XLSX.write(wb, { bookType: 'xlsx', type: 'array' })
      FileSaver.saveAs(
        new Blob([wbout], { type: 'application/octet-stream' }),
        '导出数据.xlsx'
      )
    }
  }
}

使用模板文件导出

准备 Excel 模板文件并上传到项目静态资源目录

实现模板填充功能

import * as XLSX from 'xlsx'

export default {
  methods: {
    async fillTemplate() {
      const response = await fetch('/static/template.xlsx')
      const arrayBuffer = await response.arrayBuffer()

      const workbook = XLSX.read(arrayBuffer)
      const worksheet = workbook.Sheets[workbook.SheetNames[0]]

      // 填充数据到模板
      worksheet['B2'].v = '张三'
      worksheet['C2'].v = 25
      worksheet['D2'].v = '北京'

      const wbout = XLSX.write(workbook, { bookType: 'xlsx', type: 'array' })
      FileSaver.saveAs(
        new Blob([wbout], { type: 'application/octet-stream' }),
        '填充后的模板.xlsx'
      )
    }
  }
}

后端配合导出方案

前端请求导出接口

export default {
  methods: {
    exportFromBackend() {
      axios.get('/api/export/excel', {
        responseType: 'blob'
      }).then(response => {
        const url = window.URL.createObjectURL(new Blob([response.data]))
        const link = document.createElement('a')
        link.href = url
        link.setAttribute('download', '后端导出数据.xlsx')
        document.body.appendChild(link)
        link.click()
      })
    }
  }
}

后端示例(Node.js)

const excel = require('exceljs')

router.get('/api/export/excel', async (req, res) => {
  const workbook = new excel.Workbook()
  const worksheet = workbook.addWorksheet('Sheet1')

  worksheet.columns = [
    { header: '姓名', key: 'name' },
    { header: '年龄', key: 'age' },
    { header: '地址', key: 'address' }
  ]

  worksheet.addRow({ name: '张三', age: 25, address: '北京' })
  worksheet.addRow({ name: '李四', age: 30, address: '上海' })

  res.setHeader(
    'Content-Type',
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
  )
  res.setHeader(
    'Content-Disposition',
    'attachment; filename=export.xlsx'
  )

  await workbook.xlsx.write(res)
  res.end()
})