How to format columns on export to Excel.

Keith_H

I am trying to work out how to format the data before it gets output to an Excel file.

            $('#tblTransDetails').dataTable({
                    "dom": 'Bfrtip'
                , "buttons": [
                            {   extend: 'excelHtml5'
                            ,   title: 'SalesLedgerTransactions'
                            }
                        ]
                , "autoWidth":false
                , "fixedColumns": {leftColumns: 12 }
                , "info":false
                , "JQueryUI":true
                , "ordering":true
                , "paging":false
                , "scrollY":"500px"
                , "scrollX":"1485px"
                , "scrollCollapse":true
                , "columnDefs": [
                            { className: "LeftNoWrap", "targets": [ 1,2,3,4,5,6,7,8,10 ] }
                        ,   { className: "CenterNoWrap", "targets": [ 0,9 ] }
                        ,   { className: "RightNoWrap", "targets": [ 11,12,13,14,15,16,17,18 ] }
                    ]
            });

I have some columns which are strings, but contain just numbers (e.g. 001234). I want to format these as strings.

Also, is there any way to style the header row with background colour/font colour when exporting.

Thanks.

Keith_H

Sorry, this is DataTables 1.10.9

allan

I have some columns which are strings, but contain just numbers (e.g. 001234). I want to format these as strings.

Good one! At the moment yes, that will always be sent as a number. i'll fix that and post back.

Also, is there any way to style the header row with background colour/font colour when exporting.

I'm afraid not - not. it is something I would like to add in future, but is not currently available.

Allan

allan

Fix committed here and the nightly version is now up to date. The fix will also be included in the next release.

Regards,
Allan