11.6 Excel文档处理

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

11.6.1 读取云存储的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的错误,针对这个问题还有其他解决方法,这里就不介绍啦;还有尽管你可能已经把云函数的超时时间设置到了60s,但是仍然会出现,数据并没有完全导入的情况,显然你的Excel文件过大或者一次性导入的数据太多,超出了这个云函数的极限,建议分割处理,这种方法只适用于几百条的数据。

11.6.2、将数据库里的数据保存为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, }) }

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

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

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

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

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

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

11.6.4 将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); }); }); }); })