Get data from entire table, but only displayed columns

bdk0172 · June 2015

We've set up the option for the user to choose which columns they want to display and are now trying to figure out how to get only the data from the columns they've chosen from the entire table. So say we are returning 20 columns of data, but the user has only chosen to display 10 of them and they have 5 pages with 10 rows on each page in the datatable, I want some way to get the column headers and all 50 rows of data, showing only those 10 columns, and pass them to a web service api call to be able to export to a file. If I do something like table.data(), it returns all columns.

bdk0172 · June 2015

Figured out how to do this based on example from another site: http://nirajkvinit.blogspot.com/2013/07/exporting-and-printing-jquery-datatable.html

We were already building a dynamic array of columns(self.dataColumns) to pass to the "columns" property when initializing the datatable. I used that in conjunction with the fnGetFilteredData extension to be able to get the columns and the correct rows if it's been filtered from the datatable as they are displayed onscreen.

        function fn_get_rep_table() {
            var oTable = $('#js-ticketsearch-grid').dataTable();
            var oSettings = oTable.fnSettings();
            var colTitles = $.map(oSettings.aoColumns, function (node) {
                return node.sTitle;
            });

            var $str_return = '<thead><tr>';

            var thNum = 0;
            jQuery.each(colTitles, function () {
                //omit select/checkbox column and wtb image column
                if (thNum != 0 && thNum != 1)
                    $str_return += '<th>' + this + '</th>';
                thNum++;
            });

            $str_return += '</tr></thead><tbody>';

            var $rep_data = oTable.fnGetFilteredData();

            for (var row = 0; row < $rep_data.length; row++)
            {
                $str_return += '<tr>';
                for (var col = 0; col < self.dataColumns.length; col++)
                {
                    //omit select/checkbox column and wtb image column
                    if (col != 0 && col != 1)
                    {
                        if (self.dataColumns[col].data.indexOf('dt') > -1)
                        {
                            var formatedDT = moment($rep_data[row][self.dataColumns[col].data]);
                            $str_return += '<td>' + moment(formatedDT).format('M/D/YYYY h:mm:ss A') + '</td>';
                        }
                        else
                            $str_return += '<td>' + $rep_data[row][self.dataColumns[col].data] + '</td>';
                    }
                }
                $str_return += '</tr>';
            }

            $str_return += '</tbody>';
            return $str_return;
        }

Here is the code for the extension which I included right after the datatable was iniitialized:

$.fn.dataTableExt.oApi.fnGetFilteredData = function (oSettings) {
    var a = [];
    for (var i = 0, iLen = oSettings.aiDisplay.length ; i < iLen ; i++) {
        a.push(oSettings.aoData[oSettings.aiDisplay[i]]._aData);
    }

    return a;
}

All of this gets called from a button click event:

$('#btnExcelExport').on('click', function () {
    var $test = fn_get_rep_table();
    var uri = 'data:application/vnd.ms-excel;base64,',
            template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>',
base64 = function (s) 
{ return window.btoa(unescape(encodeURIComponent(s))) },
    format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) };
    var ctx = { worksheet: 'Ticket Grid' || 'Worksheet', table: $test };
    window.location.href = uri + base64(format(template, ctx));
});

It all works well using only JavaScript and not having to use Flash.

Get data from entire table, but only displayed columns

Get data from entire table, but only displayed columns

Answers

Howdy, Stranger!

Categories

DataTables

Get data from entire table, but only displayed columns

Get data from entire table, but only displayed columns

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables