excel export convert number to string while exporting

nikki111

i am having this problem when i export the data into excel , excel treat's a column as numeric and performs some rounding on it but i want to keep it as text.

rf1234

Looks like Excel recognizes your column as numeric. You can override this using built in styles: https://datatables.net/reference/button/excelHtml5 See "Customization" and "Built in Styles"

Here are some examples: https://datatables.net/extensions/buttons/examples/html5/index.html

See the Excel examples on how to apply built-in styles.

nikki111

I already checked those links, applying styles are not working for me

rf1234

... then you have a different problem ... but without your code is is pretty much impossible to give you further advice.

jssatish

hi,
I want to prevent "1.65461E+11" in my cell.

jssatish

I want just normal text without E+11.

rf1234

just put a blank in front of it when formatting for export. Then Excel shouldn't recognize it as numeric any longer.

Take a look at this post for questions on how to format these things:
https://datatables.net/forums/discussion/45846/datatable-excel-export-how-can-we-apply-multiple-styles-to-same-cell#latest

allan

It is a plain number if you look at the raw XML. It is Excel which is detecting the number and formatting it as such. If you typed such a number into Excel, then it would do the same thing.

Allan

rf1234

True, Excel does these things unfortunately. Inserting a leading space stops Excel from recognizing it as a number. Had the same problem with PHPSpreadsheet. Then checked for numeric in PHP and inserted the space whenever I came across a numeric value for that field. Inserting a leading “ ' “ unfortunately didn't work. You can do the same in JavaScript using the export options.

lyassa

Prepending "\0" to the column I wanted Excel to treat as string worked for me. Note that you will not see any extra zero in your data, and Excel will not treat your data as numeric anymore.

Here is a code snippet:

Note: it is the 3rd column in my table that I wanted as string, hence "column ===2"

buttons: [
       {extend: 'excelHtml5', 
        filename: 'my_file_name', 
        title: null,
        exportOptions: {
            format: {
                     body: function (data, row, column, node ) {
                                return column === 2 ? "\0" + data : data;
                                }
              }
           }
      }
]

I just wish the syntax was a bit easier.

urbanfernando

@lyassa Best answer

Prashanth_O

Add '\u200C' before large number, it will convert as string

buttons: [{
                    extend: 'excel',
                    text: '<img src="Content/Images/Excel-Logo.jpg" style="width:50px;height:50px;"/>',
                    tag: 'span',
                    title: '',
                    init: function (api, node, config) {
                        $(node).removeClass('btn btn-secondary buttons-excel buttons-html5')
                    },
                    exportOptions: {
                        columns: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13]
                    },
                    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][11] = '\u200C' + data.body[i][11];
                            }
                        }
                    }
                }],

Prabhosha

Prashant_O
Tried with '\u200C' it works but its keeping some hidden value in excel. how to avoid the hidden value(?)

colin

@Prabhosha We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin