云开发 Excel文档处理

Excel是存储数据比较常见的格式，它是日常办公的运营数据的载体，也是很多非技术人士常用于数据转移的一个方式，使用非常频繁，因此研究如何将Excel(CSV)的数据导入数据库，将数据库里的数据导出为Excel(CSV)是一个比较重要的话题。我们除了可以在云开发控制台里导入导出csv文件外，还可以在云函数使用Nodejs的一些模块来处理Excel文档。

一、读取云存储的Excel文件

我们可以在Github上搜索关键词“Node Excel”，去筛选Star比较多，条件比较契合的，这里推荐使用node-xlsx，Github地址：node-xlsx。

使用开发者工具新建一个云函数比如node-excel，在package.json里添加latest最新版的node-xlsx，并右键云函数目录选择在终端中打开输入命令npm install安装依赖：

"dependencies": {
  "wx-server-sdk": "latest",
  "node-xlsx": "latest"
}

然后再在index.js里输入以下代码，这里有几点需要注意：

• 使用云函数处理的Excel文件的来源是你的云存储，所以你需要事先将数据csv文件上传到云存储，在下面的代码里换成你的云存储csv地址；当然这个fileID也可以是你在小程序端上传Excel文件返回的云文件地址；

• 云函数会先从云存储里下载csv文件，然后使用node-xlsx解析Exce文件，然后再将每行每行的写入数据库，这个Excel文件用的是前面介绍过的中国经济数据，这里只是写入了部分字段；

• 由于下面是读取数据的每一行，并将读取的数据循环写入数据库，也就是把数据库的add请求放在循环里面，一般情况下我们非常不推荐大家这么做，如果要这么做，主要要把云函数的超时时间设置为更长，比如20s~60s之间，保证云函数执行成功，不然会出现只成功了一部分的情况；

const cloud = require('wx-server-sdk')
cloud.init({
  env: cloud.DYNAMIC_CURRENT_ENV
})
const xlsx = require('node-xlsx');
const db = cloud.database()
exports.main = async (event, context) => {
  const fileID = 'cloud://xly-xrlur.786c-xly-xrlur-1300446086/china.csv' //你需要将该csv的地址替换成你的云存储的csv地址
  const res = await cloud.downloadFile({
    fileID: fileID,
  })
  const buffer = await res.fileContent
  const sheets = await xlsx.parse(buffer);  //解析下载后的Excel Buffer文件，sheets是一个对象，而sheets['data']是数组，Excel有多少行数据，这个数组里就有多少个数组；
  const sheet = sheets[0].data  //取出第一张表里的数组，注意这里的sheet为数组
  const tasks = [] 
  for (let rowIndex in sheet) { //如果你的Excel第一行为字段名的话，从第2行开始
    let row = sheet[rowIndex];
    const task = await db.collection('chinaexcel')
    .add({
      data: {
        city: row[0], 
        province: row[1], 
        city_area: row[2], 
        builtup_area: row[3],
        reg_pop: row[4],
        resident_pop: row[5],
        gdp: row[6]
      }
    })
    tasks.push(task) //task是数据库add请求返回的值，包含数据添加之后的_id，以及是否添加成功
  }
  return tasks;
}

使用xlsx.parse解析Excel文件得到的数据是一个数组，也就是上面所说的sheets，数组里的值都是Excel的每张表，而sheets[0].data 则是第一张表里面的数据，sheets[0].data仍然是一个数组，数组里的值是Excel表的每一行数据。

在解析返回的对象里，每个数组都是Excel的一行数据，

[
  {
    name: 'Sheet1',
    data: [
      [Array], [Array],
      ... 233 more items
    ]
  }
]

发现有不少人使用云函数往数据库里导入大量数据的时候，使用的是Promise.all()方法，这个方法会出现并发的问题，会报[LimitExceeded.NoValidConnection] Connection num overrun的错误，这是因为数据库的同时连接数是有限制的，不同套餐数据库的连接数不同，比如免费的是20。针对这个问题还有其他解决方法，这里就不介绍啦；还有尽管你可能已经把云函数的超时时间设置到了60s，但是仍然会出现，数据并没有完全导入的情况，显然你的Excel文件过大或者一次性导入的数据太多，超出了这个云函数的极限，建议分割处理，这种方法只适用于几百条的数据。

二、将数据库里的数据保存为CSV

node-xlsx不仅可以解析Excel文件从中取出数据，还能将数据生成Excel文件，因此我们可以将云数据库里面的数据取出来之后保存为Excel文件，然后再将保存的Excel文件上传到云存储。

我们可以将node-excel的云函数修改为如下代码之后直接更新文件（因为依赖相同所以不需要安装依赖）：

• 这个云函数是先将数据库里面的数据取出来，你也可以根据你自己的需要对数据进行筛选，我们知道云函数每次最多可以 get 1000条数据，如果超过1000条，需要你自己遍历处理；

• dataList.data是数组，里面的格式是键：值对，我们可以使用dataList.data[index].key的形式取出相应的value，因此这种方式也支持嵌套子文档，比如dataList.data[index].key.subkey取出嵌套子文档里面的值；

