詳解使用koa2完成Excel導(dǎo)入導(dǎo)出

更新時(shí)間：2023年03月31日 09:47:22 作者：我擱這敲代碼呢

這篇文章主要為大家介紹了詳解使用koa2完成Excel導(dǎo)入導(dǎo)出示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪

一、安裝 node-xlsx

npm i node-xlsx

二、導(dǎo)出excel表

首先是查詢(xún)出數(shù)據(jù)庫(kù)內(nèi)所有的表的信息，然后傳至下一個(gè)下劃線轉(zhuǎn)大小寫(xiě)的公用函數(shù)，進(jìn)行key的轉(zhuǎn)換，然后就進(jìn)行數(shù)據(jù)的加工；

大小寫(xiě)轉(zhuǎn)換函數(shù)封裝可看此篇文章

因?yàn)閷?dǎo)出except表的宮功能會(huì)在業(yè)務(wù)中頻繁使用，所以我們需要給它封裝成一個(gè)公用函數(shù)，其他的業(yè)務(wù)使用直接傳參調(diào)用即可！

1、封裝公用函數(shù)

/**
 * excel 導(dǎo)出
 * list:[{}]
 * headers:表頭中文名
 * headerKeys:與表頭中文名一一對(duì)應(yīng)的數(shù)據(jù)區(qū)key
 * tableName：導(dǎo)出的表名稱(chēng)以什么開(kāi)頭
 */
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: [
    '用戶(hù)序號(hào)',
    '登錄名稱(chēng)',
    '用戶(hù)郵箱',
    '手機(jī)號(hào)碼',
    '用戶(hù)性別',
    '帳號(hào)狀態(tài)',
    '最后登錄IP',
    '最后登錄時(shí)間',
    '部門(mén)名稱(chēng)',
    '部門(mén)負(fù)責(zé)人'
  ],

3、headerKeys例子：

  userHeaderKeys: [
    'userId',
    'userName',
    'email',
    'phonenumber',
    'sex',
    'status',
    'loginIp',
    'loginDate',
    'dept.deptName',
    'dept.leader'
  ]

4、因?yàn)閿?shù)據(jù)可能存在跨表查詢(xún)，會(huì)出現(xiàn)對(duì)象嵌套結(jié)構(gòu)，故需要封裝對(duì)象扁平化函數(shù)flatten，將多層結(jié)構(gòu)的key變成dept.deptName這種格式

/**
 * 對(duì)象扁平化
 */
export const flatten = (obj) => {
  let result = {}
  let process = (key, value) => {
    // 首先判斷是基礎(chǔ)數(shù)據(jù)類(lèi)型還是引用數(shù)據(jù)類(lèi)型
    if (Object(value) !== value) {
      // 基礎(chǔ)數(shù)據(jù)類(lèi)型
      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接收的數(shù)據(jù)格式

 [
  [
    '用戶(hù)序號(hào)',
    '登錄名稱(chēng)',
    '用戶(hù)郵箱',
    '手機(jī)號(hào)碼',
    '用戶(hù)性別',
    '帳號(hào)狀態(tài)',
    '最后登錄IP',
    '最后登錄時(shí)間',
    '部門(mén)名稱(chēng)',
    '部門(mén)負(fù)責(zé)人'
  ],
  [
    1,
    'admin',
    '12012311715@163.com',
    '12012311715',
    '男',
    '正常',
    '',
    '00:00:00',
    '深圳總公司',
    'wen'
  ],
  [
    2,          'password',
    null,       null,
    '未知',     '正常',
    null,       null,
    '研發(fā)部門(mén)', 'wen'
  ]
 ]

6、在業(yè)務(wù)函數(shù)中調(diào)用導(dǎo)出excel表的數(shù)據(jù)

 const buffer = excelExport(
      users,
      excelExportMap.userHeader,
      excelExportMap.userHeaderKeys,
      'user'
    )

7、最終excel導(dǎo)出的效果

三、導(dǎo)入excel表

因?yàn)閷?dǎo)入的情況比較復(fù)雜，會(huì)分為多種情況上傳excel文件：

1、單文件單工作表；

2、單文件多工作表；

3、多文件(單)多工作表；

我個(gè)人解決辦法是獲取放置excel文件的文件夾內(nèi)所有的excel文件，然后進(jìn)行數(shù)據(jù)的提取，在提取完數(shù)據(jù)后，就將該次的excel文件刪除掉，當(dāng)然導(dǎo)入excel功能也是需要進(jìn)行公用封裝的；

1、解析本次導(dǎo)入的所有excel文件內(nèi)數(shù)據(jù)

// 導(dǎo)入excel--解析
export const importExcelsMid = (tableMap: string) => {
  return async (ctx: Context, next: () => Promise<void>) => {
    try {
      const fileExistPath = path.resolve() + '\src\upload'
      let fileName = [] // 多個(gè)excel文件保存地
      fs.readdirSync(path.format({ dir: fileExistPath })).forEach((excel) => {
        if (excel.split('.')[excel.split('.').length - 1] === 'xlsx' && 'xls') {
          fileName.push(excel)
        }
      })
      // 拿去多個(gè)excel文件
      const workSheetsFromBuffer = []
      fileName.forEach((item) => {
        const absoluteFilePath = fileExistPath + '\' + item //整個(gè)文件的絕對(duì)路徑
        workSheetsFromBuffer.push(xlsx.parse(fs.readFileSync(absoluteFilePath))) //這種方式是解析buffer
      })
      // 生成默認(rèn)用戶(hù)密碼
      const salt = bcrypt.genSaltSync(10)
      const hash = bcrypt.hashSync('123456', salt)
      const arr = [] // 存儲(chǔ)sql批量創(chuàng)建的信息 object[]
      workSheetsFromBuffer.forEach((element) => {
        element.forEach((item: any) => {
          // 此層是遍歷表數(shù)量(單表數(shù)據(jù)提取)
          const data = item.data
          for (let j = 1; j < data.length; j++) {
            // 此層是加入每行數(shù)據(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)
          }
        })
      })
      // 獲取數(shù)據(jù)后刪除excel文件
      fileName.forEach((path) => {
        removeSpecifyFile(path)
      })
      ctx.state.excelData = arr
    } catch (error) {
      console.error('用戶(hù)excel上傳表頭格式不正確!', ctx.request['body'])
      return ctx.app.emit('error', importUserListErr, ctx)
    }
    await next()
  }
}

2、寫(xiě)入本次導(dǎo)入的所有excel文件內(nèi)數(shù)據(jù)

updates：是控制你更新哪些的key數(shù)組

 [    'dept_id',    'user_name',    'nick_name',    'email',    'phonenumber',    'sex',    'status'  ]

updates案例 ?

// 導(dǎo)入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: '用戶(hù)信息上傳成功！'
      }
    } catch (error) {
      console.error('user excel新增與修改錯(cuò)誤', ctx.request['body'])
      return ctx.app.emit('error', { code: '400', message: error.errors[0].message }, ctx)
    }
  }
}

3、導(dǎo)入的案例excel

4、解析后的數(shù)據(jù)

[
  {
    password: '$2a$10$Mp19aHpTTIZXwAYpwAg8QuOUQ6DmBswHFhwR8iRqjduNw9tQU.xRO',
    undefined: 'test',
    user_name: 'test',
    email: 'test',
    phonenumber: 'test',
    sex: '0',
    status: '0'
  }
]

5、已寫(xiě)入數(shù)據(jù)庫(kù)