Datatables / TableTools: format data as text when exporting to Excel

I tried the first option given by Aureltime but I found a little side effect. If the column only contains numbers and you use the render function, the sorting option doesn't work. Hopefully, from 1.10.12 datatables version there is a new option to customize data before creating the excel file.

In this customize function I added the /u002C and it works perfect, even the sorting of numbers.

        "buttons": [{
            extend: 'excel',
            exportOptions: {
                orthogonal: 'sort'
            },
            customizeData: function ( data ) {
                for (var i=0; i<data.body.length; i++){
                    for (var j=0; j<data.body[i].length; j++ ){
                        data.body[i][j] = '\u200C' + data.body[i][j];
                    }
                }
            }               
            }],

I have the solution to this problem.

It was broken my head very much time... So the explain is below this:

1. It fix works fine in DatatableJS version 1.10.11 (for HTML Excel export option)
2. Open datatables.js and search this: "DataTable.ext.buttons.excelHtml5 = {"

3. Search in the follow lines until take this code, and comment it:

            cells.push( typeof row[i] === 'number' || (row[i].match && $.trim(row[i]).match(/^-?\d+(\.\d+)?$/) && row[i].charAt(0) !== '0') ?
               '<c t="n"><v>'+row[i]+'</v></c>' :
               '<c t="inlineStr"><is><t>'+(
                   ! row[i].replace ?
                       row[i] :
                       row[i]
                           .replace(/&(?!amp;)/g, '&amp;')
                           .replace(/</g, '&lt;')
                           .replace(/>/g, '&gt;')
                           .replace(/[\x00-\x09\x0B\x0C\x0E-\x1F\x7F-\x9F]/g, ''))+ // remove control characters
               '</t></is></c>'                                                      // they are not valid in XML
           );
   

4. Put this new code :

               cells.push( '<c t="inlineStr"><is><t>'+(
                           ! row[i].replace ?
                               row[i] :
                               row[i]
                                   .replace(/&(?!amp;)/g, '&amp;')
                                   .replace(/</g, '&lt;')
                                   .replace(/>/g, '&gt;')
                                   .replace(/[\x00-\x09\x0B\x0C\x0E-\x1F\x7F-\x9F]/g, ''))+ // remove control characters
                       '</t></is></c>'                                                      // they are not valid in XML
                   );
   

5. Save your datatables.js

6. Enjoy your holy Text Cells Forever!!

This solution helps to maintain the number, date, and decimal format's.

I changed the code to force to write in text format all values from the HTML to the XLSX.

If anybody have a question about this solution, I will try to response all of them questions.

Thanks to all.

TableTools does not create a real excel file, it creates a csv file instead. Those contain only raw data, no formatting. Although the leading zeros are there, Excel usually will not show them. You have several options here:

• change the formatting from within Excel
• open the csv file from Excel's open dialog, from which you should be able to mark columns as text (you might need to change the file type to txt)
• add quotes around the data
• create a real excel file via some external library