vue excel导出_VUE项目，纯前端实现导出EXCEL功能

Excel 导出

Excel 的导入导出都是依赖于js-xlsx来实现的。

在 js-xlsx的基础上又封装了Export2Excel.js来方便导出数据。

使用

由于 Export2Excel不仅依赖js-xlsx还依赖file-saver和script-loader。

所以你先需要安装如下命令：

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

由于js-xlsx体积还是很大的，导出功能也不是一个非常常用的功能，所以使用的时候建议使用懒加载。使用方法如下：

import('@/vendor/Export2Excel').then(excel => {  excel.export_json_to_excel({    header: tHeader, //表头 必填    data, //具体数据 必填    filename: 'excel-list', //非必填    autoWidth: true, //非必填    bookType: 'xlsx' //非必填  })})

参数

参数	说明	类型	可选值	默认值
header	导出数据的表头	Array	/	[]
data	导出的具体数据	Array	/	[]]
filename	导出文件名	String	/	excel-list
autoWidth	单元格是否要自适应宽度	Boolean	true / false	true
bookType	导出文件类型	String	xlsx, csv, txt, more	xlsx

示例

import('@/vendor/Export2Excel').then(excel => {  const tHeader = ['Id', 'Title', 'Author', 'Readings', 'Date']  const data = this.list  excel.export_json_to_excel({    header: tHeader, //表头 必填    data, //具体数据 必填    filename: 'excel-list', //非必填    autoWidth: true, //非必填    bookType: 'xlsx' //非必填  })})

excel.vue文件代码

<template>  <div class="app-container">    <div>      <el-button @click="handleDownload"> Export Excel el-button>    div>    <el-table :data="list">      <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">          <el-tag>{{ scope.row.author }}el-tag>        template>      el-table-column>      <el-table-column label="Readings" width="115" align="center">        <template slot-scope="scope">          {{ scope.row.pageviews }}        template>      el-table-column>      <el-table-column align="center" label="Date" width="220">        <template slot-scope="scope">          <i class="el-icon-time" />          <span>{{ scope.row.timestamp }}span>        template>      el-table-column>    el-table>  div>template><script>export default {  name: "ExportExcel",  data() {    return {      list: null,      listLoading: true,      filename: "",      autoWidth: true,      bookType: "xlsx",    };  },  created() {    this.fetchData();  },  methods: {    fetchData() {      /**       * mock的假数据       */      this.list = [        {          title: "test",          author: "machangzhi",          pageviews: "777",          timestamp: "12345678910",        },      ];    },    handleDownload() {      import("@/excel/Export2Excel").then((excel) => {        const tHeader = ["Id", "Title", "Author", "Readings", "Date"];        const filterVal = [          "id",          "title",          "author",          "pageviews",          "display_time",        ];        const list = this.list;        const data = this.formatJson(filterVal, list);        excel.export_json_to_excel({          header: tHeader,          data,          filename: this.filename,          autoWidth: this.autoWidth,          bookType: this.bookType,        });      });    },    formatJson(filterVal, jsonData) {      return jsonData.map((v) =>        filterVal.map((j) => {          return v[j];        })      );    },  },};script><style>.radio-label {  font-size: 14px;  color: #606266;  line-height: 40px;  padding: 0 12px 0 30px;}style>

Export2Excel.js文件代码

/* eslint-disable */import { saveAs } from "file-saver";import XLSX from "xlsx";function generateArray(table) {  var out = [];  var rows = table.querySelectorAll("tr");  var ranges = [];  for (var R = 0; R < rows.length; ++R) {    var outRow = [];    var row = rows[R];    var columns = row.querySelectorAll("td");    for (var C = 0; C < columns.length; ++C) {      var cell = columns[C];      var colspan = cell.getAttribute("colspan");      var rowspan = cell.getAttribute("rowspan");      var cellValue = cell.innerText;      if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;      //Skip ranges      ranges.forEach(function(range) {        if (          R >= range.s.r &&          R <= range.e.r &&          outRow.length >= range.s.c &&          outRow.length <= range.e.c        ) {          for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);        }      });      //Handle Row Span      if (rowspan || colspan) {        rowspan = rowspan || 1;        colspan = colspan || 1;        ranges.push({          s: {            r: R,            c: outRow.length          },          e: {            r: R + rowspan - 1,            c: outRow.length + colspan - 1          }        });      }      //Handle Value      outRow.push(cellValue !== "" ? cellValue : null);      //Handle Colspan      if (colspan) for (var k = 0; k < colspan - 1; ++k) outRow.push(null);    }    out.push(outRow);  }  return [out, ranges];}function datenum(v, date1904) {  if (date1904) v += 1462;  var epoch = Date.parse(v);  return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);}function sheet_from_array_of_arrays(data, opts) {  var ws = {};  var range = {    s: {      c: 10000000,      r: 10000000    },    e: {      c: 0,      r: 0    }  };  for (var R = 0; R != data.length; ++R) {    for (var C = 0; C != data[R].length; ++C) {      if (range.s.r > R) range.s.r = R;      if (range.s.c > C) range.s.c = C;      if (range.e.r < R) range.e.r = R;      if (range.e.c < C) range.e.c = C;      var cell = {        v: data[R][C]      };      if (cell.v == null) continue;      var cell_ref = XLSX.utils.encode_cell({        c: C,        r: R      });      if (typeof cell.v === "number") cell.t = "n";      else if (typeof cell.v === "boolean") cell.t = "b";      else if (cell.v instanceof Date) {        cell.t = "n";        cell.z = XLSX.SSF._table[14];        cell.v = datenum(cell.v);      } else cell.t = "s";      ws[cell_ref] = cell;    }  }  if (range.s.c < 10000000) ws["!ref"] = XLSX.utils.encode_range(range);  return ws;}function Workbook() {  if (!(this instanceof Workbook)) return new Workbook();  this.SheetNames = [];  this.Sheets = {};}function s2ab(s) {  var buf = new ArrayBuffer(s.length);  var view = new Uint8Array(buf);  for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;  return buf;}export function export_table_to_excel(id) {  var theTable = document.getElementById(id);  var oo = generateArray(theTable);  var ranges = oo[1];  /* original data */  var data = oo[0];  var ws_name = "SheetJS";  var wb = new Workbook(),    ws = sheet_from_array_of_arrays(data);  /* add ranges to worksheet */  // ws['!cols'] = ['apple', 'banan'];  ws["!merges"] = ranges;  /* add worksheet to workbook */  wb.SheetNames.push(ws_name);  wb.Sheets[ws_name] = ws;  var wbout = XLSX.write(wb, {    bookType: "xlsx",    bookSST: false,    type: "binary"  });  saveAs(    new Blob([s2ab(wbout)], {      type: "application/octet-stream"    }),    "test.xlsx"  );}export function export_json_to_excel({  multiHeader = [],  header,  data,  filename,  merges = [],  autoWidth = true,  bookType = "xlsx"} = {}) {  /* original data */  filename = filename || "excel-list";  data = [...data];  data.unshift(header);  for (let i = multiHeader.length - 1; i > -1; i--) {    data.unshift(multiHeader[i]);  }  var ws_name = "SheetJS";  var wb = new Workbook(),    ws = sheet_from_array_of_arrays(data);  if (merges.length > 0) {    if (!ws["!merges"]) ws["!merges"] = [];    merges.forEach(item => {      ws["!merges"].push(XLSX.utils.decode_range(item));    });  }  if (autoWidth) {    /*设置worksheet每列的最大宽度*/    const colWidth = data.map(row =>      row.map(val => {        /*先判断是否为null/undefined*/        if (val == null) {          return {            wch: 10          };        } else if (val.toString().charCodeAt(0) > 255) {        /*再判断是否为中文*/          return {            wch: val.toString().length * 2          };        } else {          return {            wch: val.toString().length          };        }      })    );    /*以第一行为初始值*/    let result = colWidth[0];    for (let i = 1; i < colWidth.length; i++) {      for (let j = 0; j < colWidth[i].length; j++) {        if (result[j]["wch"] < colWidth[i][j]["wch"]) {          result[j]["wch"] = colWidth[i][j]["wch"];        }      }    }    ws["!cols"] = result;  }  /* add worksheet to workbook */  wb.SheetNames.push(ws_name);  wb.Sheets[ws_name] = ws;  var wbout = XLSX.write(wb, {    bookType: bookType,    bookSST: false,    type: "binary"  });  saveAs(    new Blob([s2ab(wbout)], {      type: "application/octet-stream"    }),    `${filename}.${bookType}`  );}