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
– Alexandre Elshobokshy, Commented Aug 18, 2021 at 7:55
Possibly a precision issue. If the value is an int try converting it to a string instead — Rory McCrossan
– Rory McCrossan, Commented Aug 18, 2021 at 7: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
– Esar-ul-haq Qasmi, Commented Aug 18, 2021 at 8: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
– Esar-ul-haq Qasmi, Commented Aug 18, 2021 at 8:02
Hard to tell without reproducing, try editing the values on display for example, removing all spaces datatables.net/forums/discussion/comment/82902/#Comment_82902 — Alexandre Elshobokshy
– Alexandre Elshobokshy, Commented Aug 18, 2021 at 8:12

Esar-ul-haq Qasmi · Accepted Answer · 2021-08-18 12:59:40Z

0

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.

answered Aug 18, 2021 at 12:59

Esar-ul-haq Qasmi

1,0842 gold badges14 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

Numeric value gets change while exporting to Excel - Dynamic Datatables

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related