Numeric value gets change while exporting to Excel - Dynamic Datatables

Question

I am trying to export data as excel from dynamic data tables, but in excel the numeric value of a column gets change. The Original Value showing in table in web application is like 1031001746692014 But upon exporting to Excel it becomes

$(document).ready(function(){ 
    $('#tracker_list').DataTable({
        dom: 'ZBflrtip',  
        'processing': true,
        'serverSide': true,
        'pageLength': 25,
        'serverMethod': 'post',
        'lengthMenu': [[10,25,50,100, -1], [10,25,50, 100, "All"]],
        'order': [ 0, 'desc' ],
        'ajax': {
          'url':'ajax/url_to_method'  
        },  
                    
        'columns': [
            { data : 'id'}, 
            { data: 'account_number' },   
             
        ], 
        
        
        'columnDefs': [{ 
            'targets': [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19], /* column index */
             'orderable': true, /* true or false */
             'order': [ 1, 'desc' ]
        }],
        //'buttons': [  'copy', 'csv', 'excel', 'pdf', 'print' ]
    });

Trying to find problem, either in datatables export mechanism or in excel ?

Hi. We can't guess unfortunately, if you can create a snippet reproducing it'd be great! — Alexandre Elshobokshy, Aug 18 '21 at 07:55
Possibly a precision issue. If the value is an int try converting it to a string instead — Rory McCrossan, Aug 18 '21 at 07:58
@RoryMcCrossan I tried converting to string it does not work. Though concatenating and Letter works. But we need data as it is i.e in number format. — Esar-ul-haq Qasmi, Aug 18 '21 at 08:01
@AlexandreElshobokshy Creating a snippet is hard. Would you able to tell me if there is any limit in excel cell for numeric values? Is the number value is exceeding the limit etc? — Esar-ul-haq Qasmi, Aug 18 '21 at 08:02
Hard to tell without reproducing, try editing the values on display for example, removing all spaces https://datatables.net/forums/discussion/comment/82902/#Comment_82902 — Alexandre Elshobokshy, Aug 18 '21 at 08:12
You have hit [this limit](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3) in Excel. Scroll down to "Number precision" where you will see "15 digits". You can provide [custom styles](https://stackoverflow.com/a/67340599/12567365), but even if you force the affected cells to use Excel "text" (number as text) style, the import will still cause a loss of precision, first. — andrewJames, Aug 18 '21 at 13:07
How you deal with this may depend on what the number actually represents, and your tolerance for adjusting how it is displayed. For example, if the digits are just an ID, then you can add separators (spaces, hyphens, etc.) inside the string: `103-100174-6692014`. Now, it really is a string, not an integer. (And if it's sensitive data such as a credit card number, then you should not be passing around the full number, anyway, of course.) In your case, it's an account number - so, maybe you can format that in some way which is acceptable to you. — andrewJames, Aug 18 '21 at 13:07

score 0 · Answer 1 · answered Aug 18 '21 at 12:59

Found the reason. The problem is in Excel itself. Excel cells have a limit to a specific range. Therefore added one Apostrophe at the start of the number so it reflects in the results but in excel we can manage it by replacing it to the correct number easily.

Numeric value gets change while exporting to Excel - Dynamic Datatables

1 Answers1