File
Add library
Share
Login or Register
Help
HTML
 
<!DOCTYPE html>
<html>
  <head>
    <script src="https://code.jquery.com/jquery-1.11.3.min.js"></script>
​
<link href="https://cdn.datatables.net/v/dt/jszip-3.10.1/dt-1.13.8/b-2.4.2/b-html5-2.4.2/b-print-2.4.2/datatables.min.css" rel="stylesheet">
 
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/v/dt/jszip-3.10.1/dt-1.13.8/b-2.4.2/b-html5-2.4.2/b-print-2.4.2/datatables.min.js"></script>
​
    <meta charset=utf-8 />
    <title>DataTables - JS Bin</title>
  </head>
  <body>
    <div class="container">
      <table id="example" class="display nowrap" 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>
      </table>
    </div>
  </body>
</html>
​
CSS
 
body {
  font: 90%/1.45em "Helvetica Neue", HelveticaNeue, Verdana, Arial, Helvetica, sans-serif;
  margin: 0;
  padding: 0;
  color: #333;
  background-color: #fff;
}
​
JavaScript
 
​
var data = [
    ['Tiger \nNixon', 'System Architect', 'Edinburgh', '5421', '2011/04/25', '$320,800'],
];
​
var table = new DataTable('#example', {
  data: data,
  dom: 'Bfrtip',
  buttons: [
    {
      extend: 'print',
      exportOptions: {
        stripHtml: false
      }
    }, 
    {
      extend: 'excel',
​
      customize: function( xlsx ) {
        var sheet = xlsx.xl.worksheets['sheet1.xml'];
​
        $('row c[r^="A"]', sheet).attr( 's', '55' );
      },
      exportOptions: {
        stripHtml: false,
        orthogonal: 'export',
        format: {
          body: function ( data, row, column, node ) {
            if (column === 3) {
              //need to change double quotes to single
              data = data.replace( /"/g, "'" );
              //split at each new line
              splitData = data.split('\n');
              data = '';
              for (i=0; i < splitData.length; i++) {
                //add escaped double quotes around each line
                data += '\"' + splitData[i] + '\"';
                //if its not the last line add CHAR(13)
                if (i + 1 < splitData.length) {
                  data += ', CHAR(10), ';
                }
              }
              //Add concat function
              data = 'CONCATENATE(' + data + ')';
              return data;
            }
            return data;
          }
        },
      }
    }, 
    {
      extend: 'pdf',
      exportOptions: {
        stripNewlines: false,
        orthogonal: 'export'
      }
    }
  ],
  columnDefs: [
    {
      targets: 0,
      render: function (data, type, row) {
        if (type === 'display') {
          return data.replace(/\n/g, '<br>');
        }
        if (type === 'export') {
          return data;
        }
        return data;
      }
    }
  ]
});
​
​
Output

You can jump to the latest bin by adding /latest to your URL

Dismiss x
public
Bin info
anonymouspro
0viewers