Datatable set title from response data list specific column value in excel export

Question

I am using jquery Datatable and I am geting my data list properly. I am returning a list data. Now I want to set excel title from my returning list. I have a column that's returning the name of my client and now I want to print my 1st row 2nd column value Imean client name like from list (data[0].Name) when export the excel. But I can't set the 1st row 2nd column value to my excel title. Can anyone help me to do this?

var clientN;
$(document).ready(function () {
    //datatable configuration
    debugger;
    var test = [];
    
    var citDatatable2 = $("#citCollectionTable2").DataTable({
        //"dom": 'Blfrtip',
        "dom": '<"row"fr<"ml-auto"B>> <"row" <"ml-auto mt-2"l>>' +
            't' +
            `<i><p>`,
        "oLengthChange": true,
        "oLanguage": {
            "sSearch": "Search",
            "sProcessing": "Fetching Data. Please Wait..."
        },
        "ajax": {
            "url": "GetCollections",
            "type": "POST",
            "datatype": "json",
            "data": function (data) {
                var startDate = $("#FromDate").val();
                var endDate = $("#ToDate").val();               
            }, 
           
            dataFilter: function (response) {
                test = response;
                JSON.stringify(test);
                clientN = response.data[0].Name;
                return response
            },
        },
        "columnDefs": [
            {
                "targets": -1,
                "data": null,
                "defaultContent": "<button>Click!</button>"
            }
        ],
        "columns": [
            {
                "data": "cId",
                "name": "CId",
            },
            {
                data: "name", name: "Name",
                
            },          
        ],

        "serverSide": "true",
        "processing": "true",
        "language": {
            "processing": "Please wait..."
        },
        "buttons": ["excel", {

            extend: 'excelHtml5',
            text: 'Sauver en tant que fichier Excel',
            title: "Client Name" +  clientN // **`I want to print the client name here`**
        }, "csv"],
        "initComplete": function () {
            $('.buttons-excel').html('<i class="fas fa-file-excel"></i> Export to Excel');
            $('.buttons-excel').removeClass("dt-button").addClass("btn btn-primary");
            $('.buttons-csv').html('<i class="fas fa-file-excel"></i> Export to CSV');
            $('.buttons-csv').removeClass("dt-button").addClass("btn btn-primary");
        },      
    })

 });

score 3 · Accepted Answer · answered Aug 21 '20 at 21:40

3

You can use a function to get the contents of the cell at the 1st row & 2nd column - and pass the result of that function to the title property.

title: function() {
   var selectedCell = $('#citCollectionTable2').dataTable().api().cell(0, 1).data();
   return "Client Name: " + selectedCell;
}

Rows and columns are zero-indexed, so we use cell(0, 1) in this case.

This will result in your Excel file having this data displayed in row 1 of the worksheet, in a merged cell.

One important point here: The row indexes are based on the order in which data is first loaded into DataTables - and not necessarily the order in which data is displayed in the table (after sorting and filtering).

answered Aug 21 '20 at 21:40

andrewJames

19,570
8
19
51

Thank you very much. If I want to add multiple title using new line it's not working. it's print the same line. How can I set new line? `title: function() { var selectedCell = $('#citCollectionTable2').dataTable().api().cell(0, 1).data(); var selectedId = $('#citCollectionTable2').dataTable().api().cell(0, 3).data(); return "Client Name: " + selectedCell + '/n' + selectedId }` – Antora jahan Aug 21 '20 at 22:06
You can use "\r\n" (note the use of backspaces!) as the string to represent a new line (carriage return + line feed) - but when I tried inserting that into my title text, it was added to Excel, but it was not "activated" until I manually clicked on the Excel cell. And then I also had to manually adjust the row height to be able to see that second line. Unsatisfactory. So, no - I don't know any good, simple way to do this, unfortunately. – andrewJames Aug 21 '20 at 22:33
A more complicated way to do this is to not use the `title` option - and instead, to insert new rows into the top of the Excel sheet (shifting all the existing data downwards), and then write your customized title data to those rows. An example can be seen in [this answer](https://stackoverflow.com/questions/61664874/how-to-add-additional-data-from-an-array-to-export-to-excel-in-jquery-data-table/61704469#61704469). This involves manipulating the underlying XML used by the spreadsheet. – andrewJames Aug 21 '20 at 22:36

Datatable set title from response data list specific column value in excel export

1 Answers1