Answers

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

  January 2020

  Well you can do anything but it is complex and you will be more or less on your own. You will need to develop a basic understanding of Open Office XML as well. No fun ...
  https://datatables.net/reference/button/excelHtml5
  Search for "Customisation" on that page.

  Here is an example for an excel export in which I also move cells for certain columns etc. In addition I define my own fonts and apply them etc.

  Would I do it again? Probably not client side, but do it with something like PHP Spreadsheet on the server. PHP Spreadsheet allows you to use your own templates as well.

  //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 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 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);
      //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: function(column, data, node) {
              if (column > 15) {
                  return false;
              }
              return true;
          },
          //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 === 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 ) {
                  return data;
              }
          }
      }
  };
  
• rashad Posts: 3Questions: 2Answers: 0

  January 2020

          $(function() {
              grid = {
                  dt: null,
                  init: function() {
                      window.dt = $('#gridEmployeeFullReport').DataTable({
                          autoFill: true,
                          dom: 'Bfrtip',
                          buttons: [{
                              text: '<img src="@Url.Content("~/Content/Images/icons/object/Printer_32x32.png")" data-toggle="tooltip" data-placement="top" title="@SimilarResources.PrintButton">',
                              filename: '@EmployeeFullReportResources.HeaderEmployeeFullReport',
                              sheetName: 'EmployeeFullReport',
                              orientation: 'landscape',
                              autoFilter: false,
                              charset: 'utf-8',
                              extend: 'excel',
                              titleAttr: null,
                              title: null
                          }],
                          "serverSide": true,
                          "processing": true,
                          "responsive": false,
                          "searching": false,
                          "rowId": "Id",
                          "language": @Html.Raw(DataTableResources.Language),
                          "lengthMenu": [[10, 25, 50, 100], [10, 25, 50, 100]],
                          "ajax": {
                              "url": '@Url.Action("GetEmployeeFullReport")',
                              "type": "POST"
                          },
                          "columns": columns
                      });
                  }
              }
              grid.init();
              $('#gridEmployeeFullReport').wrap("<div class='scrolled-table'></div>");
  
          });
  
• rf1234 Posts: 2,993Questions: 87Answers: 421

  January 2020

  ok, some code. You are not doing any customization of the excel export in that code. So this is not quite in line with "i want transpose rows and columns placement."
  Good luck!