Answers

• rf1234 Posts: 2,990Questions: 87Answers: 421

  September 5 edited September 5

  This can be very, very cumbersome. The problem is that you cannot apply multiple styles to a cell at a time. If you have styling like bold, grey background, centered just for one cell you need to define a new style and apply that.

  Here is something from my own coding that may or may not help you. Sorry, but I can't help you with more ...

  In this code I
  - define a bold and underlined font
  - define a new number format (percent with 4 decimal places)
  - define 4 new styles (four decimal places; grey background, bold and centered; two decimal places and bold; grey background, wrapped text)

  Most of the other stuff you may probably ignore, I guess. I made my own little column transformation variable to convert column numbers into Excel column letters, too.

  var xlsLetters = 
      [ 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M',
        'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 
        'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 
        'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV',
        'AW', 'AX', 'AY', 'AZ', 
        'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 
        'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV',
        'BW', 'BX', 'BY', 'BZ' ];
  
  customize: function( xlsx ) {
  // see built in styles here: https://datatables.net/reference/button/excelHtml5
  // take a look at https://cdn.datatables.net/buttons/2.2.2/js/buttons.html5.js , search for "xl/styles.xml"
  //styleSheet.childNodes[0].childNodes[0] ==> number formats  <numFmts count="6"> </numFmts>
  //styleSheet.childNodes[0].childNodes[1] ==> fonts           <fonts count="5" x14ac:knownFonts="1"> </fonts>
  //styleSheet.childNodes[0].childNodes[2] ==> fills           <fills count="6"> </fills>
  //styleSheet.childNodes[0].childNodes[3] ==> borders         <borders count="2"> </borders>
  //styleSheet.childNodes[0].childNodes[4] ==> cell style xfs  <cellStyleXfs count="1"> </cellStyleXfs>
  //styleSheet.childNodes[0].childNodes[5] ==> cell xfs        <cellXfs count="67"> </cellXfs>
  //on the last line we have the 67 currently built in styles (0 - 66), see link above
  
      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; var z;
  //n1, n2 ... are number formats; s1, s2, ... are styles
      var n1 = '<numFmt formatCode="##0.0000%" numFmtId="300"/>';
      var s1 = '<xf numFmtId="300" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>';
      //define the style with the new font (number passed in as a variable)
      var s2 = '<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 s3 = '<xf numFmtId="4" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>'
      var s4 = '<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[0].innerHTML += n1;  //new number format
      sSh.childNodes[0].childNodes[1].innerHTML += f1; //new font
      sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3 + s4; //new styles
  
      var fourDecPlaces    = lastXfIndex + 1;
      var greyBoldCentered = lastXfIndex + 2;
      var twoDecPlacesBold = lastXfIndex + 3;
      var greyBoldWrapText = lastXfIndex + 4;
  
      var sheet = xlsx.xl.worksheets['sheet1.xml'];
  //create array of all columns (0 - N)
      var cols = $('col', sheet);
  
      //convert the numbers of the exported columns to Excel column letters
      var xlsTwoDecPlacesCols = [];
      var xlsFourDecPlacesCols = [];
      var xlsLengthSpecifiedCols = [];
      var xlsLengthIfSpecifiedCols = [];
      var xlsExportColumnsCols = []; //all Export columns - we want them with letters
  
      for ( i=0; i < exportColumns.length; i++ ) {
          if ( exportColumnsTwoDecPlaces[i] ) {
              xlsTwoDecPlacesCols.push( xlsLetters[ i ] );
          }
          if ( exportColumnsFourDecPlaces[i] ) {
              xlsFourDecPlacesCols.push( xlsLetters[ i ] );
          }
          //no letters we stay numeric here
          if ( exportColumnsLengthSpecified[i] ) {
              xlsLengthSpecifiedCols.push( i );
          }
          //we take the content which is the Excel column length
          if ( exportColumnsLengthIfSpecified[i] > 0 ) {
              xlsLengthIfSpecifiedCols.push( exportColumnsLengthIfSpecified[i] );
          }
          xlsExportColumnsCols.push( xlsLetters[ i ] );
      };
  
      //two decimal places columns                      
      for ( i=0; i < xlsTwoDecPlacesCols.length; i++ ) {
          $('row c[r^='+xlsTwoDecPlacesCols[i]+']', sheet).attr( 's', '64' );
      }
      //four decimal places columns                      
      for ( i=0; i < xlsFourDecPlacesCols.length; i++ ) {
          $('row c[r^='+xlsFourDecPlacesCols[i]+']', sheet).attr( 's', fourDecPlaces );
      }
      //set lenght of some columns: col number: length (excl. first column)
      for ( i=0; i < xlsLengthSpecifiedCols.length; i++ ) {
          $( cols [ xlsLengthSpecifiedCols[i] ] ).attr('width', parseInt( xlsLengthIfSpecifiedCols[i] ) );  
      }
  
  //set lenght of some columns: col number: length (excl. first column)
  //            var colsLength = ['01:12', '02:12', '03:16', '04:10', '05:12', '06:16',
  //                              '07:16', '08:16', '09:16', '10:16', '11:16', '12:16',
  //                              '13:16', '14:16', '15:16'];
  //            for ( i=0; i < colsLength.length; i++ ) {
  //                $( cols [ parseInt( colsLength[i].substr(0,2) ) ] )
  //                        .attr('width', parseInt( colsLength[i].substr(3) ) );                
  //            }
  
      $('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
  
  //move text from column B to column A and empty columns B through F of rows with totals
      var rows = $('row', sheet);  //create array of all rows (0 - N)      
      var copyPaste; var dateRefRate; var newDate;
      var emptyCellCols = [];
      var twoDecPlacesBoldCols = xlsTwoDecPlacesCols;
      var boldCols = ['A'];
      for ( i=2; i < rows.length; i++ ) {  
          for (z=0; z < xlsExportColumnsCols.length; z++) {
              copyPaste = $('row:eq('+i+') c[r^='+xlsExportColumnsCols[z]+'] t', sheet).text();
  //                    copyPaste = $('row:eq('+i+') c[r^="B"] t', sheet).text();            
              //this will not be formatted if column "payment date" is not exported and is not in position B; this is ok!
              if ( copyPaste == 'Summen per Laufzeitende' ||
                   copyPaste == 'Totals'                     ) {
                  emptyCellCols.push(xlsExportColumnsCols[z]);
                  for ( y=0; y < emptyCellCols.length; y++ ) {
                      $('row:eq('+i+') c[r^='+emptyCellCols[y]+'] t', sheet).text('');
                  }
                  for ( y=0; y < twoDecPlacesBoldCols.length; y++ ) {
                      $('row:eq('+i+') c[r^='+twoDecPlacesBoldCols[y]+']', sheet).attr( 's', twoDecPlacesBold );
                  }
                  for ( y=0; y < boldCols.length; y++ ) {
                      $('row:eq('+i+') c[r^='+boldCols[y]+']', sheet).attr( 's', '2' );  //bold
                  }
                  $('row:eq('+i+') c[r^="A"] t', sheet).text(copyPaste); 
                  break;
              }
          }
      }            
  },