Answers

• rf1234 Posts: 2,955Questions: 87Answers: 416

  August 2017

  Here is an example for a custom button that also uses exportOptions to format what is being exported. You can also use exportOptions in the table definition itself ... and hence get rid of your spaces or whatever you would like to do before exporting the data.

  In this example I had to get rid of local number formatting and a couple of columns. I also had to change a percentage into a fraction etc. The customize piece makes sure that everything gets formatted according to the standards of the respective user's Excel installation.

  //custom button for cashflow excel generation
  $.fn.dataTable.ext.buttons.excelCashFlow = {
      extend: 'excel', filename: 'cashFlow', sheetName: 'cashflow1',
      customize: function( xlsx ) {
              var sheet = xlsx.xl.worksheets['sheet1.xml'];
              //numeric columns except for rate get thousand separators
              $('row c[r^="F"]', sheet).attr( 's', '64' );
  //                $('row c[r^="G"]', sheet).attr( 's', '60' );  //% 1 dec. place
  //                $('row c[r^="G"]', sheet).attr( 's', '56' );  //% no dec. place
              $('row c[r^="I"]', sheet).attr( 's', '64' );
              $('row c[r^="J"]', sheet).attr( 's', '64' );
              $('row c[r^="K"]', sheet).attr( 's', '64' );
              $('row c[r^="L"]', sheet).attr( 's', '64' );
              //first row has grey backround
              $('row:first c', sheet).attr( 's', '7' );
          },
      exportOptions: {
          format: {
              body: function ( data, row, column, node ) {
                  // Strip $ from salary column to make it numeric
                  if (typeof data !== 'undefined') {
                      if (data !== null) {  
                          if ( column > 11 ) {
                              return '';  //get rid of the changed manually column
                          }
                          if (column === 5 || column === 6 || column > 7) {
                              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].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.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.replace( /[^\d.-]/g, "" ); 
                              //rate must be adjusted to fraction to be able to calculate
                              if (column == 6) {
                                  if (data !== '') {
                                      data = data / 100;
  //                                        data = data.toString();
  //                                        data = data + '%';
                                  }
                              }
                              return data;                                
                          }
                      }
                  }
                  return data;
              },
              header: function ( data, column ) {
                  if (typeof data !== 'undefined') {
                      if (data !== null) {
                          if ( column > 11 ) {
                              return '';  //get rid of the changed manually column
                          }
                      }
                  }
                  return data;
              }
          }
      }
  };
  

  https://datatables.net/extensions/buttons/examples/print/columns
• allan Posts: 63,280Questions: 1Answers: 10,425 Site admin

  August 2017

  how to remove spaces between text in columns

  What spaces? Can you link to a test case showing the issue if @rf1234's reply doesn't help to resolve it.

  Allan