Vue：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

  
    
      
        导入Excel
      

      导出Excel

      清空数据
    

    
      
        
        
      
    
  



import { readExcelToJson, saveJsonToExcel } from './utils.js'
import { tableData } from './data.js'

export default {
  data() {
    return {
      file: null,
      list: [],
    }
  },

  methods: {
    handlehttpRequest() {},

    // 读取文件为json数据
    onUploadChange(file) {
      console.log(file)
      this.file = file
      readExcelToJson(file).then((res) => {
        this.list = res
      })
    },

    handleDownload() {
      saveJsonToExcel(this.list, 'data.xlsx')
    },

    handleClear() {
      this.list = null
    },
  },

  created() {
    this.list = tableData
  },
}



body {
  background: #f4f4f4;
  padding: 0;
  margin: 0;
}

.container {
  width: 1024px;
  // min-height: 100vh;
  margin: 0 auto;
  padding: 20px;
  background: #fff;
}

工具类文件 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 导入和导出功能