社区/学习指南/小程序·云开发高级教程

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的错误,这是因为数据库的同时连接数是有限制的,不同套餐数据库的连接数不同,比如免费的是 20。针对这个问题还有其他解决方法,这里就不介绍啦;还有尽管你可能已经把云函数的超时时间设置到了 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);
        });
      });
    });
  });

本文出自 李东bbsky