Ant Design of Vue 中table表格导出

流码
3,506 阅读2分钟

背景

在vue项目table的导出功能十分常见,需要将页面中展示的table,导出为excel文件.本项目采用vue+Ant Design of Vue的形式,以下都将围绕着Ant Design of Vue中的table进行讲解如何进行导出.

实现方法

1.简单的table的导出实现

image.png
简单的table的导出通过xlsx就可以.

安装

CDN:

CDNURL
unpkgunpkg.com/xlsx/
jsDelivrjsdelivr.com/package/npm…
CDNjscdnjs.com/libraries/x…
packdbundle.run/xlsx@latest…
<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>

With npm:

$ npm install xlsx

With bower:

$ bower install js-xlsx

实现

这里采用npm方式引入xlsx.

<template>
  <a-card>
		<!-- 给table组件添加id -->
		<a-table :columns="columns" :data-source="data" id="table">
    <a slot="name" slot-scope="text">{{ text }}</a>
  </a-table>
   <!-- 添加导出事件 -->
	<a-button type="primary" @click="onExport">导出</a-button>
	</a-card>
</template>
<script>
// 引入xlsx
import XLSX from 'xlsx'
const columns = [
  {
    title: 'Name',
    dataIndex: 'name',
    key: 'name',
    scopedSlots: { customRender: 'name' },
  },
  {
    title: 'Age',
    dataIndex: 'age',
    key: 'age',
    width: 80,
  },
  {
    title: 'Address',
    dataIndex: 'address',
    key: 'address 1',
    ellipsis: true,
  },
  {
    title: 'Long Column Long Column Long Column',
    dataIndex: 'address',
    key: 'address 2',
    ellipsis: true,
  },
  {
    title: 'Long Column Long Column',
    dataIndex: 'address',
    key: 'address 3',
    ellipsis: true,
  },
  {
    title: 'Long Column',
    dataIndex: 'address',
    key: 'address 4',
    ellipsis: true,
  },
];

const data = [
  {
    key: '1',
    name: 'John Brown',
    age: 32,
    address: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    tags: ['nice', 'developer'],
  },
  {
    key: '2',
    name: 'Jim Green',
    age: 42,
    address: 'London No. 2 Lake Park, London No. 2 Lake Park',
    tags: ['loser'],
  },
  {
    key: '3',
    name: 'Joe Black',
    age: 32,
    address: 'Sidney No. 1 Lake Park, Sidney No. 1 Lake Park',
    tags: ['cool', 'teacher'],
  },
];

export default {
  data() {
    return {
      data,
      columns,
    };
  },
	methods:{
		onExport(){
      const wb = XLSX.utils.table_to_book(document.getElementById('table'))
      XLSX.writeFile(wb, `报表导出.xlsx`)
		},
	},
};
</script>

效果

image.png
优点:简单方便,将table标签直接导出,适用于原生html的table标签导出,以及简单table组件导出.
缺点:导出的样式不好看,其次table组件上有复杂的功能时,会产生bug.

2.带有fixed简单的table的导出实现

image.png
通过vue-json-excel实现.

安装

With npm:

npm install vue-json-excel

在main.js中引入

import JsonExcel from 'vue-json-excel'
Vue.component('downloadExcel', JsonExcel)

实现

<template>
  <a-card>
    <a-table :columns="columns" :data-source="data" :scroll="{ x: 'auto' }">
      <a slot="name" slot-scope="text">{{ text }}</a>
    </a-table>
    <download-excel class="btn btn-default" :data="data" :fields="json_fields" worksheet="Worksheet" name="报表.xls">
      <a-button type="primary" style="margin-left: 15px"> 导出 </a-button>
    </download-excel>
  </a-card>
</template>
<script>
const columns = [
  {
    title: 'Name',
    dataIndex: 'name',
    key: 'name',
    scopedSlots: { customRender: 'name' },
  },
  {
    title: 'Age',
    dataIndex: 'age',
    key: 'age',
    width: 80,
  },
  {
    title: 'Address',
    dataIndex: 'address1',
    key: 'address1',
    ellipsis: true,
  },
  {
    title: 'Long Column Long Column Long Column',
    dataIndex: 'address2',
    key: 'address2',
    ellipsis: true,
  },
  {
    title: 'Long Column Long Column',
    dataIndex: 'address3',
    key: 'address3',
    ellipsis: true,
  },
  {
    title: 'Long Column',
    dataIndex: 'address4',
    key: 'address4',
    fixed: 'right',
    ellipsis: true,
  },
]

