Wednesday, March 29, 2017

Datatable export excel wraptext and newline



I have 14 column table id is tab1 under div class ajax_list, following code
is to solve Datatable export excel wraptext and newline

$(document).ready( function() {
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,6,7,8,9,10,11,12,13]
      },
      customize: function(xlsx) {
        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', '50' ); //<-- left aligned text
        $('row c[r^="C"]', sheet).attr( 's', '55' ); //<-- wrapped text
   //     $('row:first c', sheet).attr( 's', '32' );
        $('row c[r^="D"]', sheet).attr( 's', '55' ); //<-- wrapped text
        $('row c[r^="E"]', sheet).attr( 's', '55' ); //<-- wrapped text
        $('row c[r^="F"]', sheet).attr( 's', '55' ); //<-- wrapped text
        $('row c[r^="G"]', sheet).attr( 's', '55' ); //<-- wrapped text
        $('row c[r^="H"]', sheet).attr( 's', '55' ); //<-- wrapped text
        $('row c[r^="I"]', sheet).attr( 's', '55' ); //<-- wrapped text
        $('row c[r^="J"]', sheet).attr( 's', '55' ); //<-- wrapped text
        $('row c[r^="K"]', sheet).attr( 's', '55' ); //<-- wrapped text
        $('row c[r^="L"]', sheet).attr( 's', '55' ); //<-- wrapped text
        $('row c[r^="M"]', sheet).attr( 's', '55' ); //<-- wrapped text       
        $('row c[r^="N"]', sheet).attr( 's', '55' ); //<-- wrapped text       
      }
    })
  ]
});
      $("div.ajax_list").find('#tab1').DataTable({

                    paging: false,
                    fixedHeader: true
                } );

} );
Reference:
https://jsfiddle.net/lbriquet/0n9j52jx/

In some case, we need to update the cell and  need to reinitialize datatable.
To reinitialize datatable: (for table id is tab1)
              $('#tab1').DataTable( {
                     destroy: true,

                    paging: false,
                    fixedHeader: true
                } );


Monday, March 6, 2017

Powerful js library datable to export table in pdf, excel, sorting, freezing column and table header fixed (sticky)



Datable js library can be downloaded from
https://datatables.net/download/release

DataTables

DataTables is the core software of the DataTables project, and involves two primary files, the DataTables Javascript and CSS.

js:
https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js
css:
https://cdn.datatables.net/1.10.13/css/jquery.dataTables.min.css

FixedHeader

The FixedHeader plug-in will freeze in place the header, footer and left and/or right most columns in a DataTable, ensuring that title information will remain always visible.
js:
https://cdn.datatables.net/fixedheader/3.1.2/js/dataTables.fixedHeader.min.js
css
https://cdn.datatables.net/fixedheader/3.1.2/css/fixedHeader.dataTables.min.css 

Example:

$('#myTable').DataTable( {
    fixedHeader: true
} );

FixedColumns

FixedColumns "freezes" in place the left most columns in a scrolling DataTable, to provide a guide to the end user (for example an index column).
js:
https://cdn.datatables.net/fixedcolumns/3.2.2/js/dataTables.fixedColumns.min.js 
css
https://cdn.datatables.net/fixedcolumns/3.2.2/css/fixedColumns.dataTables.min.css

Export pdf and excel

Example

$(document).ready(function() {
    $('#example').DataTable( {
        dom: 'Bfrtip',
        buttons: [
            'copy', 'csv', 'excel', 'pdf', 'print'
        ]
    } );
} );
In addition to the above code, the following Javascript library files are loaded for use in this example:
 Reference:
https://datatables.net/extensions/buttons/examples/initialisation/export.html 

Sorting

https://datatables.net/examples/basic_init/table_sorting.html