需求:
Vue2.js + ElementUI 项目,通过xlsx实现一个前端的 Excel 导入和导出功能
文档
/package/xlsx//SheetJS/sheetjs
兼容性
依赖
$ node -vv16.14.0npm 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.jsimport 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 = falsenew Vue({render: function (h) {return h(App)},}).$mount('#app')
data.js
// data.jsexport const tableData = [{date: "-05-02",name: "王小虎",address: "上海市普陀区金沙江路 1518 弄",},{date: "-05-04",name: "王小虎",address: "上海市普陀区金沙江路 1517 弄",},{date: "-05-01",name: "王小虎",address: "上海市普陀区金沙江路 1519 弄",},{date: "-05-03",name: "王小虎",address: "上海市普陀区金沙江路 1516 弄",},];
App.vue
<template><div class="container"><div style="display: flex"><el-uploadref="upload"action="action"accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet":show-file-list="false":http-request="handlehttpRequest":on-change="onUploadChange"><el-buttonsize="small"type="primary"icon="el-icon-upload">导入Excel</el-button></el-upload><el-buttonstyle="margin-left: 20px"size="small"icon="el-icon-download"@click="handleDownload">导出Excel</el-button><el-buttonstyle="margin-left: 20px"size="small"icon="el-icon-delete"@click="handleClear">清空数据</el-button></div><el-table:data="list"borderstyle="width: 100%; margin-top: 20px"><template v-if="list && list.length > 0"><el-table-columnv-for="key in Object.keys(list[0] || {})":prop="key":label="key"></el-table-column></template></el-table></div></template><script>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 = filereadExcelToJson(file).then((res) => {this.list = res})},handleDownload() {saveJsonToExcel(this.list, 'data.xlsx')},handleClear() {this.list = null},},created() {this.list = tableData},}</script><style lang="less">body {background: #f4f4f4;padding: 0;margin: 0;}.container {width: 1024px;// min-height: 100vh;margin: 0 auto;padding: 20px;background: #fff;}</style>
工具类文件 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);}
导出截图
完整代码:/mouday/vue-excel
在线演示:https://mouday.github.io/vue-excel/
参考文章
如何使用JavaScript实现纯前端读取和导出excel文件前端导出Excel和下载后端Excel以及前端处理Excel给我实现一个前端的 Excel 导入和导出功能