一、安装 node-xlsx
|
1
|
npm i node-xlsx |
二、导出excel表
首先是查询出数据库内所有的表的信息，然后传至下一个下划线转大小写的公用函数，进行key的转换，然后就进行数据的加工；
大小写转换函数封装可看此篇文章
因为导出except表的宫功能会在业务中频繁使用，所以我们需要给它封装成一个公用函数，其他的业务使用直接传参调用即可！
1、封装公用函数
|
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
|
/** * excel 导出 * list:[{}] * headers:表头中文名 * headerKeys:与表头中文名一一对应的数据区key * tableName：导出的表名称以什么开头 */export const excelExport = (list, headers, headerKeys, tableName = 'excel') => { // excel 通用样式 const sheetOptions = { '!cols': [] } headers.forEach(() => { sheetOptions['!cols'].push({ wch: 20 }) }) const data = [] list.forEach((item) => { let arr = [] const item2 = flatten(item) headerKeys.forEach((key) => { if (excelMap.changDictExport[key]) { arr.push(excelMap.changDictExport[key][item[key]]) } else { arr.push(item2[key]) } }) data.push(arr) }) data.unshift(headers) const buffer = xlsx.build( [{ options: {}, name: `${tableName}_${new Date().valueOf()}`, data: data }], { sheetOptions } ) return buffer} |
2、headers例子：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
userHeader: [ '用户序号', '登录名称', '用户邮箱', '手机号码', '用户性别', '帐号状态', '最后登录IP', '最后登录时间', '部门名称', '部门负责人'], |
3、headerKeys例子：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
userHeaderKeys: [ 'userId', 'userName', 'email', 'phonenumber', 'sex', 'status', 'loginIp', 'loginDate', 'dept.deptName', 'dept.leader'] |
4、因为数据可能存在跨表查询，会出现对象嵌套结构，故需要封装对象扁平化函数flatten，将多层结构的key变成dept.deptName这种格式
|
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
|
/** * 对象扁平化 */export const flatten = (obj) => { let result = {} let process = (key, value) => { // 首先判断是基础数据类型还是引用数据类型 if (Object(value) !== value) { // 基础数据类型 if (key) { result[key] = value } } else if (Array.isArray(value)) { for (let i = 0; i < value.length; i++) { process(`${key}[${i}]`, value[i]) } if (value.length === 0) { result[key] = [] } } else { let objArr = Object.keys(value) objArr.forEach((item) => { process(key ? `${key}.${item}` : `${item}`, value[item]) }) if (objArr.length === 0 && key) { result[key] = {} } } } process('', obj) return result} |
5、node-xlsx接收的数据格式
|
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
|
[ [ '用户序号', '登录名称', '用户邮箱', '手机号码', '用户性别', '帐号状态', '最后登录IP', '最后登录时间', '部门名称', '部门负责人' ], [ 1, 'admin', '12012311715@163.com', '12012311715', '男', '正常', '', '00:00:00', '深圳总公司', 'wen' ], [ 2, 'password', null, null, '未知', '正常', null, null, '研发部门', 'wen' ]] |
6、在业务函数中调用导出excel表的数据
|
1
2
3
4
5
6
|
const buffer = excelExport( users, excelExportMap.userHeader, excelExportMap.userHeaderKeys, 'user' ) |
7、最终excel导出的效果
三、导入excel表
因为导入的情况比较复杂，会分为多种情况上传excel文件：
1、单文件单工作表；
2、单文件多工作表；
3、多文件(单)多工作表；
我个人解决办法是获取放置excel文件的文件夹内所有的excel文件，然后进行数据的提取，在提取完数据后，就将该次的excel文件删除掉，当然导入excel功能也是需要进行公用封装的；
1、解析本次导入的所有excel文件内数据
|
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
|
// 导入excel--解析export const importExcelsMid = (tableMap: string) => { return async (ctx: Context, next: () => Promise<void>) => { try { const fileExistPath = path.resolve() + '\src\upload' let fileName = [] // 多个excel文件保存地 fs.readdirSync(path.format({ dir: fileExistPath })).forEach((excel) => { if (excel.split('.')[excel.split('.').length - 1] === 'xlsx' && 'xls') { fileName.push(excel) } }) // 拿去多个excel文件 const workSheetsFromBuffer = [] fileName.forEach((item) => { const absoluteFilePath = fileExistPath + '\' + item //整个文件的绝对路径 workSheetsFromBuffer.push(xlsx.parse(fs.readFileSync(absoluteFilePath))) //这种方式是解析buffer }) // 生成默认用户密码 const salt = bcrypt.genSaltSync(10) const hash = bcrypt.hashSync('123456', salt) const arr = [] // 存储sql批量创建的信息 object[] workSheetsFromBuffer.forEach((element) => { element.forEach((item: any) => { // 此层是遍历表数量(单表数据提取) const data = item.data for (let j = 1; j < data.length; j++) { // 此层是加入每行数据 const obj = { password: hash } for (let i = 0; i < data[0].length; i++) { let key = excelMap[tableMap][data[0][i]] if (excelMap.changDict[key]) { obj[key] = excelMap.changDict[key][data[j][i]] } else { obj[key] = data[j][i] } } arr.push(obj) } }) }) // 获取数据后删除excel文件 fileName.forEach((path) => { removeSpecifyFile(path) }) ctx.state.excelData = arr } catch (error) { console.error('用户excel上传表头格式不正确!', ctx.request['body']) return ctx.app.emit('error', importUserListErr, ctx) } await next() }} |
2、写入本次导入的所有excel文件内数据
updates：是控制你更新哪些的key数组
|
1
|
[ 'dept_id', 'user_name', 'nick_name', 'email', 'phonenumber', 'sex', 'status' ] |
updates案例
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
// 导入excel--新增修改sqlexport const judegImportMid = (table, updates) => { return async (ctx: Context, next: () => Promise<void>) => { const { updateSupport } = ctx.query try { if (updateSupport === '1') { // 新增 且 修改 await table.bulkCreate(ctx.state.excelData, { updateOnDuplicate: updates }) } else { // 不更改 只新增 await table.bulkCreate(ctx.state.excelData) } ctx.body = { code: 200, message: '用户信息上传成功！' } } catch (error) { console.error('user excel新增与修改错误', ctx.request['body']) return ctx.app.emit('error', { code: '400', message: error.errors[0].message }, ctx) } }} |
3、导入的案例excel
4、解析后的数据
|
1
2
3
4
5
6
7
8
9
10
11
|
[ { password: '$2a$10$Mp19aHpTTIZXwAYpwAg8QuOUQ6DmBswHFhwR8iRqjduNw9tQU.xRO', undefined: 'test', user_name: 'test', email: 'test', phonenumber: 'test', sex: '0', status: '0' }] |
5、已写入数据库
结语
上面的功能仅仅是完成了基本的excel导入和导出，更多关于koa2导入导出Excel的资料请关注服务器之家其它相关文章！
原文链接：https://juejin.cn/post/7211388928241418296
