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.
- Select the Person BO (you need to create a BO named Person manually)
- Select the relevant fields
- 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