jQuery DataTable export to excel is exporting wrong column values

Question

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

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.

Please read Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers? - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions. — halfer
– halfer, Commented Dec 18, 2019 at 15:02

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 20, 2019 at 1:53

Sergey Nudnov

1,44113 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aftab Ahmed Kalhoro Over a year ago

Thanks @Sergey I didn't know this before about 15 digits limit about excel. I solved the issue by converting cell value to text format instead of integer

Aftab Ahmed Kalhoro · Accepted Answer · 2019-12-21 09:06:33Z

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'

Collectives™ on Stack Overflow

jQuery DataTable export to excel is exporting wrong column values

2 Answers 2

Is there a way around this

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Is there a way around this

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related