8

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 would need all the data resp. the columns in the resulting Excel table being formatted as text as otherwise I am losing data in some columns.

Examples: - I have a column that has leading zeros (e.g. 0022) which only appears with the leading zeros cut off (e.g. 22) in the Excel file if this is not formatted as text. - Another column contains 19-digit account numbers (e.g. 1234567890123456789) which appears with the last four digits being changed to zeros (e.g. 1234567890123450000) in the Excel file if this is not formatted as text.

Is there any way I can set this in my Datatables / TableTools initialisation so that it always exports all data as text into the Excel file ?

Many thanks for any help with this, Tim.

user2571510
  • 11,167
  • 39
  • 92
  • 138

10 Answers10

24

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];
                    }
                }
            }               
            }],
Emilio Esteve
  • 241
  • 2
  • 2
  • great solution for datatables javascript ! – error1009 Apr 11 '17 at 09:56
  • 2
    Thanks for this solution. I used your idea in the render function of the column as follows: render: function (data){return '\u200C'+data}. Now the Excel export treat the numbers as strings. – C J Sep 05 '18 at 07:08
  • Thank you for your solution – Parsa Saei Jun 25 '20 at 13:30
  • This is a terrible idea. u200C is a "zero-width non-joiner" char. Anybody that copies the data, puts it into a spreadsheet, does _anything with it_ will think they are working with a number but its now a string and its not obvious because there's a zero-width invisible char preceding it. Formulas won't work, math will fail, data imports break, etc. Brilliant. I just diagnosed an issue caused by a dev blindly copying and pasting this answer from SO. Thankfully they left a comment w/ a link. For your own sakes, please don't blindly copy code that you don't understand the implications of. – firxworx Jun 11 '21 at 15:49
  • the customizedData function is really help me alot, thx! – ahmfarisi Sep 16 '22 at 04:02
  • Thx, this is really help me a lot. But I have question. Just said I already export 16 characters data to excel using this way. But when I count the character's number of the data, it told me 17 characters. We have \u200c as 1 more character in excel but it is hidden. Is anyone has a solution to make this data still on 16 characters when I count it on excel? – ahmfarisi Sep 23 '22 at 03:47
5

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.

Richard Rebeco
  • 753
  • 11
  • 13
