How to add second footer to print in Tfoot?

chael711

Guys, I can't print the second footer in <tfoot> tag?
How can I resolve this issue?

allan

There is no option to print multiple headers or footers using the print button at this time.

If this is a required feature for you, you'd need to modify the source to add that ability.

Allan

chael711

allan would you mind if you can tell me, what file contains that functionality?
and Im on in modifying the file

allan

This is where Buttons gets the header information. Likewise the footer follows that.

Currently that is a 1D array. You'll need to update that to get a 2D array of data (i.e. rows and columns) and then update the output functions to accept the 2D array, instead of the 1D they currently expect.

Allan

dfader

I've updated lisarush's answer https://datatables.net/forums/discussion/22592/export-multiple-row-headers for the latest version of DataTables. My changes were only made to support multiple headers (for .csv, .xlsx, and copy), but the same logic should easily apply for footers as well. I haven't worked on the code for multiple headers for PDF yet, but will update if I do.

The lines commented out with // are the original source code.

My changes are below:

dataTables.buttons.js:

New Code:

/* ----- BEGIN added Code ----- */
var getHeaders = function( dt ){
    var thRows = dt.nTHead.rows;
    var numRows = thRows.length;
    var matrix = [];

    // Iterate over each row of the header and add information to matrix.
    for ( var rowIdx = 0;  rowIdx < numRows;  rowIdx++ ) {
        var $row = $(thRows[rowIdx]);

        // Iterate over actual columns specified in this row.
        var $ths = $row.children("th");
        for ( var colIdx = 0;  colIdx < $ths.length;  colIdx++ )
        {
            var $th = $($ths.get(colIdx));
            var colspan = $th.attr("colspan") || 1;
            var rowspan = $th.attr("rowspan") || 1;
            var colCount = 0;
         
            // ----- add this cell's title to the matrix
            if (matrix[rowIdx] === undefined) {
                matrix[rowIdx] = [];  // create array for this row
            }
            // find 1st empty cell
            for ( var j = 0;  j < (matrix[rowIdx]).length;  j++, colCount++ ) {
                if ( matrix[rowIdx][j] === "PLACEHOLDER" ) {
                    break;
                }
            }
            var myColCount = colCount;
            matrix[rowIdx][colCount++] = $th.text();
        
            // ----- If title cell has colspan, add empty titles for extra cell width.
            for ( var j = 1;  j < colspan;  j++ ) {
                matrix[rowIdx][colCount++] = "";
            }
         
            // ----- If title cell has rowspan, add empty titles for extra cell height.
            for ( var i = 1;  i < rowspan;  i++ ) {
                var thisRow = rowIdx+i;
                if ( matrix[thisRow] === undefined ) {
                    matrix[thisRow] = [];
                }
                // First add placeholder text for any previous columns.                 
                for ( var j = (matrix[thisRow]).length;  j < myColCount;  j++ ) {
                    matrix[thisRow][j] = "PLACEHOLDER";
                }
                for ( var j = 0;  j < colspan;  j++ ) {  // and empty for my columns
                    matrix[thisRow][myColCount+j] = "";
                }
            }
        }
    }
   
    return matrix;
};
/* ----- END added Code ----- */`

Modified Code:

In _exportData function:

    /* ----- BEGIN changed Code ----- */
    var headerMatrix = getHeaders( dt.settings()[0] );
    
/*
    var header = dt.columns( config.columns ).indexes().map( function (idx) {
        var el = dt.column( idx ).header();
        return config.format.header( el.innerHTML, idx, el );
    } ).toArray();
*/
    /* ----- END changed Code ----- */    


/* ----- BEGIN changed Code ----- */
    var columns = headerMatrix[headerMatrix.length - 1].length;
//  var columns = header.length;
/* ----- END changed Code ----- */


/* ----- BEGIN changed Code ----- */
    return {
        header: headerMatrix,
        footer: footer,
        body:   body
    };
/* ----- END changed Code ----- */

buttons.html5.js:

Modified Code:

In _exportData function:

    /* ----- BEGIN changed Code ----- */
    
    var header = '';
    if (config.header){
        for (i = 0; i < data.header.length; i++){
            header = header + join( data.header[i] )+newLine;
        }
    }

//  var header = config.header ? join( data.header )+newLine : '';
    /* ----- END changed Code ----- */

In DataTable.ext.buttons.excelHtml5 action function:

//  var header = config.header ? join( data.header )+newLine : '';
    /* ----- END changed Code ----- */    

    /* ----- BEGIN changed Code ----- */
    for (i = 0; i < data.header.length; i++){
        addRow( data.header[i] );
    }
    /* ----- END changed Code ----- */

buttons.print.js:

Modified Code:

In DataTable.ext.buttons.print function:

            /* ----- BEGIN changed Code ----- */
            for (i = 0; i < data.header.length; i++){
                html += '<thead>'+ addRow( data.header[i], 'th' ) +'</thead>';
            }
            /* ----- END changed Code ----- */      
//          html += '<thead>'+ addRow( data.header, 'th' ) +'</thead>';

allan

Very nice indeed - thank you for sharing this with us!

Allan

laylantrin

@dfader, Great work, very helpful for us newbies! Could you please provide an example for footers as well? I have been trying to modify your code to work for a multiple row footer, but I cannot get it to work.

chrisvwn

Hi,

I have been able to add multiple headers for pdf. Also a basic merge for colspan/rowspan headers in xlsx. Unfortunately, I was working with rstudio/DT which I am not sure is exactly the same as the datatables.net code or maybe the versions are different. All the same I think it might be portable to datatables even if only in concept. I have posted my steps here: https://github.com/rstudio/DT/issues/418. I hope this helps.

Chris

puffster

@dfader thanks for the code, this is working well except for one thing...if I use exportOptions, columns to omit certain columns, the export is still picking up all the columns in the header. Have you experienced this? For instance, I have a simple table that has three columns, but I only wish to export the second and third columns. When I export to Excel, let's say, it's still showing all three column headers, but then exporting data for the second and third columns only, which is throwing off all the rows.

puffster

I was able to make a simple modification to get this to work.

In the _exportData function a variable already exists that contains the column indexes that have been selected: config.columns. I'm passing this value into the getHeaders function that you created above, along with the datatable, like so:

    /* ----- BEGIN changed Code for Multiple Export Headers----- */
    var headerMatrix = getHeaders(dt.settings()[0], config.columns);
    
        //var header = dt.columns( config.columns ).indexes().map( function (idx) {
        //    var el = dt.column(idx).header();
        //    return config.format.header(el.innerHTML, idx, el);
        //} ).toArray();
    
    /* ----- END changed Code ----- */

In the getHeaders function, where you start to parse through the columns in each row, I'm wrapping the code in this statement:

if ($.inArray(colIdx, selectedColumns) != -1) {
}

So now the getHeaders function looks like this:

/* ----- BEGIN added Code ----- */
var getHeaders = function( dt, selectedColumns ){
    var thRows = dt.nTHead.rows;
    var numRows = thRows.length;
    var matrix = [];

    // Iterate over each row of the header and add information to matrix.
    for ( var rowIdx = 0;  rowIdx < numRows;  rowIdx++ ) {
        var $row = $(thRows[rowIdx]);
 
        // Iterate over actual columns specified in this row.
        var $ths = $row.children("th");
        for ( var colIdx = 0;  colIdx < $ths.length;  colIdx++ )
        {
            if ($.inArray(colIdx, selectedColumns) != -1) {
                var $th = $($ths.get(colIdx));
                var colspan = $th.attr("colspan") || 1;
                var rowspan = $th.attr("rowspan") || 1;
                var colCount = 0;

                // ----- add this cell's title to the matrix
                if (matrix[rowIdx] === undefined) {
                    matrix[rowIdx] = [];  // create array for this row
                }
                // find 1st empty cell
                for (var j = 0; j < (matrix[rowIdx]).length; j++, colCount++) {
                    if (matrix[rowIdx][j] === "PLACEHOLDER") {
                        break;
                    }
                }
                var myColCount = colCount;
                matrix[rowIdx][colCount++] = $th.text();

                // ----- If title cell has colspan, add empty titles for extra cell width.
                for (var j = 1; j < colspan; j++) {
                    matrix[rowIdx][colCount++] = "";
                }

                // ----- If title cell has rowspan, add empty titles for extra cell height.
                for (var i = 1; i < rowspan; i++) {
                    var thisRow = rowIdx + i;
                    if (matrix[thisRow] === undefined) {
                        matrix[thisRow] = [];
                    }
                    // First add placeholder text for any previous columns.                
                    for (var j = (matrix[thisRow]).length; j < myColCount; j++) {
                        matrix[thisRow][j] = "PLACEHOLDER";
                    }
                    for (var j = 0; j < colspan; j++) {  // and empty for my columns
                        matrix[thisRow][myColCount + j] = "";
                    }
                }
            }
        }
    }

    return matrix;
};
/* ----- END added Code ----- */

And that's it!! Hope this helps others who may run into this problem.

Kergnob

Hey @puffster I have a question to further go down this path. So I was experiencing the same issue as far as export taking all the columns even if you set them to not be selected. I added in the code you posted and it works for Copy/Excel(CSV) but Print is not acting how I suspect it should. It appears to print the first row (2 TR in my case) fully without restricting the print like the sorted row does

so instead of the column visibility affecting all the columns/<tr>'s, it seems to not affect the extra <tr> (that doesn't have the sorts on it).

I tried forcing the array's to match one another in length depending on if it was trying to print or not but I couldn't find a way to get it to fire off the click event in that class (dt-buttons), let alone the print specific class.

Kergnob

it looks like it isn't taking in ColSpan's, so even though I have 9 physical TH rows, its spanned over 27 and thus extends out the side while the sort row has 27 columns so it properly removes the columns based on what is hidden on the print button.

Any ideas?

Also ran into an IE Edge/FF issue with Copy and Excel complaing about .length when I used @puffster code to have it properly remove the hidden columns (the array is returning null/unidentified so length on it screws it up?)

puffster

@Kergnob sorry for not responding sooner, I was hoping to get some time to try to create your issue for myself, but I haven't. Unfortunately I'm not sure why printing would not behave in the same manner as exporting to Excel does...have you had any luck in solving your issue yet?

lisarush

In building from my original post (which I'm unable to comment on) and subsequent work/updates here (by dfader) to handle dataTables updates, I've included how I've updated the code to support multi-row headers -- taking into account columns that may be initially hidden (this is where I had the biggest issue as they are not included in the DOM) and any columns that may be marked to exclude from an export (we do this with a class .noExport). The result will export all currently visible columns that are configured via 'config.columns'. Also handles 'messageTop/Bottom' options. "getHeaders" is only slightly changed from the original; it's the loop afterwards that's different. Would probably be more efficient to combine (like puffster did), but I like the logic separation. All code included here for clarity (all original code is still there, just commented out). Hope this helps.

inside buttons _exportData:

    /* ----- BEGIN added code */
    var getHeaders = function( dt ) {
        var thRows = dt.table().header().rows;
        var numRows = thRows.length;
        var matrix = [];
     
        // Iterate over each row of the header and add information to matrix.
        for ( var rowIdx = 0;  rowIdx < numRows;  rowIdx++ ) {
            var $row = $(thRows[rowIdx]);
     
            // Iterate over all columns specified in this row.
            var $ths = $row.children("th");
            for ( var colIdx = 0;  colIdx < $ths.length;  colIdx++ )
            {
                var $th = $($ths.get(colIdx));
                var colspan = $th.attr("colspan") || 1;
                var rowspan = $th.attr("rowspan") || 1;
                var colCount = 0;

                // ----- add this cell's title to the matrix
                if (matrix[rowIdx] === undefined) {
                    matrix[rowIdx] = [];  // create array for this row
                }
                // find 1st empty cell
                for ( var j = 0;  j < (matrix[rowIdx]).length;  j++, colCount++ ) {
                    if ( matrix[rowIdx][j] === "PLACEHOLDER" ) {
                        break;
                    }
                }
                var myColCount = colCount;
                matrix[rowIdx][colCount++] = $th.text();

                // ----- If title cell has colspan, add empty titles for extra cell width.
                for ( var j = 1;  j < colspan;  j++ ) {
                    matrix[rowIdx][colCount++] = "";
                }

                // ----- If title cell has rowspan, add empty titles for extra cell height.
                for ( var i = 1;  i < rowspan;  i++ ) {
                    var thisRow = rowIdx+i;
                    if ( matrix[thisRow] === undefined ) {
                        matrix[thisRow] = [];
                    }
                    // First add placeholder text for any previous columns.                
                    for ( var j = (matrix[thisRow]).length;  j < myColCount;  j++ ) {
                        matrix[thisRow][j] = "PLACEHOLDER";
                    }
                    for ( var j = 0;  j < colspan;  j++ ) {  // and empty for my columns
                        matrix[thisRow][myColCount+j] = "";
                    }
                }
            }
        }
        
        return matrix;
    }
    /* ----- END added code */

    /* ----- BEGIN changed code */
    var rawHeaderMatrix = getHeaders( dt );
    var headerMatrix = [];
        var numRows = dt.table().header().rows.length;
        for ( var rowIdx = 0;  rowIdx < numRows;  rowIdx++ ) {
          var headerRow = dt.columns( config.columns ).indexes('visible').map( function (colIdx) {
             var $th = rawHeaderMatrix[rowIdx][colIdx];
             return $th;
       } ).toArray();
       headerMatrix.push( headerRow );
    }
/*  
    var header = dt.columns( config.columns ).indexes().map( function (idx) {
        var el = dt.column( idx ).header();
        return config.format.header( el.innerHTML, idx, el );
    } ).toArray();
*/  
    /* ----- END changed code */

   ...

    /* -----  BEGIN changed code */
    // var columns = header.length;
    var columns = headerMatrix[headerMatrix.length - 1].length;
    /* ------ END changed code */

   ...

   return {
      header: headerMatrix,   // ----- CHANGED FROM: header,
      footer: footer,
      body: body
   };

in buttons.html5 _exportData:

   /* ----- BEGIN changed code */
   // var header = config.header ? join( data.header )+newLine : '';
   var header = '';
   if ( config.header ) {
      for ( i = 0;  i < data.header.length;  i++ ) {
         header = header + join( data.header[i] ) + newLine;
      }
   }
   /* ----- END changed code */

in DataTable.ext.buttons.excelHtml5 action:

   /* ----- BEGIN added code */
   var numColumns = data.header[data.header.length - 1].length;  // # of columns in last header row
   /* ----- END added code */
   if ( exportInfo.title ) {
      addRow( [exportInfo.title], rowPos );
      /* ----- BEGIN changed code */
      // mergeCells( rowPos, data.header.length - 1 );
      mergeCells( rowPos, numColumns - 1 );
      /* ----- END changed code */
   }

   if ( exportInfo.messageTop ) {
      addRow( [exportInfo.messageTop], rowPos );
      /* ----- BEGIN changed code */
      // mergeCells( rowPos, data.header.length - 1 );
      mergeCells( rowPos, numColumns - 1 );
      /* ----- END changed code */
   }

   if ( config.header ) {
      /* ----- BEGIN changed code */
      // addRow( data.header, rowPos );
      for ( i = 0;  i < data.header.length;  i++ ) {
         addRow( data.header[i], rowPos, data.bodyDom[i] ) ;
      }
      $('row c', rels).attr( 's', '2' );  // bold
      /* ----- END changed code */
   }

   ...

   // Below the table
   if ( exportInfo.messageBottom ) {
      addRow( [exportInfo.messageBottom], rowPos );
      /* ----- BEGIN changed code */
      // mergeCells( rowPos, data.header.length - 1 );
      mergeCells( rowPos, numColumns - 1 );
      /* ----- END changed code */
   }

in DataTable.ext.buttons.print action:

   if ( config.header ) {
      /* ----- BEGIN changed code */
      // html += '<thead>'+ addRow( data.header, 'th' ) +'</thead>';
      for ( i = 0;  i < data.header.length;  i++ ) {
         html += '<thead>'+ addRow( data.header[i], 'th' ) +'</thead>';
      }
      /* ----- END changed code */
   }

steevos

Thanks for this and all the effort you put into it. I applied all of the changes but the code seems to stop on

    var columns = headerMatrix[headerMatrix.length - 1].length;

I get the following error:

dataTables.buttons.js:1667 Uncaught TypeError: Cannot read property 'length' of undefined

I tried using an older version of dataTables and the newer but I get the same error on both...

Any idea?

rimoi

it work for me but i change my _exportData function in buttons.html5.js file like this :

var getHeaders = function( dt ){//...} // --> you put function (see top)

var _exportData = function ( dt, config )
{
  // ... 

      /* ----- BEGIN changed Code ----- */
        var headerMatrix = getHeaders( dt.settings()[0] );
        // var header = config.header ? join( data.header )+newLine : ''; // --> comment this line
        var header = '';
        if (config.header){
            for (i = 0; i < headerMatrix.length; i++){
                header = header + join( headerMatrix[i] )+newLine;
            }
        }
        /* ----- end changed Code ----- */

   // ...

emoretto

I've created a custom buttons.html5.js (based in lisarush's solution) that allows Multiple Headers and Footers for Excel export:

https://gist.github.com/emersonmoretto/41993309f74a4b09f8e90c0a541de342

cris19n

@emoreto tu codigo funciona pero, se puede hacer que se combiene las seldas para que sea más facil de saber que grupos pertecen a ese encabezados, ya que uso diferentes encabezados, algo similar a las tablas de excel.

cris19n

@emoretto Your code works, but can you combine the cells to make it easier to tell which groups belong to those headers, so that the result is similar to the table being exported?

This is the table of my page: as you see the titles are ordered.

This is what the file looks like when exported to Excel - as you can see, the titles are not ordered:

I want the excel file to be similar to the table that was exported or at least the headers are sorted: