Replies

• Apezdr Posts: 43Questions: 4Answers: 5

  May 2019

  https://datatables.net/reference/button/excelHtml5

  This should help you get started but the pertinent bits...

  https://docs.microsoft.com/en-us/previous-versions/office/developer/office-xp/aa140066(v=office.10)#ssnumberformat-tag
  This should give you an idea of what you need to do with the next piece of information I came across for you.

  $('#myTable').DataTable( {
      buttons: [
          {
              extend: 'excelHtml5',
              text: 'Save as Excel',
              customize: function( xlsx ) {
                  var sheet = xlsx.xl.worksheets['sheet1.xml'];
                  var lastCol = sheet.getElementsByTagName('col').length - 1;
                  var colRange = createCellPos( lastCol ) + '1';
                  //Has to be done this way to avoid creation of unwanted namespace atributes.
                  var afSerializer = new XMLSerializer();
                  var xmlString = afSerializer.serializeToString(sheet);
                  var parser = new DOMParser();
                  var xmlDoc = parser.parseFromString(xmlString,'text/xml');
                  var xlsxFilter = xmlDoc.createElementNS('http://schemas.openxmlformats.org/spreadsheetml/2006/main','autoFilter');
                  var filterAttr = xmlDoc.createAttribute('ref');
                  filterAttr.value = 'A1:' + colRange;
                  xlsxFilter.setAttributeNode(filterAttr);
                  sheet.getElementsByTagName('worksheet')[0].appendChild(xlsxFilter);
              }
          }
      ]
  } );
  
  function createCellPos( n ){
      var ordA = 'A'.charCodeAt(0);
      var ordZ = 'Z'.charCodeAt(0);
      var len = ordZ - ordA + 1;
      var s = "";
  
      while( n >= 0 ) {
          s = String.fromCharCode(n % len + ordA) + s;
          n = Math.floor(n / len) - 1;
      }
  
      return s;
  }
  

  Took the above code from a post on https://datatables.net/reference/button/excelHtml5 by F12Magic.

  Based on what I was able to find you will need to dig into the file itself and change the code above to accommodate your field type requirements.

  Good luck,
  Adam
• colin Posts: 15,240Questions: 1Answers: 2,599

  May 2019

  Hi @ibrahimayhan ,

  If Adam's suggestion doesn't work, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

  Cheers,

  Colin
• ibrahimayhan Posts: 13Questions: 0Answers: 0

  September 2019

  @Apezdr Thank you for your support,
  In my worksheet, the Currency on L Column is Dry
  how do I edit the code you specify
  Can you describe me by L column?
• ibrahimayhan Posts: 13Questions: 0Answers: 0

  September 2019

  thank you for informing,

  {
  extend: 'excel',
  text: 'Testing Excel',
  customize: function (xlsx, row) {
  var sheet = xlsx.xl.worksheets['sheet1.xml'];
  $('row c[r^="L"]', sheet).attr('s', 57);
  }
  }
  Which format should I use as 5,3316 in Turkish lira format?