Applet export data to excel table, save Excel data with the help of cloud development background

As we develop applets, there may be a need to export data from cloud databases in batches to excel tables.
This requirement can be easily achieved with powerful cloud development!
Cloud functions, cloud storage, and cloud databases are needed here.It can be said that through this example, knowledge related to applet cloud development is used.Here's how

Ideas for implementation

• 1, Create Cloud Function
• 2. Read data from cloud databases in cloud functions
• 3, Install the node-xlsx class library (node class library)
• 4. Store the data read from the cloud database in excel
• 5, save excel in cloud storage and return the corresponding cloud file address
• 6. Download excel files from the cloud file address

1. Create excel cloud function

I won't go into details here about how to create cloud development applets.Students who don't know how to create cloud development applets can go to the teaching video in Tencent's Cloud Development Public Number Menu (Technology Exchange-Video Tutorial).

There are two things you should be aware of when creating cloud functions.

• 1. Be sure to replace the environment id in app.js with your own

• 2, Your Cloud Functions Catalog chooses your cloud development environment (usually selected here by default)

But the cloud development environment here should be the same as in your app.js

2. Reading data from cloud databases

Once we have created the cloud function in the first step, we can read the data in our cloud database in the cloud function first.

• 1. Look at the data in our cloud database first

• 2. Write cloud functions and read data from cloud databases (remember to deploy cloud functions)

• 3. Successfully read data

Paste the complete code to read the user table.

// Cloud Function Entry File
const cloud = require('wx-server-sdk')
cloud.init({
  env: "test-vsbkm"
})
// Cloud function entry function
exports.main = async(event, context) => {
  return await cloud.database().collection('users').get();
}

3. Install the class library node-xlsx that generates excel files

As you can see from the second step above, we have successfully obtained the source data that needs to be saved to excel. The next thing we need to do is save the data to excel

• 1. Install the node-xlsx class library

This requires us to install the node beforehand, because we will use the npm command from the command line

npm install node-xlsx

You can see that we have one more package-lock.json file after the installation

4. Write code to save data to excel.

The following is our core code:

The data here is the data of the user table that we query, then iterate through the array with the code below, and save it in excel.Here we need to note that our id,name,weixin corresponds to the user table.

   for (let key in userdata) {
      let arr = [];
      arr.push(userdata[key].id);
      arr.push(userdata[key].name);
      arr.push(userdata[key].weixin);
      alldata.push(arr)
    }

The next section of code saves excel to cloud storage

    //4. Save excel files in cloud storage
    return await cloud.uploadFile({
      cloudPath: dataCVS,
      fileContent: buffer, //excel binary
    })

Now paste the index.js code from the complete excel and remember to replace the cloud development environment id with your own.

const cloud = require('wx-server-sdk')
//It's also a good idea to initialize your cloud development environment here
cloud.init({
  env: "test-vsbkm"
})
//Class libraries for manipulating excel
const xlsx = require('node-xlsx');

// Cloud function entry function
exports.main = async(event, context) => {
  try {
    let {userdata} = event
    
    //1, Define excel table name
    let dataCVS = 'test.xlsx'
    //2, Define what stores data
    let alldata = [];
    let row = ['id', 'Full name', 'Wechat number']; //Table Properties
    alldata.push(row);

    for (let key in userdata) {
      let arr = [];
      arr.push(userdata[key].id);
      arr.push(userdata[key].name);
      arr.push(userdata[key].weixin);
      alldata.push(arr)
    }
    //3, Save the data in excel
    var buffer = await xlsx.build([{
      name: "mySheetName",
      data: alldata
    }]);
    //4. Save excel files in cloud storage
    return await cloud.uploadFile({
      cloudPath: dataCVS,
      fileContent: buffer, //excel binary
    })

  } catch (e) {
    console.error(e)
    return e
  }
}

5. Store excel in cloud storage and return the corresponding cloud file address

With the steps above, we have successfully saved the data to excel and the Excel files to cloud storage.You can see the effect.

Next, you can download the excel file from the download address shown above.

In fact, this is almost the basic function of saving data to excel, but to avoid the trouble of downloading excel in the background every time you export data, the next step is to get the download address dynamically.

6. Obtain the address of cloud file to download excel file

From the above image, we can see that we need a fileID to get the download link, which returns when we save excel to cloud storage, as shown below.We can just pass the fileID to us to get the download link.

• 1. We got the download link, and now we need to show it to the page

• 2. After the code is displayed on the page, we will copy this link to make it easy for users to paste it into their browser or WeChat to download.

Here is the complete code:

Page({
  onLoad: function(options) {
    let that = this;
    //Read users table data
    wx.cloud.callFunction({
      name: "getUsers",
      success(res) {
        console.log("Read Successfully", res.result.data)
        that.savaExcel(res.result.data)
      },
      fail(res) {
        console.log("read failure", res)
      }
    })
  },

  //Save data to excel and Excel to cloud storage
  savaExcel(userdata) {
    let that = this
    wx.cloud.callFunction({
      name: "excel",
      data: {
        userdata: userdata
      },
      success(res) {
        console.log("Save Successfully", res)
        that.getFileUrl(res.result.fileID)
      },
      fail(res) {
        console.log("Save failed", res)
      }
    })
  },

  //Get the cloud storage file download address, which is valid for one day
  getFileUrl(fileID) {
    let that = this;
    wx.cloud.getTempFileURL({
      fileList: [fileID],
      success: res => {
        // get temp file URL
        console.log("File Download Link", res.fileList[0].tempFileURL)
        that.setData({
          fileUrl: res.fileList[0].tempFileURL
        })
      },
      fail: err => {
        // handle error
      }
    })
  },
  //Copy excel file download link
  copyFileUrl() {
    let that=this
    wx.setClipboardData({
      data: that.data.fileUrl,
      success(res) {
        wx.getClipboardData({
          success(res) {
            console.log("Copy succeeded",res.data) // data
          }
        })
      }
    })
  }
})

Comb the logic of the above code:

• 1. Get data from the cloud database by getUsers cloud function first.
• 2. Save the data to excel through the excel cloud function, and then store it in the cloud.
• 3. Get file download links from cloud storage.
• 4. Copy the download link and download the excel file in your browser.

Here we have fully implemented the function of saving data to excel.

The article is a bit long and has a bit of knowledge, but once you understand the above, you can have a complete understanding of cloud functions, cloud databases and cloud storage for applet cloud development.

If you want to know more about CloudBase related technical stories/technical warfare experience, please follow the Public Number of Tencent Cloud Development!