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


9 comments:

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

    Hadoop Training in Chennai

    ReplyDelete
  3. 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
  4. 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
  5. This blog useful of education!!!


    and devlopment skils!!
    thank u for giving information..


    Java Training in Chennai.

    ReplyDelete
  6. Thank you for sharing such a nice and interesting blog with us.
    SAP SD Training in Chennai


    ReplyDelete
  7. GOOD DAY AND WELCOME TO ASDA FINANCE LTD®

    We Are Currently Trying To Invest In A Good Profitable Business, Please Present Only Viable Business Ideas. If You Have Projects That Need Financing Kindly Come Back With Your Business Plan.
    If We Find Your Business (Investment) Profitable, Then you Will Be Contacted As Soon As Possible.
    Whatever Your Circumstances are, Self-Employed, Retired, With Poor Credit.
    We Can Help You With Our Flexible Repayment Schedule of about 2.7 to 3.2% interest rate only.
    We Can Also Meet Your Financial Need Without Repayment Problems(1year) after the loan is transferred.
    Please Contact Us:
    asdaservices24@gmail.com
    Thanks And God Blessings.

    ReplyDelete
  8. Jiansen Lu'S Computing Blog: Datatable Export Excel Wraptext And Newline >>>>> Download Now

    >>>>> Download Full

    Jiansen Lu'S Computing Blog: Datatable Export Excel Wraptext And Newline >>>>> Download LINK

    >>>>> Download Now

    Jiansen Lu'S Computing Blog: Datatable Export Excel Wraptext And Newline >>>>> Download Full

    >>>>> Download LINK

    ReplyDelete
  9. Jiansen Lu'S Computing Blog: Datatable Export Excel Wraptext And Newline >>>>> Download Now

    >>>>> Download Full

    Jiansen Lu'S Computing Blog: Datatable Export Excel Wraptext And Newline >>>>> Download LINK

    >>>>> Download Now

    Jiansen Lu'S Computing Blog: Datatable Export Excel Wraptext And Newline >>>>> Download Full

    >>>>> Download LINK pn

    ReplyDelete