Exporting Options in SisenseJS
SisenseJS is a powerful tool that allows embedding widgets from Sisense into external applications. Despite it being a very straightforward way of embedding, SisenseJS has some missed functionality. For example, there are no OOTB methods to export widget's data into CSV and Excel. In this article, we will implement this functionality. We will use a combination of Sisense REST API endpoints for exporting and capabilities of SisenseJS in order to prepare payloads. In our sample, we will create the required logic for one widget. You can replicate this logic for every widget in your implementation.
Common logic
When you load a dashboard, you need to save its object. For these purposes we will use a variable [currentDashboard]:
Sisense.connect('https://example.com').then(function (app) {
app.dashboards.load(dashboardId).then(function (dashboard) {
currentDashboard = dashboard;
//Further logic to render widgets on the page
});
});
In terms of our task we need to get the widget’s object we are going to export. To get this object you can run the following logic:
const getWidget = (widgetId) => {
return currentDashboard.widgets.get(widgetId).$$model;
}
This function returns the widget’s object. We do have the widget’s object, so we know the widget’s metadata and also we have information about the dashboard’s filters. We could use this to build a query manually, but it would be quicker to utilize the SisenseJS capabilities.
When we call the function getWidget() we need to provide the widget’s identifier as an argument:
const currentWidget = getWidget('63cfd64645e8ec00324a13aa'); //Replace "63cfd64645e8ec00324a13aa" with your widget's identfier. Note, that it should be loaded and rendered
As a result, we will get the widget’s model. Later we will use it to prepare the payload. Also, we need to implement some functions to generate unique identifiers. This is not a mandatory step, but it can be useful if you want to cancel queries. Sample of the function:
function uuidv4() {
return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
var r = Math.random() * 16 | 0, v = c == 'x' ? r : (r & 0x3 | 0x8);
return v.toString(16);
});
}
Excel
To generate payload for Excel we will use the function below:
const prepareExcelPayload = (widget) => {
const query = widget.dashboard.$query.buildWidgetQuery(widget); //Use Sisense capabilities to prepare query
query.queryGuid = uuidv4(); //Generate queryGuid
query.dashboard = widget.dashboardid || widget.dashboard.oid;
query.culture = "en-us"; //This can be set from navigator.language
query.format = 'pivot';
query.metadata.forEach(function(m) {
if (m.jaql && m.jaql.dim) { //We need to remove [table] and [column]
delete m.jaql.table;
delete m.jaql.column;
}
})
return JSON.stringify({
query: query,
options: {}
}); //Return payload for exporting in Excel
}
Once the payload is prepared, we can send it to Sisense’s endpoint /engine/excelExport to retrieve the prepared Excel file. After this, we need to load the generated Excel file explicitly.
Code:
function getExcel(widget) {
const xhttp = new XMLHttpRequest();
xhttp.withCredentials = true; //This will be cross-domain request, so we force the browser to add cookies
xhttp.onreadystatechange = function () {
if (xhttp.readyState === 4 && xhttp.status === 200) {
const a = document.createElement('a');
a.href = window.URL.createObjectURL(xhttp.response);
a.download = widget.title ? `${widget.title}.xlsx` : 'Details.xlsx';
a.style.display = 'none';
document.body.appendChild(a);
a.click();
}
};
xhttp.open("POST", `${sisenseUrl}/engine/excelExport`);
xhttp.setRequestHeader("Content-Type", "application/json");
xhttp.responseType = 'blob';
xhttp.send(payload);
}
CSV
A function to generate query:
const prepareCSVPayload = (widget) => {
let query = widget.$query.buildWidgetQuery(widget, 'exportToCSV');
query = Object.assign(query, {
format: "csv",
isMaskedResponse: true,
download: true,
count: 0,
offset: 0
});
query = widget.dashboard.$query.createJaqlRequestConfig(query);
query.metadata.filter((item) => {
return defined(item.format);
})
return {
data: encodeURIComponent(JSON.stringify(query))
};
}
This function returns a payload, which can be sent at the endpoint `/api/datasources/${widget.datasource.title}/jaql/csv`. As you can see, this endpoint depends on the datasource’s title. Since the dashboard’s datasource can differ from the widgets’ datasources, I do recommend getting the correct datasource from the widget itself and for sure you need to avoid any hardcoded values.
function getCSV(widget) {
let csvDownloader = new XMLHttpRequest();
csvDownloader.open('POST', `/api/datasources/${widget.datasource.title}/jaql/csv`);
csvDownloader.responseType = 'arraybuffer';
csvDownloader.onload = function () {
if (this.status === 200) {
let filename = "";
const disposition = csvDownloader.getResponseHeader('Content-Disposition');
if (disposition && disposition.indexOf('attachment') !== -1) {
const filenameRegex = /filename[^;=\n]*=((['"]).*?\2|[^;\n]*)/;
const matches = filenameRegex.exec(disposition);
if (matches != null && matches[1]) filename = matches[1].replace(/['"]/g, '');
}
const type = csvDownloader.getResponseHeader('Content-Type');
const blob = typeof File === 'function'
? new File([this.response], filename, { type: type })
: new Blob([this.response], { type: type });
if (typeof window.navigator.msSaveBlob !== 'undefined') {
// IE workaround for "HTML7007: One or more blob URLs were revoked by closing the blob for which they were created. These URLs will no longer resolve as the data backing the URL has been freed."
window.navigator.msSaveBlob(blob, filename);
} else {
const URL = window.URL || window.webkitURL;
const downloadUrl = URL.createObjectURL(blob);
if (filename) {
const a = document.createElement("a");
if (typeof a.download === 'undefined') {
window.location = downloadUrl;
} else {
a.href = downloadUrl;
a.download = filename;
document.body.appendChild(a);
a.click();
}
} else {
window.location = downloadUrl;
}
setTimeout(() => {
URL.revokeObjectURL(downloadUrl);
}, 100); // cleanup
}
}
};
csvDownloader.withCredentials = true;
csvDownloader.setRequestHeader('Content-type', 'application/x-www-form-urlencoded');
$.param(payload);
csvDownloader.send(payload);
}
I hope you find this article useful and leverage the knowledge shared about exporting widgets data in different formats. Please share your experience in the comments!
