We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

ExportDataTableAsString Decimal Separator with Numeric Column

Hi. I’m having a problem with ExportDataTableAsString and how it interprets strings.

My Excel file has number columns, and the system settings are set to European (with a comma as the decimal separator), but the Excel settings are set to use a period as the decimal separator.

When I do ExportDataTableAsString, it seems to use the system separator (a comma) instead of a period as it should. This happens no matter what data type I set the column to (General, Numeric, Text, etc.). If the column type is set to Text, why does ExportDataTableAsString bring the column in as text as it’s displayed/stored (with the periods as separators).

Is there any way to import a numeric column to us the Excel decimal separator definition instead of the system setting? It is more difficult for us to have our users change their system setting, as that requires an install and reboot. However, changing the decimal separator in Excel is easy and can even be done via macro.

Thank you.

Hi,

Could you post your sample code here or preferably a sample application to show the issue.
Moreover, I think you may use ExportDataTable() method instead of ExportDataTableAsString(), if you want to export data with its types (e.g numeric, text etc.).

Thank you.

Hi.

I am using ExportDataTableAsString because we are dealing with large or very large Excel files with columns that are mostly numbers, but may be alphanumeric. For example, the first 20,000 rows of a column may be numeric, but the 20,001th record may have letters. My understanding (and it has been a while) was that ExportDataTableAsString was the only way to get around the JET autodetection, which would detect that column as Numeric and then reject the row with letters.

I’ll post an example later this morning.

I have an example in VS2008/.NET 3.5 which I’m attaching here. Our actual application is much more complex, but this reproduces the issue with minimal code. I removed my Aspose.Cells.lic file since this is being posted

To see the problem I’m showing, you will need to have the International settings however:

(this is Windows XP)
In Start -> Control Panel -> Regional and Language Options
In the Regional Options tab, set both drop-downs to a European language such as German (Germany)
In the Advanced tab, set the drop-down to German (Germany)
Note: Making this change will require a reboot

Then in Excel (this is Excel 2003):
Under Tools -> Options -> International, uncheck “Use system separators” and Put . as the decimal separator and , as the thousands separator (i.e. U.S. separators).

If you open the test file in Excel, you will see the decimal values in the second column (the FTE column) display as US numbers. I have the column formatted as text also, but this is a problem no matter how the cells are formatted.

In this case, I would like the column be input as a string as it’s saved in the file (so the decimals should be “0.5” and not “0,5”). Also note the first column is an example as I was talking about in the first post (all of the early rows are numeric, but there is an alphanumeric value towards the end).

In the example code, it does two things with the text file. First it reads it in using ExportDataTableAsString, and you can see the international problem. Then it reads it in using ExprotDataTable and you can see the typing problem I explain above (it guesses that the first column is numeric and it throws an exception on the last row, which has an alphanumeric value in that column).

Thanks.

Hi,

Thanks for providing us the sample project with details.

We will look into your issue and get back to you soon.
Your issue has been logged into our issue tracking system with an id: CELLSNET-19537.

Thank you.

Hi,

After further analysis, we cannot support this setting for MS Excel. We just simply format double value with double.ToString() method.
Please change your system setting to:
Click Start -> Control Panel -> Regional and Language Options. In the Regional Options tab, set both drop downs to a European language such as German (Germany).
Now click customize button. In the Advanced tab, set the drop-down to German (Germany).

Thank you.

Thanks. I’m familiar with the Windows settings, but we need a solution that doesn’t involve changing those because:

1) It affects all applications, and users don’t want to change their entire computer language/regional settings just for our application

2) Changes here require a reboot

If there’s no workaround in Aspose then we’ll look for another solution on the database end.

Hi,


<o:p></o:p>

We have found a solution for your issue. Please post your template files which contain your desired number formats you will use. We cannot support all number formats soon because there are too many formats in MS Excel. We will support your most important number formats at first.


Thank you.

Hi. By “template” do you just mean the number formats? Right now we just have four formats we need to support:

US w/out thousands: 1000.25 or 1000000000.2555
European w/out thousands: 1000.25 or 100000000,2555
US w/thousands: 1,000.25 or 1,000,000.2555
European w.out thousands: 1.000,25 or 1.000.000,2555

Thanks!

I was thinking about this and I wanted to clarify a little bit more how we’d like it to work (since I’m not clear on what your fix/workaround will be).

Ultimately, we’d like to be able to import a numeric column with the format we specify (the first one above, period for decimal separator and no thousands separator).

This can be done by changing the separator to a period in Excel, but ExportDataTableAsString ignores that setting. It would be nice if it brings in the numbers as they are displayed in Excel (in accordance with Excel’s decimal and thousands separator).

Alternately, we could also use ExportDataTable if it’s possible to use that and specify the column data types (to get around the case of a column that is mostly numbers but has a couple string values).