How do I fix the dollar symbol appearing at the end of amounts in exported Excel spreadsheets?

EdwinPS

This is in Excel for Mac version 15.41. The cells seem to be formatted with type "Currency" but reselecting "$ Cherokee (United States)", which seems to be the default dollar symbol here, fixes the placement.

rf1234

Hi Edwin,

I took a look at file "DataTables/Buttons .../js/buttons.html5.js"

According to this: https://datatables.net/reference/button/excelHtml5
Dollar Currency Values are built-in style 57.

This is built-in style 57:

'<xf numFmtId="164" fontId="0" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>'

As you can see it points to numFmtId 164.
This is that format id:

'<numFmt numFmtId="164" formatCode="#,##0.00_-\ [$$-45C]"/>'

I copied this into Excel: #,##0.00_-\ [$$-45C] as a custom cell format.
and entered 88 this is the result: 88.00 $

Then I tried num format 166 ([$€-2]\ #,##0.00) which is Euro currency values and entered 88 and got this:
€ 88.00.

So works as designed I would say ... Question is does it make sense to have such a different rendering. The dollar rendering would be German style with the trailing dollar sign while the Euro rendering would be US style with the leading Euro sign. Opposite rendering would at least make more sense to me ...

Of course you can edit the buttons.html5.js file accordingly but then it gets overwritten in the next release. I used the customize function to add my own styles that won't get overwritten with a new release like this:

//custom button for cashflow excel generation
    $.fn.dataTable.ext.buttons.excelCashFlow = {
        extend: 'excel', filename: 'cashFlow', sheetName: 'cashflow1', title: cashFlowTitle,
        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;
    //n1, n2 ... are number formats; s1, s2, ... are styles
            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>';
    //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 s3 = '<xf numFmtId="4" fontId="2" fillId="0" borderId="0" applyFont="1" applyFill="1" applyBorder="1" xfId="0" applyNumberFormat="1"/>'
            var s4 = '<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[0].innerHTML += n1;
            sSh.childNodes[0].childNodes[5].innerHTML += s1 + s2 + s3 + s4;
            
            var fourDecPlaces    = lastXfIndex + 1;
            var greyBoldCentered = lastXfIndex + 2;
            var twoDecPlacesBold = lastXfIndex + 3;
            var greyBoldWrapText = lastXfIndex + 4;

applying some of the styles in the customize function as well:

//two decimal places columns          
            var twoDecPlacesCols = ['D', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O'];            
            for ( i=0; i < twoDecPlacesCols.length; i++ ) {
                $('row c[r^='+twoDecPlacesCols[i]+']', sheet).attr( 's', '64' );
            }
            $('row c[r^="E"]', sheet).attr( 's', fourDecPlaces );  //% 4 decimal places, as added above
//                $('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', greyBoldWrapText );  //grey background bold, text wrapped
            $('row:last c', sheet).attr( 's', '2' );  //bold

wittich

@rf1234 thx for the post, it helped me a lot to understand the whole thing.

scottsauber

This might be super hacky.... but it works... for now!

"buttons": [
            {
                extend: 'excelHtml5',
                customize: function( xlsx ) {
                    $(xlsx.xl["styles.xml"]).find('numFmt[numFmtId="164"]').attr('formatCode', '[$$-45C] #,##0.00_-');
            }}
]

To piggy back on what @rf1234 said. The format is originally

'<numFmt numFmtId="164" formatCode="#,##0.00_-\ [$$-45C]"/>'

So what this is doing is parsing through the styles, finding 164, and prepending the currency on it.