Maliki Hassani
asked on
Tableau and Javascript: How to export an array to excel with formatting
Hi Team,
Just want to say thank you for all the folks on this site who help us newbies advance our skills.
Overview:
I am using Tableau 9.3 and I have a request to make the underlying data of a dashboard export to a predefined formatting in excel. So headers will be bolded, etc. (just to make a pretty report).
I have an example html code that connects to my Tableau public account for testing and builds the array when you hit "get data using selection, pre 10.0". You will then see the data displayed below "Underlying Data".
This data shown is what I need to have pushed to excel with formatting options.
Assistance:
I am not sure how to make this happen. Looking to add to what I have already to add a button for export to excel, and the user will be presented with the download button of a excel file with a title that's bolded "Customer forecast". It will need to be usable on Internet explorer. I am new to java script so any assistance would be greatly appreciated.
tableau dashboard link: https://public.tableau.com/profile/james.brown3770#!/vizhome/SelectionTest_0/Performance
Thanks
index.html
Just want to say thank you for all the folks on this site who help us newbies advance our skills.
Overview:
I am using Tableau 9.3 and I have a request to make the underlying data of a dashboard export to a predefined formatting in excel. So headers will be bolded, etc. (just to make a pretty report).
I have an example html code that connects to my Tableau public account for testing and builds the array when you hit "get data using selection, pre 10.0". You will then see the data displayed below "Underlying Data".
This data shown is what I need to have pushed to excel with formatting options.
Assistance:
I am not sure how to make this happen. Looking to add to what I have already to add a button for export to excel, and the user will be presented with the download button of a excel file with a title that's bolded "Customer forecast". It will need to be usable on Internet explorer. I am new to java script so any assistance would be greatly appreciated.
tableau dashboard link: https://public.tableau.com/profile/james.brown3770#!/vizhome/SelectionTest_0/Performance
Thanks
index.html
ASKER
Thank you. This functionality in Tableau's javascript API doesn't quite support an easy solution. That is why I am piecing it all together.
I did learn that I can call a web table to export to excel with formatting as an example. (see attached below)
So I am getting some where.. lol It only works using chrome which I need it to be in Internet Explorer so that is a challenge. However I have two codes so far one that gives me an array (attached at the top) and the code to export to excel with formatting.
What I need help on now is how can I integrate both codes into one. I just need an example to get started.
Thanks
tableexport.html
I did learn that I can call a web table to export to excel with formatting as an example. (see attached below)
So I am getting some where.. lol It only works using chrome which I need it to be in Internet Explorer so that is a challenge. However I have two codes so far one that gives me an array (attached at the top) and the code to export to excel with formatting.
What I need help on now is how can I integrate both codes into one. I just need an example to get started.
Thanks
tableexport.html
So you successfully can see your Tableau data in the "dataTarget" div ? If that's the case, then it looks like you need to get your data into an HTML table (loop over "value_array" and parse it into table rows / cells). Or use a plugin like jQuery DataTables that does this for you.
Then, in tableexport, you need to change the id from "table_wrapper" to "dataTarget", which is the id of div that contains your HTML formatted Tableau data:
Then, in tableexport, you need to change the id from "table_wrapper" to "dataTarget", which is the id of div that contains your HTML formatted Tableau data:
var table_div = document.getElementById('table_wrapper'); //change the id to dataTarget
ASKER
Yes, that would make sense. I am not sure which is easier to implement loop over or jquery data tables. I've never coded in javascript.
Both are relatively easy to do.
Here's an example of how to format an array into an HTML table using DataTables:
https://jsfiddle.net/zephyr_hex/8rpeox7w/
HTML
jQuery
And here's an example without DataTables:
https://jsfiddle.net/zephyr_hex/5evqx3ks/1/
HTML
jQuery
Here's an example of how to format an array into an HTML table using DataTables:
https://jsfiddle.net/zephyr_hex/8rpeox7w/
HTML
<table id="example" class="display" width="100%"></table>
jQuery
var dataSet = [
[ "Tiger Nixon", "System Architect", "Edinburgh", "5421", "2011/04/25", "$320,800" ],
[ "Garrett Winters", "Accountant", "Tokyo", "8422", "2011/07/25", "$170,750" ],
[ "Ashton Cox", "Junior Technical Author", "San Francisco", "1562", "2009/01/12", "$86,000" ]
];
$(document).ready(function() {
$('#example').DataTable( {
data: dataSet,
columns: [
{ title: "Name" },
{ title: "Position" },
{ title: "Office" },
{ title: "Extn." },
{ title: "Start date" },
{ title: "Salary" }
]
} );
} );
And here's an example without DataTables:
https://jsfiddle.net/zephyr_hex/5evqx3ks/1/
HTML
<table id="example" class="display" width="100%"></table>
jQuery
var dataSet = [
[ "Tiger Nixon", "System Architect", "Edinburgh", "5421", "2011/04/25", "$320,800" ],
[ "Garrett Winters", "Accountant", "Tokyo", "8422", "2011/07/25", "$170,750" ],
[ "Ashton Cox", "Junior Technical Author", "San Francisco", "1562", "2009/01/12", "$86,000" ]
];
$(document).ready(function() {
var table = $('#example');
table.append($('<thead>')
.append($('<tr>')
.append($('<td>').text("Name"))
.append($('<td>').text("Position"))
.append($('<td>').text("Office"))
.append($('<td>').text("Extn."))
.append($('<td>').text("Start date"))
.append($('<td>').text("Salary"))
)
);
table.append($('<tbody>'));
//loop over array and add rows
$.each( dataSet, function( index, value ){
var row = $('<tr>');
$.each( dataSet[index], function(index2, value2) {
row.append($('<td>').text(value2));
});
table.append(row);
});
} );
ASKER
I am not sure if this helps but I found a way to write it to a table. I haven't test yet. Not sure if this is what you mean by getting it into a html table
https://github.com/bryantbhowell/tableau_js_api/blob/master/datatables.html
https://github.com/bryantbhowell/tableau_js_api/blob/master/datatables.html
ASKER
Trying piece it together by taking the export function and the html table (not adding tableau's array at this point). Not sure what I am doing wrong. I am basically making up an array to feed the data export. Any ideas?
<!DOCTYPE html>
<html>
<head>
<title>HTML Table Export</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js"></script>
<script src="https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"></script>
<script type='text/javascript'>//<![CDATA[
var dataSet = [
[ "Tiger Nixon", "System Architect", "Edinburgh", "5421", "2011/04/25", "$320,800" ],
[ "Garrett Winters", "Accountant", "Tokyo", "8422", "2011/07/25", "$170,750" ],
[ "Ashton Cox", "Junior Technical Author", "San Francisco", "1562", "2009/01/12", "$86,000" ],
[ "Cedric Kelly", "Senior Javascript Developer", "Edinburgh", "6224", "2012/03/29", "$433,060" ],
[ "Airi Satou", "Accountant", "Tokyo", "5407", "2008/11/28", "$162,700" ],
[ "Brielle Williamson", "Integration Specialist", "New York", "4804", "2012/12/02", "$372,000" ],
[ "Herrod Chandler", "Sales Assistant", "San Francisco", "9608", "2012/08/06", "$137,500" ],
[ "Rhona Davidson", "Integration Specialist", "Tokyo", "6200", "2010/10/14", "$327,900" ],
[ "Colleen Hurst", "Javascript Developer", "San Francisco", "2360", "2009/09/15", "$205,500" ],
[ "Sonya Frost", "Software Engineer", "Edinburgh", "1667", "2008/12/13", "$103,600" ],
[ "Jena Gaines", "Office Manager", "London", "3814", "2008/12/19", "$90,560" ],
[ "Quinn Flynn", "Support Lead", "Edinburgh", "9497", "2013/03/03", "$342,000" ],
[ "Charde Marshall", "Regional Director", "San Francisco", "6741", "2008/10/16", "$470,600" ],
[ "Haley Kennedy", "Senior Marketing Designer", "London", "3597", "2012/12/18", "$313,500" ],
[ "Tatyana Fitzpatrick", "Regional Director", "London", "1965", "2010/03/17", "$385,750" ],
[ "Michael Silva", "Marketing Designer", "London", "1581", "2012/11/27", "$198,500" ],
[ "Paul Byrd", "Chief Financial Officer (CFO)", "New York", "3059", "2010/06/09", "$725,000" ],
[ "Gloria Little", "Systems Administrator", "New York", "1721", "2009/04/10", "$237,500" ],
[ "Bradley Greer", "Software Engineer", "London", "2558", "2012/10/13", "$132,000" ],
[ "Dai Rios", "Personnel Lead", "Edinburgh", "2290", "2012/09/26", "$217,500" ],
[ "Jenette Caldwell", "Development Lead", "New York", "1937", "2011/09/03", "$345,000" ],
[ "Yuri Berry", "Chief Marketing Officer (CMO)", "New York", "6154", "2009/06/25", "$675,000" ],
[ "Caesar Vance", "Pre-Sales Support", "New York", "8330", "2011/12/12", "$106,450" ],
[ "Doris Wilder", "Sales Assistant", "Sidney", "3023", "2010/09/20", "$85,600" ],
[ "Angelica Ramos", "Chief Executive Officer (CEO)", "London", "5797", "2009/10/09", "$1,200,000" ],
[ "Gavin Joyce", "Developer", "Edinburgh", "8822", "2010/12/22", "$92,575" ],
[ "Jennifer Chang", "Regional Director", "Singapore", "9239", "2010/11/14", "$357,650" ],
[ "Brenden Wagner", "Software Engineer", "San Francisco", "1314", "2011/06/07", "$206,850" ],
[ "Fiona Green", "Chief Operating Officer (COO)", "San Francisco", "2947", "2010/03/11", "$850,000" ],
[ "Shou Itou", "Regional Marketing", "Tokyo", "8899", "2011/08/14", "$163,000" ],
[ "Michelle House", "Integration Specialist", "Sidney", "2769", "2011/06/02", "$95,400" ],
[ "Suki Burks", "Developer", "London", "6832", "2009/10/22", "$114,500" ],
[ "Prescott Bartlett", "Technical Author", "London", "3606", "2011/05/07", "$145,000" ],
[ "Gavin Cortez", "Team Leader", "San Francisco", "2860", "2008/10/26", "$235,500" ],
[ "Martena Mccray", "Post-Sales support", "Edinburgh", "8240", "2011/03/09", "$324,050" ],
[ "Unity Butler", "Marketing Designer", "San Francisco", "5384", "2009/12/09", "$85,675" ]
];
$(document).ready(function() {
$('#example').DataTable( {
data: dataSet,
columns: [
{ title: "Name" },
{ title: "Position" },
{ title: "Office" },
{ title: "Extn." },
{ title: "Start date" },
{ title: "Salary" }
]
} );
} );
window.onload=function(){
$(document).ready(function() {
$("#btnExport").click(function(e) {
e.preventDefault();
//getting data from our table
var data_type = 'data:application/vnd.ms-excel';
var table_div = document.getElementById('dataSet');
var table_html = table_div.outerHTML.replace(/ /g, '%20');
var a = document.createElement('a');
a.href = data_type + ', ' + table_html;
a.download = 'exported_table_' + Math.floor((Math.random() * 9999999) + 1000000) + '.xls';
a.click();
});
});
}//]]>
</script>
</head>
<body>
<button id="btnExport">Export to xls</button>
<br />
<br />
<div id="dataSet">
</div>
</body>
</html>
ASKER
The challenge I see is that window.open isn't supported for IE. I found this thread: http://codepattern.net/Blog/post/jQuery-export-table-data-into-MS-Excel
info in blog: window.open() has its scope and limitations(ex IE not working & filename) that has been well explained in this post:
So I think I am back to step2, how to export an array in IE.
info in blog: window.open() has its scope and limitations(ex IE not working & filename) that has been well explained in this post:
So I think I am back to step2, how to export an array in IE.
ASKER
With this code you can download using IE to excel.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Document sans nom</title>
<script language=javascript>
Object.prototype.getName = function() {
var funcNameRegex = /function (.{1,})\(/;
var results = (funcNameRegex).exec((this).constructor.toString());
return (results && results.length > 1) ? results[1] : "";
};
function exportToExcel()
{
var oExcel = new ActiveXObject("Excel.Application");
var oBook = oExcel.Workbooks.Add;
var oSheet = oBook.Worksheets(1);
for (var y=0;y<GridView3.rows.length;y++)
// GridView3 is the table where the content to be exported is
{
for (var x=0;x<GridView3.rows(y).cells.length;x++)
{
a = GridView3.rows(y).cells(x);
if(!(a.innerHTML.match("<INPUT")))
oSheet.Cells(y+1,x+1) = a.innerText;
else {
inner = a.getElementsByTagName("INPUT");
oSheet.Cells(y+1,x+1) = inner[0].getAttribute("value");
}
}
}
oExcel.Visible = true;
oExcel.UserControl = true;
}
</script>
</head>
<body onload="exportToExcel()">
<table width="200" border="1" id="GridView3">
<tr>
<td>117</td><script language=javascript>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Appreciate the feedback and yes I am a beginner and was looking for experts to guide me to a solution. I will do some research before posting.
I recommend that you start with the Tableau JavaScript API documentation. Use one of the basic examples in the documentation to learn how to get your data in JavaScript. Then tackle the task of learning how export data to an Excel file.