Excel customization: % with 4 decimal places

rf1234

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?

allan

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

rf1234

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

rf1234

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;
                }
            }
        }
    };

rf1234

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

allan

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

rf1234

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!

LePatay

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!

itajackass

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 ?

rf1234

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"/>'+
.....

ramboniq

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');