// json_fields键为标题,值为对应数据的字段名(键名)
const json_fields = {
  Name: 'name',
  Address: 'address1',
  'Long Column Long Column Long Column': 'address2',
  'Long Column Long Column': 'address3',
  'Long Column': 'address4',
}

const data = [
  {
    key: '1',
    name: 'John Brown',
    age: 32,
    address1: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    address2: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    address3: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    address4: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
  },
  {
    key: '2',
    name: 'Jim Green',
    age: 42,
    address1: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    address2: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    address3: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    address4: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
  },
  {
    key: '3',
    name: 'Joe Black',
    age: 32,
    address1: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    address2: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    address3: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    address4: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
  },
]

export default {
  data() {
    return {
      data,
      columns,
      json_fields,
    }
  },
}
</script>

效果

image.png
优点:除了行合并,列合并和复合头部的复杂table外,都可以实现table数据导出.导出样式也还不错.
缺点:复杂table无法导出

3.复杂的table的导出实现

image.png
复杂的table,值得时头部为复合头部的table,采用Export2Excel2进行导出.

安装

安装必要的依赖

npm install -S file-saver
npm install -S xlsx
npm install -D script-loader

引入Blob和Export2Excel2js文件Blob.jsExport2Excel2.js;
image.png

实现

<template>
  <a-card>
    <a-table :columns="columns" :data-source="data" :scroll="{ x: 'auto' }" bordered>
      <a slot="name" slot-scope="text">{{ text }}</a>
    </a-table>

    <a-button type="primary" style="margin-left: 15px" @click="onExport"> 导出 </a-button>
  </a-card>
</template>
<script>
const columns = [
  {
    title: 'Name',
    dataIndex: 'name',
    key: 'name',
    scopedSlots: { customRender: 'name' },
  },
  {
    title: 'Age',
    dataIndex: 'age',
    key: 'age',
    width: 80,
  },
  {
    title: 'Address',
    children: [
      {
        title: 'Long Column Long Column Long Column',
        dataIndex: 'address1',
        key: 'address1',
        ellipsis: true,
      },
      {
        title: 'Long Column Long Column',
        dataIndex: 'address2',
        key: 'address2',
        ellipsis: true,
      },
      {
        title: 'Long Column',
        dataIndex: 'address3',
        key: 'address3',
        ellipsis: true,
      },
    ],
  },
]
const data = [
  {
    key: '1',
    name: 'John Brown',
    age: 32,
    address1: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    address2: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    address3: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
  },
  {
    key: '2',
    name: 'Jim Green',
    age: 42,
    address1: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    address2: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    address3: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
  },
  {
    key: '3',
    name: 'Joe Black',
    age: 32,
    address1: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    address2: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
    address3: 'New York No. 1 Lake Park, New York No. 1 Lake Park',
  },
]

export default {
  data() {
    return {
      data,
      columns,
    }
  },
  methods:{
    onExport(){
       const that = this
       const multiHeader=[['Name','Age', 'Address','','']];//注意这个时二维数组
       const header=['','','Long Column Long Column Long Column','Long Column Long Column','Long Column']//复合标题的二级标题中的第一个标题位置与一级标题的位置相同,参见'Address'和'Long Column Long Column Long Column'写法
       const merges=['A1:A2','B1:B2','C1:E1']
       const dataKey=['name','age','address1','address2','address3']//复合标题的一级标题没有对应的数据key.这里的一级标题指Address
      require.ensure([], () => {
        const { export_json_to_excel } = require('@/vendor/Export2Excel2')//引入Export2Excel2
        const data = that.formatJson(dataKey, that.data) // 生成json数据
        export_json_to_excel({
          multiHeader, // 这里是第一行的表头
          header, // 这里是第二行的表头
          data,//这里时表格数据
          filename: '报表', //这里填的是导出excel的名称
          merges,//这里填需要合并的单元格
        })
      })
    },
    formatJson(filterVal, jsonData) {
      return jsonData.map((v) => filterVal.map((j) => v[j]))
    },
  },
}
</script>

效果

image.png
优点:可以导出头部复杂的table数据
缺点:操作麻烦

总结

  • xlsx导出针对于html中table标签,table标签中包含什么,就导出什么;不适用于table组件带有复杂功能(例如组件自身带有查询,排序和固定列等功能时,会发生未知bug)的导出;适用于简单功能的table组件导出,结构可以复杂,但table标签结构要清晰.(具体可以尝试带有fixed的a-table的导出,加深理解)
  • vue-json-excel的导出与table组件带有什么功能影响不大.只与table数据有关.适用于数据形式简单的场合,没有复合头部等情况.
  • Export2Excel2适合用于处理头部复杂且table组件功能复杂的情况导出.
avatar
文章
阅读
粉丝