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

一、项目场景

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

二、实现思路

使用mock模拟后端生成数据
通过Export2Excel.js实现数据导出

三、准备工作

1、下载js-xlsx

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

npm i xlsx
1

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' //非必填
  })
})
1
2
3
4
5
6
7
8
9

excel导出参数的介绍

3、下载file-saver和script-loader

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

npm install xlsx file-saver -S
npm install script-loader -S -D
1
2

4、下载mock

使用mock来模拟后端接口

npm i mock
1

四、代码实现

1、mock数据

新建src/mock/excel.json

[
    {
        "id": "604f764971f93f3ac8f365c2",
        "mobile": "13800000002",
        "username": "脸太凉了是吧，是想吃嘴巴子",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2018-11-02",
        "formOfEmployment": "后端没有返回数据,改了没效果,保存修改接收这个参数",
        "workNumber": "9002",
        "correctionTime": "2018-11-30",
        "departmentName": "总裁办",
        "staffPhoto": "http://hr-picture-1315259797.cos.ap-guangzhou.myqcloud.com/123.jpg"
    },
    {
        "id": "604f764971f93f3ac8f365c3",
        "mobile": "13800000003",
        "username": "1楼666",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2018-11-04",
        "formOfEmployment": 1,
        "workNumber": "111",
        "correctionTime": "2018-11-20",
        "departmentName": "市场部",
        "staffPhoto": "http://xzj21-1315284655.cos.ap-beijing.myqcloud.com/userphoto.jpg"
    },
    {
        "id": "604f764971f93f3ac8f365c4",
        "mobile": "13800000004",
        "username": "禁止套娃",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2018-12-02",
        "formOfEmployment": 1,
        "workNumber": "1111",
        "correctionTime": "2018-12-31",
        "departmentName": "人事部",
        "staffPhoto": "https:lcl-1315284633.cos.ap-beijing.myqcloud.com/tttt.png"
    },
    {
        "id": "604f764971f93f3ac8f365c5",
        "mobile": "13400000001",
        "username": "南京黑马",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2018-01-01",
        "formOfEmployment": 1,
        "workNumber": "1001",
        "correctionTime": "1970-01-01",
        "departmentName": "人事部",
        "staffPhoto": "https:lcl-1315284633.cos.ap-beijing.myqcloud.com/tttt.png"
    },
    {
        "id": "604f764971f93f3ac8f365c6",
        "mobile": "13400000002",
        "username": "M78星云",
        "password": "3d9188577cc9bfe9291ac66b5cc872b7",
        "timeOfEntry": "2018-01-01",
        "formOfEmployment": 1,
        "workNumber": "1002",
        "correctionTime": "1970-01-01",
        "departmentName": "人事部",
        "staffPhoto": "http://hr-picture-1315259797.cos.ap-guangzhou.myqcloud.com/6004.jpg"
    },
    {
        "id": "604f764971f93f3ac8f365c7",
        "mobile": "13500000001",
        "username": "3",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2018-01-01",
        "formOfEmployment": 1,
        "workNumber": "2001",
        "correctionTime": "1970-01-01",
        "departmentName": "总裁办",
        "staffPhoto": "https://zhiyin-1315331394.cos.ap-shanghai.myqcloud.com/1669364404889.jpg"
    },
    {
        "id": "604f764971f93f3ac8f365c8",
        "mobile": "13500000002",
        "username": "5",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2018-01-31",
        "formOfEmployment": 1,
        "workNumber": "2002",
        "correctionTime": "1970-01-01",
        "departmentName": "总裁办",
        "staffPhoto": "https://zhiyin-1315331394.cos.ap-shanghai.myqcloud.com/1669364472536.jpg"
    },
    {
        "id": "604f764971f93f3ac8f365c9",
        "mobile": "13500000003",
        "username": "6",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2018-01-01",
        "formOfEmployment": 1,
        "workNumber": "2003",
        "correctionTime": "1970-01-01",
        "departmentName": "财务部",
        "staffPhoto": "https://zhiyin-1315331394.cos.ap-shanghai.myqcloud.com/1669365556365.jpg"
    },
    {
        "id": "604f764971f93f3ac8f365ca",
        "mobile": "13600000001",
        "username": "8",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "1992-08-04",
        "formOfEmployment": 1,
        "workNumber": "0001",
        "correctionTime": "2020-01-01",
        "departmentName": "总裁办",
        "staffPhoto": "https://zhiyin-1315331394.cos.ap-shanghai.myqcloud.com/1669365533382.jpg"
    },
    {
        "id": "604f764971f93f3ac8f365cb",
        "mobile": "13600000002",
        "username": "保罗乔治",
        "password": "4297f44b13955235245b2497399d7a93",
        "timeOfEntry": "1993-08-04",
        "formOfEmployment": 1,
        "workNumber": "0002",
        "correctionTime": "2020-01-01",
        "departmentName": "总裁办",
        "staffPhoto": "https://zhiyin-1315331394.cos.ap-shanghai.myqcloud.com/1669365585378.jpg"
    },
    {
        "id": "604f764971f93f3ac8f365cc",
        "mobile": "13600000003",
        "username": "河马1",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "1993-08-04",
        "formOfEmployment": 1,
        "workNumber": "0003",
        "correctionTime": "2020-01-01",
        "departmentName": "行政部",
        "staffPhoto": "https://cql-1315331299.cos.ap-nanjing.myqcloud.com/1669361855846.jpg"
    },
    {
        "id": "604f764971f93f3ac8f365cd",
        "mobile": "13600000004",
        "username": "宋高昂",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "1993-08-04",
        "formOfEmployment": 1,
        "workNumber": "0004",
        "correctionTime": "2020-01-01",
        "departmentName": "人事部",
        "staffPhoto": "//hrsass-151-1315334652.cos.ap-beijing.myqcloud.com/1669363404981%E6%B0%B8%E6%81%A9.jpg"
    },
    {
        "id": "604f764971f93f3ac8f365ce",
        "mobile": "13600000005",
        "username": "师玉堂",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "1993-08-04",
        "formOfEmployment": 1,
        "workNumber": "0005",
        "correctionTime": "2020-01-01",
        "departmentName": "财务部",
        "staffPhoto": "http://rubbish-1315218803.cos.ap-nanjing.myqcloud.com/params.file.name"
    },
    {
        "id": "604f764971f93f3ac8f365cf",
        "mobile": "13600000006",
        "username": "武高丽",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "1993-08-04",
        "formOfEmployment": 1,
        "workNumber": "0006",
        "correctionTime": "2020-01-01",
        "departmentName": "技术部",
        "staffPhoto": ""
    },
    {
        "id": "604f764971f93f3ac8f365d0",
        "mobile": "13600000007",
        "username": "任志",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "1993-08-04",
        "formOfEmployment": 1,
        "workNumber": "0007",
        "correctionTime": "2020-01-01",
        "departmentName": "运营部",
        "staffPhoto": ""
    },
    {
        "id": "604f764971f93f3ac8f365d1",
        "mobile": "13600000008",
        "username": "瞿光明",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "1993-08-04",
        "formOfEmployment": 1,
        "workNumber": "0008",
        "correctionTime": "2020-01-01",
        "departmentName": "市场部",
        "staffPhoto": ""
    },
    {
        "id": "63807028e804505b35fa0408",
        "mobile": "15751786628",
        "username": "张飞1",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-03-10",
        "workNumber": "88088",
        "correctionTime": "2019-09-10",
        "staffPhoto": ""
    },
    {
        "id": "63807351e804505b35fa041b",
        "mobile": "13900333333",
        "username": "撒旦法",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2022-11-07T16:00:00.000Z",
        "formOfEmployment": "2",
        "workNumber": "12312",
        "correctionTime": "2022-11-15T16:00:00.000Z",
        "departmentName": "财务部",
        "staffPhoto": ""
    },
    {
        "id": "638077abe804505b35fa043c",
        "mobile": "13041130789",
        "username": "高小山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20099",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "638077abe804505b35fa043d",
        "mobile": "13041119799",
        "username": "高老山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20189",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "638077bfe804505b35fa0444",
        "mobile": "13041139879",
        "username": "高大山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20089",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "638077bfe804505b35fa0445",
        "mobile": "13041130789",
        "username": "高小山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20099",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "638077bfe804505b35fa0446",
        "mobile": "13041119799",
        "username": "高老山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20189",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "63807919e804505b35fa0458",
        "mobile": "13041139879",
        "username": "高大山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20089",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "63807919e804505b35fa0459",
        "mobile": "13041130789",
        "username": "高小山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20099",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "63807919e804505b35fa045a",
        "mobile": "13041119799",
        "username": "高老山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20189",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "63807d2ee804505b35fa046d",
        "mobile": "13041139879",
        "username": "高大山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20089",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "63807d2ee804505b35fa046f",
        "mobile": "13041119799",
        "username": "高老山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20189",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "63807de3e804505b35fa0478",
        "mobile": "13041131235",
        "username": "高小山123",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20099",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "63807de3e804505b35fa0479",
        "mobile": "13041111236",
        "username": "高老山123",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20189",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "63807dede804505b35fa0480",
        "mobile": "13800000002",
        "username": "我去",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2022-10-31T16:00:00.000Z",
        "formOfEmployment": "1",
        "workNumber": "2131231",
        "correctionTime": "2022-11-25T16:00:00.000Z",
        "departmentName": "行政部",
        "staffPhoto": ""
    },
    {
        "id": "63807ebbe804505b35fa0489",
        "mobile": "13041139879",
        "username": "高大山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-03-09T16:00:00.000Z",
        "workNumber": "20089",
        "correctionTime": "2019-09-09T16:00:00.000Z",
        "staffPhoto": ""
    },
    {
        "id": "63807ebbe804505b35fa048a",
        "mobile": "13041130789",
        "username": "高小山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-03-09T16:00:00.000Z",
        "workNumber": "20099",
        "correctionTime": "2019-09-09T16:00:00.000Z",
        "staffPhoto": ""
    },
    {
        "id": "63807ebbe804505b35fa048b",
        "mobile": "13041119799",
        "username": "高老山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-03-09T16:00:00.000Z",
        "workNumber": "20189",
        "correctionTime": "2019-09-09T16:00:00.000Z",
        "staffPhoto": ""
    },
    {
        "id": "63807fa5e804505b35fa0497",
        "mobile": "13041431234",
        "username": "高9大山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-03-09T16:00:00.000Z",
        "workNumber": "20089",
        "correctionTime": "2019-09-09T16:00:00.000Z",
        "staffPhoto": ""
    },
    {
        "id": "63807fa5e804505b35fa0498",
        "mobile": "13041141235",
        "username": "高9小山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-03-09T16:00:00.000Z",
        "workNumber": "20099",
        "correctionTime": "2019-09-09T16:00:00.000Z",
        "staffPhoto": ""
    },
    {
        "id": "63807fa5e804505b35fa0499",
        "mobile": "13041115236",
        "username": "高老山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-03-09T16:00:00.000Z",
        "workNumber": "20189",
        "correctionTime": "2019-09-09T16:00:00.000Z",
        "staffPhoto": ""
    },
    {
        "id": "63808107e804505b35fa04a7",
        "mobile": "13944466622",
        "username": "123",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2022-10-31T16:00:00.000Z",
        "formOfEmployment": "1",
        "workNumber": "阿萨德",
        "correctionTime": "2022-11-13T16:00:00.000Z",
        "departmentName": "财务部",
        "staffPhoto": ""
    },
    {
        "id": "6380822ee804505b35fa04ac",
        "mobile": "18329032903",
        "username": "学霸本霸",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2022-11-24T16:00:00.000Z",
        "formOfEmployment": "1",
        "workNumber": "100000",
        "correctionTime": "2022-11-24T16:00:00.000Z",
        "departmentName": "行政部",
        "staffPhoto": ""
    },
    {
        "id": "638083f6e804505b35fa04b4",
        "mobile": "18735291056",
        "username": "www",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2022-11-24T16:00:00.000Z",
        "formOfEmployment": "1",
        "workNumber": "1111",
        "correctionTime": "2022-11-24T16:00:00.000Z",
        "departmentName": "人事部",
        "staffPhoto": ""
    },
    {
        "id": "63808f1de804505b35fa04e1",
        "mobile": "19112421250",
        "username": "巴拉啦",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2022-11-15T16:00:00.000Z",
        "formOfEmployment": "1",
        "workNumber": "1888",
        "correctionTime": "2022-11-27T16:00:00.000Z",
        "departmentName": "技术部",
        "staffPhoto": "http://biubiu-223-1315227161.cos.ap-chongqing.myqcloud.com/QQ%E5%9B%BE%E7%89%8720220916170032.jpg"
    },
    {
        "id": "638091b6e804505b35fa0522",
        "mobile": "13222222222",
        "username": "天天",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2022-11-14T16:00:00.000Z",
        "formOfEmployment": "2",
        "workNumber": "123",
        "correctionTime": "2022-11-09T16:00:00.000Z",
        "departmentName": "财务核算部",
        "staffPhoto": ""
    },
    {
        "id": "6380980fe804505b35fa0556",
        "mobile": "13042239879",
        "username": "高大山1",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20089",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "6380980fe804505b35fa0557",
        "mobile": "13041330789",
        "username": "高小山2",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20099",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "6380980fe804505b35fa0558",
        "mobile": "13042319799",
        "username": "高老山3",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20189",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "63809846e804505b35fa055f",
        "mobile": "13042239879",
        "username": "高大山1",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20089",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "63809846e804505b35fa0560",
        "mobile": "13041330789",
        "username": "高小山2",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20099",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "63809846e804505b35fa0561",
        "mobile": "13042319799",
        "username": "高老山3",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20189",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "6380987be804505b35fa0571",
        "mobile": "13042239879",
        "username": "高大山1",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20089",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "6380987be804505b35fa0572",
        "mobile": "13041330789",
        "username": "高小山2",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20099",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "6380987be804505b35fa0573",
        "mobile": "13042319799",
        "username": "高老山3",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20189",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "638098afe804505b35fa057e",
        "mobile": "13041330789",
        "username": "高小山2",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20099",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "638098afe804505b35fa057f",
        "mobile": "13042319799",
        "username": "高老山3",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "43535",
        "workNumber": "20189",
        "correctionTime": "43719",
        "staffPhoto": ""
    },
    {
        "id": "638098cfe804505b35fa0586",
        "mobile": "13222222222",
        "username": "123",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2022-11-07T16:00:00.000Z",
        "formOfEmployment": "2",
        "workNumber": "123",
        "correctionTime": "2022-11-16T16:00:00.000Z",
        "departmentName": "人事部",
        "staffPhoto": ""
    },
    {
        "id": "638099dee804505b35fa0595",
        "mobile": "13042239879",
        "username": "高大山1",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-03-09T16:00:00.000Z",
        "workNumber": "20089",
        "correctionTime": "2019-09-09T16:00:00.000Z",
        "staffPhoto": ""
    },
    {
        "id": "638099dee804505b35fa0596",
        "mobile": "13041330789",
        "username": "高小山2",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-03-09T16:00:00.000Z",
        "workNumber": "20099",
        "correctionTime": "2019-09-09T16:00:00.000Z",
        "staffPhoto": ""
    },
    {
        "id": "638099dee804505b35fa0597",
        "mobile": "13042319799",
        "username": "高老山3",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-03-09T16:00:00.000Z",
        "workNumber": "20189",
        "correctionTime": "2019-09-09T16:00:00.000Z",
        "staffPhoto": ""
    },
    {
        "id": "63809a22e804505b35fa059e",
        "mobile": "13042239879",
        "username": "高大山1",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-03-06T16:00:00.000Z",
        "workNumber": "20089",
        "correctionTime": "2019-09-09T16:00:00.000Z",
        "staffPhoto": ""
    },
    {
        "id": "63809a22e804505b35fa059f",
        "mobile": "13041330789",
        "username": "高小山2",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-04-06T16:00:00.000Z",
        "workNumber": "20099",
        "correctionTime": "2019-09-09T16:00:00.000Z",
        "staffPhoto": ""
    },
    {
        "id": "63809a54e804505b35fa05a8",
        "mobile": "13042239879",
        "username": "高大山1",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-03-07T16:00:00.000Z",
        "workNumber": "20089",
        "correctionTime": "2019-09-10T16:00:00.000Z",
        "staffPhoto": ""
    },
    {
        "id": "63809a54e804505b35fa05aa",
        "mobile": "13042319799",
        "username": "高老山3",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-05-07T16:00:00.000Z",
        "workNumber": "20189",
        "correctionTime": "2019-09-10T16:00:00.000Z",
        "staffPhoto": ""
    },
    {
        "id": "63809c48e804505b35fa05b7",
        "mobile": "13042239879",
        "username": "高大山1",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-03-09T16:00:00.000Z",
        "workNumber": "20089",
        "correctionTime": "2019-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": "2019-3-10",
        "workNumber": "20089",
        "correctionTime": "2019-9-10",
        "staffPhoto": ""
    },
    {
        "id": "6380a744e804505b35fa05fb",
        "mobile": "18329032902",
        "username": "高小山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-3-10",
        "workNumber": "20099",
        "correctionTime": "2019-9-10",
        "staffPhoto": ""
    },
    {
        "id": "6380a744e804505b35fa05fc",
        "mobile": "18229032903",
        "username": "高老山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-3-10",
        "workNumber": "20189",
        "correctionTime": "2019-9-10",
        "staffPhoto": ""
    },
    {
        "id": "6380ac66e804505b35fa0645",
        "mobile": "17515700507",
        "username": "哈哈",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2022-11-07T16:00:00.000Z",
        "formOfEmployment": "2",
        "workNumber": "110",
        "correctionTime": "2022-11-29T16:00:00.000Z",
        "departmentName": "技术部",
        "staffPhoto": "https://xingziwei-1315284586.cos.ap-beijing.myqcloud.com/a6a3b531d7840a1f43c7223eb30b2d9.png"
    },
    {
        "id": "6380ac99e804505b35fa0648",
        "mobile": "15751786628",
        "username": "海绵",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-3-10",
        "workNumber": "88088",
        "correctionTime": "2019-9-10",
        "staffPhoto": ""
    },
    {
        "id": "6380ac99e804505b35fa0649",
        "mobile": "15751786630",
        "username": "唔西",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019-3-10",
        "workNumber": "88089",
        "correctionTime": "2019-9-10",
        "staffPhoto": ""
    },
    {
        "id": "6380c222e804505b35fa0763",
        "mobile": "17666666666",
        "username": "5654",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2022-11-07T16:00:00.000Z",
        "formOfEmployment": "1",
        "workNumber": "6365",
        "correctionTime": "2022-11-25T16:00:00.000Z",
        "departmentName": "前端研发部",
        "staffPhoto": ""
    },
    {
        "id": "6380c2bfe804505b35fa0766",
        "mobile": "13041139819",
        "username": "高大山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019/3/11",
        "workNumber": "20089",
        "correctionTime": "2019/9/11",
        "staffPhoto": ""
    },
    {
        "id": "6380c2bfe804505b35fa0767",
        "mobile": "13041130189",
        "username": "高小山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019/3/11",
        "workNumber": "20099",
        "correctionTime": "2019/9/11",
        "staffPhoto": ""
    },
    {
        "id": "6380c2bfe804505b35fa0768",
        "mobile": "13041119899",
        "username": "高老山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019/3/11",
        "workNumber": "20189",
        "correctionTime": "2019/9/11",
        "staffPhoto": ""
    },
    {
        "id": "6380c2d8e804505b35fa076f",
        "mobile": "13041139819",
        "username": "高大山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019/3/11",
        "workNumber": "20089",
        "correctionTime": "2019/9/11",
        "staffPhoto": ""
    },
    {
        "id": "6380c2d8e804505b35fa0770",
        "mobile": "13041130189",
        "username": "高小山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019/3/11",
        "workNumber": "20099",
        "correctionTime": "2019/9/11",
        "staffPhoto": ""
    },
    {
        "id": "6380c2d8e804505b35fa0771",
        "mobile": "13041119899",
        "username": "高老山",
        "password": "e10adc3949ba59abbe56e057f20f883e",
        "timeOfEntry": "2019/3/11",
        "workNumber": "20189",
        "correctionTime": "2019/9/11",
        "staffPhoto": ""
    }
]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824

新建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,
});
1
2
3
4
5
6
7
8

在main.js中引入mock

import "@/mock/mock.js";
1

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233

3、App.vue代码

<template>
  <div>
    <el-button
      type="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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

五、运行结果

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

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

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

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

mutiHeader应该这样定义

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

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

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

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

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

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

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

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

<template>
  <div>
    <el-button
      type="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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69