1500字范文 > vue2 - 基于Export2Excel.js导出Excel案例（js-xlsx插件二次封装使用）

vue2 - 基于Export2Excel.js导出Excel案例（js-xlsx插件二次封装使用）

时间：2021-03-08 12:26:21

一、项目场景二、实现思路三、准备工作1、下载js-xlsx2、下载Export2Excel.js3、下载file-saver和script-loader4、下载mock四、代码实现1、mock数据2、使用Export2Excel.js导入导出excel数据3、App.vue代码五、运行结果六、进阶（复杂表头的导出）

一、项目场景

从后台拿到数据之后，然后通过excel表格的形式下载到桌面

二、实现思路

使用mock模拟后端生成数据

通过Export2Excel.js实现数据导出

三、准备工作

1、下载js-xlsx

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

npm i xlsx

2、下载Export2Excel.js

vue-element-admin后台管理系统在js-xlsx的基础上又封装了Export2Excel.js来方便导出数据。

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

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

excel导出参数的介绍

3、下载file-saver和script-loader

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

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

4、下载mock

使用mock来模拟后端接口

npm i mock

四、代码实现

1、mock数据

新建src/mock/excel.json

[{"id": "604f764971f93f3ac8f365c2","mobile": "13800000002","username": "脸太凉了是吧，是想吃嘴巴子","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-11-02","formOfEmployment": "后端没有返回数据,改了没效果,保存修改接收这个参数","workNumber": "9002","correctionTime": "-11-30","departmentName": "总裁办","staffPhoto": "http://hr-picture-1315259797.cos.ap-/123.jpg"},{"id": "604f764971f93f3ac8f365c3","mobile": "13800000003","username": "1楼666","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-11-04","formOfEmployment": 1,"workNumber": "111","correctionTime": "-11-20","departmentName": "市场部","staffPhoto": "http://xzj21-1315284655.cos.ap-/userphoto.jpg"},{"id": "604f764971f93f3ac8f365c4","mobile": "13800000004","username": "禁止套娃","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-12-02","formOfEmployment": 1,"workNumber": "1111","correctionTime": "-12-31","departmentName": "人事部","staffPhoto": "https:lcl-1315284633.cos.ap-/tttt.png"},{"id": "604f764971f93f3ac8f365c5","mobile": "13400000001","username": "南京黑马","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-01-01","formOfEmployment": 1,"workNumber": "1001","correctionTime": "1970-01-01","departmentName": "人事部","staffPhoto": "https:lcl-1315284633.cos.ap-/tttt.png"},{"id": "604f764971f93f3ac8f365c6","mobile": "13400000002","username": "M78星云","password": "3d9188577cc9bfe9291ac66b5cc872b7","timeOfEntry": "-01-01","formOfEmployment": 1,"workNumber": "1002","correctionTime": "1970-01-01","departmentName": "人事部","staffPhoto": "http://hr-picture-1315259797.cos.ap-/6004.jpg"},{"id": "604f764971f93f3ac8f365c7","mobile": "13500000001","username": "3","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-01-01","formOfEmployment": 1,"workNumber": "2001","correctionTime": "1970-01-01","departmentName": "总裁办","staffPhoto": "https://zhiyin-1315331394.cos.ap-/1669364404889.jpg"},{"id": "604f764971f93f3ac8f365c8","mobile": "13500000002","username": "5","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-01-31","formOfEmployment": 1,"workNumber": "2002","correctionTime": "1970-01-01","departmentName": "总裁办","staffPhoto": "https://zhiyin-1315331394.cos.ap-/1669364472536.jpg"},{"id": "604f764971f93f3ac8f365c9","mobile": "13500000003","username": "6","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-01-01","formOfEmployment": 1,"workNumber": "","correctionTime": "1970-01-01","departmentName": "财务部","staffPhoto": "https://zhiyin-1315331394.cos.ap-/1669365556365.jpg"},{"id": "604f764971f93f3ac8f365ca","mobile": "13600000001","username": "8","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "1992-08-04","formOfEmployment": 1,"workNumber": "0001","correctionTime": "-01-01","departmentName": "总裁办","staffPhoto": "https://zhiyin-1315331394.cos.ap-/1669365533382.jpg"},{"id": "604f764971f93f3ac8f365cb","mobile": "13600000002","username": "保罗乔治","password": "4297f44b13955235245b2497399d7a93","timeOfEntry": "1993-08-04","formOfEmployment": 1,"workNumber": "0002","correctionTime": "-01-01","departmentName": "总裁办","staffPhoto": "https://zhiyin-1315331394.cos.ap-/1669365585378.jpg"},{"id": "604f764971f93f3ac8f365cc","mobile": "13600000003","username": "河马1","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "1993-08-04","formOfEmployment": 1,"workNumber": "0003","correctionTime": "-01-01","departmentName": "行政部","staffPhoto": "https://cql-1315331299.cos.ap-/1669361855846.jpg"},{"id": "604f764971f93f3ac8f365cd","mobile": "13600000004","username": "宋高昂","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "1993-08-04","formOfEmployment": 1,"workNumber": "0004","correctionTime": "-01-01","departmentName": "人事部","staffPhoto": "//hrsass-151-1315334652.cos.ap-/1669363404981%E6%B0%B8%E6%81%A9.jpg"},{"id": "604f764971f93f3ac8f365ce","mobile": "13600000005","username": "师玉堂","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "1993-08-04","formOfEmployment": 1,"workNumber": "0005","correctionTime": "-01-01","departmentName": "财务部","staffPhoto": "http://rubbish-1315218803.cos.ap-/params.file.name"},{"id": "604f764971f93f3ac8f365cf","mobile": "13600000006","username": "武高丽","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "1993-08-04","formOfEmployment": 1,"workNumber": "0006","correctionTime": "-01-01","departmentName": "技术部","staffPhoto": ""},{"id": "604f764971f93f3ac8f365d0","mobile": "13600000007","username": "任志","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "1993-08-04","formOfEmployment": 1,"workNumber": "0007","correctionTime": "-01-01","departmentName": "运营部","staffPhoto": ""},{"id": "604f764971f93f3ac8f365d1","mobile": "13600000008","username": "瞿光明","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "1993-08-04","formOfEmployment": 1,"workNumber": "0008","correctionTime": "-01-01","departmentName": "市场部","staffPhoto": ""},{"id": "63807028e804505b35fa0408","mobile": "15751786628","username": "张飞1","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-03-10","workNumber": "88088","correctionTime": "-09-10","staffPhoto": ""},{"id": "63807351e804505b35fa041b","mobile": "13900333333","username": "撒旦法","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-11-07T16:00:00.000Z","formOfEmployment": "2","workNumber": "12312","correctionTime": "-11-15T16:00:00.000Z","departmentName": "财务部","staffPhoto": ""},{"id": "638077abe804505b35fa043c","mobile": "13041130789","username": "高小山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "638077abe804505b35fa043d","mobile": "13041119799","username": "高老山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "638077bfe804505b35fa0444","mobile": "13041139879","username": "高大山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "638077bfe804505b35fa0445","mobile": "13041130789","username": "高小山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "638077bfe804505b35fa0446","mobile": "13041119799","username": "高老山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "63807919e804505b35fa0458","mobile": "13041139879","username": "高大山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "63807919e804505b35fa0459","mobile": "13041130789","username": "高小山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "63807919e804505b35fa045a","mobile": "13041119799","username": "高老山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "63807d2ee804505b35fa046d","mobile": "13041139879","username": "高大山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "63807d2ee804505b35fa046f","mobile": "13041119799","username": "高老山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "63807de3e804505b35fa0478","mobile": "13041131235","username": "高小山123","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "63807de3e804505b35fa0479","mobile": "13041111236","username": "高老山123","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "63807dede804505b35fa0480","mobile": "13800000002","username": "我去","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-10-31T16:00:00.000Z","formOfEmployment": "1","workNumber": "2131231","correctionTime": "-11-25T16:00:00.000Z","departmentName": "行政部","staffPhoto": ""},{"id": "63807ebbe804505b35fa0489","mobile": "13041139879","username": "高大山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-03-09T16:00:00.000Z","workNumber": "9","correctionTime": "-09-09T16:00:00.000Z","staffPhoto": ""},{"id": "63807ebbe804505b35fa048a","mobile": "13041130789","username": "高小山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-03-09T16:00:00.000Z","workNumber": "9","correctionTime": "-09-09T16:00:00.000Z","staffPhoto": ""},{"id": "63807ebbe804505b35fa048b","mobile": "13041119799","username": "高老山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-03-09T16:00:00.000Z","workNumber": "9","correctionTime": "-09-09T16:00:00.000Z","staffPhoto": ""},{"id": "63807fa5e804505b35fa0497","mobile": "13041431234","username": "高9大山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-03-09T16:00:00.000Z","workNumber": "9","correctionTime": "-09-09T16:00:00.000Z","staffPhoto": ""},{"id": "63807fa5e804505b35fa0498","mobile": "13041141235","username": "高9小山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-03-09T16:00:00.000Z","workNumber": "9","correctionTime": "-09-09T16:00:00.000Z","staffPhoto": ""},{"id": "63807fa5e804505b35fa0499","mobile": "13041115236","username": "高老山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-03-09T16:00:00.000Z","workNumber": "9","correctionTime": "-09-09T16:00:00.000Z","staffPhoto": ""},{"id": "63808107e804505b35fa04a7","mobile": "13944466622","username": "123","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-10-31T16:00:00.000Z","formOfEmployment": "1","workNumber": "阿萨德","correctionTime": "-11-13T16:00:00.000Z","departmentName": "财务部","staffPhoto": ""},{"id": "6380822ee804505b35fa04ac","mobile": "18329032903","username": "学霸本霸","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-11-24T16:00:00.000Z","formOfEmployment": "1","workNumber": "100000","correctionTime": "-11-24T16:00:00.000Z","departmentName": "行政部","staffPhoto": ""},{"id": "638083f6e804505b35fa04b4","mobile": "18735291056","username": "www","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-11-24T16:00:00.000Z","formOfEmployment": "1","workNumber": "1111","correctionTime": "-11-24T16:00:00.000Z","departmentName": "人事部","staffPhoto": ""},{"id": "63808f1de804505b35fa04e1","mobile": "19112421250","username": "巴拉啦","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-11-15T16:00:00.000Z","formOfEmployment": "1","workNumber": "1888","correctionTime": "-11-27T16:00:00.000Z","departmentName": "技术部","staffPhoto": "http://biubiu-223-1315227161.cos.ap-/QQ%E5%9B%BE%E7%89%870916170032.jpg"},{"id": "638091b6e804505b35fa0522","mobile": "13222222222","username": "天天","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-11-14T16:00:00.000Z","formOfEmployment": "2","workNumber": "123","correctionTime": "-11-09T16:00:00.000Z","departmentName": "财务核算部","staffPhoto": ""},{"id": "6380980fe804505b35fa0556","mobile": "13042239879","username": "高大山1","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "6380980fe804505b35fa0557","mobile": "13041330789","username": "高小山2","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "6380980fe804505b35fa0558","mobile": "13042319799","username": "高老山3","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "63809846e804505b35fa055f","mobile": "13042239879","username": "高大山1","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "63809846e804505b35fa0560","mobile": "13041330789","username": "高小山2","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "63809846e804505b35fa0561","mobile": "13042319799","username": "高老山3","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "6380987be804505b35fa0571","mobile": "13042239879","username": "高大山1","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "6380987be804505b35fa0572","mobile": "13041330789","username": "高小山2","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "6380987be804505b35fa0573","mobile": "13042319799","username": "高老山3","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "638098afe804505b35fa057e","mobile": "13041330789","username": "高小山2","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "638098afe804505b35fa057f","mobile": "13042319799","username": "高老山3","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "9","correctionTime": "43719","staffPhoto": ""},{"id": "638098cfe804505b35fa0586","mobile": "13222222222","username": "123","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-11-07T16:00:00.000Z","formOfEmployment": "2","workNumber": "123","correctionTime": "-11-16T16:00:00.000Z","departmentName": "人事部","staffPhoto": ""},{"id": "638099dee804505b35fa0595","mobile": "13042239879","username": "高大山1","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-03-09T16:00:00.000Z","workNumber": "9","correctionTime": "-09-09T16:00:00.000Z","staffPhoto": ""},{"id": "638099dee804505b35fa0596","mobile": "13041330789","username": "高小山2","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-03-09T16:00:00.000Z","workNumber": "9","correctionTime": "-09-09T16:00:00.000Z","staffPhoto": ""},{"id": "638099dee804505b35fa0597","mobile": "13042319799","username": "高老山3","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-03-09T16:00:00.000Z","workNumber": "9","correctionTime": "-09-09T16:00:00.000Z","staffPhoto": ""},{"id": "63809a22e804505b35fa059e","mobile": "13042239879","username": "高大山1","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-03-06T16:00:00.000Z","workNumber": "9","correctionTime": "-09-09T16:00:00.000Z","staffPhoto": ""},{"id": "63809a22e804505b35fa059f","mobile": "13041330789","username": "高小山2","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-04-06T16:00:00.000Z","workNumber": "9","correctionTime": "-09-09T16:00:00.000Z","staffPhoto": ""},{"id": "63809a54e804505b35fa05a8","mobile": "13042239879","username": "高大山1","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-03-07T16:00:00.000Z","workNumber": "9","correctionTime": "-09-10T16:00:00.000Z","staffPhoto": ""},{"id": "63809a54e804505b35fa05aa","mobile": "13042319799","username": "高老山3","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-05-07T16:00:00.000Z","workNumber": "9","correctionTime": "-09-10T16:00:00.000Z","staffPhoto": ""},{"id": "63809c48e804505b35fa05b7","mobile": "13042239879","username": "高大山1","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-03-09T16:00:00.000Z","workNumber": "9","correctionTime": "-09-09T16:00:00.000Z","staffPhoto": ""},{"id": "6380a6bde804505b35fa05f0","mobile": "15751786628","username": "海绵","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "88088","correctionTime": "43719","staffPhoto": ""},{"id": "6380a6bde804505b35fa05f1","mobile": "15751786630","username": "唔西","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "43535","workNumber": "88089","correctionTime": "43719","staffPhoto": ""},{"id": "6380a744e804505b35fa05fa","mobile": "18329032901","username": "高大山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-3-10","workNumber": "9","correctionTime": "-9-10","staffPhoto": ""},{"id": "6380a744e804505b35fa05fb","mobile": "18329032902","username": "高小山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-3-10","workNumber": "9","correctionTime": "-9-10","staffPhoto": ""},{"id": "6380a744e804505b35fa05fc","mobile": "18229032903","username": "高老山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-3-10","workNumber": "9","correctionTime": "-9-10","staffPhoto": ""},{"id": "6380ac66e804505b35fa0645","mobile": "17515700507","username": "哈哈","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-11-07T16:00:00.000Z","formOfEmployment": "2","workNumber": "110","correctionTime": "-11-29T16:00:00.000Z","departmentName": "技术部","staffPhoto": "https://xingziwei-1315284586.cos.ap-/a6a3b531d7840a1f43c7223eb30b2d9.png"},{"id": "6380ac99e804505b35fa0648","mobile": "15751786628","username": "海绵","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-3-10","workNumber": "88088","correctionTime": "-9-10","staffPhoto": ""},{"id": "6380ac99e804505b35fa0649","mobile": "15751786630","username": "唔西","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-3-10","workNumber": "88089","correctionTime": "-9-10","staffPhoto": ""},{"id": "6380c222e804505b35fa0763","mobile": "17666666666","username": "5654","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "-11-07T16:00:00.000Z","formOfEmployment": "1","workNumber": "6365","correctionTime": "-11-25T16:00:00.000Z","departmentName": "前端研发部","staffPhoto": ""},{"id": "6380c2bfe804505b35fa0766","mobile": "13041139819","username": "高大山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "/3/11","workNumber": "9","correctionTime": "/9/11","staffPhoto": ""},{"id": "6380c2bfe804505b35fa0767","mobile": "13041130189","username": "高小山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "/3/11","workNumber": "9","correctionTime": "/9/11","staffPhoto": ""},{"id": "6380c2bfe804505b35fa0768","mobile": "13041119899","username": "高老山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "/3/11","workNumber": "9","correctionTime": "/9/11","staffPhoto": ""},{"id": "6380c2d8e804505b35fa076f","mobile": "13041139819","username": "高大山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "/3/11","workNumber": "9","correctionTime": "/9/11","staffPhoto": ""},{"id": "6380c2d8e804505b35fa0770","mobile": "13041130189","username": "高小山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "/3/11","workNumber": "9","correctionTime": "/9/11","staffPhoto": ""},{"id": "6380c2d8e804505b35fa0771","mobile": "13041119899","username": "高老山","password": "e10adc3949ba59abbe56e057f20f883e","timeOfEntry": "/3/11","workNumber": "9","correctionTime": "/9/11","staffPhoto": ""}]

新建src/mockk/mock.js

import Mock from "mockjs";import excelData from "./excel.json";// mock数据：第一个参数：请求地址第二个参数：请求数据Mock.mock("http://localhost:8080/mock/excelData", {code: 200,data: excelData,});

在main.js中引入mock

import "@/mock/mock.js";

2、使用Export2Excel.js导入导出excel数据

新建src/vendor/Export2Excel.js

/* eslint-disable */import {saveAs } from "file-saver";// import XLSX from 'xlsx'import * as 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 rangesranges.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 Spanif (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 ValueoutRow.push(cellValue !== "" ? cellValue : null);//Handle Colspanif (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、App.vue代码

<template><div><el-buttontype="primary"@click="excelData"style="margin: 50px 50px 50px 50px">导出excel</el-button></div></template><script>import axios from "axios";export default {name: "App",data() {return {tableData: [],};},created() {this.getData();},methods: {// 封装获取数据的函数获取数据getData() {axios.get("http://localhost:8080/mock/excelData").then((res) => {this.tableData = res.data.data;});},// 导出excel数据excelData() {// 表头对应关系（因为数据中的key是英文，想要导出的表头是中文的话，需要将中文和英文做对应）const headers = {姓名: "username",手机号: "mobile",入职日期: "timeOfEntry",聘用形式: "formOfEmployment",转正日期: "correctionTime",工号: "workNumber",部门: "departmentName",};// 懒加载import("@/vendor/Export2Excel").then((excel) => {const data = this.formatJson(headers, this.tableData);excel.export_json_to_excel({header: Object.keys(headers),data,filename: "员工资料表",});});},// 该方法负责将数组转化成二维数组formatJson(headers, rows) {//首先遍历数组return rows.map((item) => {// item是对象 => 转化成只有值的数组 => 数组值的顺序依赖headers {username: '张三' }// Object.keys(headers) => ["姓名", "手机号",...]return Object.keys(headers).map((key) => {return item[headers[key]]; // 得到 ['张三'，’129‘，’dd‘,'dd']});});},},};</script>

五、运行结果

点击导出excel表格，会导出一个excel表格

六、进阶（复杂表头的导出）

vue-element-admin提供的导出方法中有multiHeader和merges的参数

multiHeader里面是一个二维数组，里面的一个元素是一行表头，假设你想得到一个如图的结构

mutiHeader应该这样定义

const multiHeader = [['姓名', '主要信息', '', '', '', '', '部门']]

multiHeader中的一行表头中的字段的个数需要和真正的列数相等，假设想要跨列，多余的空间需要定义成空串

它主要对应的是标准的表头

const header = ['姓名', '手机号', '入职日期', '聘用形式', '转正日期', '工号', '部门']

如果，我们要实现其合并的效果，需要设定merges选项

const merges = ['A1:A2', 'B1:F1', 'G1:G2']

merges的顺序是没关系的，只要配置这两个属性，就可以导出复杂表头的excel了

App.vue代码（复杂表头导出）

<template><div><el-buttontype="primary"@click="excelData"style="margin: 50px 50px 50px 50px">导出excel</el-button></div></template><script>import axios from "axios";export default {name: "App",data() {return {tableData: [],};},created() {this.getData();},methods: {// 封装获取数据的函数获取数据getData() {axios.get("http://localhost:8080/mock/excelData").then((res) => {this.tableData = res.data.data;});},// 导出excel数据excelData() {// 表头对应关系const headers = {姓名: "username",手机号: "mobile",入职日期: "timeOfEntry",聘用形式: "formOfEmployment",转正日期: "correctionTime",工号: "workNumber",部门: "departmentName",};import("@/vendor/Export2Excel").then((excel) => {const data = this.formatJson(headers, this.tableData);const multiHeader = [["姓名", "主要信息", "", "", "", "", "部门"]]; // 复杂表头格式const merges = ["A1:A2", "B1:F1", "G1:G2"]; // 合并选项excel.export_json_to_excel({header: Object.keys(headers),data,filename: "复杂员工资料表",multiHeader, // 复杂表头merges, // 合并选项});});},// 格式化数据formatJson(headers, rows) {//首先遍历数组return rows.map((item) => {// item是对象 => 转化成只有值的数组 => 数组值的顺序依赖headers {username: '张三' }// Object.keys(headers) => ["姓名", "手机号",...]return Object.keys(headers).map((key) => {return item[headers[key]]; // 得到 ['张三'，’129‘，’dd‘,'dd']});});},},};</script>

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。