kreycraft
Cloud Apps
Joined 5 years ago
User Profile
User Widgets
Contributions
Re: Export buttons [Excel, CSV] for the Pivot widget
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); }); }
