The best way to hide empty columns in jquery dataTable

Question

I lost two days searching for a good solution on how to hide empty columns in jQuery dataTables via javascript, so i came to my own solution coding a new plugin, i think this will help others do it very quickly, if you find this plugin usefull feel free to extend it and post your code to help others improve their dataTables.

$.fn.dataTableExt.oApi.fnHideEmptyColumns = function ( oSettings, tableObject )
{ 
    /**
     * This plugin hides the columns that are empty.
     * If you are using datatable inside jquery tabs
     * you have to add manually this piece of code
     * in the tabs initialization
     * $("#mytable").datatables().fnAdjustColumnSizing();
     * where #mytable is the selector of table 
     * object pointing to this plugin.
     * This plugin should only be invoked from 
     * fnInitComplete callback.
     * @author John Diaz
     * @version 1.0
     * @date 06/28/2013
     */
    var selector = tableObject.selector; 
    var columnsToHide = [];

    $(selector).find('th').each(function(i) {

        var columnIndex = $(this).index(); 
        var rows = $(this).parents('table').find('tr td:nth-child(' + (i + 1) + ')'); //Find all rows of each column  
        var rowsLength = $(rows).length;
        var emptyRows = 0; 

        rows.each(function(r) { 
            if (this.innerHTML == '') 
                emptyRows++; 
        });  

        if(emptyRows == rowsLength) { 
            columnsToHide.push(columnIndex);  //If all rows in the colmun are empty, add index to array
        }  
    }); 
    for(var i=0; i< columnsToHide.length; i++) {
        tableObject.fnSetColumnVis( columnsToHide[i], false ); //Hide columns by index
    }
    /**
     * The following line doesn't work when the plugin 
     * is used inside jquery tabs, then you should
     * add manually this piece of code inside
     * the tabs initialization where ("#myTable") is 
     * your table id selector 
     * ej: $("#myTable").dataTable().fnAdjustColumnSizing();
     */

    tableObject.fnAdjustColumnSizing();
}

The plugin call:

"fnInitComplete": function () { 
    /**
     * Go to plugin definition for
     * instructions on how to use.
     */ 
    this.fnHideEmptyColumns(this);
}

If you have some observation on the code, please be polite, this it is not the last word on how to hide empty columns for jQuery dataTables plugin.

Answer 1

I was trying a lots of solutions before I found some code that fixed the problem - I use the "fnDrawCallback" with "api" variable to access columns() function. I also want to keep the first column in my table empty because I use some CSS to change the table appearance.

   $(document).ready(function(){
table = $("#tableofproducts").DataTable({
                "dom": '<"top"<"left"l>pf<"clear">>rt<"bottom"ip<"clear">>',
                "oLanguage": {
                    "sSearch": "Search in table"
                },
                responsive: true,
                "processing": true,
                'ajax': {
                    'url': '<%=ResolveUrl("~/GenericHendler/SearchResultHandler.ashx")%>'
                },
                "fnDrawCallback": function () {
                    $("#loading").hide();
                    var api = this.api();
                    setTimeout( function () {
                        api.columns().flatten().each(function (colIdx) {
                            var columnData = api.columns(colIdx).data().join('');
                            if (columnData.length == (api.rows().count() - 1) && colIdx != 0) {
                                api.column(colIdx).visible(false);
                            }
                        });
                    },0)
                }
            });
})

Answer 2

Would this be a way to handle this? Based on grabbing all columns at once, and filtering where row is empty. If all empty, then hide the column.

Could be added to your function you wrote. Like to try speed test.

//get how many columns there are
var columnCount = $('yourTable tr:first > td').length;

for(var x=0;x<columnCount;x++){
    var $columnRows = $("yourTable tbody td:nth-child(" + x + ")");
    if($columnRows.length < 0)
    {
        var $filteredRows = $columnRows.filter(function() { return $(this).html() != ""; } //only return rows where this column value is not empty
        if($filterdRows.length < 1)
        {
            $("yourTable tr td:nth-child(x)");    
        }

    }
}

Please, tell me what you think.

Answer 3

For anyone stumbling upon this while searching for solutions, there is a quite recent plugin for DataTables, which does what you want and is more customizeable than your function.

You can simply activate it by adding the hideEmptyCols option when creating your DataTable

$('#example-1').DataTable({
    hideEmptyCols: true
});

For a full set of options check the Plugin Github page. Still, it might here be necessary to call $("#example-1").DataTables().fnAdjustColumnSizing(); manually for some use cases, since columns got a lot wider when others were hidden.

Answer 4

If anyone lands on this in the search of a solution, we found a different route.

We started looking into this because we had groups of data, where the headings were basically an empty row other than a title. It basically evolved from starting with a static table for a while, letting it grow for a month or two and than having to add the sort/search functionality later. As such, when we sorted alphabetically, the group titles or headings weren't handling proper.

What we ended up was a replacement to the need to replace empty columns by using the RowGroup extension: https://datatables.net/extensions/rowgroup/examples/initialisation/simple.html

While I'm sure this isn't applicable to everyone who search this, I hope it helps someone who lands here from google in the future.

Answer 5

I came up with this. Add this as a DataTable option during init. You can change the check for empty ['0', '-', ' ', ''] being any characters.

initComplete: function (settings, json) {
    const api = this.api();
    api.columns().every( function (index) {
        const isEmpty = this.data().map(x => ['0', '-', ' ', ''].includes(x) ? "" : x).join('') === '';
        if (isEmpty) {
            api.columns(index).visible(false)
        }
    } );
},