I have a datatable in my application that I need to export to Excel but when the datatable is exported in excel, the format type for each cell is 'General'. However, I want all my cells to be displayed in 'Text' format in Excel.

I tried converting my data to String but it still displays the result in General. If I try to convert my data to Int then it does displays it in Number format in Excel but I want everything in Text format.

Here's the function I'm trying to change the data type:

{
   "targets": 13,
   "render": function (data, type, row){
       var d = data.toString();
       return d;
       }
}

Here I'm targeting to one column only. However, I want to target all my columns. Even targeting a single column won't work.

Any help would be appreciated.

I have a datatable in my application that I need to export to Excel but when the datatable is exported in excel, the format type for each cell is 'General'. However, I want all my cells to be displayed in 'Text' format in Excel.

I tried converting my data to String but it still displays the result in General. If I try to convert my data to Int then it does displays it in Number format in Excel but I want everything in Text format.

Here's the function I'm trying to change the data type:

{
   "targets": 13,
   "render": function (data, type, row){
       var d = data.toString();
       return d;
       }
}

Here I'm targeting to one column only. However, I want to target all my columns. Even targeting a single column won't work.

Any help would be appreciated.

• javascript
• jquery
• datatable
• datatables
• phpexcel

Share Follow asked Aug 10, 2018 at 5:41 hira12hira12 10311 gold badge33 silver badges88 bronze badges 1

• I would appreciate you reviewing my answer to your question and if I was able to help you, marking answer as accepted by clicking on v under the answer's score. Thank you – Sergey Nudnov Commented Apr 14, 2019 at 19:28

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 4

You could apply the following code to acplish your task:

$('#example').DataTable( {
    dom: 'Bfrtip',
    columns: [
        { data: 'name' },
        { data: 'surname' },
        { data: 'position' },
        { data: 'office' },
        { data: 'salary' }
    ],
    buttons: [
        {
            extend: 'excelHtml5',
            customize: function(xlsx) {
                //Get the built-in styles
                //refer buttons.html5.js "xl/styles.xml" for the XML structure
                var styles = xlsx.xl['styles.xml'];

                //Create our own style to use the "Text" number format with id: 49
                var style = '<xf numFmtId="49" fontId="0" fillId="0" borderId="0" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyNumberFormat="1"/>';
                // Add new node and update counter
                el = $('cellXfs', styles);
                el.append(style).attr('count', parseInt(el.attr('count'))+1);
                // Index of our new style
                var styleIdx = $('xf', el).length - 1;

                //Apply new style to the first (A) column
                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                //Set new style default for the column (optional)
                $('col:eq(0)', sheet).attr('style', styleIdx);
                //Apply new style to the existing rows of the first column ('A')
                //Skipping the header row
                $('row:gt(0) c[r^="A"]', sheet).attr('s', styleIdx);
            },
            exportOptions: {
                format: {
                    body: function(data, row, column, node) {
                        return column === 0 ? "\0" + data : data;
                    }
                }
            },
        },
    ]
} );

Here is JSFiddle

In this example we work with column 0 ('A').
For your column 13, use this code:

                //Set new style default for the column (optional)
                $('col:eq(13)', sheet).attr('style', styleIdx);
                //Apply new style to the existing rows of the 13th column ('N')
                //Skipping the header row
                $('row:gt(0) c[r^="N"]', sheet).attr('s', styleIdx);

---

Update Apr 4th, 2019

Added an additional trick in the code above to ensure very large numbers are still being treated as text:

exportOptions: {
    format: {
        body: function(data, row, column, node) {
            return column === 0 ? "\0" + data : data;
        }
    }
},

JSFiddle was updated