DataTables Export to Excel

DataTables Export with some hidden columns

by Muhammad Mushtaq Sheikh

HTML

<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.4/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/2.5.0/jszip.min.js"></script>
<script src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/pdfmake.min.js"></script>
<script src="https://cdn.rawgit.com/bpampuch/pdfmake/0.1.18/build/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.4/js/buttons.html5.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.13/css/jquery.dataTables.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.2.4/css/buttons.dataTables.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-alpha.5/css/bootstrap.min.css">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-alpha.5/js/bootstrap.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/tether/1.3.7/js/tether.min.js"></script>
<table id="example" class="display" cellspacing="0" width="100%">
  <thead>
    <tr>
      <th>Name</th>
      <th>Position</th>
      <th>Office</th>
      <th>Age</th>
      <th>Start date</th>
      <th>Salary</th>
    </tr>
  </thead>
  <tfoot>
    <tr>
      <th>Name</th>
      <th>Position</th>
      <th>Office</th>
      <th>Age</th>
      <th>Start date</th>
      <th>Salary</th>
    </tr>
  </tfoot>
  <tbody>
    <tr>
      <td>Tiger Nixon</td>
      <td>System Architect</td>
      <td>Edinburgh</td>
      <td>61</td>
      <td>2011/04/25</td>
      <td>$320,800</td>
    </tr>
    <tr>
      <td>Garrett Winters</td>
      <td>Accountant</td>
      <td>Tokyo</td>
      <td>63</td>
      <td>2011/07/25</td>
      <td>$170,750</td>
    </tr>
    <tr>
      <td>Ashton Cox</td>
      <td>Junior Technical Author</td>
      <td>San Francisco</td>
      <td>66</td>
     ...

CSS

body {
  margin: 15px;
}

JavaScript

var dt;
$(document).ready(function() {
  //var buttonCommon = {
    //exportOptions: {
    // format: {
    //    body: function ( data, row, column, node ) {
    // Strip $ from salary column to make it numeric
    //   return column === 5 ?
    //     data.replace( /[$,]/g, '' ) :
    //     data;
    //    }
    //  }
    // }
  //};
  // Configure Export Buttons


dt =  $('#example').DataTable({
    columns: [{
      data: 'name'
    }, {
      data: 'position'
    }, {
      data: 'office'
    }, {
      data: 'age',
      visible: false
    }, {
      data: 'start_date'
    }, {
      data: 'salary',
      visible: false
    }],
    dom: 'Bfrtip'
      });
  
});
  new $.fn.dataTable.Buttons( dt, {
    buttons: [
        {
            text: '<i class="fa fa-lg fa-clipboard"></i>',
            extend: 'copy',
            className: 'btn btn-xs btn-primary p-5 m-0 width-35 assets-export-btn export-copy ttip'
        }, {
            text: '<i class="fa fa-lg fa-file-text-o"></i>',
            extend: 'csv',
            className: 'btn btn-xs btn-primary p-5 m-0 width-35 assets-export-btn export-csv ttip',
            title: export_filename,
            extension: '.csv'
        }, {
            text: '<i class="fa fa-lg fa-file-excel-o"></i>',
            extend: 'excel',
            className: 'btn btn-xs btn-primary p-5 m-0 width-35 assets-export-btn export-xls ttip',
            title: export_filename,
            extension: '.xls'
        }, {
            text: '<i class="fa fa-lg fa-file-pdf-o"></i>',
            extend: 'pdf',
            className: 'btn btn-xs btn-primary p-5 m-0 width-35 assets-export-btn export-pdf ttip',
            title: export_filename,
            extension: '.pdf'
        }
    ]
} );
// Add the Export buttons to the toolbox
dt.buttons( 0, null ).container().appendTo( '#export-assets' );