How Do I Use Huawei Cloud Astro Zero Scripts to Export an XLS File for Users to Download?

Description

Huawei Cloud Astro Zero provides a standard Excel script library for operating Excel files, for example, generating Excel files. The standard output format of Huawei Cloud Astro Zero script orchestration is JSON. Therefore, some special processing is required so that the system can generate non-JSON data to export .xlsx files.

Procedure

1. Create a blank script named cube__download by referring to Creating a Blank Script.
2. In the script editor, enter the following script code:

   import * as excel from 'excel';
   import * as context from 'context';
   import * as buffer from 'buffer';
   
   export class Downlaod {
   
       @action.method({ input: "Input", output: "Output", description: "do a operation" })
       run(): void {
           // Raw data, which can be obtained from the data table. Static data is used here for demonstration.
           let binary = excel.encode(['a', 'b', 'c'], [{ 'a': 1, 'b': 2, 'c': 3 }, { 'a': 4, 'b': 5, 'c': 6 }, { 'a': 7, 'b': 2, 'c': 3 }]);
   
           // For an .xlsx file of Excel 2007 or later, set the content type to application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.
           context.getHttp().response.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64');
           // Return the binary value as the request body.
    context.getHttp().response.setBody(buffer.fromBytes(binary).toString(buffer.Encoding.Base64));
       }
   }

   In this case, the backend returns a Base64-encoded binary data, which is not in JSON format. Therefore, no output is generated when the script is directly executed. You need to match the data on the frontend page to process it.

3. On the standard page, call the cube__download script.

   To simplify the configuration, the script is not encapsulated into a public API. In actual use, the service needs to use the public API for encapsulation to implement finer-grained permissions management.

   The following uses the ajax() method of jQuery as an example:

   var url = "/u-route/baas/script/v1.0/run/cube__download";
   
   context.$utils.getCSRFToken().then(function(token) {
       $.ajax({
           type: "POST",
           headers: {
               'Content-Type': "application/json",
               'responseType':"arraybuffer",
               'csrf-token': token,
           },
           url: url,
           data: JSON.stringify({}),
           dataType: 'text',
           async: false,
           success: function(resp){
               var fileName = "test" + '.xlsx';
               var file = new Blob([s2ab(atob(resp))], {type: ''});
               if (window.navigator.msSaveOrOpenBlob) { //Download using Internet Explorer
                   window.navigator.msSaveOrOpenBlob(file, fileName);
               } else {  
                   var fileUrl = URL.createObjectURL(file);
                   var a = document.createElement('a');
                   a.href = fileUrl;
                   a.target = '_blank';
                   a.download = fileName;
                   document.body.appendChild(a);
                   a.click();
               }
           },
           error: function(resp){
           }
       });
   });

   s2ab() is the key to data processing. This method is short for "string to array buffer", which is used to convert a character string to the ArrayBuffer type so that the frontend can process binary data. The function is defined as follows:

   function s2ab(s) {
       var buf = new ArrayBuffer(s.length);
       var view = new Uint8Array(buf);
       for (var i=0; i!=s.length; ++i) {
           view[i] = s.charCodeAt(i) & 0xFF;
       }
       return buf;
   }

   Pay attention to the following in the preceding code:

   • Add 'responseType':"arraybuffer" to the header.
   • The type in the header is dataType: 'text'.
   • Add csrf-token to the header.