Old rules, first look at the effect map

The figure above is the excel file that we save the user data to excel.

Ideas for Realization

• 1. Create cloud functions
• 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 cloud database in excel
• 5. Store excel in cloud storage and return the corresponding cloud file address
• 6. Download excel files from the cloud file address

First, create excel cloud function

I won't say much about the creation of cloud functions. If you don't even know how to create cloud functions, it's recommended that you go to the official documents of cloud development for small programs. Or take a look at the video I recorded about cloud development: https://edu.csdn.net/course/detail/9604

There are two points to note when creating cloud functions. Let's talk about them.

• 1. Be sure to replace the environment id in app.js with your own.
  
• 2. Your cloud function directory should select your corresponding cloud development environment (usually selected by default here).
  But your cloud development environment here should be consistent with your app.js
  

Second, read data from cloud databases

After we create 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. Successful reading of data
  

Put the complete code of reading user data table to everyone.

// 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

From the second step above, we can see that we have succeeded in getting the source data that need to be saved to excel. What we need to do next is to save the data to excel.

• 1. Install the node-xlsx class library
  
  This step requires us to install node beforehand, because we need to use the npm command, through the command line

npm install node-xlsx

As you can see, we have an additional package-lock.json file after the installation.

Fourth, write the code to save the data to excel.

The following is our core code

The data here is the data of the users table that we queried, then traverse the array through the following code, and then save it in excel. Here we need to note that our id,name,weixin should correspond 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)
    }

And here's the code that saves excel to cloud storage

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

Now paste the index.js code in the complete excel, remember to change the cloud development environment id into your own.

const cloud = require('wx-server-sdk')
//It's better to initialize your cloud development environment here as well.
cloud.init({
  env: "test-vsbkm"
})
//Class libraries for operating 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 the stored data
    let alldata = [];
    let row = ['id', 'Full name', 'Wechat number']; //Table attributes
    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 file
    })

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

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

We have successfully stored data in excel and excel files in cloud storage. You can see the effect.

At this time, we can download excel files from the download address shown above.

Let's open the downloaded excel

In fact, this has almost achieved the basic function of saving data to excel, but we have to download excel, we can not always go to the cloud development background. So we need to get the download address dynamically next.

6. Get the address of cloud file and download excel file

From the above figure, we can see that we need a file ID to get the download link, and this file ID returns when we save excel to cloud storage, as shown below. We can pass the file ID to us to get the download link.

• 1. We got the download link. Next, we need to show the download link to the page.
  
• 2. After the code is displayed on the page, we will copy the link to make it easy for users to paste it into browser or wechat to download.
  

Next, I will post the complete code of this page to you.

Page({
  onLoad: function(options) {
    let that = this;
    //Read user 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 the data to excel and save Excel to cloud storage
  savaExcel(userdata) {
    let that = this
    wx.cloud.callFunction({
      name: "excel",
      data: {
        userdata: userdata
      },
      success(res) {
        console.log("Successful Preservation", 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("Successful replication",res.data) // data
          }
        })
      }
    })
  }
})

Let's talk about the steps of the above code.

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

So far, we have fully realized the function of saving data to excel.

The article is a bit long and has a lot of knowledge points, but after you get to know this, you can complete the development of small program cloud: cloud function, cloud database, cloud storage. It can be said that this is a comprehensive case.

If you don't know anything, or have any questions, please leave a message at the bottom of the article. I can see that it will be answered in time. Later, I will come up with a series of articles on cloud development, please pay attention.