Answers

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

  February 2019

  Here is an example of a complex custom button that has its own export options plus many other things:

  //custom button for cashflow excel generation
  $.fn.dataTable.ext.buttons.excelCashFlow = {
      extend: 'excel', filename: 'cashFlow', sheetName: 'cashflow1', title: cashFlowTitle,
      customize: function( xlsx ) {
  // see built in styles here: https://datatables.net/reference/button/excelHtml5
  // take a look at "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 i; var y;
  //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"/>';
          var s2 = '<xf numFmtId="0" fontId="2" 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;
          sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3 + s4;
  
          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);
      //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) ) );                
          }
  
  //two decimal places columns          
          var twoDecPlacesCols = ['D', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P'];            
          for ( i=0; i < twoDecPlacesCols.length; i++ ) {
              $('row c[r^='+twoDecPlacesCols[i]+']', sheet).attr( 's', '64' );
          }
          $('row c[r^="E"]', sheet).attr( 's', fourDecPlaces );  //% 4 decimal places, as added above
  //                $('row c', sheet).attr( 's', '25' ); //for all rows
          $('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 c', sheet);  //create array of all rows (0 - N)      
          var copyPaste; var dateRefRate; var newDate;
          var emptyCellCols = ['B', 'C', 'D', 'E', 'F'];
          var twoDecPlacesBoldCols = ['G', 'H', 'I', 'J', 'L', 'M', 'O', 'P'];
          var boldCols = ['A'];
          for ( i=2; i < rows.length; i++ ) {                  
              copyPaste = $('row:eq('+i+') c[r^="B"] t', sheet).text();
              if ( copyPaste == 'Summen per Laufzeitende' ||
                   copyPaste == 'Totals'                     ) {
                  $('row:eq('+i+') c[r^="A"] t', sheet).text(copyPaste);                    
                  for ( y=0; y < emptyCellCols.length; y++ ) {
                      $('row:eq('+i+') c[r^='+emptyCellCols[y]+']', 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
                  }
              }           
          }            
      },
      exportOptions: {
  //            columns: ':visible',
          //passing "undefined" also works but only with quotes
          modifier: { selected: null }, //make sure all records show up
          format: {
              body: function ( data, row, column, node ) {
                  if (typeof data !== 'undefined') {
                      if (data != null) {  
                          if ( column > 15 ) {
                              return '';  //get rid of the changed manually column
                          }
                          if (column === 3 || column === 4 || column > 5) {
                              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
                              data = data.toString().replace( /[^\d.-]/g, "" ); 
                              //percent must be adjusted to fraction to work ok
                              if (column == 4) {
                                  if (data !== '') {
                                      data = data / 100;
                                  }
                              }
                              return data;                                
                          }
                      }
                  }
                  return data;
              },
              header: function ( data, column ) {
                  if (typeof data !== 'undefined') {
                      if (data != null) {
                          if ( column > 15 ) {
                              return '';  //get rid of the changed manually column
                          }
                      }
                  }
                  return data;
              }
          }
      }
  };