后台管理系统中，通常有这样的需求:将当前 table 表格导出/导入 Excel 表格，实现方式通常为后端主导（后端使用插件，前端通过点击事件生成一个 a 标签，导出 Excel 表格）、前端主导（使用 xlsx、file-saver、script-loader 插件封装工具函数，通过点击事件，获取当前页面后台返回的表格数据)。

一、前端导出 Excel 表格的实现

1、安装 plugin

npm install file-saver script-loader xlsx --save

2、Export2Excel 工具函数

点我展示代码

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}`
  );
}

3、导出函数

a-button 为 ant-design-vue 的 button 组件

<a-button
  type="primary"
  icon="export"
  class="select-bottom"
  :loading="exportLoading"
  @click="handleExport"
  >导出
</a-button>

handleExport 导出函数

1、点击导出按钮，去加载在 vendor 文件夹中的 Export2Excel 模块
2、import 方法执行完毕返回一个 promise 对象，在 then 方法中我们可以拿到使用的模块对象（excel）
3、Excel 导出参数部分的 data，是一个严格的二维数组，header 为导出的数据表头，filename 为导出的文件名，bookType 为导出文件类型，autoWidth 为单元格是否要自适应宽度。

handleExport() {
    //由于是前端导出，所以只能导出当前页的数据
    this.exportLoading = true;
      import('@/vendor/Export2Excel').then(excel => {
        const header = [],
          filterVal = [];
        this.tableHead.forEach(item => {
          if (item.title != '操作' && item.title != '序号') {
            header.push(item.title);
            filterVal.push(item.dataIndex);
          }
        });
        const data = formatJson(this.tableData, filterVal);

        excel.export_json_to_excel({
          header,
          data,
          filename: '表单统计'
        });
        this.exportLoading = false;
      });
    }

二、前端导入 Excel 的实现

1、ant-design-vue 上传组件的使用

<div class="file-wrapper relative">
  <input
    ref="excel-upload"
    type="file"
    accept=".xlsx, .xls"
    @change="handleChange"
    style="display:none"
  />
  <a-button
    type="primary"
    icon="file-excel"
    size="large"
    style="z-index:99"
    @click="handleUpload"
    :loading="loading"
  >
    上传Excel
  </a-button>
  <p class="text">请选择Excel上传 支持(xlsx,xls)格式</p>
</div>
<!-- table组件 tableData:[]  tableHead:[] -->
<a-card
  :hoverable="true"
  :bordered="false"
  v-if="tableHead.length > 0"
  style="margin-top:30px"
>
  <standard-table
    :pagination="false"
    :tableData="tableData"
    :tableHead="tableHead"
  />
</a-card>

.file-wrapper {
  width: 100%;
  height: 350px;
  line-height: 350px;
  text-align: center;
  background: #e6ecf4;
  .text {
    position: absolute;
    top: 50px;
    left: 50%;
    transform: translateX(-50%);
  }
}

2、导入函数

// 引入xlsx table组件
import XLSX from "xlsx";
import standardTable from "@/components/standardTable/index";

点我展示代码

handleChange(e) {
  const file = e.target.files[0];
  if (!file) return;
  //判断格式
  if (!this.isExcel(file)) {
    this.$message.warning('只能选择xlxs,xls文件');
    return;
  }
  //判断大小
  if (file.size / 1024 / 1024 > 1) {
    this.$message.warning('上传文件大小不能超过1M');
    return;
  }
  this.loading = true;

  const reader = new FileReader();
  reader.readAsArrayBuffer(file);
  reader.onload = e => {
    const data = e.target.result;
    const workbook = XLSX.read(data, { type: 'array' });
    const firstSheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[firstSheetName];

    setTimeout(() => {
      this.loading = false;
      const head = this.getHeaderRow(worksheet);
      this.tableHead = head.map(item => {
        return {
          title: item,
          dataIndex: item
        };
      });
      const tableData = XLSX.utils.sheet_to_json(worksheet);
      tableData.forEach(item => {
        item.id = parseInt(Math.random(0, 1) * 10000);
      });
      this.tableData = tableData;
    }, 500);
  };
},

generateData({ header, results }) {
  this.excelData.header = header;
  this.excelData.results = results;
  this.onSuccess && this.onSuccess(this.excelData);
},

//获取表头
getHeaderRow(sheet) {
  const headers = [];
  const range = XLSX.utils.decode_range(sheet['!ref']);
  let C;
  const R = range.s.r;

  for (C = range.s.c; C <= range.e.c; ++C) {
    const cell = sheet[XLSX.utils.encode_cell({ c: C, r: R })];
    let hdr = 'UNKNOWN ' + C;
    if (cell && cell.t) hdr = XLSX.utils.format_cell(cell);
    headers.push(hdr);
  }
  return headers;
},

handleUpload() {
  this.$refs['excel-upload'].click();
},
// 文件类型
isExcel(file) {
  return /\.(xlsx|xls|csv)$/.test(file.name);
}

三、table 子组件

点我展示代码

<div class="tableCommon-wrapper">
  <a-table
    :columns="tableHead"
    :dataSource="tableData"
    :loading="loading"
    :pagination="pagination"
    :row-selection="rowSelection"
    @change="handleTableChange"
    rowKey="id"
    :scroll="scroll"
  >
    <template
      slot-scope="text, record, index"
      :slot="slot"
      v-for="slot in Object.keys($scopedSlots).filter(key => key !== 'expandedRowRender')"
    >
      <slot :name="slot" v-bind="{ text, record, index }"></slot>
    </template>
  </a-table>
</div>

export default {
  name: "standardTable",
  props: {
    tableHead: {
      type: Array,
      required: true,
    },
    tableData: {
      type: Array,
      required: true,
    },
    loading: {
      type: Boolean,
      default: false,
    },
    pagination: {
      type: Boolean | Object,
    },
    rowSelection: {
      type: Object,
    },
    scroll: {
      type: Object,
    },
  },
  methods: {
    handleTableChange(val) {
      this.$emit("changeCurrent", val.current);
    },
  },
};