BugReport: Buttons - Excel - Currency Symbol Appearing on Right

MarkS

Exporting to excel is rendering currency strings (eg. $1,234) with the symbol '$ Cherokee (Cherokee)'; resulting in the output of '1,234 $'.

This person is experiencing the same: https://datatables.net/forums/discussion/39707/export-to-xlsx-with-customized-styles

allan

That's how Excel formats currency. If you click on the cell and look in the editing box at the top of the page, you'll be able to see that it contains just the number. The currency symbol is added by Excel.

Allan

mrmccrac

If you select one of these sells that was formatted as "$ Cherokee" from datatables and format it with something like "$ English", it will put the $ to the left. Switching it back to "$ Cherokee" doesn't put it back again to the right, so it's a mystery to me why the $ symbol is initially right-aligned.

mscarchilli

I am having the same issue. Telling the user/client to reformat large spreadsheets being exported is not an option, nor a real solution as clients don't want to hear excuses, they want what they want. I'm surprised more people haven't brought up this issue.

Another fun one has to do with percentages. If I export the body it is formatted as a percentage in the spreadsheet as 1%, but when it exports the footer it exports it as a decimal (0.01). Why would it format things differently between the body and footer when there is no difference between the number or string being exported?

kthorngren

The Excel export option has many built in styles:
https://datatables.net/reference/button/excelHtml5#Built-in-styles

If the built in styles don't meet your needs you can expand them by appending the styles you need. The built in style sheet uses number format id 57 for dollars. I'm not sure why that ends up as 1,234 $ in Excel. However with a few lines of code the export can be customized to represent this in the format desired. I put together an example using the information found in the link of original post:
http://live.datatables.net/sojarepe/1/edit

It creates a new numFmt 170 with the format $ #,##0.00 and appends to the built in styles. The export customize function assigns the new style to the Salary column. You can apply the style to the full column in one line (commented out) or through a loop to control which rows are affected. In the example I'm skipping the header and footer rows.

If I export the body it is formatted as a percentage in the spreadsheet as 1%, but when it exports the footer it exports it as a decimal (0.01)

It seems footers are processed separately from the body and may not have the style rules applied. Allan may comment further on this. You can apply styles to the footer (or header) as desired. In the same example I show how to set the style of the Salary footer by using the builtin style 40 (Normal text, green background, thin black border). I could have used the newly created format. You can use one of the built in percent formats (56 or 60) to format your footer. I found the footer row number by adding two to table.page.info().recordsTotal.

Kevin

allan

but when it exports the footer it exports it as a decimal (0.01). Why would it format things differently between the body and footer when there is no difference between the number or string being exported?

There really shouldn't be any difference. That sounds like a bug to me.

If you have an example that I can use to debug it, that would be useful, otherwise I'll try to put one together when I get a chance.

Allan

kthorngren

You can use this as a test case.
http://live.datatables.net/yagoyele/1/edit

Its not percentages but the Salary column. I'm totaling the Salary column into the footer and included it in the export. There is no formatting done by the export. The body is formatted as 1,234 $ but the footer is General in Excel.

Kevin

allan

Nice one - thanks Kevin!

Allan

allan

As I'm sure no one will be surprised, this isn't quite as easy as might have been hoped...

This is how Buttons does its output for the footer:

            addRow( data.footer, rowPos);
            $('row:last c', rels).attr( 's', '2' ); // bold

addRow will actually format the cell correctly as currency, percentage, or whatever, but the next line overwrites that setting the styling to bold and thus removing the formatter.

The way the Excel styling works is that you have to build the font and formatter into a styling option which can then be applied. So in this simple case we'd both a plain bold style and a bold style with a percentage formatter. That's fair enough, but we'd also need bold styles for the other formatters, and the background colours, and borders, and...

I've resisted writing an abstraction layer to the Excel file building thus far, but I think this is going to be the one that pushed me over the edge to actually creating it. I do consider this a bug, but fixing it isn't going to be quick and easy I'm afraid.

Allan

NET247

So I've been dealing with this issue as well, and the simplest solution I could come up with is as follows:

customize: function( xlsx ) {
                $(xlsx.xl["styles.xml"]).find('numFmt[numFmtId="164"]').attr('formatCode', '[$$-en-AU]#,##0.00;[Red]-[$$-en-AU]#,##0.00');
}

This will format the currency with dollar symbols to the left, two decimal places and when its a negative amount it will show in red in excel.