Hello Support,We are using Infragistics Excel engine to read and write the values from Excel. We want to write a date values in the a specific column with the date format as set in the regional settings. Also we want that when reading the date values from these columns, the values should be read in the format set in the regional settings on the machine. However, we found that the dates values are not read correctly when read from different machine having different regional settings.FOr e.g. suppose from a machine having regional setting set to "English (New Zeland)" with the date format as "mm/dd/yyyy", when we open the excel file from a machine having regional settings as "English (United States)" with date format as "dd/mm/yyyy", some of the date values are not read correctly.We wanted to know is there any property in the Infragstics Excel Engine so that when writing to excel we can set cell format to default date (* format) or set the "locale" of the cell. And later when we open and read the excel file it would pick up and read the dates in the format set on the machine?
Hello,
I am just checking about the progress of this issue. Let me know If you need any further assistance on this issue?
Thank you for using Infragistics Components.
Hello,
When you export UltraGrid with UltraGridExcelExporter, exporter exports DateTime columns in DatetTime data type. So actual value of the DateTime cell in excel contains a value of double type that represent the date (this doesn’t depends on the culture). So when you read from this Excel you should read this double value and to construct your DateTime based on this value. Once you have get the DateTiem object you could format it based on the culture you want. Here is very simple code that demonstrates this approach:
Workbook wb = Workbook.Load("Test.xlsx");
Worksheet ws = wb.Worksheets[0];
object val = ws.Rows[2].Cells[0].Value;
//construct DateTime object based on the Excel cell’s DateTime value
DateTime dd = DateTime.FromOADate((double)val);
Debug.WriteLine(dd);
Please let me know if you have any further questions.