4

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
TPete
  • 2,049
  • 4
  • 24
  • 26
  • Thanks for this ! I tried adding quotes around the data on my page but this also exports the quotes. – user2571510 Jun 05 '14 at 08:08
  • 1
    @user2571510 If you need more control about the exported data, you might consider creating a *real* excel file. If you are using php for the backend, you might want to take a look at the [phpexcel](https://phpexcel.codeplex.com/) library. – TPete Jun 05 '14 at 08:40
  • Thanks, I'll have a look at this. Was hoping I could utilise TableTools as they have a lot of other useful features like the row selection on mouse click etc. – user2571510 Jun 05 '14 at 08:54
2

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 start
if ( 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 output
    var 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 output
var 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.

armstb01
  • 623
  • 8
  • 11
1

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.

Community
  • 1
  • 1
Marquistador
  • 1,841
  • 19
  • 26
0

Update :

From datatables 1.10.8 and introduction of Buttons API, tabletools is deprecated and here is the way to do it via buttons API:

Demo here

It extends the excel button with exportOptions parameter which allow few options, one is orthogonal when you can say that it should use the 'sort' type :

exportOptions: {
                orthogonal: 'sort'
            }

After you need to precise the sort type and which columns should be impacted with the columnDefs datatable option :

columnDefs: [{
       targets:[1],
       render: function(data, type, row, meta){
          if(type === 'sort'){
             //data = ' ' + data ;
              return "\u200C" + data ; 
          }

          return data ;   

       }
    }]

Here the second column ( zero indexed) is considered. For the 'sort' type, we prefix the date with the ZERO WIDTH NON-JOINER so excel will consider it as string. Didnt notice any side effects.

Enjoy

there is a way to do it via the tabletools plugin of datatables.

Using Stop Excel from automatically converting certain text values to dates ,

and the fncellrender button option, the solution is to use the fnCellRender option on the xls button like this :

{
"sExtends":    "xls", 
"fnCellRender": function ( sValue, iColumn, nTr,iDataIndex ) {
     if ( iColumn === colIndexOfString ) {
         return '"=""' + sValue + '"""' ;
         }
     return sValue;
     }
}

This way data is extracted in csv file with the special characters needed to make Excel considered them as string and avoid to use automatic type detection.

And yes now you can keep your leading and trailing zeros

Community
  • 1
  • 1
Aureltime
  • 373
  • 3
  • 10
0

Solution of datatable expord CSV with special characters. Find charset from https://cdn.datatables.net/buttons/1.1.2/js/buttons.html5.js or https://cdn.datatables.net/buttons/1.1.2/js/buttons.html5.min.js

and change it to UTF-8-BOM from UTF-8

chicks
  • 2,393
  • 3
  • 24
  • 40
Tauqeer Afzal
  • 1
  • 1
0

For the numbers that are 19 digits, the issue is actually Excel rounding the numbers when it opens the data. Please see this thread for more info and possible workarounds: https://datatables.net/forums/discussion/comment/132633#Comment_132633

Lisa
  • 1
  • 1
0

Below code will help you to create a query data table with export to Text file as an custom option. And also included out-of-box copy, excel, csv.

     $('#tableid').DataTable({
           buttons: [
                {
                    extend: 'copyHtml5',
                    filename: function() {
                        return "Copy Table";
                    }
                },
                {
                    extend: 'excelHtml5',

                    filename: function() {
                        return "Export To Excel";
                    }
                },
                {
                    extend: 'csvHtml5',

                    filename: function() {
                        return "Export to CSV";
                    }
                },
                {
                    text: 'Text',
                    footer: false,
                    header: false,
                    exportOptions: {
                        orthogonal: 'sort'
                    },
                    action: function(e, dt, node, config) {

                        var data = dt.buttons.exportData();
                        var retContent = [];
                        var retString = '';
                        for (var i = 0; i < data.body.length; i++) {
                            var text = '\u200C' + data.body[i];
                            retContent.push(text);
                        }
                        retString = retContent.join(',\r\n');
                        $.fn.dataTable.fileSave(
                            new Blob( [ retString ] ),
                            "Export to text"+ ".txt"
                        );
                    }
                }
            ]
        });

Libraries included are:

  1. datatables.min.js
  2. dataTables.bootstrap4.min.js
  3. dataTables.buttons.min.js
  4. buttons.flash.min.js
  5. jszip.min.js
  6. vfs_fonts.js
  7. buttons.html5.min.js

and found above libraries in this link https://editor.datatables.net/examples/extensions/exportButtons

Raju M
  • 1
  • 2
    Don’t use action like this, it will cause the CSV to be generated without double quotes around each cell so if you have a comma in your data it will get split into two cells. I recommend this solution instead: https://stackoverflow.com/a/165052. To implement that solution, you can do something like this: `exportOptions: { format: { body: function (data, row, column, node) { return "=\"" + data.replace("\"", "\"\"") + "\""; } } }` – Dennis T --Reinstate Monica-- Sep 15 '19 at 18:47
0

I tried zero width non-joiner character ('\u200C') and although it worked, it caused havoc when I tried to do anything involving that column.

Adding $('row c[r^="A"]', sheet).attr('s', '0') solved my issue:

buttons: [
    {
        extend: 'excelHtml5',
        orientation: 'landscape',
        autoFilter: true,
        title: null,
        messageTop: '...',
        messageBottom: '...',
        //  BAD IDEA!
        //customizeData: function (data) {
        //    for (var i = 0; i < data.body.length; i++) {
        //        data.body[i][0] = '"' + data.body[i][0] + '"';
        //        //    for (var j = 0; j < data.body[i].length; j++) {
        //        //        data.body[i][j] = '\u200C' + data.body[i][j];
        //        //    }
        //    }
        //},
        customize: function (xlsx) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
            var sheet2 = xlsx.xl['styles.xml'];

            $('row:first c', sheet).attr('s', '2').attr('s', '32'); // first row is bold
            $('row c[r^="A"]', sheet).attr('s', '0');  // first column as text

            $(sheet.body)
                .css('font-size', '10pt');

            $(sheet.body).find('table')
                .addClass('compact')
                .css('font-size', 'inherit');
        },
        exportOptions: {
            columns: [0, 1, 2, 3, 4]
        },
    },
NoBullMan
  • 2,032
  • 5
  • 40
  • 93