Export to Excel issue when column has long integer strings

NoBullMan

Link to test case:
Unfortunately, it is an intranet site.
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:
I have a 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]
                },
            },

I posted to SOF site but no response in two days so I am hoping some guru here knows how to fix this.

colin

Lots of gurus here, you can't walk anywhere without tripping over them

This example from this thread should get you going. It's specifically converting that column to use strings,

Colin

NoBullMan

Thank you Colin. Grateful for all you gurus. Problem solved.

Are you aware of any documentation that explain excel formatting within the "customize" code; things such as $('row c[r^=A], sheet).attr('s', 50))?
How to access different cells or rows or columns; how to specify the attributes and what attributes 2, 32, 50, ... mean? I am assuming above means "all Axx cells".

rf1234

Are you aware of any documentation that explain excel formatting within the "customize" code; things such as $('row c[r^=A], sheet).attr('s', 50))?

https://datatables.net/reference/button/excelHtml5
Search for "Built in styles".

This is about manipulating the Excel XML using jQuery. Enjoy

If you search the forum you'll find posts on this from me and other people. Can become very cumbersome.

Like in here:

{extend: "excel",
    title:    function () { return lang === 'de' ? 'Vertragsübersicht' : 'Contract Overview' },
    filename: function () { return lang === 'de' ? 'Vertragsübersicht' : 'Contract Overview' },
    messageBottom: function() {
        return tableFooter + ctrTable.rows( {search: "applied"} ).count();
    },
    customize: function( xlsx ) {
        var sSh = xlsx.xl['styles.xml'];
        var lastXfIndex = $('cellXfs xf', sSh).length - 1;            
        var lastFontIndex = $('fonts font', sSh).length - 1; 
        var f1 = //bold and underlined font
        '<font>'+
                '<sz val="11" />'+
                '<name val="Calibri" />'+
                '<b />'+'<u />'+
        '</font>'

        var i; var y;
//n1, n2 ... are number formats; s1, s2, ... are styles
        var s1 = '<xf numFmtId="0" fontId="'+(lastFontIndex+1)+'" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                    '<alignment horizontal="center"/></xf>';         
//s3 is a combination of built in fonts 64 (2 dec places which has numFmtId="4") AND 2 (bold)
//just copied the xf of "two decimal places" and and changed the fontId based on "bold"  
        var s2 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                    '<alignment horizontal="center" wrapText="1"/></xf>';
        sSh.childNodes[0].childNodes[1].innerHTML += f1; //new font
        sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2; //new styles

        var greyBoldCentered = lastXfIndex + 1;
        var greyBoldWrapText = lastXfIndex + 2;

        var sheet = xlsx.xl.worksheets['sheet1.xml'];
    //create array of all columns (0 - N)
        var cols = $('col', sheet);

//two decimal places columns                      
        for ( i=0; i < xlsTwoDecPlacesCols.length; i++ ) {
            $('row c[r^='+xlsTwoDecPlacesCols[i]+']', sheet).attr( 's', '64' );
        }
//aktenzeichen und vertragsnummer as right aligned text
        for ( i=0; i < xlsRightAlignedCols.length; i++ ) {
            $('row c[r^='+xlsRightAlignedCols[i]+']', sheet).attr( 's', '52' );
        }

        $('row:eq(0) c', sheet).attr( 's', greyBoldCentered );  //grey background bold and centered, as added above
        $('row:eq(1) c', sheet).attr( 's', greyBoldWrapText );  //grey background bold, text wrapped
        $('row:last c', sheet).attr( 's', '2' );  //bold
    },
    exportOptions: {
        columns: ':not(.noExport)',
        modifier: { selected: null }, //make sure all records show up
        format: {
            body: function ( data, row, column, node ) {
                if (typeof data !== 'undefined') {
                    if (data != null) {
                        if ($.inArray(column, xlsTwoDecPlacesColsNumbers ) >= 0) {
                            if (lang == 'de') { //this time we use the English formatting
                                //data contain only one comma we need to split there
                                var arr = data.split(',');
                                //subsequently replace all the periods with spaces
                                arr[0] = arr[0].toString().replace( /[\.]/g, "" );
                                //join the pieces together with a period if not empty
                                if (arr[0] > ''  || arr[1] > '') {
                                    data = arr[0] + '.' + arr[1];
                                } else {
                                    return '';
                                }
                            } else {
                                data = data.toString().replace( /[\,]/g, "" );
                            }
                            //result a number still as a string with decimal . and
                            //no thousand separators
                            //replace everything except numbers, decimal point and minus
                            return data.toString().replace( /[^\d.-]/g, "" );     
                        }
                    }
                }
                //replace ampersands and other special chars
                data = data.replace(/&gt;/g, '>')
                           .replace(/&lt;/g, '<')
                           .replace(/&amp;/g, '&')
                           .replace(/&quot;/g, '"')
                           .replace(/&#163;/g, '£')
                           .replace(/&#39;/g, '\'')
                           .replace(/&#10;/g, '\n');
                //replace html tags with one space
                data = data.replace(/<[^>]*>/g, ' ');
                //replace multiple spaces and tabs etc with one space
                return data.replace(/\s\s+/g, ' ');
            },
            header: function ( data, column ) {
                //replace html tags with one space
                return data.replace(/<[^>]*>/g, ' ');
            }
        }
    }
},

NoBullMan

Thank you all for your help. Much appreciated.