下载excel报表

本文总结出三种目前比较常见的excel报表导出方式

1. 后端将excel二进制文件流返回给前端，前端使用URL.createObjectURL(obj)生成指向二进制文件流的url，利用a标签或者window.open()导出。如果想前端收到文件流后自动下载，就改用form表单发起请求。另外，前端如何知道影响体结束了呢？

   • 使用HTTP 协议的 Transfer-Encoding: chunked 分段传输特性，确保文件流传输完后，关闭tcp链接
   • 使用HTTP 协议的 content-length 指定文件流大小，前端获取到改大小的文件流后自动关闭tcp链接
2. 后端生成excel报表，存到服务器上，然后将报表链接返回到前端，使用a标签或者window.open()导出
3. 后端返回表格数据，前端将表格数据渲染到隐藏的table标签中，然后将table解析成excel，使用URL.createObjectURL(obj)生成指向二进制文件流的url，利用a标签或者window.open()导出

HTTP 协议中的 Transfer-Encoding与content-length

二进制数据转换

一、处理二进制excel导出

<template>
  <el-button type="primary" size="small" @click="doExport">导出</el-button>
  // 利用表单请求到二进制文件流后会自动下载
  <form action="/excel/exportExcel" method="post">
      <input type="submit" value="提交"/>
  </form>
</template>

<script>
import { exportRequest } from '@/service/request'

export default {
  name: 'BinaryExcel',
  data () {
    return {}
  },
  methods: {
    /**
     * @description excel导出
     * @param data excel二进制数据
     * @param name 文件名
     */
    exportExcel (data, name = 'excel导出') {
      if (window.navigator && window.navigator.msSaveOrOpenBlob) {
        // 兼容IE11
        window.navigator.msSaveOrOpenBlob(data, name + '.xls')
      } else {
        const link = document.createElement('a')
        // xlxs格式
        const blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' })

        // xls格式
        // const blob = new Blob([data], { type: 'application/vnd.ms-excel' })
        link.style.display = 'none'
        link.href = URL.createObjectURL(blob)
        link.setAttribute('download', name + '.xlsx')
        // document.body.appendChild(link)
        link.click()
        // document.body.removeChild(link)
      }
    },
    // 导出excel
    doExport () {
      exportRequest('/excel/exportExcel', {}).then(res => {
        this.exportExcel(res.data)
      })
    }
  }
}
</script>

<style scoped>

</style>

var express = require('express');
var router = express.Router();
var nodeExcel = require('excel-export') // 用于将数据集导出到Excel xlsx文件
router.post('/exportExcel', async (req, res, next) => {
  // 模拟从数据库获取表格数据
  var tableData = [
    { name: '张三', age: 20, school: '中南财经政法大学', date: '2020/12/12', safe: true },
    { name: '李四', age: 22, school: '华中科技大学', date: '2020/12/12', safe: true },
    { name: '王二', age: 23, school: '武汉大学', date: '2020/12/12', safe: true }
  ]
  var conf ={};
  conf.name = "mysheet";
  conf.cols = [{
    caption:'姓名',
    type:'string',
  },{
    caption:'年龄',
    type:'number',
  },{
    caption:'学校',
    type:'string',
    width: 50
  },{
    caption:'入学时间',
    type:'string',
    width: 50
  },{
    caption:'体温是否正常',
    type:'bool',
    width: 50
  }];
  conf.rows = tableData.reduce((init, item) => {
    init.push([item.name, item.age, item.school, item.date, item.safe])
    return init
  }, [])
  var result = nodeExcel.execute(conf);
  // xlsx格式
  res.setHeader('Content-Type', 'application/vnd.openxmlformats;charset=utf-8');
  res.setHeader("Content-Disposition", "attachment; filename=" + "Report.xlsx");

  // xls格式
  // res.setHeader('Content-Type', 'application/vnd.ms-excel;charset=utf-8');
  // res.setHeader("Content-Disposition", "attachment; filename=" + "Report.xls");
  res.end(result, 'binary');
})

二、服务端生成excel

<template>
  <el-button type="primary" size="small" @click="doExport">导出</el-button>
