微信小程序 - excel通过云函数导入云数据库

GitHub Demo 地址: jh-weapp-demo 实现一些常用效果、封装通用组件和工具类

小程序码

excel数据量比较大时，导入时数据会丢失，可先把数据处理好返回本地，在本地循环插入

excel格式：

###首先新建 handleExcel 云函数

安装 node-xlsx

npm install node-xlsx

然后处理数据 插入数据库

云函数 代码：

const cloud = require('wx-server-sdk')
cloud.init({
  // API 调用都保持和云函数当前所在环境一致
  env: cloud.DYNAMIC_CURRENT_ENV
})
var xlsx = require('node-xlsx');
const db = cloud.database()
const _ = db.command
const $ = db.command.aggregate

exports.main = async (event, context) => {

  function removeSpaces(String) {
    var arr = []
    for (var i in String) {
      if (String[i] != ' ') {
        arr += String[i]
      }
    }
    return arr
  }

  function getRandomCode(length) {
    if (length > 0) {
      var data = ["0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z"];
      var nums = "";
      for (var i = 0; i < length; i++) {
        var r = parseInt(Math.random() * 61);
        nums += data[r];
      }
      return nums;
    } else {
      return false;
    }
  }

  function genID(length) {
    // 13
    return (getRandomCode(length - 13) + new Date().getTime());
  }
  let fatherId = genID(32)


  //把 "2019-05-20 00:00:00" 转成 时间戳  2020/7/28 13:00:00
  function Jh_convertTimeStamp(time) {
    //用正则主要是把“2019-05-20 00:00:00”转换成“2019/05/20 00:00:00”兼容ios
    let newTime = time.replace(/-/g, '/');
    return Date.parse(time)
  }

  // 时间戳转年月日时分秒 2020/06/30 13:34:00
  function Jh_timestampToYMDHMS(timestamp) {
    timestamp = new Date(timestamp);
    var temp = '/'
    var date = new Date(Date.parse(timestamp));
    var YY = date.getFullYear() + temp;
    var MM = (date.getMonth() + 1 < 10 ? '0' + (date.getMonth() + 1) : date.getMonth() + 1) + temp;
    var DD = (date.getDate() < 10 ? '0' + (date.getDate()) : date.getDate());
    var hh = (date.getHours() < 10 ? '0' + date.getHours() : date.getHours()) + ':';
    var mm = (date.getMinutes() < 10 ? '0' + date.getMinutes() : date.getMinutes()) + ':';
    var ss = (date.getSeconds() < 10 ? '0' + date.getSeconds() : date.getSeconds());
    return YY + MM + DD + " " + hh + mm + ss;
  }

  /* excel 时间转换  (云函数只返回中国标准时间，再使用时间戳转换)
   * num 44044.7673611111(2020/8/1 18:25:00)
   * return  2020/08/01 18:25:00
   */
  function Jh_excelDateToYMDHMS(num,isCloudFun) {
    var utc_days = Math.floor(num - 25569);
    var utc_value = utc_days * 86400;
    var date_info = new Date(utc_value * 1000);
    var fractional_day = num - Math.floor(num) + 0.0000001;
    var total_seconds = Math.floor(86400 * fractional_day);
    var seconds = total_seconds % 60;
    total_seconds -= seconds;
    var hours = Math.floor(total_seconds / (60 * 60));
    var minutes = Math.floor(total_seconds / 60) % 60;
    //中国标准时间
    var time = new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);
    // console.log(time); Sat Aug 01 2020 18:25:00 GMT+0800 (中国标准时间)
    return time
    //云函数中 中国标准时间转换 不加 8 * 3600 * 1000 ，加了时间不正确
    // var dateStr = new Date(time).toJSON();
    // // var date = new Date(+new Date(dateStr) + 8 * 3600 * 1000).toISOString().replace(/T/g, ' ').replace(/\.[\d]{3}Z/, '')
    // var date = new Date(+new Date(dateStr)).toISOString().replace(/T/g, ' ').replace(/\.[\d]{3}Z/, '')
    // var newTime = date.replace(/-/g, '/');
    // return newTime
  }

  // 1,通过fileID下载云存储里的excel文件
  const res = await cloud.downloadFile({
    // fileID: fileID,
    fileID: event.fileID,
  })

  const buffer = res.fileContent

  const tasks = [] //用来存储所有的添加数据操作
  //2,解析excel文件里的数据
  var sheets = xlsx.parse(buffer); //获取到所有sheets

  for (let index = 0; index < sheets.length; index++) {
    let sheet = sheets[index]
    let sheetName = sheet['name']
    //sheet1
    if (index == 0) {

      let rowArr = sheet['data']
      if (rowArr.length < 0) {
        return null;
      } else {
        //循环添加
        for (let j = 1; j < rowArr.length; j++) {
          row = rowArr[j];
          let tempDic = {}
          //
          var time = Jh_excelDateToYMDHMS(row[3])
          var newTime= Jh_timestampToYMDHMS(time)

          tempDic['AB_Year'] = row[0].toString()
          tempDic['AB_Month'] = row[1] < 10 ? ('0' + row[1]) : row[1].toString()
          tempDic['AB_Day'] = row[2] < 10 ? ('0' + row[2]) : row[2].toString()
          tempDic['AB_YMD'] = tempDic['AB_Year'] + tempDic['AB_Month'] + tempDic['AB_Day']
          tempDic['AB_Date'] = newTime
          tempDic['AB_SortDate'] = time

          // tasks.push(tempDic)

          //3，把解析到的数据存到数据表里
          const promise = db.collection('TableName').add({
            data: {
              AB_Date: tempDic['AB_Date'],
              AB_SortDate: tempDic['AB_SortDate'],
              AB_YMD: tempDic['AB_YMD'],
              AB_Year: tempDic['AB_Year'],
              AB_Month: tempDic['AB_Month'],
              AB_Day: tempDic['AB_Day'],
            }
          })

          tasks.push(promise)
     
        }

      }
    }

  }


  // 等待所有数据添加完成
  let result = await Promise.all(tasks).then(res => {
    return res
  }).catch(function (err) {
    return err
  })
  return result
}

调用 代码：

//选择excel文件
function chooseExcelFile() {
  return new Promise((resolve, reject) => {
    wx.chooseMessageFile({
      count: 1,
      type: 'file',
      success(res) {
        // console.log('选择文件成功！',res)
        let name = res.tempFiles[0].name
        let path = res.tempFiles[0].path
        wx.showLoading({
          title: "正在上传...",
        })
        wx.cloud.uploadFile({
          // cloudPath: new Date().getTime() + '.xls',
          cloudPath: 'res/'+ name,
          filePath: path, //文件路径
          success(res) {
            console.log("excel上传成功：", res.fileID)
            wx.hideLoading()
            wx.showLoading({
              title: "正在导入数据...",
            })
            //导入处理数据
            wx.cloud.callFunction({
              "name": "handleExcel",
              data: {
                fileID: res.fileID,
              },
              success(res) {
                console.log("excel导入成功：", res)
                wx.hideLoading()
                wx.showToast({
                  title: '数据导入成功',
                })
                return resolve(true)
              },
              fail(res) {
                console.log("excel导入失败：", res)
                wx.hideLoading()
                wx.showToast({
                  title: '数据导入失败，请重试',
                  icon: 'none'
                })
                return resolve(false)
              }
            })
          },
          fail(err) {
            console.log("excel上传失败：", err)
            wx.hideLoading()
            wx.showToast({
              title: 'excel上传失败',
              icon: 'none'
            })
            return resolve(false)
          }
        })
      }
    })
  })
}