一、安装 node-xlsx
npm i node-xlsx
二、导出excel表
首先是查询出数据库内所有的表的信息，然后传至下一个下划线转大小写的公用函数，进行key的转换，然后就进行数据的加工；
大小写转换函数封装可看此篇文章
因为导出except表的宫功能会在业务中频繁使用，所以我们需要给它封装成一个公用函数，其他的业务使用直接传参调用即可！
1、封装公用函数
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例子：
userHeader: [
'用户序号',
'登录名称',
'用户邮箱',
'手机号码',
'用户性别',
'帐号状态',
'最后登录IP',
'最后登录时间',
'部门名称',
'部门负责人'
],
3、headerKeys例子：
userHeaderKeys: [
'userId',
'userName',
'email',
'phonenumber',
'sex',
'status',
'loginIp',
'loginDate',
'dept.deptName',
'dept.leader'
]
4、因为数据可能存在跨表查询，会出现对象嵌套结构，故需要封装对象扁平化函数flatten，将多层结构的key变成dept.deptName这种格式
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接收的数据格式
[
[
'用户序号',
'登录名称',
'用户邮箱',
'手机号码',
'用户性别',
'帐号状态',
'最后登录IP',
'最后登录时间',
'部门名称',
'部门负责人'
],
[
1,
'admin',
'12012311715@163.com',
'12012311715',
'男',
'正常',
'',
'00:00:00',
'深圳总公司',
'wen'
],
[
2, 'password',
null, null,
'未知', '正常',
null, null,
'研发部门', 'wen'
]
]
6、在业务函数中调用导出excel表的数据
const buffer = excelExport(
users,
excelExportMap.userHeader,
excelExportMap.userHeaderKeys,
'user'
)
7、最终excel导出的效果
三、导入excel表
因为导入的情况比较复杂，会分为多种情况上传excel文件：
1、单文件单工作表；
2、单文件多工作表；
3、多文件(单)多工作表；
我个人解决办法是获取放置excel文件的文件夹内所有的excel文件，然后进行数据的提取，在提取完数据后，就将该次的excel文件删除掉，当然导入excel功能也是需要进行公用封装的；
1、解析本次导入的所有excel文件内数据
// 导入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数组
[ 'dept_id', 'user_name', 'nick_name', 'email', 'phonenumber', 'sex', 'status' ]
updates案例 ☝
// 导入excel--新增修改sql
export 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、解析后的数据
[
{
password: '$2a$10$Mp19aHpTTIZXwAYpwAg8QuOUQ6DmBswHFhwR8iRqjduNw9tQU.xRO',
undefined: 'test',
user_name: 'test',
email: 'test',
phonenumber: 'test',
sex: '0',
status: '0'
}
]
5、已写入数据库
结语
上面的功能仅仅是完成了基本的excel导入和导出，更多关于koa2导入导出Excel的资料请关注编程网其它相关文章！
