Answers

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

  February 2022 Answer ✓

  Lots of gurus here, you can't walk anywhere without tripping over them

  This example from this thread should get you going. It's specifically converting that column to use strings,

  Colin
• NoBullMan Posts: 61Questions: 17Answers: 2

  February 2022

  Thank you Colin. Grateful for all you gurus. Problem solved.

  Are you aware of any documentation that explain excel formatting within the "customize" code; things such as $('row c[r^=A], sheet).attr('s', 50))?
  How to access different cells or rows or columns; how to specify the attributes and what attributes 2, 32, 50, ... mean? I am assuming above means "all Axx cells".
• rf1234 Posts: 2,991Questions: 87Answers: 421

  February 2022 Answer ✓

  Are you aware of any documentation that explain excel formatting within the "customize" code; things such as $('row c[r^=A], sheet).attr('s', 50))?

  https://datatables.net/reference/button/excelHtml5
  Search for "Built in styles".

  This is about manipulating the Excel XML using jQuery. Enjoy

  If you search the forum you'll find posts on this from me and other people. Can become very cumbersome.

  Like in here:

  {extend: "excel",
      title:    function () { return lang === 'de' ? 'Vertragsübersicht' : 'Contract Overview' },
      filename: function () { return lang === 'de' ? 'Vertragsübersicht' : 'Contract Overview' },
      messageBottom: function() {
          return tableFooter + ctrTable.rows( {search: "applied"} ).count();
      },
      customize: function( xlsx ) {
          var sSh = xlsx.xl['styles.xml'];
          var lastXfIndex = $('cellXfs xf', sSh).length - 1;            
          var lastFontIndex = $('fonts font', sSh).length - 1; 
          var f1 = //bold and underlined font
          '<font>'+
                  '<sz val="11" />'+
                  '<name val="Calibri" />'+
                  '<b />'+'<u />'+
          '</font>'
  
          var i; var y;
  //n1, n2 ... are number formats; s1, s2, ... are styles
          var s1 = '<xf numFmtId="0" fontId="'+(lastFontIndex+1)+'" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                      '<alignment horizontal="center"/></xf>';         
  //s3 is a combination of built in fonts 64 (2 dec places which has numFmtId="4") AND 2 (bold)
  //just copied the xf of "two decimal places" and and changed the fontId based on "bold"  
          var s2 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                      '<alignment horizontal="center" wrapText="1"/></xf>';
          sSh.childNodes[0].childNodes[1].innerHTML += f1; //new font
          sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2; //new styles
  
          var greyBoldCentered = lastXfIndex + 1;
          var greyBoldWrapText = lastXfIndex + 2;
  
          var sheet = xlsx.xl.worksheets['sheet1.xml'];
      //create array of all columns (0 - N)
          var cols = $('col', sheet);
  
  //two decimal places columns                      
          for ( i=0; i < xlsTwoDecPlacesCols.length; i++ ) {
              $('row c[r^='+xlsTwoDecPlacesCols[i]+']', sheet).attr( 's', '64' );
          }
  //aktenzeichen und vertragsnummer as right aligned text
          for ( i=0; i < xlsRightAlignedCols.length; i++ ) {
              $('row c[r^='+xlsRightAlignedCols[i]+']', sheet).attr( 's', '52' );
          }
  
          $('row:eq(0) c', sheet).attr( 's', greyBoldCentered );  //grey background bold and centered, as added above
          $('row:eq(1) c', sheet).attr( 's', greyBoldWrapText );  //grey background bold, text wrapped
          $('row:last c', sheet).attr( 's', '2' );  //bold
      },
      exportOptions: {
          columns: ':not(.noExport)',
          modifier: { selected: null }, //make sure all records show up
          format: {
              body: function ( data, row, column, node ) {
                  if (typeof data !== 'undefined') {
                      if (data != null) {
                          if ($.inArray(column, xlsTwoDecPlacesColsNumbers ) >= 0) {
                              if (lang == 'de') { //this time we use the English formatting
                                  //data contain only one comma we need to split there
                                  var arr = data.split(',');
                                  //subsequently replace all the periods with spaces
                                  arr[0] = arr[0].toString().replace( /[\.]/g, "" );
                                  //join the pieces together with a period if not empty
                                  if (arr[0] > ''  || arr[1] > '') {
                                      data = arr[0] + '.' + arr[1];
                                  } else {
                                      return '';
                                  }
                              } else {
                                  data = data.toString().replace( /[\,]/g, "" );
                              }
                              //result a number still as a string with decimal . and
                              //no thousand separators
                              //replace everything except numbers, decimal point and minus
                              return data.toString().replace( /[^\d.-]/g, "" );     
                          }
                      }
                  }
                  //replace ampersands and other special chars
                  data = data.replace(/&gt;/g, '>')
                             .replace(/&lt;/g, '<')
                             .replace(/&amp;/g, '&')
                             .replace(/&quot;/g, '"')
                             .replace(/&#163;/g, '£')
                             .replace(/&#39;/g, '\'')
                             .replace(/&#10;/g, '\n');
                  //replace html tags with one space
                  data = data.replace(/<[^>]*>/g, ' ');
                  //replace multiple spaces and tabs etc with one space
                  return data.replace(/\s\s+/g, ' ');
              },
              header: function ( data, column ) {
                  //replace html tags with one space
                  return data.replace(/<[^>]*>/g, ' ');
              }
          }
      }
  },
  
• NoBullMan Posts: 61Questions: 17Answers: 2

  February 2022

  Thank you all for your help. Much appreciated.