Datatables / Tabletools: Format Data As Text When Exporting To Excel

July 26, 2023 Post a Comment

I am using the Datatables TableTools plugin in order to provide an Export to Excel option for a table on my page. In general everything works as intended. My only issue is that I

Solution 1:

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];
                    }
                }
            }               
            }],

Solution 2:

I have the solution to this problem.

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

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

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
        );

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
                );

Save your datatables.js
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.

Solution 3:

TableTools does not create a realexcel 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

Solution 4:

I would like to expand on Richards answer. Like Richard, I could not figure out the solution based on the Datatables documentation. I wanted an excelHtml5 export with all fields being exported as text only. Richards solution helped me get to the solution that I will post below.

For Datatables 1.10.12 the html5 buttons code appears in a separate file buttons.html5.js.

As Richard noted, search for the DataTable.ext.buttons.excelHtml5 block.

The piece of code I was interested in was:

// Detect numbers - don't match numbers with leading zeros or a negative// anywhere but the startif ( typeof row[i] === 'number' || (
        row[i].match &&
        $.trim(row[i]).match(/^-?\d+(\.\d+)?$/) &&
        ! $.trim(row[i]).match(/^0\d+/) )
) {
    cell = _createNode( rels, 'c', {
        attr: {
            t: 'n',
            r: cellId
        },
        children: [
            _createNode( rels, 'v', { text: row[i] } )
        ]
    } );
}
else {
    // Replace non standard characters for text outputvar text = ! 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, '');

    cell = _createNode( rels, 'c', {
        attr: {
            t: 'inlineStr',
            r: cellId
        },
        children:{
            row: _createNode( rels, 'is', {
                children: {
                    row: _createNode( rels, 't', {
                        text: text
                    } )
                }
            } )
        }
    } );
}

In order to make the excelHtml5 button export ONLY text, I removed the IF block that would identify a field as a potential number. Our customer also had a specific request to have '<>' in any field that was blank so I removed the two replace methods for < and >.

// Replace non standard characters for text outputvar text = ! row[i].replace ?
    row[i] :
    row[i]
        .replace(/&(?!amp;)/g, '&amp;')
        .replace(/[\x00-\x09\x0B\x0C\x0E-\x1F\x7F-\x9F]/g, '');

cell = _createNode( rels, 'c', {
    attr: {
        t: 'inlineStr',
        r: cellId
    },
    children:{
        row: _createNode( rels, 'is', {
            children: {
                row: _createNode( rels, 't', {
                    text: text
                } )
            }
        } )
    }
} );

This change allows the excel button to export all values as text. Excel no longer switches my < and > and my numbers are all text, no scientific notation.

Solution 5:

This specific problem has been answered elegantly in this post - https://stackoverflow.com/a/165052/6169225. Let's say that you have an integer your_integer that you want displayed as a string (i.e. the leading zeros displayed). Then you simply format it as such before you export to excel - ="<your_integer>". When the excel doc auto-downloads, your integer will already display as a string using this method.

JavaScript Passion