I am trying to read an excel file in European date format and I noticed that when Aspose reads the date the formatting is lost.
My spreadsheet has this date: 02.10.2014
getting the value using the code worksheet.Cells(row, col).StringValue returns 02/10/2014.
So trying to format that date value in european format fails. Is there a way to get the exact date value displayed in my spreadsheet which is using period(.) as the date separator?
Well, as you are using some older version of the product which might not support your needs well. But, in recent versions of the product e.g (versions >= v7.x. (v8.0.0 latest)), it will suit your requirements well. Please try our latest version of the product and set the region before getting the value, it would work fine.
I will brought this to their attention if getting a newer version can be an option for us.
But just to clarify, are you saying that there is no way I can correct the date format retrieved with the version I currently have? that it is somehow a limitation.
We have evaluated your presented scenario and we suspect that the problem you are having is due to the locale settings, and you can avoid it by setting the CultureInfo for the workbook in question. Please check the below provided code snippet for your kind reference.
C#
var book = new Workbook(myDir + “book1.xls”);
book.Settings.CultureInfo = System.Globalization.CultureInfo.GetCultureInfo(“de-DE”);
Console.WriteLine(book.Worksheets[0].Cells[“A1”].StringValue);
As you are using a very old version of the Aspose.Cells for .NET API, therefore the presented problem could also be a bug. In case the above solution does not work, please provide us the sample spreadsheet so we could test it against the latest version of the API on our end.
I have seen this example from other posts and tried to check if my version already supported the CultureInfo, but unfortunately Settings is not accessible from my workbook, so this was not able to help me.
I have attached an example of the spreadsheet that has the European date format. My local setting is currently set to German (Germany) that has the date format dd.mm.yyyy.
I am afraid, you have to use our latest versions of the product (e.g v8.0.0 ) as there are some APIs which might be missing in older versions of the product. Our new versions are enhanced to support retrieving DateTime values based on locales and regional settings accordingly. I have tested your scenario/ case using your template file with the following sample code and it works fine.
e.g
Sample code:
var book = new Workbook(“e:\test2\SampleSpreadsheet.xlsx”);