• 云函数是先将excel每一行的字段值(相当于excel的每一个格子) push成一行数据，再将每一行的数组push成一个表格，然后再将表格写成xlsx Buffer文件，最后再上传到云存储。

const cloud = require('wx-server-sdk')
cloud.init({
  env: 'xly-xrlur'
})
const xlsx = require('node-xlsx');
const db = cloud.database()
const _ = db.command
exports.main = async (event, context) => {
  const dataList = await db.collection("chinaexcel").where({
    _id:_.exists(true)
  }).limit(1000).get()
  const data = dataList.data  //data是获取到的数据数组，每一个数组都是一个key:value的对象
  let sheet = [] // 其实最后就是把这个数组写入excel   
  let title = ['id','builtup_area','city','city_area','gdp','province','reg_pop','resident_pop']//这是第一行
  await sheet.push(title) // 添加完列名 下面就是添加真正的内容了
  for(let rowIndex in data){ //
    let rowcontent = []  //这是声明每一行的数据
    rowcontent.push(data[rowIndex]._id) //注意下面这个与title里面的值的顺序对应
    rowcontent.push(data[rowIndex].builtup_area)
    rowcontent.push(data[rowIndex].city)
    rowcontent.push(data[rowIndex].city_area)
    rowcontent.push(data[rowIndex].gdp)
    rowcontent.push(data[rowIndex].province)
    rowcontent.push(data[rowIndex].reg_pop)
    rowcontent.push(data[rowIndex].resident_pop)
    await sheet.push(rowcontent) //将每一行的字段添加到rowcontent里面
  }
  const buffer = await xlsx.build([{name: "china", data: sheet}])
  return await cloud.uploadFile({
    cloudPath: 'china.xlsx',
    fileContent: buffer,
  })
}

三、导入Excel更多数据的解决方法

在前面我们已经了解到，要将Excel里面的数据导入到数据库，会出现将数据库新增请求add放在循环里的情况，这种做法是非常低效的，即使是将云函数的超时时间设置为60s，也仍然只能导入少量的数据，如果你的业务经常需要往数据库里导入数据，我们应该如何处理呢？我们可以使用内嵌子文档的设计。

数据库的请求add是往数据库里一条一条的增加记录，有多少条就会请求多少次，而数据库的请求是非常耗时、耗资源、耗性能，而且数据量比较大时成功率也很难把控，但是如果把你要添加的所有数据，作为一整个数组添加到某个字段的值里时，就只需要执行一次数据库请求的操作即可，比如某个集合可以设计为：

{
  china:[{...//几百个城市的数据
  }]
}

由于是记录里的某个字段的值，我们可以使用更新指令，往数组里面push数组，这样就能大大提升数据导入的性能了。

db.collection('china').doc(id).update({
  data: {
    china: _.push([数组])
  }
})

四、将Excel文件一键转成云数据库的json文件

以下是一个脚本文件，是在自己电脑的本地运行的哦，不是在云函数端执行的。该脚本文件只是将Excel文件转成云数据库所需要json格式，实用性其实并没有非常大。

使用Excel导入云开发的数据库，数据量比较大的时候会出现一些问题，我们可以将Excel转成CSV文件，让CSV的第一行为字段名（要是英文哦），然后使用以下代码将CSV文件转成json文件。

• 第一步，安装Nodejs环境，然后使用vscode新建一个 csv2json.js 的文件，将下面的代码拷贝进来；

• 第二步，在vscode的资源管理器里右键csv2json.js，在终端中打开，然后输入命令 npm install csvtojson replace-in-file；

• 第三步，把要转化的csv文件放在同一个目录，这里换成你的文件即可，也就是下面的china.csv换成你的csv文件；

• 第四步，后面的代码都不用管，然后打开vscode终端，输入 node csv2json.js 执行，就会生成两个文件，一个是json文件，一个是可以导入到云开发数据库的data.json

//用vscode打开文件之后，npm install csvtojson replace-in-file
const csv=require('csvtojson')
const replace = require('replace-in-file');
const fs = require('fs')
const csvFilePath='china.csv' //把要转化的csv文件放在同一个目录，这里换成你的文件即可
//后面的代码都不用管，然后打开vscode终端，就会生成两个文件，一个是json文件，一个是可以导入到
csv()
.fromFile(csvFilePath)
.then((jsonObj)=>{
    // console.log(jsonObj);
    var jsonContent = JSON.stringify(jsonObj);
    console.log(jsonContent);   
    fs.writeFile("output.json", jsonContent, 'utf8', function (err) {
        if (err) {
            console.log("保存json文件出错.");
            return console.log(err);
        }
        console.log("JSON文件已经被保存为output.json.");
        fs.readFile('output.json', 'utf8', function (err,data) {
            if (err) {
              return console.log(err);
            }
            var result = data.replace(/},/g, '}\n').replace(/\[/,'').replace(/\]/,'')
            fs.writeFile('data.json', result, 'utf8', function (err) {
               if (err) return console.log(err);
            });
          });
    });
})