詳解使用koa2完成Excel匯入匯出

2023-04-02 06:02:06

一、安裝 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() + 'srcupload'
      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的資料請關注it145.com其它相關文章！

詳解使用koa2完成Excel匯入匯出

目錄

一、安裝 node-xlsx

二、匯出excel表

三、匯入excel表

結語

熱門文章