This script is great. I've added to it to have a PDF export button as well.
/*
Welcome to your Widget's Script.
To learn how you can access the Widget and Dashboard objects, see the online documentation at https://sisense.dev/guides/js/extensions
*/
widget.on('domready', () => {
if(!$(`#${widget.oid}_excelController`).length){
let excelButton = document.createElement('button')
excelButton.onclick = (lmnt) => { exportToExcel(widget) }
excelButton.id = widget.oid + "_excelController"
excelButton.innerHTML = 'Export to Excel'
excelButton.style.margin = "0 0 5px 10px"
excelButton.style.backgroundColor = "white"
excelButton.style.border = "1px solid lightgrey"
$(`widget[widgetid=${widget.oid}]`).parent().append(excelButton)
}
if(!$(`#${widget.oid}_csvController`).length){
let csvButton = document.createElement('button')
csvButton.onclick = (lmnt) => { exportToCSV(widget) }
csvButton.id = widget.oid + "_csvController"
csvButton.innerHTML = 'Export to CSV'
csvButton.style.margin = "0 0 5px 10px"
csvButton.style.backgroundColor = "white"
csvButton.style.border = "1px solid lightgrey"
$(`widget[widgetid=${widget.oid}]`).parent().append(csvButton)
}
if(!$(`#${widget.oid}_pdfController`).length){
let pdfButton = document.createElement('button')
pdfButton.onclick = (lmnt) => { exportToPDF(widget) }
pdfButton.id = widget.oid + "_pdfController"
pdfButton.innerHTML = 'Export to PDF'
pdfButton.style.margin = "0 0 5px 10px"
pdfButton.style.backgroundColor = "white"
pdfButton.style.border = "1px solid lightgrey"
$(`widget[widgetid=${widget.oid}]`).parent().append(pdfButton)
}
})
const exportToExcel = (widget) => {
let payload = prepareExcelPayload(widget);
payload = JSON.stringify(payload);
const xhttp = new XMLHttpRequest();
xhttp.withCredentials = true;
xhttp.onreadystatechange = () => {
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", `${location.origin}/api/v1/export/jaql/xlsx`);
xhttp.setRequestHeader("Content-Type", "application/json");
xhttp.responseType = 'blob';
xhttp.send(payload);
}
const exportToCSV = (widget) => {
let payload = prepareCSVPayload(widget);
const xhttp = new XMLHttpRequest();
xhttp.withCredentials = true;
xhttp.onreadystatechange = () => {
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}.csv` : 'Details.csv';
a.style.display = 'none';
document.body.appendChild(a);
a.click();
}
};
xhttp.open("POST", `${location.origin}/api/datasources/${encodeURIComponent(widget.datasource.title)}/jaql/csv`);
xhttp.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
xhttp.responseType = 'blob';
xhttp.send(payload);
}
const exportToPDF = (widget) => {
let payload = preparePDFPayload(widget);
payload = JSON.stringify(payload);
console.log("Widget Info");
console.log(widget);
const xhttp = new XMLHttpRequest();
xhttp.withCredentials = true;
xhttp.onreadystatechange = () => {
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}.pdf` : 'Details.pdf';
a.style.display = 'none';
document.body.appendChild(a);
a.click();
}
};
const dash = prism.activeDashboard.oid;
const wid = widget.oid;
xhttp.open("POST", `${location.origin}/api/v1/export/dashboards/${dash}/widgets/${wid}/pdf`);
xhttp.setRequestHeader("Content-Type", "application/json");
xhttp.responseType = 'blob';
xhttp.send(payload);
}
const prepareExcelPayload = (widget) => {
const query = {}
query.jaql = JSON.parse(prism.debugging.GetJaql(widget))
query.jaql.by = 'export'
query.jaql.count = -1
query.jaql.queryGuid = uuidv4()
query.jaql.widget = widget.oid
query.dashboardId = widget.dashboard.oid
query.widgetId = widget.oid
query.widgetType = "pivot2"
query.language = "en-US"
query.widgetStyles = {
"coloringRules": {
"alternatingRows": widget.style.colors.rows,
"alternatingColumns": widget.style.colors.columns,
"headersHighlighting": widget.style.colors.headers,
"rowMembersHighlighting": widget.style.colors.members,
"totalsHighlighting": widget.style.colors.totals
}
}
return query
}
const prepareCSVPayload = (widget) => {
const query = {}
query.data = JSON.parse(prism.debugging.GetJaql(widget))
query.data.by = 'export'
query.data.count = 0
query.data.download = true
query.data.filename = widget.title ? `${widget.title}.csv` : 'Details.csv';
query.data.format = 'csv'
query.data.offset = 0
query.data.widget = widget.oid
query.data.isMaskedResponse = true
query.data.culture = "en-US"
query.data = encodeURIComponent(JSON.stringify(query.data))
query.localized = true
return new URLSearchParams(query).toString()
}
const preparePDFPayload = (widget) => {
const query = {}
query.params = {}
// query.params = JSON.parse(prism.debugging.GetJaql(widget))
query.params.paperFormat = 'A2'
query.params.paperOrientation = 'landscape'
query.params.rowCount = 0
query.params.showTitle = true
query.params.showFooter = true
query.params.title = widget.title
query.params.titleSize = 'large'
query.params.titlePosition = 'flex-start'
// query.params = encodeURIComponent(JSON.stringify(query.params))
// return new URLSearchParams(query).toString()
return query
}
const uuidv4 = () => {
return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, (c) => {
var r = Math.random() * 16 | 0, v = c == 'x' ? r : (r & 0x3 | 0x8);
return v.toString(16);
});
}
