如何使用koa2完成Excel導入導出

這篇文章主要介紹了如何使用koa2完成Excel導入導出的相關知識，內容詳細易懂，操作簡單快捷，具有一定借鑒價值，相信大家閱讀完這篇如何使用koa2完成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<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、已寫入數據庫

關于“如何使用koa2完成Excel導入導出”這篇文章的內容就介紹到這里，感謝各位的閱讀！相信大家對“如何使用koa2完成Excel導入導出”知識都有一定的了解，大家如果還想學習更多知識，歡迎關注億速云行業資訊頻道。