</template>

<script>
import { post } from '@/service/request'

export default {
  name: 'LinkExcel',
  data () {
    return {}
  },
  methods: {
    doExport () {
      post('/excel/linkExcel', {}).then(res => {
        console.log(res)
        window.open(res.data.downloadUrl) // 下载excel文件
      })
    }
  }
}
</script>

var express = require('express');
var router = express.Router();
var xlsx = require('node-xlsx') // 服务端生成excel
var fs = require('fs')
var path = require('path')
router.post('/linkExcel', async (req, res, next) => {
  var tableData = [
    { name: '张三', age: 20, school: '中南财经政法大学', date: '2020/12/12', safe: true },
    { name: '李四', age: 22, school: '华中科技大学', date: '2020/12/12', safe: true },
    { name: '王二', age: 23, school: '武汉大学', date: '2020/12/12', safe: true }
  ]
  var title = ['姓名', '年龄', '学校', '入学日期', '体温是否正常']
  var excelData = []
  excelData = excelData.concat([title], tableData.reduce((init, item) => {
    init.push([item.name, item.age, item.school, item.date, item.safe])
    return init
  }, []))
  var buffer = xlsx.build([
    {
      name: 'mySheet', // 文件名
      data: excelData // 二维数组
    }
  ])
  var timeStamp = new Date().getTime()
  fs.writeFileSync(`../source/${timeStamp}.xlsx`, buffer, {'flag':'w'})
  res.json({
    code: 1,
    downloadUrl: path.resolve(__dirname, `../source/${timeStamp}.xlsx`)
  })
})

三、前端解析table生成excel导出

<template>
  <div>
    <el-button type="primary" size="small" @click="doExport">导出</el-button>
    <table id="table" style="display: none;">
      <thead>
        <tr>
          <th>姓名</th>
          <th>年龄</th>
          <th>学校</th>
          <th>入学日期</th>
          <th>体温是否正常</th>
        </tr>
      </thead>
      <tbody v-html="tbodyHtml">
      </tbody>
    </table>
  </div>
</template>

<script>
import FileSaver from 'file-saver'
import XLSX from 'xlsx'
import { post } from '@/service/request'

export default {
  name: 'DOMExcel',
  data () {
    return {
      tbodyHtml: ''
    }
  },
  mounted () {
    this.getData()
  },
  methods: {
    getData () {
      post('/excel/domExcel', {}).then(res => {
        if (res.data.code === 1) {
          var tbodyHtml = ''
          res.data.rows.forEach(e => {
            tbodyHtml += '<tr>'
            tbodyHtml += '<td>' + e.name + '</td>'
            tbodyHtml += '<td>' + e.age + '</td>'
            tbodyHtml += '<td>' + e.school + '</td>'
            tbodyHtml += '<td>' + e.date + '</td>'
            tbodyHtml += '<td>' + e.safe + '</td>'
            tbodyHtml += '<tr>'
          })
          this.tbodyHtml = tbodyHtml
        }
      })
    },
    doExport () {
      let wb = XLSX.utils.table_to_book(document.querySelector('#table'))
      /* get binary string as output */
      let wbout = XLSX.write(wb, {bookType: 'xlsx', bookSST: true, type: 'array'})
      try {
        FileSaver.saveAs(new Blob([wbout], {type: 'application/octet-stream'}), 'excel导出.xlsx')
      } catch (e) {
        if (typeof console !== 'undefined') {
          console.log(e, wbout)
        }
      }
      return wbout
    }
  }
}
</script>

var express = require('express');
var router = express.Router();
router.post('/domExcel', async (req, res, next) => {
  var tableData = [
    { name: '张三', age: 20, school: '中南财经政法大学', date: '2020/12/12', safe: true },
    { name: '李四', age: 22, school: '华中科技大学', date: '2020/12/12', safe: true },
    { name: '王二', age: 23, school: '武汉大学', date: '2020/12/12', safe: true }
  ]
  res.json({
    code: 1,
    rows: tableData
  })
})

项目git地址：https://github.com/Revelation...