Excel customization: % with 4 decimal places

rf1234 · August 2017

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 · August 2017

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 · August 2017

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 · August 2017

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 · August 2017

@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 · August 2017

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 · 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!

LePatay · June 2018

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 · December 2019

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 · December 2019

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 · February 2021

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

Excel customization: % with 4 decimal places

Excel customization: % with 4 decimal places

Answers

Howdy, Stranger!

Categories

DataTables

Excel customization: % with 4 decimal places

Excel customization: % with 4 decimal places

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables