Save a cell with number having comma separator for 1000's

[ashwin-ragha1]

Hi,

I'm having the following files in my page ...
xlsx.core.min.js
Blob.js
FileSaver.js
Export2Excel.js

And I'm calling the function export_table_to_excel by passing html table which has a cell that contains a number with comma separator... e.g. 1,000.00

When I open the exported excel file, I find that this cell is left aligned and I want to make it right aligned.

Please help.

[SheetJSDev]

@ashwin-ragha1 Export2Excel.js is interpreting the cell as text. The relevant line in the generateArray function is

            if(cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;

This is a super simple check, and it works for very basic data like 10000 but will not work for numbers like "1,000.00". There are simple workarounds for special cases, such as commas:

            var cellValueNoCommas = cellValue.replace(/,/g,"");
            if(cellValue !== "" && cellValueNoCommas == +cellValueNoCommas) cellValue = +cellValueNoCommas;

If you had a list of cases to consider, we could devise an appropriate technique.

The larger problem (of determining whether a string of characters is text or numeric or datetime) is much more challenging since it would have to be locale-specific (for example, in some locales, the thousands separator is "." and the decimal separator is ",")

[ashwin-ragha1]

Hi,
Thanks for your quick reply...
Currently I'm just dealing with English US locale so I don't have other cases.

I modified your suggestion a bit as I'm also having other cell values with a comma which are string.

var cellValueNoCommas = cellValue.replace(/,/g, "");
var num = parseFloat(cellValueNoCommas);
if (!isNaN(num)) {
if (cellValue !== "" && cellValueNoCommas == +cellValueNoCommas) cellValue = +cellValueNoCommas;
}
else {
if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
}

Now even though the values in that particular column are right - aligned, I lose the comma.

I would like to export the values with a comma. Is there any way of doing that?

[ashwin-ragha1]

With the same files as above -

xlsx.core.min.js
Blob.js
FileSaver.js
Export2Excel.js

I also notice that when we run the export code in IE9, it doesn't work. It throws an error ArrayBuffer is undefined. So is there an alternate way to get it working in IE9?

[thisissami]

Hey I have a similar issue to this, where commas are causing issues (though I'm not trying to export - just trying to read). @ashwin-ragha1 - are the files that you mentioned part of this repository or taken from elsewhere? @SheetJSDev - if I wanted to solve my issue, should I be looking into the xlsx.js file in the dist folder?

[SheetJSDev]

To write cells with thousands separators, after parsing you can set the individual cell's z property to "#,##0.00. This will use thousands separator and will force 2 decimal places.