Answers

• kthorngren Posts: 21,470Questions: 26Answers: 4,977

  June 2017

  Are you using the latest version of Buttons (1.3.1)?

  I seem to remember reading a recent thread that suggested this issue is resolved in the latest version. I may be wrong though.

  Kevin
• allan Posts: 63,696Questions: 1Answers: 10,500 Site admin

  June 2017

  Yes, a plain 0 should work okay with the latest version. There is still an issue with 0% which I'm going to look at today.

  Allan
• gnsrihari Posts: 2Questions: 1Answers: 0

  June 2017

  I tried adding the latest version., but still issue exists. Please check the attached image. Where if the loan number starts with 0 it displayed as text and left aligned, if it is started with a number it is rendered as numeric and right aligned. I want all to be rendered as text.

  error.JPG 15K
• allan Posts: 63,696Questions: 1Answers: 10,500 Site admin

  June 2017

  Happy to take a look at a test case showing the issue.

  Allan
• kthorngren Posts: 21,470Questions: 26Answers: 4,977

  June 2017 Answer ✓

  Sorry, I misread your question. Most people want number columns to be numbers not text and ask why 0 is text. You can use one of the built in excelHtml5 styles to format the cells. In this case you probably will use style 50 (Left aligned text). You can use this example to learn how:
  https://datatables.net/extensions/buttons/examples/html5/excelTextBold.html

  Just change this line to match the column and style you want:
  $('row c[r^="C"]', sheet).attr( 's', '2' );

  This changes the whole column including headers and footers. Also this sets the cell to General not Text. May or may not be what you want.

  I'm not aware of a built in style that will set the cells to Text. You can add styles to the built in styles. It takes a bit of work (extracting and reviewing the Excel XML for the styles and coding the appending of the style to the built in styles). There are a couple recent threads covering this. Please let us know if you need help finding them.

  Kevin
• allan Posts: 63,696Questions: 1Answers: 10,500 Site admin

  June 2017

  Sorry - I misunderstood as well. Thanks for the correction and fine answer Kevin.

  Allan
• ooklam Posts: 3Questions: 0Answers: 0

  September 2017 edited September 2017

  How can I apply those changes when having build the DataTables like this:

  var table = $('#ClassificationTable').DataTable();
  table.page.len(25).draw();
  new $.fn.dataTable.Buttons( table, {
      buttons: [
          'copy', 
          'excel', 
          'csv'
      ]
  } );
  table.buttons( 0, 0 ).containers().appendTo( '.inner' );
  

  I had to extent the table with buttons like this, because the normal way $('#example').DataTable( { ... }) does not work with my search.
• allan Posts: 63,696Questions: 1Answers: 10,500 Site admin

  September 2017

  Use the customize method which is documented in the excel reference documentation.

  Allan
• ooklam Posts: 3Questions: 0Answers: 0

  September 2017

  Hey Allan, I dont know how. This is how I would call that method table.buttons([0]).customize(); but I dont know how to specify it.

  Doing something like this does not work:

  new $.fn.dataTable.Buttons( table, {
      buttons: [
          { extend: 'copy', className: 'copyButton' },
          { extend: 'excel', className: 'excelButton' }, 
          { extend: 'csv', className: 'csvButton' },
          customize: function( xlsx ) {
                  var sheet = xlsx.xl.worksheets['sheet1.xml'];
                  $('row:first c', sheet).attr( 's', '42' );
              }
      ]
  } );
  

  SyntaxError: missing ] after element list
• allan Posts: 63,696Questions: 1Answers: 10,500 Site admin

  September 2017

  This example shows how you are use the customize callback.

  Allan
• ooklam Posts: 3Questions: 0Answers: 0

  September 2017 edited September 2017

  Thanks alot, that works. However I got a last question. How I can apply the style to the whole sheet? With this I only get the C columns, I dont want to apply it not column by column but for the whole sheet

  $('row c[r^="C"]', sheet).each( function () {
                      $(this).attr( 's', '50' );
                  });
  
• allan Posts: 63,696Questions: 1Answers: 10,500 Site admin

  September 2017

  Change the selector:

  $('row c', sheet)
  

  Allan
• tnthieu Posts: 26Questions: 9Answers: 0

  September 2019 edited September 2019

  Hi Allan,

  I would like to indicate multi columns (F and G and X). Could you please tell me what I should do?

  buttons: [{
                  extend: 'excelHtml5',
                  text: 'Excel',
                  className: 'btn-export-xsl',
                  exportOptions: {
                      columns: [0, 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 31, 32]
                  },
                  init: function (api, node, config) {
                      $(node).removeClass('dt-button')
                  },
                  customize: function (xlsx) {
                      var sheet = xlsx.xl.worksheets['sheet1.xml'];
                      $('c[r^="F"]', sheet).attr('s', '50');
                  }
              }],
  

  Furthermore, in my table, I have 33 columns and 6000 records. When I export it with
  $('row c', sheet).attr('s', '50'); I takes a lot of time 40 seconds and the browser is freezed before the file is download. Is there a problem with my code or it takes a lot of time to format the excel file. Do you have any suggestion for the performance improvement?

  Thank you

  Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.
• colin Posts: 15,240Questions: 1Answers: 2,599

  September 2019

  Hi @tnthieu ,

  You would just need to repeat it for the three columns.

  There was a performance issue with the recent releases - see thread here. You can find the nightly releases here, they should be working better.

  Cheers,

  Colin