How to get the row header and title of the excel export to be the supplier name from data

minhal

I am trying to get the supplier name (column: PL1) to be as my excel file name as well as it to be shown on the first row of my excel file as the header. Below is my code.

   $(document).ready(function () {
        var mesa = $('.datatable').DataTable({
            filename: "Application",
            responsive: true,
            "bAutoWidth": false, // toggle this depending on how wide you want the table
            "ajax": {
                "url": "/ApplicationImportTgts/GetData",
                "type": "GET",
                "datatype": "json"
            },
            responsive: 'true',
            dom: 'Bfrtip',
            buttons: [
                'copy',
                {
                    extend: 'excelHtml5',
                    text: 'Excel',
                    customize: function (xlsx) {
                        var source = xlsx.xl['workbook.xml'].getElementsByTagName('sheet')[0];
                        source.setAttribute('name', 'Application');

                        var source = xlsx['[Content_Types].xml'].getElementsByTagName('Override')[1];
                        var clone = source.cloneNode(true);
                        clone.setAttribute('PartName', '/xl/worksheets/sheet2.xml');
                        xlsx['[Content_Types].xml'].getElementsByTagName('Types')[0].appendChild(clone);

                        //Add sheet relationship to xl/_rels/workbook.xml.rels => Relationships
                        //=====================================================================
                        var source = xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationship')[0];
                        var clone = source.cloneNode(true);
                        clone.setAttribute('Id', 'rId3');
                        clone.setAttribute('Target', 'worksheets/sheet2.xml');
                        xlsx.xl._rels['workbook.xml.rels'].getElementsByTagName('Relationships')[0].appendChild(clone);

                        //Add second sheet to xl/workbook.xml => <workbook><sheets>
                        //=========================================================
                        var source = xlsx.xl['workbook.xml'].getElementsByTagName('sheet')[0];
                        var clone = source.cloneNode(true);
                        clone.setAttribute('name', 'Cover Sheet');
                        clone.setAttribute('sheetId', '2');
                        clone.setAttribute('r:id', 'rId3');
                        xlsx.xl['workbook.xml'].getElementsByTagName('sheets')[0].appendChild(clone);

                        //Add sheet2.xml to xl/worksheets
                        //===============================
                        var today = new Date();
                        var cMonth = today.getMonth() + 1;
                        var cDay = today.getDate();
                        var dateNow = ((cMonth < 10) ? '0' + cMonth : cMonth) + '-' + ((cDay < 10) ? '0' + cDay : cDay) + '-' + today.getFullYear();
                        var newSheet = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
                            '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" mc:Ignorable="x14ac">' +

                            '</sheetData>' +
                            '<mergeCells count="1">' +
                            '<mergeCell  ref="A1:A11"/>' +
                            '<mergeCell  ref="B1:B11"/>' +
                            '</mergeCells>' +
                         
                            '</worksheet>';
                        xlsx.xl.worksheets['sheet2.xml'] = $.parseXML(newSheet);                
                    }
                }, 'pdf'
            ],
            "columns": [
                { "data": "PARTNO" },
                { "data": "PARTNO" },
                { "data": "PartsName" },
                { "data": "QTY" },
                { "data": "PL1" },
                { "data": "PL2" },
                { "data": "MODEL" },
                { "data": "TYPE" },        
                { "data": "PL3" },
                { "data": "Discrepancies" },
                { "data": "Comments" }
            ],

            initComplete: function () { // After DataTable initialized
                this.api().columns([4]).every(function () {
                    /* use of [2] for third column.  Leave blank - columns() - for all.
                    Multiples? Use columns[0,1]) for first and second, e.g. */
                    var column = this;
                    var select = $('<select><option value=""/></select>')
                        .appendTo($('.datatable .dropdown .fifth').empty()) /* for multiples use .appendTo( $(column.header()).empty() ) or .appendTo( $(column.footer()).empty() ) */
                        .on('change', function () {
                            var val = $.fn.dataTable.util.escapeRegex(
                                $(this).val()
                            );
                          
                            column
                                .search(val ? '^' + val + '$' : '', true, false)
                                .draw();
                        });
                    column.data().unique().sort().each(function (d, j) {
                        select.append('<option value="' + d + '">' + d + '</option>')
                    });
                }); // this.api function
            } //initComplete function

        });
    });
    $(window).resize(function () {
        $('.datatable').removeAttr('style');
    });

minhal

I have removed the columns I am creating for my new excel worksheet because the code was getting too long.