Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
4341
Infragistics Excel engine to read date format with different regional settings
posted

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?

  • 48586
    posted

    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.

     

  • 48586
    posted

    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.