Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am sure that I am not the only one that at some point a Qlik Sense table was needed to be exported into a spreadsheet. While working with the APIs like Mashup and Engine API, this may get a little trivial, especially when we have so many solutions on the the web but not one that works in all major browsers and especially on our Qlik Sense table Object.
Even though this sound very simple and a simple copy and paste would do, here is a proper way of getting only the relevant fields displayed onto our webpage. This works on a simple html table as well as with a Qlik Sense Table Object.
In my previous posts I have showed you on how to create a webpage with Mashup API Creating a webpage based on the Qlik Sense Desktop Mashup API and for styling purposes, how to beautify your page with bootstrap Aligning objects and making a mashup responsive using Twitter’s Bootstrap and jquery
For this project I used an existing app for College Football Rankings Preseason College Football Rankings vs. Final Rankings Over The Years May Surprise You - RantSports
var me = {
config: {
host: window.location.host,
prefix: "/",
port: 443,
isSecure: true,
},
vars: {
id: '1b4194fd-0ace-4934-80ff-2c679b19624e'
},
data: {},
obj: {
qlik: null,
app: null
},
init: function () {
require.config( {
baseUrl: ( me.config.isSecure ? "https://" : "http://" ) + me.config.host + (me.config.port ? ":" + me.config.port: "") + me.config.prefix + "resources"
});
},
boot: function () {
me.init();
me.log('Boot', 'Success!');
require(['js/qlik'], function (qlik) {
me.obj.qlik = qlik;
qlik.setOnError( function ( error ) {
alert( error.message );
} );
// Get the Qlik Sense Object Table
me.obj.app = qlik.openApp(me.vars.id, me.config);
} );
},
<div class="row">
<div class="col-md-12">
<article style="height: 250px" class="qvobject" data-qvid="DBujmm" id="DBujmm"></article>
</div>
</div>
// Get the Qlik Sense Table Object
me.obj.app.getObject(document.getElementById('DBujmm'), 'DBujmm');
// Get raw data with HyperQube to create the Table
getData: function (callback) {
me.obj.app.createCube({
qDimensions : [{
qDef : {
qFieldDefs : ["School"]
}
},{
qDef : {
qFieldDefs : ["Conference"]
}
}
],
qMeasures : [
{
"qLabel": "# Preseason Top 10",
"qLibraryId": "HdsZnjL",
"qSortBy": {
"qSortByState": 0,
"qSortByFrequency": 0,
"qSortByNumeric": 0,
"qSortByAscii": 1,
"qSortByLoadOrder": 0,
"qSortByExpression": 0,
"qExpression": {
"qv": " "
}
}
},
{
"qLabel": "# Postseason Top 10",
"qLibraryId": "tEknwb",
"qSortBy": {
"qSortByState": 0,
"qSortByFrequency": 0,
"qSortByNumeric": 0,
"qSortByAscii": 1,
"qSortByLoadOrder": 0,
"qSortByExpression": 0,
"qExpression": {
"qv": " "
}
}
}
],
qInitialDataFetch : [{
qTop : 0,
qLeft : 0,
qHeight : 20,
qWidth : 5
}]
}, function(reply) {
me.log('getData', 'Success!');
me.data.hq = reply.qHyperCube.qDataPages[0].qMatrix;
me.refactorData();
callback(true);
});
},
// Refactor Data to a more readable format rather than qText etc.
refactorData: function () {
var data = [];
$.each(me.data.hq, function(key, value) {
data[key] = {};
data[key].school = value[0].qText;
data[key].conference = value[1].qText;
data[key].pre10 = value[2].qText;
data[key].post10 = value[3].qText;
});
me.data.rf = data;
},
<div class="row">
<div class="col-md-12">
<table id="tableData">
<tr>
<th>Team Name</th>
<th>Times in Preseason Top 10</th>
<th>Times in Postseason Top 10</th>
<th>Times in Pre & Postseason Top 10s</th>
<th></th>
<th>Conference</th>
</tr>
</table>
</div>
</div>
// Prepare Data for Display
displayData: function () {
$.each(me.data.rf, function(key, value) {
var html = '<tr>\
<td>' + value.school + '</td>\
<td>' + value.pre10 + '</td>\
<td>' + value.post10 + '</td>\
<td></td>\
<td></td>\
<td>' + value.conference + '</td>\
</tr>';
$('#tableData').append(html);
});
// After everything is rendered, enable the buttons for export
$('#export').removeClass('disabled');
$('#exportSense').removeClass('disabled');
},
<div class="row">
<div class="col-md-12">
<a href="#" class="btn btn-default disabled" id="export">Export Html Table to CSV</a>
</div>
</div>
<div class="row">
<div class="col-md-12">
<a href="#" class="btn btn-default disabled" id="exportSense">Export Sense Table to CSV</a>
</div>
</div>
$(".export").on('click', function (event) {
me.exportTableToCSV.apply(this, [$('#tableData'), 'QlikSenseExport.csv']);
});
$(".exportSense").on('click', function (event) {
me.exportTableToCSV.apply(this, [$('.qv-object-table'), 'QlikSenseExport.csv']);
});
exportTableToCSV: function ($table, filename) {
var $rows = $table.find('tr:has(th), tr:has(td)'),
// Temporary delimiter characters unlikely to be typed by keyboard
// This is to avoid accidentally splitting the actual contents
tmpColDelim = String.fromCharCode(11), // vertical tab character
tmpRowDelim = String.fromCharCode(0), // null character
// actual delimiter characters for CSV format
colDelim = '","',
rowDelim = '"\r\n"';
// Grab text from table into CSV formatted string
var csv = '"' + $rows.map(function (i, row) {
var $row = $(row),
// Select all of the TH and TD tags
// If its a Sense Object, remove the search column
$cols = $row.find('th:not(.qv-st-header-cell-search), td');
return $cols.map(function (j, col) {
var $col = $(col),
text = $col[0].outerText;
text.replace(/"/g, '""'); // escape double quotes
return text;
}).get().join(tmpColDelim);
}).get().join(tmpRowDelim)
.replace(/\r?\n|\r/g, '')
.split(tmpRowDelim).join(rowDelim)
.split(tmpColDelim).join(colDelim) + '"',
// Data URI
csvData = 'data:application/csv;charset=utf-8,' + encodeURIComponent(csv);
// Check if browser is IE
if ( window.navigator.msSaveOrOpenBlob && window.Blob ) {
var blob = new Blob( [ csv ], { type: "text/csv" } );
navigator.msSaveOrOpenBlob( blob, filename );
} else {
$(this)
.attr({
'download': filename,
'href': csvData,
'target': '_blank'
});
}
me.log('exportTableToCSV', 'Success!');
},
That's it. I hope this will help you to export your tables to a format for your favorite spreadsheet.
The Files and the entire working project is at
https://github.com/yianni-ververis/Export-Table-to-Csv
Also, you can view it live at
Yianni
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.