Replies

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

  January 16 edited January 16

  The underlying buttons for the split button (deleted quite a bit of code, but kept the most relevant parts. and yes, I know this could be coded in a more concise way ... but I don't really care if it works ):

  /custom button for cashflow excel generation
      $.fn.dataTable.ext.buttons.excelCashFlow = {
          extend: 'excel',
          title:    function () { return cfTitle; },
          filename: function () { return cfFilename; },
          messageBottom: function() {
              return tableFooter + cashFlowTable.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; 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] ) );  
              }
              $('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: function(ix, data, node) {
                  return xlsExportColumns(cashFlowTable, ix, "full");
              },
              //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') {
                          return;
                      }
                      if (data == null) {
                          return data;
                      }
                      if ( exportColumnsTwoDecPlaces[column] || exportColumnsFourDecPlaces[column] ) {
                          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 ( exportColumnsFourDecPlaces[column] ) {
                              if (data !== '') {
                                  data = data / 100;
                              }
                          }   
                      }
                      return data;                
                 }
              }
          }
      };
      
      $.fn.dataTable.ext.buttons.excelCashFlowVisible = 
          $.extend( true, {}, $.fn.dataTable.ext.buttons.excelCashFlow, {
              text: lang === 'de' ? 'Nur ausgewählte Spalten exportieren' : 'Export selected columns only',
              exportOptions: {
                  columns: function(ix, data, node) {  
                      return xlsExportColumns(cashFlowTable, ix, "selected");
                  }
              }
          });
  
      $.fn.dataTable.ext.buttons.excelCashFlowNoChildRows = 
          $.extend( true, {}, $.fn.dataTable.ext.buttons.excelCashFlow, {
              text: lang === 'de' ? 'Nur Hauptansicht exportieren' : 'Export main view only',
              exportOptions: {
                  columns: function(ix, data, node) {   
                      return xlsExportColumns(cashFlowTable, ix, "main");
                  }
              }
          });
  
• rf1234 Posts: 2,991Questions: 87Answers: 421

  January 16 edited January 16

  I do the conversion from numbers to Excel letters in a very simple way using the array below avoiding any kind of calculation. I am not very good at math ...

  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' ];
  

  The "exportOptions" method is executed prior to the "customize" method. Depending on the button one of these lines is being executed:

  return xlsExportColumns(cashFlowTable, ix, "full");
  return xlsExportColumns(cashFlowTable, ix, "selected");
  return xlsExportColumns(cashFlowTable, ix, "main");
  

  This function creates arrays (globals) that are used in "exportOptions" and "customize" as well. The challenge is to apply the right formatting options even for partly or reordered views. Hence it is important to know the original position of a column. A transposition needs to be done. Another challenge is that "exportOptions" is called for each table column, while "customize" is only called once. Confusing.

  So this is the function:

  function xlsExportColumns(table, ix, scope) {
  // exportColumns = []; //column index of the exported columns only: always true!
  // exportColumnsTwoDecPlaces = []; //column index of the exported columns only: true if two Dec Places or false
  // exportColumnsFourDecPlaces = []; //column index of the exported columns only: true if four Dec Places or false
  // exportColumnsRightAligned = [];  //column index of the exported columns only: true if right aligned or false
      var val;
      if ( ix == 0 ) {
          exportColumns = [];
          exportColumnsTwoDecPlaces = [];
          exportColumnsFourDecPlaces = [];
          exportColumnsRightAligned = [];
          exportColumnsLengthSpecified = []; 
          exportColumnsLengthIfSpecified = []; //column length or 0, if not specified (i.e. above value is false)
      }
      
      if ( $(table.column(ix).header()).hasClass("never") ) {
          val = false;
      } else if ( scope == "full" ) {
          if ( $(table.column(ix).header()).hasClass("noExport")      || 
               $(table.column(ix).header()).hasClass("noExportFull")      ) {
              val = false;
          } else {
              val = true;
          }
      } else if ( scope == "selected" ) {    
          if ( $(table.column(ix).header()).hasClass("noExport") ) {
              val = false;
          } else if  ( ! table.column(ix).visible() ) { //these are the "selected" columns
              val = false;
          } else {
              val = true;
          }
      } else if ( scope == "main" ) {
          if ( $(table.column(ix).header()).hasClass("noExport") ) {
              val = false;
          } else if  ( ! columnsVisibleMain[ix] ) { //only the columns of the main view
              val = false;
          } else {
              val = true;
          }
      }
      //if vat is turned off those cols don't get exported in any case!
      if ( val ) {
          if ( $(table.column(ix).header()).hasClass("VAT_hidden") ) {
              val = false;
          }
      }
      //only exportable columns and whether they have tow or four decimal places
      //or are right aligned or have the length specified
      if ( val ) {
          //use the original column indexes that were saved on "preInit"
          var t = "toOriginal";
          exportColumns.push( true );
          exportColumnsTwoDecPlaces.push( xlsTwoDecPlacesColsNumbers[table.colReorder.transpose(ix, t)] );
          exportColumnsFourDecPlaces.push( xlsFourDecPlacesColsNumbers[table.colReorder.transpose(ix, t)] );
          exportColumnsRightAligned.push( xlsRightAlignedColsNumbers[table.colReorder.transpose(ix, t)] );
          exportColumnsLengthSpecified.push( xlsLengthSpecifiedColsNumbers[table.colReorder.transpose(ix, t)] );
          exportColumnsLengthIfSpecified.push( xlsLengthIfSpecifiedColsNumbers[table.colReorder.transpose(ix, t)] );
      }
      return val;
  }
  

  This is what the split button looks like:
  

  Hope that helps a little in case you have similar challenges! In case you export everything as a string this is not required of course! But my users want to be able to use the Excel for immediate calculation and don't want to reformat everything manually. I wish my bank was able to do that too!

  Roland
• allan Posts: 63,512Questions: 1Answers: 10,472 Site admin

  January 16

  How good is that! Amazing - thank you for sharing this with us.

  The Excel export, and export in general, is something I'm planning on working on intensively, probably in the second part of the year. It is way to hard to customise it at the moment.

  I'm sure others will find this useful!

  Allan