Vue2.js：xlsx实现Excel文件的导入导出

需求：

Vue2.js + ElementUI 项目，通过xlsx实现一个前端的 Excel 导入和导出功能

文档

• https://www.npmjs.com/package/xlsx
• https://sheetjs.com/
• https://github.com/SheetJS/sheetjs

兼容性 

依赖

$ node -v
v16.14.0

npm i file-saver xlsx -S

示例

依赖 package.json

{
  "name": "vue-excel",
  "version": "0.1.0",
  "private": true,
  "scripts": {
    "dev": "vue-cli-service serve",
    "build": "vue-cli-service build"
  },
  "dependencies": {
    "dayjs": "^1.11.5",
    "element-ui": "^2.15.10",
    "file-saver": "^2.0.5",
    "vue": "^2.6.14",
    "xlsx": "^0.18.5"
  },
  "devDependencies": {
    "@vue/cli-service": "~5.0.0",
    "less": "^4.0.0",
    "less-loader": "^8.0.0",
    "vue-template-compiler": "^2.6.14"
  }
}

引入element-ui

// main.js
import Vue from 'vue'
import App from './App.vue'

import ElementUI from 'element-ui'
import 'element-ui/lib/theme-chalk/index.css'

Vue.use(ElementUI)

Vue.config.productionTip = false

new Vue({
  render: function (h) {
    return h(App)
  },
}).$mount('#app')

data.js

// data.js
export const tableData = [
  {
    date: "2016-05-02",
    name: "王小虎",
    address: "上海市普陀区金沙江路 1518 弄",
  },
  {
    date: "2016-05-04",
    name: "王小虎",
    address: "上海市普陀区金沙江路 1517 弄",
  },
  {
    date: "2016-05-01",
    name: "王小虎",
    address: "上海市普陀区金沙江路 1519 弄",
  },
  {
    date: "2016-05-03",
    name: "王小虎",
    address: "上海市普陀区金沙江路 1516 弄",
  },
];

App.vue

工具类文件 utils.js

// import XLSX from "xlsx";
import * as XLSX from 'xlsx'
import FileSaver from 'file-saver';

/**
 * 异步读取Excel文件的sheet表为json数据
 * 不支持合并单元格
 * @param {File对象} file
 */
export function readExcelToJson(file) {
  return new Promise((resolve, reject) => {
    const reader = new FileReader();

    reader.onload = (e) => {
      let data = new Uint8Array(e.target.result);
      let workbook = XLSX.read(data, { type: "array" });
      //  console.log("workbook: ", workbook);

      //将Excel 第一个sheet内容转为json格式
      let worksheet = workbook.Sheets[workbook.SheetNames[0]];
      let json = XLSX.utils.sheet_to_json(worksheet);
      //   console.log("jsonExcel:", jsonExcel);
      resolve(json);
    };

    reader.readAsArrayBuffer(file.raw);
  });
}

/**
 * 保存json为Excel文件
 * @param {*} data 
 * @param {*} filename  文件名后缀为.xlsx
 */
export function saveJsonToExcel(data, filename) {
  let sheet = XLSX.utils.json_to_sheet(data);

  let workbook = {
    SheetNames: ["sheet1"],
    Sheets: {
      sheet1: sheet,
    },
  };

  let wbout = XLSX.write(workbook, {
    bookType: "xlsx",
    bookSST: true,
    type: "array",
  });

  FileSaver.saveAs(
    new Blob([wbout], { type: "application/octet-stream" }),
    filename
  );
}

导出截图

完整代码：https://github.com/mouday/vue-excel 在线演示：https://mouday.github.io/vue-excel/

参考文章

1. 如何使用JavaScript实现纯前端读取和导出excel文件
2. 前端导出Excel和下载后端Excel以及前端处理Excel
3. 给我实现一个前端的 Excel 导入和导出功能