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
                } );


2 comments:

  1. It’s the best time to make some plans for the future and it is time to be happy. I’ve read this post and if I could I want to suggest you few interesting things or suggestions.You can write next articles referring to this article. I desire to read even more things about it..
    SEO Training in Chennai
    Selenium Training in Chennai
    Web Designing Training in Chennai

    ReplyDelete
  2. Interesting blog which attracted me more.I hope you will post more update like this.
    Digital marketing company in Chennai

    ReplyDelete