纯前端table表格导出excel文件，两种方法

最新推荐文章于 2025-02-22 17:32:20 发布

小白咚

最新推荐文章于 2025-02-22 17:32:20 发布

阅读量1.2k

点赞数

文章标签：前端 excel javascript 开发语言 ecmascript

本文链接：https://blog.csdn.net/weixin_44438307/article/details/133009204

版权

先介绍第一种，因为第一种是直接获取dom节点的数据，可以直接快照形式捕获数据(比如获取过滤后或者格式化后的数据)

注意：依赖的版本，太高的话会报undefined

import FileSaver from "file-saver";
import XLSX from "xlsx";


    /** 导出按钮操作 */
    exportExcel () {
      console.log(XLSX);
      /* generate workbook object from table */
      var wb = XLSX.utils.table_to_book(document.querySelector('#out-table'))
         /* get binary string as output */
         var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: true, type: 'array' })
         try {
             FileSaver.saveAs(new Blob([wbout], { type: 'application/octet-stream' }), 'sheetjs.xlsx')
         } catch (e) { if (typeof console !== 'undefined') console.log(e, wbout) }
         return wbout
    },

　　第二种是书写一个html表格，然后base64转换成xls

exportExcelTwo() {
      let titleList = ['id', '标题', '作者', 'page', 'status', '时间']
      this.tableToExcel(this.list,titleList)
    },

    tableToExcel(data, excelStr) {
      //要导出的json数据
      const jsonData = data || []
      //列标题
      let str = excelStr || '<tr><td></td></tr>';
      //循环遍历，每行加入tr标签，每个单元格加td标签
      for (let i = 0; i < jsonData.length; i++) {
        str += '<tr>';
        for (let item in jsonData[i]) {
          //增加\t为了不让表格显示科学计数法或者其他格式
          str += `<td>${jsonData[i][item] + '\t'}</td>`;
        }
        str += '</tr>';
      }
      //Worksheet名
      let worksheet = '方法二'
      let uri = 'data:application/vnd.ms-excel;base64,';

      //下载的表格模板数据
      let template = `<html xmlns:o="urn:schemas-microsoft-com:office:office"
        xmlns:x="urn:schemas-microsoft-com:office:excel"
        xmlns="http://www.w3.org/TR/REC-html40">
        <head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>
          <x:Name>${worksheet}</x:Name>
          <x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>
          </x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
          </head><body><table>${str}</table></body></html>`;
      //下载模板
      // window.location.href = uri + base64(template)
      //通过创建a标签实现
      let link = document.createElement("a");
      link.href = uri + this.base64(template);
      //对下载的文件命名


      link.download = '标题.xls';
      document.body.appendChild(link);
      link.click();
      document.body.removeChild(link);
    },
    //输出base64编码
    base64(s) {
      return window.btoa(unescape(encodeURIComponent(s)))
    }

　　最后贴上两种方法都有的测试过程种的全部代码

<template>
  <div class="app-container">
    <el-button @click="exportExcel">导出-方法一</el-button>
    <el-button @click="exportExcelTwo">导出-方法二</el-button>
    <el-table
    id="out-table"
      v-loading="listLoading"
      :data="list"
      element-loading-text="Loading"
      border
      fit
      highlight-current-row
    >
      <el-table-column align="center" label="ID" width="95">
        <template slot-scope="scope">
          {{ scope.$index }}
        </template>
      </el-table-column>
      <el-table-column label="Title">
        <template slot-scope="scope">
          {{ scope.row.title }}
        </template>
      </el-table-column>
      <el-table-column label="Author" width="110" align="center">
        <template slot-scope="scope">
          <span>{{ scope.row.author }}</span>
        </template>
      </el-table-column>
      <el-table-column label="Pageviews" width="110" align="center">
        <template slot-scope="scope">
          {{ scope.row.pageviews }}
        </template>
      </el-table-column>
      <el-table-column :formatter="formatter" class-name="status-col" label="Status" width="110" align="center">
        <!-- <template slot-scope="scope">
          <el-tag :type="scope.row.status | statusFilter">{{ scope.row.status }}</el-tag>
        </template> -->
      </el-table-column>
      <el-table-column align="center" prop="created_at" label="Display_time" width="200">
        <template slot-scope="scope">
          <!-- <i class="el-icon-time" /> -->
          <span>{{ scope.row.display_time }}</span>
        </template>
      </el-table-column>
    </el-table>
  </div>
</template>

<script>
import { getList } from '@/api/table'
import FileSaver from "file-saver";
import XLSX from "xlsx";

export default {
  filters: {
    statusFilter(status) {
      const statusMap = {
        published: 'success',
        draft: 'gray',
        deleted: 'danger'
      }
      return statusMap[status]
    }
  },
  data() {
    return {
      list: null,
      listLoading: true
    }
  },
  created() {
    this.fetchData()
  },
  methods: {
    formatter(row, column) {
      console.log(row.status);
      if(row.status === 'published') {
        return '成功了'
      } else if (row.status === 'draft') {
        return '失败了'
      } else {
        return row.status
      }
      },
    fetchData() {
      this.listLoading = true
      getList().then(response => {
        this.list = response.data.items
        this.listLoading = false
      })
    },
    /** 导出按钮操作 */
    exportExcel () {
      console.log(XLSX);
      /* generate workbook object from table */
      var wb = XLSX.utils.table_to_book(document.querySelector('#out-table'))
         /* get binary string as output */
         var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: true, type: 'array' })
         try {
             FileSaver.saveAs(new Blob([wbout], { type: 'application/octet-stream' }), 'sheetjs.xlsx')
         } catch (e) { if (typeof console !== 'undefined') console.log(e, wbout) }
         return wbout
    },

    exportExcelTwo() {
      let titleList = ['id', '标题', '作者', 'page', 'status', '时间']
      this.tableToExcel(this.list,titleList)
    },

    tableToExcel(data, excelStr) {
      //要导出的json数据
      const jsonData = data || []
      //列标题
      let str = excelStr || '<tr><td></td></tr>';
      //循环遍历，每行加入tr标签，每个单元格加td标签
      for (let i = 0; i < jsonData.length; i++) {
        str += '<tr>';
        for (let item in jsonData[i]) {
          //增加\t为了不让表格显示科学计数法或者其他格式
          str += `<td>${jsonData[i][item] + '\t'}</td>`;
        }
        str += '</tr>';
      }
      //Worksheet名
      let worksheet = '方法二'
      let uri = 'data:application/vnd.ms-excel;base64,';

      //下载的表格模板数据
      let template = `<html xmlns:o="urn:schemas-microsoft-com:office:office"
        xmlns:x="urn:schemas-microsoft-com:office:excel"
        xmlns="http://www.w3.org/TR/REC-html40">
        <head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>
          <x:Name>${worksheet}</x:Name>
          <x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>
          </x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
          </head><body><table>${str}</table></body></html>`;
      //下载模板
      // window.location.href = uri + base64(template)
      //通过创建a标签实现
      let link = document.createElement("a");
      link.href = uri + this.base64(template);
      //对下载的文件命名


      link.download = '标题.xls';
      document.body.appendChild(link);
      link.click();
      document.body.removeChild(link);
    },
    //输出base64编码
    base64(s) {
      return window.btoa(unescape(encodeURIComponent(s)))
    }

  }
}
</script>