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;
  }