目录
一、安装 node-xlsx二、导出excel表三、导入excel表结语一、安装 node-xlsx
npm i node-xlsx
二、导出excel表
首先是查询出数据库内所有的表的信息，然后传至下一个下划线转大小写的公用函数，进行key的转换，然后就进行数据的加工；
大小写转换函数封装可看此篇文章
因为导出except表的宫功能会在业务中频繁使用，所以我们需要给它封装成一个公用函数，其他的业务使用直接传参调用即可！
(相关资料图)
1、封装公用函数
/**
* 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例子：
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) => {
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) => {
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的资料请关注脚本之家其它相关文章！
