云開發(fā) Excel文檔處理

Excel是存儲(chǔ)數(shù)據(jù)比較常見的格式，它是日常辦公的運(yùn)營數(shù)據(jù)的載體，也是很多非技術(shù)人士常用于數(shù)據(jù)轉(zhuǎn)移的一個(gè)方式，使用非常頻繁，因此研究如何將Excel(CSV)的數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫，將數(shù)據(jù)庫里的數(shù)據(jù)導(dǎo)出為Excel(CSV)是一個(gè)比較重要的話題。我們除了可以在云開發(fā)控制臺(tái)里導(dǎo)入導(dǎo)出csv文件外，還可以在云函數(shù)使用Nodejs的一些模塊來處理Excel文檔。

一、讀取云存儲(chǔ)的Excel文件

我們可以在Github上搜索關(guān)鍵詞“Node Excel”，去篩選Star比較多，條件比較契合的，這里推薦使用node-xlsx，Github地址：node-xlsx。

使用開發(fā)者工具新建一個(gè)云函數(shù)比如node-excel，在package.json里添加latest最新版的node-xlsx，并右鍵云函數(shù)目錄選擇在終端中打開輸入命令npm install安裝依賴：

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

然后再在index.js里輸入以下代碼，這里有幾點(diǎn)需要注意：

• 使用云函數(shù)處理的Excel文件的來源是你的云存儲(chǔ)，所以你需要事先將數(shù)據(jù)csv文件上傳到云存儲(chǔ)，在下面的代碼里換成你的云存儲(chǔ)csv地址；當(dāng)然這個(gè)fileID也可以是你在小程序端上傳Excel文件返回的云文件地址；

• 云函數(shù)會(huì)先從云存儲(chǔ)里下載csv文件，然后使用node-xlsx解析Exce文件，然后再將每行每行的寫入數(shù)據(jù)庫，這個(gè)Excel文件用的是前面介紹過的中國經(jīng)濟(jì)數(shù)據(jù)，這里只是寫入了部分字段；

• 由于下面是讀取數(shù)據(jù)的每一行，并將讀取的數(shù)據(jù)循環(huán)寫入數(shù)據(jù)庫，也就是把數(shù)據(jù)庫的add請(qǐng)求放在循環(huán)里面，一般情況下我們非常不推薦大家這么做，如果要這么做，主要要把云函數(shù)的超時(shí)時(shí)間設(shè)置為更長，比如20s~60s之間，保證云函數(shù)執(zhí)行成功，不然會(huì)出現(xiàn)只成功了一部分的情況；

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的地址替換成你的云存儲(chǔ)的csv地址
  const res = await cloud.downloadFile({
    fileID: fileID,
  })
  const buffer = await res.fileContent
  const sheets = await xlsx.parse(buffer);  //解析下載后的Excel Buffer文件，sheets是一個(gè)對(duì)象，而sheets['data']是數(shù)組，Excel有多少行數(shù)據(jù)，這個(gè)數(shù)組里就有多少個(gè)數(shù)組；
  const sheet = sheets[0].data  //取出第一張表里的數(shù)組，注意這里的sheet為數(shù)組
  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是數(shù)據(jù)庫add請(qǐng)求返回的值，包含數(shù)據(jù)添加之后的_id，以及是否添加成功
  }
  return tasks;
}

使用xlsx.parse解析Excel文件得到的數(shù)據(jù)是一個(gè)數(shù)組，也就是上面所說的sheets，數(shù)組里的值都是Excel的每張表，而sheets[0].data 則是第一張表里面的數(shù)據(jù)，sheets[0].data仍然是一個(gè)數(shù)組，數(shù)組里的值是Excel表的每一行數(shù)據(jù)。

在解析返回的對(duì)象里，每個(gè)數(shù)組都是Excel的一行數(shù)據(jù)，

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

發(fā)現(xiàn)有不少人使用云函數(shù)往數(shù)據(jù)庫里導(dǎo)入大量數(shù)據(jù)的時(shí)候，使用的是Promise.all()方法，這個(gè)方法會(huì)出現(xiàn)并發(fā)的問題，會(huì)報(bào)[LimitExceeded.NoValidConnection] Connection num overrun的錯(cuò)誤，這是因?yàn)閿?shù)據(jù)庫的同時(shí)連接數(shù)是有限制的，不同套餐數(shù)據(jù)庫的連接數(shù)不同，比如免費(fèi)的是20。針對(duì)這個(gè)問題還有其他解決方法，這里就不介紹啦；還有盡管你可能已經(jīng)把云函數(shù)的超時(shí)時(shí)間設(shè)置到了60s，但是仍然會(huì)出現(xiàn)，數(shù)據(jù)并沒有完全導(dǎo)入的情況，顯然你的Excel文件過大或者一次性導(dǎo)入的數(shù)據(jù)太多，超出了這個(gè)云函數(shù)的極限，建議分割處理，這種方法只適用于幾百條的數(shù)據(jù)。

二、將數(shù)據(jù)庫里的數(shù)據(jù)保存為CSV

node-xlsx不僅可以解析Excel文件從中取出數(shù)據(jù)，還能將數(shù)據(jù)生成Excel文件，因此我們可以將云數(shù)據(jù)庫里面的數(shù)據(jù)取出來之后保存為Excel文件，然后再將保存的Excel文件上傳到云存儲(chǔ)。

我們可以將node-excel的云函數(shù)修改為如下代碼之后直接更新文件（因?yàn)橐蕾囅嗤圆恍枰惭b依賴）：

