CODEWITHSUNDEEP
datatablesexport-to-excel
Aftab Ahmed Kalhoro
Aftab Ahmed Kalhoro

Reputation: 336

jQuery DataTable export to excel is exporting wrong column values

I'm using jQuery's DataTable 1.10.19 & Buttons 1.6.1. When exporting to the excel sheet integer column values are exported wrong, which doesn't exist

One column Reference No is unique number generated per row by back-end. Following is sample

enter image description here

Actual column contains value, which exist in (HTML) table & for same row, when exported, an other value is in the column.

I checked, pattern is also not same. i.e. in one row, value is incremented (as in 1st row) by 1 while in second row value is decremented by 1. In last row value is decremented by 5.

NOTE: Same table with same values, when exported to PDF, data is exported correctly.

Upvotes: 0

Views: 2192

Answers (2)

Aftab Ahmed Kalhoro
Aftab Ahmed Kalhoro

Reputation: 336

After reading answer of Mr. Sergey Nudnov, I came up with following solution

    buttons: [
               {
                   "extend": 'excel', "text": ' Excel', "className": 'btn btn-flat btn-success fa fa-file-excel-o'
                  , exportOptions: {
                      columns: [':visible']
                        , format: {
                            body: function (data, row, column, node) {
                                var cellData;
                                cellData = data.indexOf("<") < 0 ? data : $(data).text();   // Some cells contains html elements. need to strip off
                                return column === 2 ? '\u200C' + cellData : cellData;
                            }
                        }
                  }
            ]

NOTE: column === 2 is the index of the column which need to be converted from integer to string. just prefix/concatenate cell value (long number) with '\u200C'

Upvotes: 0

Sergey Nudnov
Sergey Nudnov

Reputation: 1429

You should realize that all exported values end with 0. Actually, all of them are rounded to 15 non-zero digits.

If you would type in any of values from the Actual column directly to Excel, you would see, that its last digit was replaced with 0.

One of good articles on this topic provides:

You will notice this standard (IEEE 754) in many ways in Excel, but the main is, that if you write an integer with more than 15 digits (which is quite feasible), excel will transform all integers starting with the 16th to zero.

So when you put 1234567890123456789 in a cell, you get 1234567890123450000. The same goes for 1234567890.123456789 that would give 1234567890.123450000! This is by itself quite a drawback, but it doesn’t end there, this limitation impacts all parts of Excel, including calculations.

Is there a way around this

The short answer is NO. The long answer is, you can store longer number as text (so begin writing in Excel with an apostrophe), and you will see more than 15 integers, but if you will want to convert them back to numbers and do calculations with them, you will again only work with 15 integers!

The only way to make Excel more precise, is by using an Add-In. There are many out there, here is an example: xlPrecision

Upvotes: 1

Related Questions