Column value is broken when export to Excel

yaylitzis

I have a column which contains some barcodes like:

• 20021510107903080221102112000001
• 20021510107903211220328911000001
• 20021510107903211220328900400002

However, excel in this column has values:

• 2,00215E+31
• 2,00215E+31
• 2,00215E+31

I tried to set this column as Text by

buttons: [
    {
        extend: 'excelHtml5',
        customize: function (xlsx) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
             $('row c[r^="E"]', sheet).attr( 's', '0' );  /*Column is E*/
        }
    }
],

but nothing happened....

Is there a way to show the values correctly?

colin

This thread should help, it's asking the same thing.

Cheers,

Colin

kthorngren

This thread offers another solution that may work for you.

Kevin

yaylitzis

Hi @colin

My code looks like this

var tableLabels = $('#myTable').DataTable({
    data: ${jsonList},
    orderCellsTop: true,
    fixedHeader: true,
    scrollX: true,
    pageLength: 50,
    select: true,
    colReorder: true,
    order: [[0, "desc"]],
    dom: 'Bfrtip',
    buttons: [
        {
            extend: 'excelHtml5',
            customize: function (xlsx) {
                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                 $('row c[r^="E"]', sheet).attr( 's', '0' );
            }
        }
    ],
    columns: [
        {data: "dateTime"},
        {data: "barcode"}   
    ],
    columnDefs: [
        {targets: 0, className: 'dt-body-left'},
        {targets: 1, className: 'dt-body-left'}     
    ]
});

I can't figure out where the solution is applied to ... Could you help a little more?
Thanks a lot!

colin

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

yaylitzis

Hi @colin,

Thanks for your reply, I created a test case here.

yaylitzis

I read @kthorngren answer and solved my question!
Thanks!