Datatables / TableTools: format data as text when

Question

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.

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

Answer 2

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