Download all dashboard reports in Excel using Yellowfin's API and webservice
Thing you want to do
As the title says, you can download Excel in the previous article POST conditions on the page where you can download Excel files with Fetch and download with a browser. Now that I want to download all the reports in Yellowfin's dashboard in Excel. In version 9.2.2, we are verifying that you can do various things with Base API, report API, filter API, Dashboard API and code mode, so it feels like getting the information of each report there and turning it with foreach.
Prepare
After creating the dashboard, drag and drop the button widget from the code widget to name it. Example) export This will be used in the part described in the next JS tab.
Also, place the webservice script that exports Excel under the / Yellowfin installation directory / appserver / webapps / ROOT /.
Since this is compatible with other than Excel, even if you change the format of the POST key to another PDF / CSV, you can use it if you match the format to each. If nothing is done, it will be output as a PDF file.
output.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
<%@ page import="java.util.*, java.text.*" %>
<%@ page import="com.hof.mi.web.service.*" %>
<%@ page import="java.net.URLEncoder" %>
<%
String host = "localhost";
Integer port = 8080;
String userid = "[email protected]";
String password = "test";
String orgid = "1";
String uuid = request.getParameter("uuid");
String fname = request.getParameter("fname");
String path = "/services/ReportService";
String suffix = "";
String format = request.getParameter("format");
String contentType = "application/octet-stream";
if (format == null) format = "PDF";
if (format.equals("CSV")) {
contentType = "text/comma-separated-values";
suffix = ".csv";
}
else if (format.equals("PDF")) {
contentType = "application/pdf";
suffix = ".pdf";
}
else if (format.equals("XLS")) contentType = "application/vnd.ms-excel";
else if (format.equals("XLSX")) {
contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
suffix = ".xlsx";
}
else if (format.equals("RTF")) contentType = "application/rtf";
else if (format.equals("TEXT")) contentType = "text/tab-separated-values";
HashMap filters = new HashMap();
boolean cleared = filters.size() == 0;
Iterator f = request.getParameterMap().keySet().iterator();
while (f.hasNext()) {
String key = (String) f.next();
if (key.startsWith("filter")) {
if (!cleared) {
filters.clear();
cleared = true;
}
String value = request.getParameter(key);
int pipeIndex = value.indexOf("|");
if (pipeIndex == -1) continue;
key = value.substring(0, pipeIndex);
value = value.substring(pipeIndex + 1);
filters.put(key, value);
}
}
ReportServiceClient rsc = new ReportServiceClient(host, port, userid, password, path);
i4Report report = rsc.loadReportForUser(uuid, userid, password, orgid);
f = filters.keySet().iterator();
while (f.hasNext()) {
String key = (String) f.next();
String value = (String) filters.get(key);
report.setFilter(key, value);
}
HashMap elementStorage = new HashMap();
report.run(elementStorage, format);
response.setContentType(contentType);
fname = fname + suffix;
String encodedFilename = URLEncoder.encode( fname , "UTF-8");
response.setHeader("Content-Disposition","attachment;" +
"filename=\"" + encodedFilename + "\"");
java.io.BufferedOutputStream o = new java.io.BufferedOutputStream(response.getOutputStream(), 32000);
o.write(report.renderBinary());
o.flush();
%>
Dashboard JS tab
I will make full use of the Dashboard API so that I can export all the reports by slightly modifying the part I did last time. You can get this with event listener by specifying the name of the button installed on the dashboard in the part of let button = this.apis.canvas.select ('export') ;. This is the part that is not explained in other previous article. var dash = this.apis.dashboard; var allrep = dash.getAllReports(); It calls Yellowfin's dashboard API and gets all the report information on the dashboard with getAllReports (). [The wiki part of the corresponding Yellowfin](http://wiki.yellowfinbi.jp/pages/viewpage.action?pageId=6591558#id-%E3%83%80%E3%83%83%E3%82%B7% E3% 83% A5% E3% 83% 9C% E3% 83% BC% E3% 83% 89API-% E3% 83% AC% E3% 83% 9D% E3% 83% BC% E3% 83% 88% E3 % 81% AE% E3% 83% AA% E3% 83% 95% E3% 82% A1% E3% 83% AC% E3% 83% B3% E3% 82% B9) Each report is turned by foeach, and uuid and format are passed by body to generate a link.
If you want to get the report name as well, you have to make jsp for webservice, so next time.
JS tab
this.onRender = function () {
//Write the code here. This is an ideal place to set up an event listener
let button = this.apis.canvas.select('export');
button.addEventListener('click', () => {
var dash = this.apis.dashboard;
var allrep = dash.getAllReports();
allrep.forEach( item => {
var uuid = item.reportUUID;
var obj = {
fname: uuid,
uuid: uuid,
format: 'XLSX'
};
var method = "POST";
var body = Object.keys(obj).map((key)=>key+"="+encodeURIComponent(obj[key])).join("&");
var headers = {
'Accept': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
'Content-Type': 'application/x-www-form-urlencoded; charset=utf-8',
'responseType' : "blob",
};
fetch("./output_file.jsp", {method, headers, body})
.then((res)=> res.blob())
.then(blob => {
let anchor = document.createElement("a");
anchor.href = window.URL.createObjectURL(blob);
anchor.download = uuid+".xlsx";
anchor.click();})
.then(console.log)
.catch(console.error);
});
});
};
result
The reports installed on the dashboard are dropping steadily. Probably the first time, a dialog asking for permission to download will appear on the browser side, so it is okay if you allow it.
- It seems that the contents of the filter used in the dashboard cannot be passed to the report at the moment, so if you want to do this, you need to create a dashboard for each condition in advance instead of dynamically. .. .. I think it can be used quite a bit if this area is managed.