• 這個(gè)云函數(shù)是先將數(shù)據(jù)庫里面的數(shù)據(jù)取出來，你也可以根據(jù)你自己的需要對(duì)數(shù)據(jù)進(jìn)行篩選，我們知道云函數(shù)每次最多可以 get 1000條數(shù)據(jù)，如果超過1000條，需要你自己遍歷處理；

• dataList.data是數(shù)組，里面的格式是鍵：值對(duì)，我們可以使用dataList.data[index].key的形式取出相應(yīng)的value，因此這種方式也支持嵌套子文檔，比如dataList.data[index].key.subkey取出嵌套子文檔里面的值；

• 云函數(shù)是先將excel每一行的字段值(相當(dāng)于excel的每一個(gè)格子) push成一行數(shù)據(jù)，再將每一行的數(shù)組push成一個(gè)表格，然后再將表格寫成xlsx Buffer文件，最后再上傳到云存儲(chǔ)。

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是獲取到的數(shù)據(jù)數(shù)組，每一個(gè)數(shù)組都是一個(gè)key:value的對(duì)象
  let sheet = [] // 其實(shí)最后就是把這個(gè)數(shù)組寫入excel   
  let title = ['id','builtup_area','city','city_area','gdp','province','reg_pop','resident_pop']//這是第一行
  await sheet.push(title) // 添加完列名 下面就是添加真正的內(nèi)容了
  for(let rowIndex in data){ //
    let rowcontent = []  //這是聲明每一行的數(shù)據(jù)
    rowcontent.push(data[rowIndex]._id) //注意下面這個(gè)與title里面的值的順序?qū)?yīng)
    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,
  })
}

三、導(dǎo)入Excel更多數(shù)據(jù)的解決方法

在前面我們已經(jīng)了解到，要將Excel里面的數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫，會(huì)出現(xiàn)將數(shù)據(jù)庫新增請(qǐng)求add放在循環(huán)里的情況，這種做法是非常低效的，即使是將云函數(shù)的超時(shí)時(shí)間設(shè)置為60s，也仍然只能導(dǎo)入少量的數(shù)據(jù)，如果你的業(yè)務(wù)經(jīng)常需要往數(shù)據(jù)庫里導(dǎo)入數(shù)據(jù)，我們應(yīng)該如何處理呢？我們可以使用內(nèi)嵌子文檔的設(shè)計(jì)。

數(shù)據(jù)庫的請(qǐng)求add是往數(shù)據(jù)庫里一條一條的增加記錄，有多少條就會(huì)請(qǐng)求多少次，而數(shù)據(jù)庫的請(qǐng)求是非常耗時(shí)、耗資源、耗性能，而且數(shù)據(jù)量比較大時(shí)成功率也很難把控，但是如果把你要添加的所有數(shù)據(jù)，作為一整個(gè)數(shù)組添加到某個(gè)字段的值里時(shí)，就只需要執(zhí)行一次數(shù)據(jù)庫請(qǐng)求的操作即可，比如某個(gè)集合可以設(shè)計(jì)為：

{
  china:[{...//幾百個(gè)城市的數(shù)據(jù)
  }]
}

由于是記錄里的某個(gè)字段的值，我們可以使用更新指令，往數(shù)組里面push數(shù)組，這樣就能大大提升數(shù)據(jù)導(dǎo)入的性能了。

db.collection('china').doc(id).update({
  data: {
    china: _.push([數(shù)組])
  }
})

四、將Excel文件一鍵轉(zhuǎn)成云數(shù)據(jù)庫的json文件

以下是一個(gè)腳本文件，是在自己電腦的本地運(yùn)行的哦，不是在云函數(shù)端執(zhí)行的。該腳本文件只是將Excel文件轉(zhuǎn)成云數(shù)據(jù)庫所需要json格式，實(shí)用性其實(shí)并沒有非常大。

使用Excel導(dǎo)入云開發(fā)的數(shù)據(jù)庫，數(shù)據(jù)量比較大的時(shí)候會(huì)出現(xiàn)一些問題，我們可以將Excel轉(zhuǎn)成CSV文件，讓CSV的第一行為字段名（要是英文哦），然后使用以下代碼將CSV文件轉(zhuǎn)成json文件。

• 第一步，安裝Nodejs環(huán)境，然后使用vscode新建一個(gè) csv2json.js 的文件，將下面的代碼拷貝進(jìn)來；

• 第二步，在vscode的資源管理器里右鍵csv2json.js，在終端中打開，然后輸入命令 npm install csvtojson replace-in-file；

• 第三步，把要轉(zhuǎn)化的csv文件放在同一個(gè)目錄，這里換成你的文件即可，也就是下面的china.csv換成你的csv文件；

• 第四步，后面的代碼都不用管，然后打開vscode終端，輸入 node csv2json.js 執(zhí)行，就會(huì)生成兩個(gè)文件，一個(gè)是json文件，一個(gè)是可以導(dǎo)入到云開發(fā)數(shù)據(jù)庫的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' //把要轉(zhuǎn)化的csv文件放在同一個(gè)目錄，這里換成你的文件即可
//后面的代碼都不用管，然后打開vscode終端，就會(huì)生成兩個(gè)文件，一個(gè)是json文件，一個(gè)是可以導(dǎo)入到
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文件出錯(cuò).");
            return console.log(err);
        }
        console.log("JSON文件已經(jīng)被保存為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);
            });
          });
    });
})