xlsx、xlsx-style、file-saver导表

vue 利用xlsx、xlsx-style、file-saver实现前端导出excel表格 (包括设置单元格居中、边框等样式) antdesignvue、elementui、vxetable 等都适用

导出的excel：

首先要引入三个库：

npm install file-saver --save

npm install xlsx --save

npm install xlsx-style --save

为了防止代码一致却有运行错误的情况 这里再列出我开发时用的插件的版本号：

"file-saver": "^2.0.5",
"xlsx": "^0.17.0",
"xlsx-style": "^0.8.13"
全部安装好后引入插件：

import XLSX from "xlsx";
import XLSXStyle from "xlsx-style";
import FileSaver from "file-saver";
先根据不同的框架渲染好表格的页面，外层套一层div：

代码

<template>
  <a-card :bordered="false">
    <a-button @click="exportToExcel" >导出</a-button>
    <div id="exportData"  class="css_page_body" ref="css_page_body">
      <a-table :columns="columns"
               :data-source="data"
               bordered
               :pagination="false">
      </a-table>

    </div>
  </a-card>
</template>

<script>
import XLSX from "xlsx";
import XLSXStyle from "xlsx-style";
import FileSaver from "file-saver";

export default {
  data () {
    return {
      columns:[
        {
          title: '学校', dataIndex: 'school', width: '25%',
          scopedSlots: { customRender: 'school' }, align: 'center', key: 'school',
          customRender (_, row) {
            return {
              children: row.school,
              attrs: {
                rowSpan: row.schoolRowSpan
              }
            }
          }
        },
        {
          title: '年级', dataIndex: 'grade', width: '25%',
          scopedSlots: { customRender: 'grade' }, align: 'center', key: 'grade',
          customRender (_, row) {
            return {
              children: row.grade,
              attrs: {
                rowSpan: row.gradeRowSpan
              }
            }
          }
        },
        {
          title: '班级', dataIndex: 'class', width: '25%',
          scopedSlots: { customRender: 'class' }, align: 'center'
        },
        {
          title: '姓名', dataIndex: 'name', width: '25%',
          scopedSlots: { customRender: 'name' }, align: 'center'
        },
        {
          title: '4月1日',
          children: [
            {
              title: '周五',
              dataIndex: 'companyAddress',
              width: 100,
            }
          ],
        },
        {
          title: '4月1日',
          children: [
            {
              title: '周五',
              dataIndex: 'companyAddress2',
              width: 100,
            }
          ],
        },
        {
          title: '4月1日',
          children: [
            {
              title: '周五',
              dataIndex: 'companyAddress3',
              width: 100,
            }
          ],
        },
        {
          title: '4月1日',
          children: [
            {
              title: '周五',
              dataIndex: 'companyAddress4',
              width: 100,
            }
          ],
        },
      ],
      data:[

      ]
    }
  },
  methods: {
    // 合并单元格
    rowSpan (key, data) {
      const arr = data
        .reduce((result, item) => {
          if (result.indexOf(item[key]) < 0) {
            result.push(item[key])
          }
          return result
        }, [])
        .reduce((result, keys) => {
          const children = data.filter(item => item[key] === keys)
          result = result.concat(
            children.map((item, index) => ({
              ...item,
              [`${key}RowSpan`]: index === 0 ? children.length : 0
            }))
          )
          return result
        }, [])
      return arr
    },
    // 表格合并
    mergeRowCell (data) {
      let tableData = this.rowSpan('school', data)
      tableData = this.rowSpan('grade', tableData)
      this.data = tableData

    },
    exportToExcel () {
      let ws = XLSX.utils.table_to_sheet(document.getElementById('exportData'))
      let ws2 = XLSX.utils.table_to_sheet(document.getElementById('exportData'))
      //创建一个workbook对象
      let wb = XLSX.utils.book_new()
      //把worksheet对象添加进workbook对象，第三个参数是excel中sheet的名字
      XLSX.utils.book_append_sheet(wb, ws, '月度统计报表')
      XLSX.utils.book_append_sheet(wb, ws2, '隔离库')
      this.setExlStyle(wb['Sheets']['月度统计报表']); // 设置列宽 字号等
      this.addRangeBorder(wb['Sheets']['月度统计报表']['!merges'],wb['Sheets']['月度统计报表'])
      let wb_out = XLSXStyle.write(wb, { type: 'buffer'})

      try {
        FileSaver.saveAs(new Blob([wb_out], {
          type: 'application/octet-stream'
        }), 'WMS统计报表.xlsx');   //trade-publish.xlsx 为导出的文件名
      } catch (e) {
        console.log(e, wb_out) ;
      }
      return wb_out;
    },
    setExlStyle(data) {
      let borderAll = {  //单元格外侧框线
        top: {
          style: 'thin',
        },
        bottom: {
          style: 'thin'
        },
        left: {
          style: 'thin'
        },
        right: {
          style: 'thin'
        }
      };
      data['!cols'] = [];
      for (let key in data) {
        // console.log(key)
        if (data[key] instanceof Object) {
          data[key].s = {
            border: borderAll,
            alignment: {
              horizontal: 'center',   //水平居中对齐
              vertical:'center'
            },
            font:{
              sz:11
            },
            bold:true,
            numFmt: 0
          }
          data['!cols'].push({wpx: 115});
        }
      }
      return data;
    },
    addRangeBorder (range, ws) {
      let cols = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];
      range.forEach(item => {
        console.log(item)
        let style = {
          s: {
            border: {
              top: { style: 'thin' },
              left: { style: 'thin' },
              bottom: { style: 'thin' },
              right: { style: 'thin' }
            }
          }
        }
        // 处理合并行
        for (let i = item.s.c; i <= item.e.c; i++) {
          ws[`${cols[i]}${Number(item.e.r) + 1}`] = ws[`${cols[i]}${Number(item.e.r) + 1}`] || style
          // 处理合并列
          for (let k = item.s.r + 2; k <= item.e.r + 1; k++) {
            ws[cols[i] + k] = ws[cols[k] + item.e.r] || style
          }
        }
      })
      return ws;
    },
  },
  mounted() {
    this.data = []
    for(let i=0;i<4;i++){
      this.data.push( { school: '林州一中', grade: '高一', class: '二班', name: '徐强' } )
      this.data.push( { school: '林州二中', grade: '高三', class: '一班', name: '徐强子' } )
    }
    this.mergeRowCell(this.data)
  }
}
</script>

<style lang="less" scoped>
</style>