🥱【Element Plus】el-table表格导出Excel表格（支持多表导出为一个Excel表格）

oanuyoah
4,792 阅读1分钟

一、安装依赖库

主要需要用到 xlsx 和 file-saver 两个库

# NPM
npm install xlsx
npm install file-saver

# Yarn
yarn add xlsx file-saver

二、文件导出工具

新建 src/utils/exportToExcel.ts

  1. 引入依赖库
  2. 导出单个表格只需要提供目标的 el-table 的元素绑定的 id 值
  3. 导出多个表格为 多个页签的一个Excel文件 的则需数组方式传递元素绑定的 id 和 Excel底部页签 的显示名称
import FileSaver from 'file-saver'
import * as XLSX from 'xlsx'
  
export interface IExportXlsx {
  eleById: string,
  title: string
}

const exportToExcel = async (element: string | Array<IExportXlsx>, name?: string): Promise<Object> => {
  await nextTick()
  // 设置导出的内容是否只做解析，不进行格式转换 false：要解析， true:不解析
  const xlsxParam = { raw: true }
  let wb: XLSX.WorkBook
  if (typeof element === 'string') { // 导出单个表格
    wb = XLSX.utils.table_to_book(document.getElementById(element), xlsxParam)
  } else { // 遍历导出多个表格
    wb = XLSX.utils.book_new();
    element.forEach((item: IExportXlsx) => XLSX.utils.book_append_sheet(wb,XLSX.utils.table_to_sheet(document.getElementById(item.eleById), xlsxParam), item.title))
  }
  
  // 导出excel文件名
  const fileName: string = `${name || new Date().getTime()}.xlsx`

  const wbout = XLSX.write(wb, {
    bookType: 'xlsx',
    bookSST: true,
    type: 'array'
  })
  try {
    // 下载保存文件
    FileSaver.saveAs(new Blob([wbout], {
      type: 'application/octet-stream'
    }), fileName)
  } catch (e) {
    console.log(e, wbout)
  }
  return wbout
}
export default exportToExcel

三、举个栗子

<template>
  <div class="container">
    <el-button type="success" :icon="Download" @click="exportToExcel('commTable', '导出单表')">导出单表</el-button>
    <el-button type="info" :icon="Download" @click="exportTable">导出多表</el-button>
    <el-alert title="单表数据" type="success" :closable="false" effect="dark" />
    <el-table id="commTable" :data="commData">
      <el-table-column prop="date" label="Date" align="center" />
      <el-table-column prop="name" label="Name" align="center" />
      <el-table-column prop="address" label="Address" align="center" />
    </el-table>

    <template v-for="(item, idx) in tableData" :key="idx">
      <el-alert :title="item.title" :closable="false" effect="dark" />
      <el-table :id="eleIdPrefix + idx" :data="item.data">
        <el-table-column prop="date" label="日期" align="center" />
        <el-table-column prop="name" label="姓名" align="center" />
        <el-table-column prop="state" label="状态" align="center" />
        <el-table-column prop="city" label="城市" align="center" />
        <el-table-column prop="address" label="地址" width="300" align="center" />
        <el-table-column prop="zip" label="Zip" align="center" />
      </el-table>
    </template>
  </div>
</template>
<script setup lang="ts">
import { Download } from '@element-plus/icons-vue'
import exportToExcel from "@/utils/exportToExcel"
import type { IExportXlsx } from '@/utils/exportToExcel'

const eleIdPrefix = ref<string>('prefix') // 循环Dmo绑定的ID前缀
const exportTable = () => {
  const eleIds: Array<IExportXlsx> = [{
    eleById: "commTable", title: '单表数据'
  }, ...tableData.map((item, idx) => {
    return { eleById: eleIdPrefix.value + idx, title: item.title }
  })]
  console.log(eleIds);
  exportToExcel(eleIds, '导出多表')
}

const tableData = reactive([{
  title: '多表数据-人员统计',
  data: [{
    date: '2016-05-03',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Home',
  }, {
    date: '2016-05-02',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Office',
  }, {
    date: '2016-05-04',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Home',
  }, {
    date: '2016-05-01',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Office',
  }]
}, {
  title: '多表数据-地址统计',
  data: [{
    date: '2016-05-03',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Home',
  }, {
    date: '2016-05-02',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Office',
  }, {
    date: '2016-05-04',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Home',
  }, {
    date: '2016-05-01',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Office',
  }]
}, {
  title: '多表数据-信息统计',
  data: [{
    date: '2016-05-03',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Home',
  }, {
    date: '2016-05-02',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Office',
  }, {
    date: '2016-05-04',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Home',
  }, {
    date: '2016-05-01',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Office',
  }]
}, {
  title: '多表数据-状态统计',
  data: [{
    date: '2016-05-03',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Home',
  }, {
    date: '2016-05-02',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Office',
  }, {
    date: '2016-05-04',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Home',
  }, {
    date: '2016-05-01',
    name: 'Tom',
    state: 'California',
    city: 'Los Angeles',
    address: 'No. 189, Grove St, Los Angeles',
    zip: 'CA 90036',
    tag: 'Office',
  }]
}])
const commData = reactive([{
  date: '2016-05-03',
  name: 'Tom',
  address: 'No. 189, Grove St, Los Angeles',
}, {
  date: '2016-05-02',
  name: 'Tom',
  address: 'No. 189, Grove St, Los Angeles',
}, {
  date: '2016-05-04',
  name: 'Tom',
  address: 'No. 189, Grove St, Los Angeles',
}, {
  date: '2016-05-01',
  name: 'Tom',
  address: 'No. 189, Grove St, Los Angeles',
}])
</script>

<style>
.el-alert {
  margin-top: 10px;
}
</style>

Snipaste_2023-06-15_15-21-34.png

四、导出效果

单表

Snipaste_2023-06-15_15-21-34.png

多页签表格，可以看到 Excel 文件底部共有五个页签

Snipaste_2023-06-15_15-21-34.png

avatar
文章
阅读
粉丝