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

离奇6厘米

2996人浏览 · 2022-11-25 23:17:32

离奇6厘米 · 2022-11-25 23:17:32 发布

一、项目场景

从后台拿到数据之后，然后通过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导出参数的介绍

参数	说明	类型	可选值	默认值
header	导出数据的表头	Array	/	[]
data	导出的具体数据	Array	/	[[]]
filename	导出文件名	String	/	excel-list
autoWidth	单元格是否要自适应宽度	Boolean	true / false	true
bookType	导出文件类型	String	xlsx, csv, txt, more	xlsx

3、下载file-saver和script-loader

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

npm install xlsx file-saver -S
npm 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": "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": ""
    }
]

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

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>

五、运行结果

点击导出excel表格，会导出一个excel表格
在这里插入图片描述

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

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

参数	说明	类型	可选值	默认值
multiHeader	复杂表头的部分	Array	/	[[]]
merges	需要合并的部分	Array	/	[]

multiHeader里面是一个二维数组，里面的一个元素是一行表头，假设你想得到一个如图的结构
在这里插入图片描述
mutiHeader应该这样定义

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

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

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

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

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

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

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>

Vue

前往低代码交流专区

更多推荐

vue 表单验证

1、6位小写字母和数字必须包含两个字母rules: [{ required: true, message: "XXX不能为空", trigger: "blur" },{ max: 6, message: "最大长度为6位字符", trigger: "blur" },{pattern: /^(?=(?:[^a-z]*[a-z]){2})[a-z0-9]{6,6}$/, //不连续的两位字母// /^