How to add additional data from an array to export to excel in jquery data table

Question

I have jquery datatable having below columns and data.

I am using built-in functionality to export the data to excel and it is working as expected.

 var table = $('#example').DataTable({
                dom: 'Bfrtip',
                buttons: [
                    'copyHtml5',
                    'excelHtml5',
                    'csvHtml5',
                    'pdfHtml5'
                ],
                stateSave: true,
                
                
               
                rowsGroup: [// Always the array (!) of the column-selectors in specified order to which rows grouping is applied
                    // (column-selector could be any of specified in https://datatables.net/reference/type/column-selector)

                    1, 0
                ]
               
            });

Now, I have an array having data similar to grid definition.

 var aData = new Array();
            var array1 = {

                Name: "David",
                Position: "Software Engineer",
                Office: "Tokyo",
                OfficeId: "1000",
                Age: 29,
                "Start date": "2011/04/25",
                Salary: "$320,800"
            };
            aData.push(array1);
            

Requirement is to add the array data to the excel when clicking the excel button. i.e, include data from an exteranal array to grid value while exporting. Is this possible? The excel should be as below.

Any help will be appreciated. Thanks in advance.

Answer 1

I got a resolution for this and I am sharing here.

We can make use of below to add custom data to jquery datatable while exporting data.

  customizeData: function (data) {
  
  }

Here you will get header in " data.header" and cell data will be available in "data.body".

So, we can manipulate entire excel header/cell data here.

I have created custom array for cell data and replced as below in "data.body"

customize: function (xlsx) {


                    },
customizeData: function (data) {
                        
     var excelbodyData = getBodyArray();
    var excelHeader =getHeaderArray();
     data.header = [];
     data.header = excelHeader;
     data.body = [];
      data.body = excelbodyData;
  }

Answer 2

Here is some code which shows how this can be achieved:

Assume the following starting point:

And the following "extra" row of data in a JavaScript array variable:

var extraData = [
  "David",
  "Software Engineer 2",
  "Tokyo",
  25,
  "04/25/2011",
  320800
];

Then when the data is exported to Excel, the table looks like this:

The DataTables code:

<script type="text/javascript">

$(document).ready(function() {

  var extraData = [
    "David",
    "Software Engineer",
    "Tokyo",
    29,
    "04/25/2011",
    320800
  ];

  var table = $('#example').dataTable({
    dom: 'Bfrtip',
    buttons: [
      {
        extend: 'excelHtml5',
        title: '', // no title row
        text: 'Excel',
        customize: function ( xlsx ){
          var sheet = xlsx.xl.worksheets['sheet1.xml'];
          insertRowAfter( 6, sheet );
          populateRow( 7, extraData, sheet );
        }
      }
    ]

  });

  // Insert a new row after the rowNum row. The new row is 
  // cloned from the rowNum row.
  function insertRowAfter(rowNum, sheet) {
    var $src_row = $( 'row[r=' + rowNum + ']', sheet );
    var newRowID = parseInt( $src_row.attr( 'r' ) ) + 1;
    var $new_row = $src_row.clone().attr( 'r', newRowID );

    // fix the cell references in the new row:
    $new_row.children( 'c' ).each(function( index ) {
      var newCellID = $( this ).attr( 'r' ).match(/[A-Z]+/) + newRowID
      $( this ).attr( 'r', newCellID );
    });

    // Increment the row and cell references in all rows below
    // where the new row will be inserted:
    $src_row.nextAll('row').each(function( index ) {
      var nextRowID = parseInt( $( this ).attr( 'r' )) + 1;
      $( this ).attr( 'r', nextRowID );
      $( this ).children( 'c' ).each(function( index ) {
        var nextCellID = $( this ).attr( 'r' ).match(/[A-Z]+/) + nextRowID
        $( this ).attr( 'r', nextCellID );
      });

    });

    // Insert the new row:
    $src_row.after($new_row);
  }

  function populateRow( row, data, sheet ) {
    // Assumes data starts in column A, with no gaps. Assumes 
    // no more than 26 columns (A through Z).
    data.forEach(function (item, index) {
      var col = String.fromCharCode(65 + index); // 65 = ascii 'A'
      if ( typeof item === 'string' ) {
        $('c[r=' + col + row + '] t', sheet).text( item );
      } else if ( typeof item === 'number' ) {
        $('c[r=' + col + row + '] > v', sheet).html( item );
      }
    });
  }

});
</script>

Points to Note:

1) I changed your JS {...} object for the "extra row" data to an array [...]. You can alter the code and change it back if you prefer - but for my demo, I wanted to keep it simple.

2) The data in the "extra row" array has to be formatted in a specific way - by which I mean: For numbers, make sure the data is not in quotation marks. And, for example, the dollar amount is not formatted with any commas or dollar signs. Let Excel do the formatting for you.

3) This code is somewhat basic, and Excel sheets are fairly complex. For the specific scenario in your question, this works. But it's quite possible that the code will need to be adjusted to handle other types of data - or empty/blank values, and so on. Just to warn you.

I would say, do what you can to avoid needing to insert new rows like this. Or use a library which is probably better equipped to handle this.

Edit

Assuming we start with an object {...} instead of an array [...]:

  var dataObject = {
    Name: "David",
    Position: "Software Engineer",
    Office: "Tokyo",
    Age: 29,
    "Start date": "2011/04/25",
    Salary: 320800
  };

There are various ways to handle this.

Here is one way, where we convert the data to an array, before using it:

var extraData = [];
for(var o in dataObject) {
  extraData.push(dataObject[o]);
}

Now we can use extraData in the same way as shown above.