Excel customization: % with 4 decimal places

Excel customization: % with 4 decimal places

rf1234rf1234 Posts: 2,351Questions: 76Answers: 339

I am using the customize callback for Excel https://datatables.net/reference/button/excelHtml5

//custom button for cashflow excel generation
$.fn.dataTable.ext.buttons.excelCashFlow = {
    extend: 'excel', filename: 'cashFlow', sheetName: 'cashflow1',
    customize: function( xlsx ) {
            var sheet = xlsx.xl.worksheets['sheet1.xml'];
            $('row c[r^="G"]', sheet).attr( 's', '60' );  //% 1 dec. place
        },
    exportOptions: {
        format: {
            body: function ( data, row, column, node ) {
                // do something
            },
            header: function ( data, column ) {
                // do something
            }
        }
    }
};

I am also using the built in styling option 60 wich is % with one decimal place. My problem is that I need % with 4 decimal places. How can I get this done? Any ideas?

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 56,819Questions: 1Answers: 9,031 Site admin

    There isn't a built in 4 dp percentage number formatter in the open spreadsheet spec, so you'd need to define your own. You can do that by adding a numFmt tag in here and then make use of that formatter by added a new xf in here.

    I would suggest that you add those two tags in the customize callback so you don't need to modify the library every time you update.

    Allan

  • rf1234rf1234 Posts: 2,351Questions: 76Answers: 339

    Thanks Allan, tried that yesterday as per this post by @j e harms https://datatables.net//forums/discussion/39707
    but I couldn't get it working. I gave up ...

  • rf1234rf1234 Posts: 2,351Questions: 76Answers: 339

    With the help of @bQt in this post https://datatables.net/forums/discussion/comment/116516/#Comment_116516 I got it working eventually. I added some inline comments. Hopefully this helps others too.

    I added one new number format (percentage with 4 decimal places) and two styles (the style using the new number format and another style: bold and centered text, grey background which actually is a combination of the built in styles 7 and 51).

    The numFmtId is arbitrary. Just take care you don't use a number that is already being used in an existing numFmt. For the styles: Since 67 styles (0 - 66) are currently built in I added styles 67 and 68. Whenever "buttons.html5.js" gets changed in a new release this may need maintenance! That's actually the only thing I dislike about this solution.

    Here is my custom Excel export button with the additional styles and custom export options:

    //custom button for cashflow excel generation
        $.fn.dataTable.ext.buttons.excelCashFlow = {
            extend: 'excel', filename: 'cashFlow', sheetName: 'cashflow1',
            customize: function( xlsx ) {
    // see built in styles here: https://datatables.net/reference/button/excelHtml5
    // take a look at "buttons.html5.js", search for "xl/styles.xml"
    //styleSheet.childNodes[0].childNodes[0] ==> number formats  <numFmts count="6"> </numFmts>
    //styleSheet.childNodes[0].childNodes[1] ==> fonts           <fonts count="5" x14ac:knownFonts="1"> </fonts>
    //styleSheet.childNodes[0].childNodes[2] ==> fills           <fills count="6"> </fills>
    //styleSheet.childNodes[0].childNodes[3] ==> borders         <borders count="2"> </borders>
    //styleSheet.childNodes[0].childNodes[4] ==> cell style xfs  <cellStyleXfs count="1"> </cellStyleXfs>
    //styleSheet.childNodes[0].childNodes[5] ==> cell xfs        <cellXfs count="67"> </cellXfs>
    //on the last line we have the 67 currently built in styles (0 - 66), see link above
                
                var sSh = xlsx.xl['styles.xml'];
                var n1 = '<numFmt formatCode="##0.0000%" numFmtId="300"/>';
                var s1 = '<xf numFmtId="300" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>';
                var s2 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                '<alignment horizontal="center"/></xf>';
                sSh.childNodes[0].childNodes[0].innerHTML = sSh.childNodes[0].childNodes[0].innerHTML + n1;
                sSh.childNodes[0].childNodes[5].innerHTML = sSh.childNodes[0].childNodes[5].innerHTML + s1 +s2;
                
                var sheet = xlsx.xl.worksheets['sheet1.xml'];
                //numeric columns except for rate get thousand separators
                $('row c[r^="F"]', sheet).attr( 's', '64' );
    //                $('row c[r^="G"]', sheet).attr( 's', '60' );  //% 1 dec. place
                $('row c[r^="G"]', sheet).attr( 's', '67' );  //% 4 decimal places, as added above
                $('row c[r^="I"]', sheet).attr( 's', '64' );
                $('row c[r^="J"]', sheet).attr( 's', '64' );
                $('row c[r^="K"]', sheet).attr( 's', '64' );
                $('row c[r^="L"]', sheet).attr( 's', '64' );
    //                $('row c', sheet).attr( 's', '25' ); //for all rows
                $('row:eq(0) c', sheet).attr( 's', '68' );  //grey background bold and centered, as added above
                $('row:eq(1) c', sheet).attr( 's', '7' );  //grey background bold
            },
            exportOptions: {
                format: {
                    body: function ( data, row, column, node ) {
                        // Strip $ from salary column to make it numeric
                        if (typeof data !== 'undefined') {
                            if (data !== null) {  
                                if ( column > 11 ) {
                                    return '';  //get rid of the changed manually column
                                }
                                if (column === 5 || column === 6 || column > 7) {
                                    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].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.replace( /[\,]/g, "" );
                                    }
                                    //result a number still as a string with decimal . and
                                    //no thousand separators
                                    //replace everything except numbers, decimal point and minus
                                    data = data.replace( /[^\d.-]/g, "" ); 
                                    //percent must be adjusted to fraction to work ok
                                    if (column == 6) {
                                        if (data !== '') {
                                            data = data / 100;
                                        }
                                    }
                                    return data;                                
                                }
                            }
                        }
                        return data;
                    },
                    header: function ( data, column ) {
                        if (typeof data !== 'undefined') {
                            if (data !== null) {
                                if ( column > 11 ) {
                                    return '';  //get rid of the changed manually column
                                }
                            }
                        }
                        return data;
                    }
                }
            }
        };
    
  • rf1234rf1234 Posts: 2,351Questions: 76Answers: 339

    @allan: Would you have an idea how this could be avoided? (see above as well):

    "For the styles: Since 67 styles (0 - 66) are currently built in I added styles 67 and 68. Whenever "buttons.html5.js" gets changed in a new release this may need maintenance! That's actually the only thing I dislike about this solution."

  • allanallan Posts: 56,819Questions: 1Answers: 9,031 Site admin
    Answer ✓

    Yes, don't hard code the number, just count the number of elements instead: $('xf', xlsx.xl['styles.xml']).length or similar.

    The Excel export customisation is something I'm thinking of spinning out into a separate project. Might be a good one for an intern :).

    Allan

  • rf1234rf1234 Posts: 2,351Questions: 76Answers: 339
    edited August 2017

    This XML stuff is driving me crazy. Makes me overlook even basic things. Sorry!
    Ok I am counting the xfs now (whatever that means). Found that there are also xfs in
    <cellStyleXfs> and more of them in <cellXfs> but I am only interested in the latter. Hence I changed the selector a bit:

    $('cellXfs xf', xlsx.xl['styles.xml']).length
    

    So this is my code now (only the customize part, the rest is unchanged):

    customize: function( xlsx ) {
    // see built in styles here: https://datatables.net/reference/button/excelHtml5
    // take a look at "buttons.html5.js", search for "xl/styles.xml"
    //styleSheet.childNodes[0].childNodes[0] ==> number formats  <numFmts count="6"> </numFmts>
    //styleSheet.childNodes[0].childNodes[1] ==> fonts           <fonts count="5" x14ac:knownFonts="1"> </fonts>
    //styleSheet.childNodes[0].childNodes[2] ==> fills           <fills count="6"> </fills>
    //styleSheet.childNodes[0].childNodes[3] ==> borders         <borders count="2"> </borders>
    //styleSheet.childNodes[0].childNodes[4] ==> cell style xfs  <cellStyleXfs count="1"> </cellStyleXfs>
    //styleSheet.childNodes[0].childNodes[5] ==> cell xfs        <cellXfs count="67"> </cellXfs>
    //on the last line we have the 67 currently built in styles (0 - 66), see link above
    
        var sSh = xlsx.xl['styles.xml'];
        var lastXfIndex = $('cellXfs xf', sSh).length - 1;
    
        var n1 = '<numFmt formatCode="##0.0000%" numFmtId="300"/>';
        var s1 = '<xf numFmtId="300" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>';
        var s2 = '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyAlignment="1">'+
                    '<alignment horizontal="center"/></xf>';
        sSh.childNodes[0].childNodes[0].innerHTML += n1;
        sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2;
    
        var fourDecPlaces = lastXfIndex + 1;
        var greyBoldCentered = lastXfIndex + 2;
    
        var sheet = xlsx.xl.worksheets['sheet1.xml'];
        //numeric columns except for rate get thousand separators
        $('row c[r^="F"]', sheet).attr( 's', '64' );
    //                $('row c[r^="G"]', sheet).attr( 's', '60' );  //% 1 dec. place
        $('row c[r^="G"]', sheet).attr( 's', fourDecPlaces );  //% 4 decimal places, as added above
        $('row c[r^="I"]', sheet).attr( 's', '64' );
        $('row c[r^="J"]', sheet).attr( 's', '64' );
        $('row c[r^="K"]', sheet).attr( 's', '64' );
        $('row c[r^="L"]', sheet).attr( 's', '64' );
    //                $('row c', sheet).attr( 's', '25' ); //for all rows
        $('row:eq(0) c', sheet).attr( 's', greyBoldCentered );  //grey background bold and centered, as added above
        $('row:eq(1) c', sheet).attr( 's', '7' );  //grey background bold
    },
    

    Thanks a lot for helping me out again, Allan!

  • LePatayLePatay Posts: 20Questions: 3Answers: 1

    Thanks for this practical case.

    I ran into several more browser-related issues with this, I summarized the best way to go in this StackOverflow answer: https://stackoverflow.com/a/50719888/5426777.

    Hope it will help you!

  • itajackassitajackass Posts: 109Questions: 34Answers: 3

    tried today your code @rf1234 .... it seems
    //styleSheet.childNodes[0].childNodes[4]
    //styleSheet.childNodes[0].childNodes[5]

    has index inverted.... 5 is 4 and 4 is 5..... is it a typo error or some changes are done in the structure ?

  • rf1234rf1234 Posts: 2,351Questions: 76Answers: 339

    I just double checked with file "buttons.html5.js" from the buttons extension version 1.6.1 and everything is ok and not inverted. The cellStyleXfs stuff comes before cellXfs.

    .....
    '</borders>'+
    '<cellStyleXfs count="1">'+
        '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" />'+
    '</cellStyleXfs>'+
    '<cellXfs count="67">'+
        '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1"/>'+
    .....
    
  • ramboniqramboniq Posts: 1Questions: 0Answers: 0

    Much simpler solution below. Can be added in .customize and you can change form as per your wish.
    It creates a new format and adds it to existing one. The identifies the cell format (mine is 9 but it could be 167 in your case) that applies to what data you give it and changes the the number formatting that corresponds to it.

    var sSh = xlsx.xl['styles.xml'];
    //below could be replaced with use of .innerHtml but that doesn't work in IE
    var newPercentageFormat =sSh.childNodes[0].childNodes[0].childNodes[3].cloneNode(false);
    newPercentageFormat.setAttribute('formatCode','##0.00%');
    newPercentageFormat.setAttribute('numFmtId','180');
    sSh.childNodes[0].childNodes[0].appendChild(newPercentageFormat);
    
    $(sSh).find('numFmts').attr('count', '7');
    $(sSh).find('xf[numFmtId="9"]').attr('numFmtId', '180');
    
This discussion has been closed.