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


10 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
  6. this is very nice blog this studying course information very useful to everyone who have learning this information.

    Hadoop Training in Chennai

    ReplyDelete
  7. I appreciate your style of writing because it conveys the message of what you are trying to say. It's a great skill to make even the person who doesn't know about the subject could able to understand the subject . Your blogs are understandable and also informative. I hope to read more and more interesting articles from your blog. All the best.
    Pimple Treatment

    ReplyDelete
  8. this is very nice blog this studying course information very useful to everyone who have learning this information.this education information is very helpful to start my carrier with technology.

    Hadoop Training in Chennai

    ReplyDelete
  9. I must thank you for the efforts you have put in spending this site. I am hoping to out the same high-grade content by you later on as well. In truth, your creative writing abilities has inspired me to get my own, personal blog now..
    Java Training in Chennai
    Dot Net Training in Chennai
    Cloud Computing Training in Chennai
    Digital Marketing Training in Chennai
    SAS Training in Chennai
    SEO Training in Chennai
    AWS Training in Chennai
    Microsoft Azure Training in Chennai

    ReplyDelete
  10. its really very useful topic. it should be help us more and then thanks to post article like this.
    Eczema Treatment
    Psoriasis Oil
    Hyperpigmentation Treatment

    ReplyDelete