Export to Excel issue when column has long integer strings

Export to Excel issue when column has long integer strings

NoBullManNoBullMan Posts: 61Questions: 17Answers: 2

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.

This question has accepted answers - jump to:

Answers

  • colincolin Posts: 15,118Questions: 1Answers: 2,583
    Answer ✓

    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

  • NoBullManNoBullMan Posts: 61Questions: 17Answers: 2

    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".

  • rf1234rf1234 Posts: 2,801Questions: 85Answers: 406
    Answer ✓

    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 :smile:

    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, ' ');
                }
            }
        }
    },
    
  • NoBullManNoBullMan Posts: 61Questions: 17Answers: 2

    Thank you all for your help. Much appreciated.

Sign In or Register to comment.