Excel download button will not work

siska77

I have a table that has a few modifications and I want to add an Ecxel download button, but I can't seem to get it to work.

The table has a double header, the first row specifies columnnames, the second row has two searchfields and two dropdowns.
To make that work properly I put data-dt-order="disable" in the tr-tag of the second row.
The table also has a few columns hidden.

I now want to add an Excel download button, but every example I've seen and tried gives me errors, even if it seems simple enough to implement.
I do implement all needed libraries and the furthest I have gotten is showing an Excel button but when I click it I get an error in the console:
Uncaught TypeError: t.header[e] is undefined
This leads me to believe the double header is the issue but I don't know how to fix it.

Can anybody help with a working example or help me with solving the error?

Code below is to generate the datatable without the Excel button, to add the Excel button I've added the appropriate scripts and this code:

layout: {
    topStart: {
        buttons: [
            {
                header: false,
                extend: 'excel'
            }
        ]
    }
},

Included scripts:
dataTables.dataTables.min.css
buttons.dataTables.min.css
jquery-3.7.1.min.js
jszip.min.js
dataTables.min.js
dataTables.buttons.min.js
buttons.html5.min.js

<table id="dttable" class="display">
    <thead>
        <tr>
            <th>Order</th>
            <th>Note</th>
            <th>Checked</th>
            <th>User identification</th>
            <th>Manufacturer</th>
            <th>Model</th>
            <th>Type</th>
            <th>Date registered</th>
            <!-- hidden values -->
            <th>hash_id</th>
            <th>nhsm_id</th>
            <th>user_id</th>
        </tr>
        <tr data-dt-order="disable">
            <th>Order</th> <!-- searchfield -->
            <th></th>
            <th></th>
            <th>User identification</th> <!-- searchfield -->
            <th>Manufacturer</th> <!-- dropdown -->
            <th>Model</th> <!-- dropdown -->
            <th></th>
            <th></th>
            <!-- hidden values -->
            <th></th>
            <th></th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>21182833</td> <!-- Order -->
            <td><a href="somelink.php?id=21182833"><img src="img/note.png"></a></td> <!-- Note -->
            <td><img src="img/checkmark.png"></td> <!-- Checked -->
            <td>LfhEzmtd35</td> <!-- User identification -->
            <td>Trianta Int.</td> <!-- Manufacturer -->
            <td>P238</td> <!-- Model -->
            <td>2</td> <!-- Type -->
            <td>2024-02-09</td> <!-- Date -->
            <!-- hidden values -->
            <td>11241821</td> <!-- hash_id -->
            <td>4006</td> <!-- nhsm_id -->
            <td>23383</td> <!-- user_id -->
        </tr>
        ...
        <tr>
            <td>21191924</td> <!-- Order -->
            <td><a href="somelink.php?id=21191924"><img src="img/note.png"></a></td> <!-- Note -->
            <td><img src="img/cross.png"></td> <!-- Checked -->
            <td>OxBak6P52O</td> <!-- User identification -->
            <td>Diantra Limited</td> <!-- Manufacturer -->
            <td>P288</td> <!-- Model -->
            <td>4</td> <!-- Type -->
            <td>2024-05-04</td> <!-- Date -->
            <!-- hidden values -->
            <td>30433750</td> <!-- hash_id -->
            <td>5038</td> <!-- nhsm_id -->
            <td>69731</td> <!-- user_id -->
        </tr>
    </tbody>
</table>

$(document).ready(function() {
    let tabledata = $('#dttable').DataTable( {
        order: [[7, 'desc']], // 7 = date column
        pageLength: 25,
        columnDefs: [{
            target: [8, 9, 10],
            visible: false,
            searchable: false
        }],
        initComplete: function() {
            this.api()
                .columns([0, 3]) // first & fourth column are searchfields
                .every(function() {
                    let column = this;
                    let title = column.header().textContent;

                    // create input element
                    let input = document.createElement('input');
                    input.placeholder = title;
                    column.header().replaceChildren(input);

                    // event listener for user input
                    input.addEventListener('keyup', () => {
                        if (column.search() !== this.value) {
                            column.search(input.value).draw();
                        }
                    });
                })
                .columns([4, 5]) // fifth & sixth colum are dropdowns
                .every(function() {
                    let column = this;
                    let title = column.header().textContent;

                    // create select element
                    let select = document.createElement('select');
                    select.id = title +'_select';
                    select.add(new Option(''));
                    column.header().replaceChildren(select);

                    // apply listener for user change in value
                    select.addEventListener('change', function() {
                        column
                            .search(select.value, { exact: true })
                            .draw();
                    });

                    // add list of options
                    column
                        .data()
                        .unique()
                        .sort()
                        .each(function(d, j) {
                            select.add(new Option(d));
                        });
                })
        }
    });
});

allan

Happy to take a look at a test case showing the issue so it can be debugged.

edit This example shows multi-row headers working for Excel export.

Allan

kthorngren

I built a test case for you by copying your code:
https://live.datatables.net/bumujefe/1/edit

It seems that when the header cell is updated the th becomes undefined. I added the exportOptions from this example just to demonstrate. I'm not suggesting you need to add this to your solution. Here is the output example:

@allan will need to look at why the updated th becomes undefined.

Only the second row is being exported. I suspect you will want the top row exported and likely will want to sort using only the top row. In this case use orderCellsTop. I updated the test case to show this:
https://live.datatables.net/kofijuxe/1/edit

In addition to adding orderCellsTop I also changed the column().header() API call to specify row 1 when updating the cells with the inputs.

Kevin

siska77

I was actually creating a jsfiddle myself ( https://jsfiddle.net/vqy53L90/ ), I've added your suggestions @kthorngren but it doesn't seem to work, even after double checking you example and if I've added the correct includes, it still gives an error
I slightly modified your example ( https://live.datatables.net/kofijuxe/2/edit ), and that seems to work just fine.
Only the downloaded excel has a colspanned header which says DataTables - JS Bin, the title of the HTML-file, but that just needs some tweaking or tried in its intended environment to fix it.

Thank you for your help!!

kthorngren

Add orderCellsTop and change the column().header() to specify row 1. Here are the changes:

        layout: {
            topStart: {
                buttons: ['excelHtml5', 'csvHtml5']
            }
        },
    orderCellsTop: true,
....
        initComplete: function() {
            this.api()
                .columns([0, 3]) // first & fourth column are searchfields
                .every(function() {
                    let column = this;
                    let title = column.header().textContent;

                    // create input element
                    let input = document.createElement('input');
                    input.placeholder = title;
          // Specify row 1
                    column.header(1).replaceChildren(input);

                    // event listener for user input
                    input.addEventListener('keyup', () => {
                        if (column.search() !== this.value) {
                            column.search(input.value).draw();
                        }
                    });
                })
                .columns([4, 5]) // fifth & sixth colum are dropdowns
                .every(function() {
                    let column = this;
                    let title = column.header().textContent;

                    // create select element
                    let select = document.createElement('select');
                    select.id = title +'_select';
                    select.add(new Option(''));
          // Specify row 1
                    column.header(1).replaceChildren(select);

...
        }

https://jsfiddle.net/h0ad92tg/

Kevin

siska77

Yeah, I had figured it out by comparing your sample and mine, download now also works on jsfiddle.
Now working on the colspanned header, will post my solution when I fix it.

siska77

Found the solution for the header:
Remove the header: false, from layout > topStart > buttons as it removes the header entirely, and use the data.headerStructure.pop(); to remove the second header row.