Answers

• Khalid Teli Posts: 251Questions: 71Answers: 0

  January 2021

  Hi @kthorngren

  Please ignore my previous comments. I managed to work it. As, I was using object data so I had to change it to Array data. Also I was using a break in html <br> which caused the error.

  There are two issues I am facing:

  1) The table header length is 20 however the actual length of row data from the table , length is 22. How can I control which data to show?

  console.log(header) = 20[...,..,...,..,]
  console.log(data) = 22[...,..,...,..,]
  The two extra variables are row_id and other column which I am not displaying in the actual table.

  2) live.datatables.net/capuxixa/1/edit

  As shown in the example above,(#example2) when I use the rowGroup, how can I get access to the variable where rowgroup data is saved and then display it inside the table?

  Thank you
  Your help will be highly appreciated
• colin Posts: 15,240Questions: 1Answers: 2,599

  January 2021

  1. I'm not clear what that mean, sorry. Can you create a test case to demonstrate, please.
  2. Again, sorry, I'm not clear what you're asking. Which variable are you referring to? And where and when do you want it displayed in the table?

  Colin
• Khalid Teli Posts: 251Questions: 71Answers: 0

  January 2021

  Hi @colin
  Sorry, If my question was not clear

  live.datatables.net/vijabavi/1/edit

  1)I am working on local host so I cant produce an example for case (1) , However I will explain in details

  When I implement the above code in attached example (for exporting the Excel in new sheet)
  My table has 20 headers and output of the getHeaderNames function gives the right output. The output of console.log(names) looks like this:

  (20) ["PC", "CP", "PN", "PS", "SD", "ED", "CF", "BF", "TP", "BF", "CF", "DF", "EF", "HF", "HF", "SF", "TRF", "TF", "SSW", "GT"]

        function getHeaderNames(table) {
          var header = $(table).DataTable().columns().header().toArray();
          var names = [];
          header.forEach(function(th) {
           names.push($(th).html());
          });
              console.log(names);
          return names;
        }
  

  Again, using the below code from example , to loop over table data , the output of console.log(data) gives me, which is two columns extra. (first two, they are there in database but not included in table)

  (22) ["row_7243723", 7243723, 31010615, "CM", "CW 70:30", "6 x 2kg", "2020-07-01", "2020-09-30", 545, 150, 0, 0, 90, 0, 0, 0, 0, 0, 480, 0, 200, 1465]

                  // Loop through each row to append to sheet.    
                  table.rows().every( function ( rowIdx, tableLoop, rowLoop ) {
                    var data = this.data();
  
              var data = Object.values(data);
  
              console.log(data);
  

  The client side code to read the data looks like this:

      "columns": [
        { data: "pc" },
        { data: "cp" },
        { data: "pn" },
        { data: "ps" },
        { data: "std" },
        { data: "ed" },
        { data: "cf" },
        { data: "bf" },
         { data: "tp" },
        { data: "Bf" },
        { data: "cf" },
        { data: "Df" },
        { data: "ef" },
        { data: "hf" },
        { data: "hf" },
         { data: "sf" },
        { data: "tr" },
        { data: "tf" },
        { data: "sf" },
        { data: "gt" }
        ]
  

  My Issue is , if I have only 20 columns defined on client side to read data from server side , why does it return 22 columns data?
  Apart from this it works fine

  2) live.datatables.net/vijabavi/1/edit

  I you have a look at this example, when you click on excel button, it exports the table data (#example) on Excel sheet and the data from other table (#example2) on new sheet in the same file.

  However, when I am using RowGroup to group the data by column (office, shown in red).
  How can I include this data and export this with the table data in the newsheet created?

  Thank you
  Kind Regards,
  Khalid Teli
• colin Posts: 15,240Questions: 1Answers: 2,599

  January 2021 edited January 2021

  Thanks for the explanations.

  My Issue is , if I have only 20 columns defined on client side to read data from server side , why does it return 22 columns data?

  That'll be down to your server-side script - that's sending the data to the client. The server can send anything it likes to the client, but DataTables will only use the fields that have been configured in the columns object.

  How can I include this data and export this with the table data in the newsheet created?

  Yep, RowGroup doesn't export the headers/footers automatically. There are a few threads discussing this that may help - see here and here.

  Colin
• Khalid Teli Posts: 251Questions: 71Answers: 0

  January 2021

  @colin
  Thank you the problem 1 is solved.

  2) For issue no. 2, I used the example and includes the row which includes the rowGroup information as hown in the figure:
  

  As you can see in the figure it groups the table by contracted products (cheese) and displays it on exported excel sheet.
  What I am trying to do is sum the age for that group and export it in excel file as well .

  For that I edited the code below:

  https://datatables.net/forums/discussion/comment/172979#Comment_172979

  var grouped_array_index = config.grouped_array_index;       
  // added this new_index to include a column from sum 
  var new_index = config.new_index;
  
      if ( !(grouped_array_index == undefined) ) { //don't run grouping logic if rows aren't grouped
  
          var row_array = dt.rows().nodes();
          var row_data_array = dt.rows().data();
  
          var iColspan = columns;
          var sLastGroup = "";
          var no_of_splices = 0;
  
          var ageArray = []; // this will hold the age column 
  
  
          for (var i = 0, row_length = body.length; i < row_length; i++) {
  
  
              var sGroup = row_data_array[i][grouped_array_index];
  
              var abc = row_data_array[i][new_index]; // this holds the age column data 
  
                        ageArray.push(parseInt(abc)); // pushes the data in array for addition
  
  
                             console.log(sGroup);
                            console.log(ageArray);
  
  // var sum_ages = ageArray.reduce( function (a, b) {
  //                      return parseInt(a) + parseInt(b);}, 0 );
  
              if ( sGroup !== sLastGroup) {
                  var table_data = [];
  
  
  
                  for (var $column_index = 0; $column_index < iColspan; $column_index++) {
  
                      if ($column_index === 0)
                      {
  
                          table_data[$column_index] = sGroup.replace( /<[^>]*>/gi, '' ); + " ";
  
                      }
                      else if ($column_index === 1)
                      {
  
                          table_data[$column_index] = sum_ages;
  
                      }
                      else
                      {
                          table_data[$column_index] = '';
                      }
                  }
                  body.splice(i + no_of_splices, 0, table_data);
                  no_of_splices++;
                  sLastGroup = sGroup;
              }
  
  
  
  
          }
      }
  

  I used the example and it works fine. I tried to include one more columnvar new_index = config.new_index; where new_index is the age column from the table.

  For each rowGroup I want to add the age and display in $column_index===1

  I got the age from the age column and then pushed inside an array, like this:

   var abc = row_data_array[i][new_index]; // this holds the age column data 
  
                        ageArray.push(parseInt(abc));
                            console.log(ageArray);
  

  eventually I want to add it using

       var sum_ages = ageArray.reduce( function (a, b) {
      return parseInt(a) + parseInt(b);}, 0 );
  

  and then display it using
  table_data[$column_index] = sum_ages;

  I am able to get the data add it and return the sum.
  Right now what it is doing, it is taking all the values from the column irrespective of the rowGroup. I need to put in some logic where it will only sum the column values for a particular row group.
  In some way I need to restrict the sum to only rowGroup not the all table data

  Can you please give me any suggestions
  Your help will be highly appreciated.
  Thank you
• colin Posts: 15,240Questions: 1Answers: 2,599

  January 2021

  We do plan on supporting that feature fairly soon, it's on our upcoming projects, and then it'll be officially supported. If you want to update your test case to show this code and what you've got, we or other forum member could possibly take a look and see if there's a quick fix.

  Colin
• Khalid Teli Posts: 251Questions: 71Answers: 0

  January 2021

  @colin
  That is amazing news, cant wait for that to be a feature soon.

  In terms of the current issue, as I said I am editing the export code indatables.js file. I am not sure how can I take that function and use in client side code, so I can provide a test case. I have been trying all day but no luck.

  I just replaced the var _exportDatafunction with the one below :
  I understand it is much easy if I provide a test case. if you can just give me an idea how can I use this function inside client side code , so it will be easy for me to provide a test case.

  var _exportData = function ( dt, inOpts )
  {
      var config = $.extend( true, {}, {
          rows:           null,
          columns:        '',
          modifier:       {
              search: 'applied',
              order:  'applied'
          },
          orthogonal:     'display',
          stripHtml:      true,
          stripNewlines:  true,
          decodeEntities: true,
          trim:           true,
          format:         {
              header: function ( d ) {
                  return strip( d );
              },
              footer: function ( d ) {
                  return strip( d );
              },
              body: function ( d ) {
                  return strip( d );
              }
          },
          customizeData: null
      }, inOpts );
  
      var strip = function ( str ) {
          if ( typeof str !== 'string' ) {
              return str;
          }
  
          // Always remove script tags
          str = str.replace( /<script\b[^<]*(?:(?!<\/script>)<[^<]*)*<\/script>/gi, '' );
  
          // Always remove comments
          str = str.replace( /<!\-\-.*?\-\->/g, '' );
  
          if ( config.stripHtml ) {
              str = str.replace( /<[^>]*>/g, '' );
          }
  
          if ( config.trim ) {
              str = str.replace( /^\s+|\s+$/g, '' );
          }
  
          if ( config.stripNewlines ) {
              str = str.replace( /\n/g, ' ' );
          }
  
          if ( config.decodeEntities ) {
              _exportTextarea.innerHTML = str;
              str = _exportTextarea.value;
          }
  
          return str;
      };
  
  
      var header = dt.columns( config.columns ).indexes().map( function (idx) {
          var el = dt.column( idx ).header();
          return config.format.header( el.innerHTML, idx, el );
      } ).toArray();
  
      var footer = dt.table().footer() ?
          dt.columns( config.columns ).indexes().map( function (idx) {
              var el = dt.column( idx ).footer();
              return config.format.footer( el ? el.innerHTML : '', idx, el );
          } ).toArray() :
          null;
  
      // If Select is available on this table, and any rows are selected, limit the export
      // to the selected rows. If no rows are selected, all rows will be exported. Specify
      // a `selected` modifier to control directly.
      var modifier = $.extend( {}, config.modifier );
      if ( dt.select && typeof dt.select.info === 'function' && modifier.selected === undefined ) {
          if ( dt.rows( config.rows, $.extend( { selected: true }, modifier ) ).any() ) {
              $.extend( modifier, { selected: true } )
          }
      }
  
      var rowIndexes = dt.rows( config.rows, modifier ).indexes().toArray();
      var selectedCells = dt.cells( rowIndexes, config.columns );
      var cells = selectedCells
          .render( config.orthogonal )
          .toArray();
      var cellNodes = selectedCells
          .nodes()
          .toArray();
  
      var columns = header.length;
      var rows = columns > 0 ? cells.length / columns : 0;
  
      var body = [];
      var cellCounter = 0;
  
      for ( var i=0, ien=rows ; i<ien ; i++ ) {
          var row = [ columns ];
  
          for ( var j=0 ; j<columns ; j++ ) {
              row[j] = config.format.body( cells[ cellCounter ], i, j, cellNodes[ cellCounter ] );
              cellCounter++;
          }
  
          body[i] = row;
      }
  
      var grouped_array_index = config.grouped_array_index;
       var new_index = config.new_index;
  
       // var rowGroup_index = dt.settings()[0].rowGroup.dataSrc();
       // console.log(rowGroup_index);
  
     if ( !(grouped_array_index == undefined) ) { //don't run grouping logic if rows aren't grouped
  
          var row_array = dt.rows().nodes();
          var row_data_array = dt.rows().data();
          var iColspan = columns;
          var sLastGroup = "";
          var no_of_splices = 0;
          var endRenderText = '';  // Keep track of dataSrc column value change for endRender
  
          for (var i = 0, row_length = body.length; i < row_length; i++) {
  
  
              var sGroup = row_data_array[i][grouped_array_index];
               var ageArray = [];
  
  
                     for (var i = 0, row_length = body.length; i < row_length; i++) {
  
  
                          var abc = row_data_array[i][new_index];
  
                      ageArray.push(parseInt(abc));
  
  
              var sum_ages = ageArray.reduce( function (a, b) {
                      return a + b;
                   }, 0 );
  
                                  console.log(ageArray);
                                  console.log(sum_ages);
  
          }
  
                            ageArray = []; 
  
  
  
  
              if ( sGroup !== sLastGroup) {
                  var table_data = [];
  
  
                  for (var $column_index = 0; $column_index < iColspan; $column_index++) {
  
                      if ($column_index === 0)
                      {
  
                          table_data[$column_index] = sGroup.replace( /<[^>]*>/gi, '' ); + " ";
  
                      }
                      else if ($column_index === 1)
                      {
  
                          table_data[$column_index] = sum_ages;
  
  
                      }
                      else
                      {
                          table_data[$column_index] = '';
                      }
                  }
                  body.splice(i + no_of_splices, 0, table_data);
                  no_of_splices++;
                  sLastGroup = sGroup;
  
              }
  
  
          }
      }
  
  
      var data = {
          header: header,
          footer: footer,
          body:   body
      };
  
      if ( config.customizeData ) {
          config.customizeData( data );
      }
  
      return data;
  };
  

  Thank you
• colin Posts: 15,240Questions: 1Answers: 2,599

  January 2021

  This example here should help, as it shows how exportData can be over-written - this is for getting server-side data to export, but the principle would be the same for you too,

  Colin
• Khalid Teli Posts: 251Questions: 71Answers: 0

  January 2021

  @collin Thank you very much for your help.
  I guess I have reached to a point where I can provide a test case.

  live.datatables.net/xagasone/1/edit

  1) if you see in the code provided in the link, inside exportoptions I have included the code:
  a) the output of console.log(table_data); is ["Tokyo", "123", "", "", "", ""],which I added to the table body and can be seen at the end of body data which is confirmed by console.log(data.body);But when I export , this row doesn't get exported the excel file.

  b) Once this is sorted, I can then show you the code I wrote for include the average age for a row group to be included for each group , some thing like this : ["Tokyo", "123", "append the average age here for each group ", "", "", ""],

  Thank you
