Filter dropdowns in table header mess up excel export headers

GusBeare

Hi

I used an example I found on this site to create drop down filtering for some cols in my table.

Example: https://datatables.net/examples/api/multi_filter_select.html

The code looks like this:

initComplete: function () {
this.api().columns([1,3,4,5,7,18]).every(function () {
var column = this;
var select = $('<select class="selectWrapper"><option value="">-- select --</option></select>')
.appendTo($(column.header()))
//.appendTo($(column.header()).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>`);
            });

It works great. However the example put the filters in the table footer. But I want the filters in the header. The problem here is that I also have export buttons and when I export the drop down contents ends up in the spreadsheet col headings.

I could put a separate non DT table above and put the col headings in there but it's a bit ugly. Can anyone suggest a way around this?

kthorngren

The best option is to create two header rows and place the select inputs into one of them. Use orderCellsTop if the select inputs are in the second row. The export buttons will use the orderCellsTop setting as the header row to export. See this example:
http://live.datatables.net/saqozowe/70/edit

Kevin

GusBeare

thanks Kevin that was really helpful.

I ended up with this. I needed filtering on only some cols.

 orderCellsTop: true,
 initComplete: function () {
                var api = this.api();
                $('.filterhead', api.table().header()).each(function (i) {
                    
                    const colsWithSearch = [1, 3, 4, 5, 7, 18];

                    var column = api.column(i);

                    if (colsWithSearch.includes(i)) {
                        var select = $('<select><option value=""></option></select>')
                            .appendTo($(this).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>');
                        });
                    }
                   
                });
            }