This requirement is also encountered in my private work. The requirement is to realize the implementation of exporting all the data of the specified database table to generate Excel and export on demand, that is, exporting all the data, or exporting the data of the specified day.

I had no idea at first, but I did some research and found that Node has modules for processing Excel spreadsheets.

Production environment, because I choose to convenient or cloud development, but cloud development in fact in the syntax and content and Node is no different, whether written or module, are the same or similar. Although I have not seen the underlying implementation of cloud function source code, but through the use of it can also be found that it is actually based on Node to do the packaging, for us to provide a lot of convenient API.

Without further ado, let’s cut to the chase.

const cloud = require('wx-server-sdk')
cloud.init({
  env:'Your cloud environment ID'
})
const xlsx = require('node-xlsx')    // Import the Excel library
const db = cloud.database()   // Declare database objects
const _ = db.command
exports.main = async (event, context) => {   // Main function entry
    try {
        let schoolBusInfo = event.data;
        console.log(schoolBusInfo);
        let dataCVS = `schoolBusInfo-The ${Math.floor(Math.random()*1000000000)}.xlsx`
        // Declare an Excel table with names generated using random numbers
        let alldata = [];
        let row = ['License plate'.'date'.'department'.'name'.'Contact Information']; // Attributes of the table, i.e., header objects
        alldata.push(row);  // Add row data to an array that stores data to the table
// The next step is to loop the data into the array that stores the data into the table
        for (let key = 0; key<schoolBusInfo.length; key++) {
            let arr = [];
            arr.push(schoolBusInfo[key].carNum);
            arr.push(schoolBusInfo[key].Date);
            arr.push(schoolBusInfo[key].class);
            arr.push(schoolBusInfo[key].name);
            arr.push(schoolBusInfo[key].phone);
            alldata.push(arr)
         }
            var buffer = xlsx.build([{   
            name: "mySheetName".data: alldata
            }]); 
            // Save the table to the repository and return the file ID
            return await cloud.uploadFile({
                cloudPath: dataCVS,
                fileContent: buffer, // Excel binary file})}catch (error) {
        console.error(error)
    }
}
Copy the code

If you are using node, you can copy this form, there is no pit, I will send a pure Node version sometime.

Here’s the code from the top down:

  • In fact, the most important thing is to introduce anode-xlsxThis module is used to process Excel files.
  • thislet schoolBusInfo = event.dataIt’s the data that the client sends and needs to export.
  • dataCVSIs the name of the Generated Excel file.
  • rowIt’s an array that holds the header of an Excel file, which is the first row of each column.
  • Then it is a loop through the data passed by the client.
  • schollBusInfo[key].xxxxThat’s the field of the data that’s being sent, and the previous onerowThe meaning of the header should be consistent.
var buffer = await xlsx.build([{   
name: "mySheetName".data: alldata
}]); 
Copy the code
  • ↑ : This is the last step, to build the table, using the data we prepared earlier, callxlsxBuild method of.

Since I’m using cloud development, I can store the generated data source in the cloud and then return the client with a cloud storage location.

This is basically the whole idea, since Node provides us with the Node-xlsx module, all the overall coding is not a big problem.

Similar libraries and packages must exist in other languages, so you can check them out.