search

hhurz / tableExport.jquery.plugin

jQuery plugin to export a html table to JSON, XML, CSV, TSV, TXT, SQL, Word, Excel, PNG and PDF
MIT License
984 stars 714 forks source link

Exporting to Excel results in currency amounts coming through as text #358

Closed FrankEBailey closed 10 months ago

FrankEBailey commented 1 year ago

I apologise if this has been answered elsewhere, I did search the issues and documentation and didn't find anything dealing directly with this, however I freely admit to being a bit thick-headed and half-blind on a good day.

When I export my table to Excel, currency amounts come through as text, which means trying to perform any formulas like summing them fails. Is there something I need to mark the table cells with to get them to come through as currency? Is it possibly related to regional settings (I'm in South Africa however I have my PC settings set to use "." for the decimal and "," for the thousands even though SA settings are officially "," and " " for decimal and thousands respectively.

I did try adding 

data-tableexport-xlsxformatid="164"

to the table cells containing currency amounts, however this had no apparent effect.

hhurz commented 1 year ago

Sorry for the late reply. I can't reproduce your issue. Just to be sure: You are exporting to xlsx format, right? If so, and if you are using the data-tableexport-xlsxformatid option, you should also look at the mso.xls.format.currency option. This should match the currency settings of the system. And if your system settings are "," and " " for decimal and thousands, I would set the numbers.output.decimalMark and numbers.output.thousandsSeparator options that way too.