How to download data as excel in Visual Builder

In this tutorial i will demonstrate how to use xlsx library in visual builder to download data as excel. The use of this library is entirely for demonstration purposes and me/my employer do not endorse it.

Import lib in Visual Builder

follow instructions from this post on how to import xlsx js.

Create the data table in the designer

For this tutorial i am using a Person Business Object. However you are free to choose any data source available to you. The next steps do not make assumptions about which BO/rest service you should use except table data should be SDP (service data provider) based.

Drag and Drop a table from the component palette and use quick start — Add Data to open the wizard.

1. Select the Person BO (you need to create a BO named Person manually)
2. Select the relevant fields
3. Click on finish

Code for Excel generator

Create a module function named download which accepts the person service data provider (created during table creation in last step)

SDP fetchFirst returns an asyncIterator. we will use the below code to fetch data using sdp.

var iterator = sdp.fetchFirst()[Symbol.asyncIterator]();

The above code will initiate a fetch (since we are using service data provider), so we need to wait till the iterator.next() has data.

iterator.next().then(res => {
        console.log(res.value.data)
}

res.value.data will have the person array displayed in the table. Next, we will use xlsx to create an excel workbook and download it. Using the below line of code we will convert the sdp output to xlsx required data format.

var ws_data = [Object.keys(res.value.data[0]), ...res
          .value.data.map(i => Object.values(i))
        ];

Object.keys(res.value.data[0]) returns an array for the column names

res.value.data.map(i => Object.values(i)) converts all objects into arrays, as required by xlsx js lib.

Complete JS Code for download

Entire Page module js code for downloading table data as excel.

define(['xlsx'], function(XLSX) {
  'use strict';
var PageModule = function PageModule() {};
PageModule.prototype.download = function(sdp) {
    var self = this;
    return new Promise(resolve => {
      var iterator = sdp.fetchFirst()[Symbol.asyncIterator]();
      iterator.next().then(res => {
 
        var wb = XLSX.utils.book_new();
        wb.Props = {
          Title: "SheetJS Tutorial",
          Subject: "Test",
          Author: "VBCS",
          CreatedDate: new Date()
        };
wb.SheetNames.push("Person");
        var ws_data = [Object.keys(res.value.data[0]), ...res
          .value.data.map(i => Object.values(i))
        ];
        var ws = XLSX.utils.aoa_to_sheet(ws_data);
        wb.Sheets["Person"] = ws;
var wbout = XLSX.write(wb, {
          bookType: 'xlsx',
          type: 'binary'
        });
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;
        }
self.downloadByteFile([s2ab(wbout)],
          'person.xlsx');
        resolve()
      })
    })
};
  PageModule.prototype.downloadByteFile = function(data, name) {
    //workaround for IE
    var blob = new Blob(data, {
      type: "octet/stream"
    });
    if (typeof window.navigator.msSaveBlob != 'undefined') {
      window.navigator.msSaveBlob(blob, name);
    } else {
      var URL = window.URL || window.webkitURL;
      var download_URL = URL.createObjectURL(blob);
      if (name) {
        var a_link = document.createElement('a');
        a_link.style = "display: none";
        if (typeof a_link.download == 'undefined') {
          window.location = download_URL;
        } else {
          a_link.href = download_URL;
          a_link.download = name;
          document.body.appendChild(a_link);
          a_link.click();
        }
      } else {
        window.location = download_URL;
      }
      setTimeout(function() {
        URL.revokeObjectURL(download_URL);
      }, 10000);
    }
  };
  return PageModule;
});

Version

VBCS: 19.4.3.13

JET: 8.1.3