jQuery datatable - format column as string when exporting to Excel

Question

I have a jQuery datatable where first column is barcode labels and they are 24 characters long. They could be all numeric characters or mix of alpha and numeric. My problem is when exporting to Excel and all labels look numeric.

It exports fine when label is 1234ABCD5678901234567890 or 001234567890001234567890 but labels such as 123004590218842001720584 are displayed as 123004590218842000000000 and when clicking on that cell it shows as 1.23004590218842E+23, and right justified as if number.

I tried forcing it to use column A as string using

$('row c[r^="A"]', sheet).attr('s', '50'); //"A" is Label column

Didn't work; all it did was replace 123004590218842000000000 with 1.23004590218842E+23.

This is my Excel customization section:

buttons: [
    {
        extend: "collection",
        text: "Export",
        buttons: [
            {
                extend: 'excel',
                orientation: 'landscape',
                pageSize: 'LEGAL',
                customize: function (xlsx) {
                    var sheet = xlsx.xl.worksheets['sheet1.xml'];
                    var sheet2 = xlsx.xl['styles.xml'];

                    // use font size 10
                    var tagName = sheet2.getElementsByTagName('sz');
                    for (i = 0; i < tagName.length; i++) {
                        tagName[i].setAttribute("val", "10")
                    }
                    $('c[r=A1] t', sheet).text('Label Outcomes');

                    $('row:first c', sheet).attr('s', '2').attr('s', '32'); // first row is bold
                    
                    // This didn't help, it just made the header of this column non-bold
                    $('row c[r^="A"]', sheet).attr('s', '50'); //"A" is Label column

                    $(sheet.body)
                        .css('font-size', '10pt');

                    $(sheet.body).find('table')
                        .addClass('compact')
                        .css('font-size', 'inherit');
                },
                exportOptions: {
                    columns: [0, 1, 2, 3, 4, 5, 6, 7, 8, 10]
                },
            },

Still struggling with this issue. Anyone has any suggestions? Any jQuery datatable gurus? — NoBullMan, Feb 26 '22 at 04:18
It seems like similar to $('row:first c', sheet).attr('s', '2'); that makes first row bold; one can use $('row c[r^=A]', sheet).attr('s', '50'); but it format the column as "General" not "TExt" and I don;t know what the code for text is. Does anyone know? Or knows where documentation is on specifying rows, columns, format style, etc. (like 'row c[r^=A]' or attr('s', 'xxx')? — NoBullMan, Feb 26 '22 at 05:24

score 0 · Answer 1 · answered Feb 27 '22 at 19:10

I posted to datatable forum and got a response that solved my issue. It boils down to adding a zero-width non-joiner character (\u200c) character to the columns. It preserves sorting (date, ...).

buttons: [
    {
        extend: "collection",
        text: "Export",
        buttons: [
            {
                extend: 'excelHtml5',
                orientation: 'landscape',
                title: 'My Excel Title',
                //messageTop: 'List Success Transactions',
                //messageBottom: 'The information in this table is copyright',
                customizeData: function (data) {
                    for (var i = 0; i < data.body.length; i++) {
                        for (var j = 0; j < data.body[i].length; j++) {
                            data.body[i][j] = '\u200C' + data.body[i][j];
                        }
                    }
                },
                customize: function (xlsx) {
                    ...
                }
            },
            ...

Link to solution is here

There is also a SOF post related to this issue that includes above solution here

jQuery datatable - format column as string when exporting to Excel

1 Answers1