datatable remove column on export to pdf and excel

Question

i have a problem when remove column before export to pdf,excel. and second problem that because of this column the reverse of column doesn't work right here is the code i use

$(document).ready(function(){
            var arrayCol = new Array();
            var table = $('#example').DataTable({
                dom: 'B<"top"iflp<"clear">>rt<"bottom"ip<"clear">>',
                initComplete:function (  ) {
                    var len = this.api().columns().count();
                    var array =  Array.from(Array(len).keys());
                    arrayCol = array.reverse();
                  },
            buttons: [
      { 
        extend: 'pdf',
        text: 'To PDF',
        exportOptions: {
          rows: function ( idx, data, node ) {
            return data.reverse();
          },
          format: {
          columns: ':visible:not(.not-export-col)', 
            header: function ( data, idx, node ) {
              var headers = $('#example').DataTable().table().header();
              var reversedHeaders = headers.innerText.split('	').reverse();
              return reversedHeaders[idx];
            }
          }
        }
      },
               { 
        extend: 'excel',
        text: 'exel',
        exportOptions: {
        columns: ':visible:not(.not-export-col)', 
          rows: function ( idx, data, node ) {
            return data.reverse();
          },
          format: {
            header: function ( data, idx, node ) {
              var headers = $('#example').DataTable().table().header();
              var reversedHeaders = headers.innerText.split('	').reverse();
              return reversedHeaders[idx];
            }
          }
        }
      }
    ]
                });
        });

and here is live example

user_12567365 · Answer

You can extend the approach used here to handle the extra requirement for suppressing one or more columns that you do not want to export.

Your use of the columns selector works well:

columns: ':visible:not(.not-export-col)

For building a list of the headers we want to export, the initComplete function can be used (so we only perform this process once).

We can also use the initComplete function to build an array of column indexes that we do not want to export:

var ignorePositions = []; // column indexes of data NOT to be exported
var reversedHeaders = []; // with "not-export" headings removed

The function:

initComplete:function (  ) {
  var thead = $( '#example' ).DataTable().table().header();
  var tds = $( thead ).find( 'th' ).each(function( index ) {
    if ( ! $( this ).hasClass('not-export-col') ) {
      reversedHeaders.push( $( this ).text() );
    } else {
      ignorePositions.push(index);
    }
  });
  reversedHeaders.reverse(); // to give us the export order we want
  ignorePositions.reverse(); // reversed for when we splice() - see below
}

The above code populates two arrays:

reversedHeaders - containing a list (in reverse) of the headings for those columns we will export.
ignorePositions - containing a list of column indexes to be ignored. In our example, the only such column is the final one (index 6).

Then we can use the above arrays in our modified exportOptions code:

exportOptions: {
  rows: function ( idx, data, node ) {
    var keepRowData = [];
    // we splice to remove those data fields we do not want to export:
    ignorePositions.forEach(idx => data.splice(idx, 1) );
      return data.reverse();
  },
  columns: ':visible:not(.not-export-col)',
  format: { 
    header: function ( data, idx, node ) {
      return reversedHeaders[idx];
    }
  }
}

The only tricky part is the need to use splice to directly alter the original data array. This cuts out each unwanted element from the original data array, without creating a new data array.

Putting it all together:




  
  Demo
  
  
  
  

  
   
  
  
  
  
  
  
  
  






      
        
          
            Name
            Position
            Office
            Age
            Start date
            Salary
            opr
          
        

        
          
            Tiger Nixon
            System Architect
            Edinburgh
            61
            2011/04/25
            $3,120

The table data, which looks like this:

Is exported to PDF like this:

Final notes:

I only implemented the PDF button in the above code. The Excel button code also needs to be added, but should be identical.
I commented out the section of the HTML table in my code. I think this means you will also need to add a footer: function() { ... } to your implementation, to match the header: function() { ... } code. I assume that would be as follows (but I did not test this):

format: { 
  header: function ( data, idx, node ) {
    return reversedHeaders[idx];
  },
  footer: function ( data, idx, node ) {
    return reversedHeaders[idx];
  }
}

Update

I see the annoying alert. This is caused by my code which changes the length of the data row (when it splices out an element).

A second issue is that once a row has been reversed, it remains reversed - so, on the next export, the exported data is in the wrong order.

To address both of these is messy, and my proposed solution is limited:

My solution assumes that you only want to hide the final column.

It also relies on you being able to detect when a row is reversed - and in my case this is done by checking for a specific value in the final location of the row data - again, not a very robust approach - but it is the only approach I can think of right now.

Here is the updated code:

$(document).ready(function() {
  var exportPositions = []; // column indexes of data NOT to be exported
  var ignorePositions = []; // column indexes of data NOT to be exported
  var reversedHeaders = []; // with "not-export" headings removed

  var table = $('#example').DataTable( {
    dom: 'B<"top"iflp<"clear">>rt<"bottom"ip<"clear">>',
    initComplete:function (  ) {
      var thead = $( '#example' ).DataTable().table().header();
      var tds = $( thead ).find( 'th' ).each(function( index ) {
        if ( ! $( this ).hasClass('not-export-col') ) {
          reversedHeaders.push( $( this ).text() );
          exportPositions.push(index);
        } else {
          ignorePositions.push(index);
        }
      });
      reversedHeaders.reverse(); // to give us the export order we want
      reversedHeaders.push('');
      ignorePositions.reverse(); // reversed for when we splice() - see below
    },
    buttons: [
      { 
        extend: 'pdf',
        text: 'To PDF',
        exportOptions: {
          rows: function ( idx, data, node ) {
            if (data[data.length - 1] === '~') {
              data.reverse(); // un-reverse an already reversed record
              data.push(data.splice(0, 1)[0]); // move first element to end of the array
            }
            // we splice to remove those data fields we do not want to export:
            ignorePositions.forEach(idx => {
              data.splice(idx, 1);
            } );
            data.reverse();
            ignorePositions.forEach(idx => {
              data.push('~'); // pad the array back to its original length
            } );
            return data;
          },
          columns: exportPositions, // here we use the array we built earlier
          format: { 
            header: function ( data, idx, node ) {
              return reversedHeaders[idx];
            }
          }
        }
      }
    ]
  } );
} );

If your actual implementation needs to hide multiple columns then this approach will probably not work for you. If I have any thoughts on a more robust solution, I will update the answer.

datatable remove column on export to pdf and excel

1 Answers