How do I convert a Value2 string to a DateTime (Excel Interop)

Question

I am importing data from an Excel file and converting it into a DataSet, which I have working (kind of). The problem that I am having is that two fields are Dates in Excel but when I do the import they get turned into numbers and I am not sure how to get them back to dates. The last two columns are the columns that are dates (HDate & CDate).

This is the code

    private void FillDatagrid()
    {
        Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"\\Server01\Ins\Eligible.xls");
        Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
        Excel.Range xlRange = xlWorksheet.UsedRange;

        DataTable table = new DataTable("Employees");
        table.Columns.Add("StoreID");
        table.Columns.Add("EmpID");
        table.Columns.Add("EmpName");
        table.Columns.Add("Position");
        table.Columns.Add("HDate");
        table.Columns.Add("CDate");

        int rowCount = xlRange.Rows.Count;

        for (int i = 2; i <= rowCount; i++)
        {
            table.Rows.Add(
                xlRange.Cells[i, 1].Value2.ToString(),
                xlRange.Cells[i, 2].Value2.ToString(),
                xlRange.Cells[i, 3].Value2.ToString(),
                xlRange.Cells[i, 4].Value2.ToString(),
                xlRange.Cells[i, 5].Value2.ToString(),
                xlRange.Cells[i, 5].Value2.ToString());
        }

        DataSet ds = new DataSet();
        ds.Tables.Add(table);
        dataGrid.ItemsSource = ds.Tables["Employees"].DefaultView;
    }

As far as I can tell a Value2 will only convert to a string.

Note #1 Although I am putting this data into a DataGrid in this sample I am not doing that in the actual code block. I just wanted to mention that so that it didn't seem like this could be fixed with formatting in XAML etc.

Note #2 I realize I have xlRange.Cells[i, 5].Value2.ToString() twice. I am doing so to get around the problem of column 6 have null values, which my import didn't like. I plan to come back to that after I get this problem fixed.

Note #3 When I say I am getting the date as a string I mean it is coming from Excel as a string but formatted as a number so, for instance, the cell data has a date like 6/30/2015 but it comes over to my dataset like 42185.

possible duplicate of [Reading Datetime value From Excel sheet](http://stackoverflow.com/questions/4538321/reading-datetime-value-from-excel-sheet) — CBRF23, Jul 28 '15 at 16:21

score 0 · Answer 1 · answered Jul 27 '15 at 21:16

0

What about:

table.Columns.Add("HDate", typeof(DateTime));
table.Columns.Add("CDate", typeof(DateTime));

answered Jul 27 '15 at 21:16

Yair Nevet

12,725
14
66
108

I tried that but I get a compile error. The debugger doesn't make it to that point but I suspect it is because Excel is sending my date of 6/30/2015 as 42185, which isn't a valid date. – Buck Hicks Jul 28 '15 at 12:22

score 0 · Accepted Answer · answered Jul 28 '15 at 13:55

I was able to solve this by altering my loop

        for (int i = 2; i <= rowCount; i++)
        {
            string storeId = xlRange.Cells[i, 1].Value2.ToString();
            string employeeId = xlRange.Cells[i, 2].Value2.ToString();
            string employeeName = xlRange.Cells[i, 3].Value2.ToString();
            string position = xlRange.Cells[i, 4].Value2.ToString();
            string hDate = Convert.ToString(xlRange.Cells[i, 5].Value);
            string cDate = Convert.ToString(xlRange.Cells[i, 6].Value);

            table.Rows.Add(storeId, employeeId, employeeName, position, hDate, cDate);
        }

This also took care of another problem. Which is to say the Convert.ToString(xxx.Value) handles nulls where the Value2.ToString() would not.

How do I convert a Value2 string to a DateTime (Excel Interop)

2 Answers2