• Khalid Teli Posts: 251Questions: 71Answers: 0

  January 2021 edited January 2021

  @collin
  Please see this link as previous one is not working as expected

  live.datatables.net/kowofuwu/1/

  1) This is the output of rowGroups (group by column 2)
  "Edinburgh"
  "London"
  "New York"
  "San Francisco"
  "Tokyo"

  2) This is body output which includes the above data as rows:

  [["Edinburgh", "123", "", "", "", ""], ["Tiger Nixon", "System Architect", "Edinburgh", "61", "2011/04/25", "$320,800"], ["Cedric Kelly", "Senior Javascript Developer", "Edinburgh", "22", "2012/03/29", "$433,060"], ["Sonya Frost", "Software Engineer", "Edinburgh", "23", "2008/12/13", "$103,600"], ["Quinn Flynn", "Support Lead", "Edinburgh", "22", "2013/03/03", "$342,000"],["London", "123", "", "", "", ""], ["Jena Gaines", "Office Manager", "London", "30", "2008/12/19", "$90,560"], ["Haley Kennedy", "Senior Marketing Designer", "London", "43", "2012/12/18", "$313,500"], ["Tatyana Fitzpatrick", "Regional Director", "London", "19", "2010/03/17", "$385,750"], ["Michael Silva", "Marketing Designer", "London", "66", "2012/11/27", "$198,500"], ["New York", "123", "", "", "", ""], ["Brielle Williamson", "Integration Specialist", "New York", "61", "2012/12/02", "$372,000"], ["Paul Byrd", "Chief Financial Officer (CFO)", "New York", "64", "2010/06/09", "$725,000"], ["Gloria Little", "Systems Administrator", "New York", "59", "2009/04/10", "$237,500"], ["San Francisco", "123", "", "", "", ""], ["Ashton Cox", "Junior Technical Author", "San Francisco", "66", "2009/01/12", "$86,000"], ["Herrod Chandler", "Sales Assistant", "San Francisco", "59", "2012/08/06", "$137,500"], ["Colleen Hurst", "Javascript Developer", "San Francisco", "39", "2009/09/15", "$205,500"], ["Charde Marshall", "Regional Director", "San Francisco", "36", "2008/10/16", "$470,600"], ["Tokyo", "123", "", "", "", ""], ["Garrett Winters", "Accountant", "Tokyo", "63", "2011/07/25", "$170,750"], ["Airi Satou", "Accountant", "Tokyo", "33", "2008/11/28", "$162,700"], ["Rhona Davidson", "Integration Specialist", "Tokyo", "55", "2010/10/14", "$327,900"]],

  These rows in bold doesn't get exported
  Thank you
• colin Posts: 15,240Questions: 1Answers: 2,599

  January 2021

  I'm unlikely to have time to look at that, I'm afraid, but this thread here is worth looking at to compare your code against - they appear to have got it working,

  Colin
• Khalid Teli Posts: 251Questions: 71Answers: 0

  January 2021

  @colin
  Thank you very much. This thread helped me.
  I managed to export the rowGrop information with other calculations specific to the rowgroup and export these calculations with the rowgroup info.

  In case someone needs it, I am attaching the example below.
  This example prints the additional row with the rowgroup information and also, sums the age of employees for that particular group and exports that sum age info too.
  live.datatables.net/raseguji/1/edit

  Thank you
• colin Posts: 15,240Questions: 1Answers: 2,599

  January 2021

  Very nice, I'm glad you were able to get it going, and thanks for sharing that excellent solution,

  Colin