Datatables Excel HTML5

Datatables Excel HTML5

by dying_sakura

HTML

<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.2/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.flash.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.2/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.2.2/js/buttons.print.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css">
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/1.2.2/css/buttons.dataTables.min.css">
<h1>Save Excel HTML5 test</h1>
<table id="myTable" width="100%" border="0" cellspacing="0" cellpadding="0">
  <thead>
    <tr>
      <th>Title</th>
      <th>Author</th>
      <th>Publisher's blurb</th>
      <th>First published</th>
      <th>Publisher</th>
      <th>No. of Pages</th>
    </tr>
  </thead>
  <tfoot>
  </tfoot>
  <tbody>
    <tr>
      <td>To Kill a Mockingbird</td>
      <td>Harper Lee</td>
      <td>Small-town Alabama, 1932. Based on Harper Lee's Pulitzer Prize winning book of 1961.Through the eyes of &quot;Scout&quot; a feisty six-year-old tomboy...</td>
      <td>1960</td>
      <td>J. B. Lippincott &amp; Co.</td>
      <td>281</td>
    </tr>
    <tr>
      <td>1984</td>
      <td> George Orwell</td>
      <td>The year 1984 has come and gone, but George Orwell's prophetic, nightmarish vision in 1949 of the world we were becoming is timelier than ever...</td>
      <td>1949</td>
      <td>Secker &amp; Warburg</td>
      <td>267</td>
    </tr>
    <tr>
      <td>Pride and Prejudice</td>
      <td>Jane Austen</td>
      <td>Elizabeth Bennet is...

CSS

#myTable td {
  vertical-align: right;
}

JavaScript

var buttonCommon = {
  exportOptions: {
    format: {
      body: function(data, column, row) {
        data = data.replace(/<br\s*\/?>/ig, "\r\n");
        data = data.replace(/<.*?>/g, "");
        data = data.replace("&amp;", "&");
        data = data.replace("&nbsp;", "");
        data = data.replace("&nbsp;", "");
        return data;
      }
    }
  }
};
$.extend(true, $.fn.dataTable.defaults, {
  "lengthChange": false,
  "pageLength": 100,
  "orderClasses": false,
  "stripeClasses": [],
  dom: 'Bfrtip',
  buttons: [
    $.extend(true, {}, buttonCommon, {
      extend: 'excel',
      exportOptions: {
        columns: [0, 1, 2, 3, 4, 5]
      },
      customize: function(xlsx) {
      	var ocellXfs = $('cellXfs', xlsx.xl['styles.xml']);
      	ocellXfs.append('<xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+'<alignment vertical="top"/>'+'</xf>');
        var oxf = $('xf', xlsx.xl['styles.xml']);
        var styleIndex = oxf.length;
        var sheet = xlsx.xl.worksheets['sheet1.xml'];
        $('row c[r^="A"]', sheet).attr( 's', '50' ); //<-- left aligned text
        $('row c[r^="B"]', sheet).attr( 's', styleIndex ); //<-- Top Align
        $('row c[r^="C"]', sheet).attr( 's', '55' ); //<-- wrapped text
        $('row:first c', sheet).attr( 's', '32' );
      }
    })
  ]
});
$(document).ready(function() {
  $('#myTable').DataTable();
});