Skip to main content

Design

The Design blog is all about product and Qlik solutions, such as scripting, data modeling, visual design, extensions, best practices, and more!

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
Yianni_Ververis
Employee
Employee

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

  • Now, lets start by connecting to our app and getting the table object.

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);

        } );

    },

  • Now lets put the html code that will host the object.

        <div class="row">

            <div class="col-md-12">

                <article style="height: 250px" class="qvobject" data-qvid="DBujmm" id="DBujmm"></article>

            </div>

        </div>

  • Now place the object with our code

        // Get the Qlik Sense Table Object

        me.obj.app.getObject(document.getElementById('DBujmm'), 'DBujmm');

  • Lets try and get the raw data now from a HyperQube so we can create our custom html table. I have also created here a function that refactors data since I like to be working with more meaningful objects.

  // 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;

    },

  • Now lets create the html holder for this table including the column headers.

      <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>

  • Here is the code that will generate the table

    // 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');

    },

  • OK. So far we have the code that connects to the Qlik Sense App, gets the table as an object and as row data and displays both for us to use. Lets now put the buttons that will export these two tables. The first one is for the Html Table and the second one is for the Qlik Sense Object Table

        <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>

  • As you can see I a gave them 2 different classes so I can control the on click events. I can definitely have more efficient way of handling the script execution but for this example it is better if I keep them separate.
  • Here is the code that handles the events. We are passing to the exportTableToCSV methis

            $(".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']);

            });

  • Finally, here is the code that handles the csv generation and downloading.

  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

Export to CSV

Yianni

6 Comments