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


5 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


  3. Its a wonderful post and very helpful, thanks for all this information. You are including better information regarding this topic in an effective way.Thank you so much

    Personal Installment Loans
    Payday Cash Advance loan
    Title Car loan
    Cash Advance Loan

    ReplyDelete
  4. 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..
    Office Interior Designers in Bangalore
    Office Interior Designers in Hyderabad

    ReplyDelete
  5. It's like you read my mind! You seem to know a lot about this, like you wrote the book in it or something. I think that you can do with some pics to drive the message home a little bit, but instead of that, this is fantastic blog. A great read. I will definitely be back.
    Delivery service singapore
    Singapore delivery service
    24 hour delivery singapore

    ReplyDelete