Change Column titles in dataTables when exporting to excel

Question

I am trying to rename a column in dataTables when exporting to excel.

The reason is cause I have added a select filter option on one of the header titles.

When I try to export it, the whole name shows like this:

CategoryBeautyChristmasDecorFood - BakeryFood - DeliFood - DrinksGardenGift Cards and StationaryGifts - Children - ClothingGifts - Children - ToysGifts - LadiesGifts - MenJeweleryPets

Instead of just saying: Category

Please help!

My code:

$(document).ready(function () {


        $('#datatables').DataTable({

            "pagingType": "full_numbers",
            "lengthMenu": [
                [10, 25, 50, -1],
                [10, 25, 50, "All"]
            ],
            responsive: true,
            language: {
                search: "_INPUT_",
                searchPlaceholder: "Search records",
            },
            'columns': [         // see https://datatables.net/reference/option/columns.searchable
                null,
                null,
                null,
                null,
                null,
                {'searchable': false},
                {'searchable': false}
            ],


            dom: 'Bfrtip',
            buttons: [

                {
                    extend: 'excelHtml5',
                    orientation: 'landscape',
                    exportOptions: {
                        columns: [0, 1, 2, 3],
                        customize: function ( xlsx ) {
                            var sheet = xlsx.xl.worksheets['sheet1.xml'];

                            $('c[r=A2] t', sheet).text( 'Custom text' );
                        }


                        /**format: {
                            //this isn't working....
                            header: function (data, columnIdx) {
                                return columnIdx + ': ' + data;
                            }
                        }*/

                    }
                }
                /**,{
                    extend: 'pdfHtml5',
                    orientation: 'landscape',
                    exportOptions: {
                        columns: [ 0, 1, 2, 3 ],
                        header: false
                    }
                }*/

            ],

            //add drop downs to columns
            initComplete: function () {
                this.api().columns([3]).every(function () {
                    var column = this;
                    var select = $('<select><option value="">Category</option></select>')
                        .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>')
                    });
                });
            }

        });

        var table = $('#datatables').DataTable();

    });

I've looked everywhere and cant seem to find a solution.

Answer 1

I had the exact same issue. I managed to get it working using the the header formatting function inside the exportOptions object instead of the customize function. Assuming that the header that needs to be modified is the first one i.e the header index is 0, you could script your header function like this to make it work.

...
exportOptions: {
  format: {
    header: function ( data, columnIdx ) {
      return columnIdx === 0 ? "Category" : data;
    }
  }
},
...

Reference: https://datatables.net/extensions/buttons/examples/html5/outputFormat-function

Answer 2

I managed to figure out the customize placement was wrong.

$(document).ready(function () {

        $('#datatables').DataTable({

            "pagingType": "full_numbers",
            "lengthMenu": [
                [10, 25, 50, -1],
                [10, 25, 50, "All"]
            ],
            responsive: true,
            language: {
                search: "_INPUT_",
                searchPlaceholder: "Search records",
            },
            'columns': [         // see https://datatables.net/reference  /option/columns.searchable
                null,
                null,
                null,
                null,
                null,
                {'searchable': false},
                {'searchable': false}
            ],

            dom: 'lBfrtip',
            buttons: [
                {
                    extend: 'excelHtml5',
                    text: 'Excel Export',
                    customize: function (xlsx) {
                        var sheet = xlsx.xl.worksheets['sheet1.xml'];

                        $('c[r=D2] t', sheet).text('Category');
                    },
                    exportOptions: {
                        columns: [0, 1, 2, 3],
                        modifier: {
                            page: 'current',

                        }
                    }

                }
            ],

            initComplete: function () {
                this.api().columns([3]).every(function () {
                    var column = this;
                    var select = $('<select><option value="">Category</option></select>')
                        .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>')
                    });
                });
            }

        });


        var table = $('#datatables').DataTable();

    });