C# not reading excel date from spreadsheet

Question

I uploaded an excel spreadsheet using Microsoft.Office.Interop.Excel. When I try to read cells with a date value in order to insert it into my data set, its not being recognized as a date and it comes up as a random number? this is the way that I refer to the cell:

Excel.Range startDate = objsheet.get_Range("C1:C" + lastUsedRow, System.Type.Missing);
double dbl = Convert.ToDouble(startDate);
DateTime conv = DateTime.FromOADate(dbl);
(row[3] = ((Microsoft.Office.Interop.Excel.Range)objsheet.Cells[rowIndex, 4]).Value2;)

Post part of your excel file so we can check your date format. — Kamil Budziewski, Jul 29 '13 at 14:56

score 3 · Answer 1 · edited May 23 '17 at 10:25

3

From https://stackoverflow.com/a/4538367/1397117

You need to convert the date format from OLE Automation to the .net format by using DateTime.FromOADate.
double d = double.Parse(b);  
DateTime conv = DateTime.FromOADate(d);

And I echo suggestions below that answer to use .Value instead of .Value2.

edited May 23 '17 at 10:25

Community

1
1

answered Jul 29 '13 at 14:58

Vitor M. Barbosa

3,286
1
24
36

what am i supposed to pass in as b? the range? that gives an error? – user2140832 Jul 29 '13 at 15:05
I cant seem to figure it out... I am getting such error: Unable to cast COM object of type 'System.__ComObject' to interface type 'System.IConvertible'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{805E3B62-B5E9-393D-8941-377D8BF4556B}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). – user2140832 Jul 29 '13 at 15:11

score 0 · Answer 2 · answered Jul 29 '13 at 14:59

0

row[3] = Convert.ToDateTime(((Microsoft.Office.Interop.Excel.Range)objsheet.Cells[rowIndex, 4]).Value2.ToString());

May do it for you, see this link.

answered Jul 29 '13 at 14:59

Bit

1,068
1
11
20

score 0 · Answer 3 · answered Aug 26 '14 at 08:54

In my project when I had to read data from excel, I created a method which takes cell text as input and C# DateTime as output.

public DateTime ReadDateFromExcel(string dateFromXL)
{
    Regex dateRegex = new Regex("^([1-9]|0[1-9]|1[0-2])[- / .]([1-9]|0[1-9]|1[0-9]|2[0-9]|3[0-1])[- / .](1[9][0-9][0-9]|2[0][0-9][0-9])$");
    DateTime dtParam = new DateTime();            
    if (!DateTime.TryParse(dateFromXL, out dtParam))
    {
        double oaDate = 0;
        if (Double.TryParse(dateFromXL, out oaDate))
        {
            dateFromXL = DateTime.FromOADate(oaDate).ToString("MM/dd/yyyy");
            if (!dateRegex.IsMatch(dateFromXL))
            {
                Console.Writeline("Date not in correct format");
            }
            else
            {
                dtParam = readDateFromExcel(dateFromXL);
            }
        }
        else
        {
            Console.Writeline("Date not in correct format");
        }
    }
    else
    {
        Console.Writeline("Date is in correct format");
    }
    return dtParam;
}

C# not reading excel date from spreadsheet

3 Answers3

Linked