CODEWITHSUNDEEP
c#excel
Idanovich
Idanovich

Reputation: 11

How to convert Excel values to datetime format in C#

I am trying to extract data from an Excel worksheet using Excel Interop. The cells in the used range here contain Date/Time values. I've managed to get the values as text, but how can I convert this to a numeric or Date/Time format that can be used in comparison operations?

using System;
using Excel = Microsoft.Office.Interop.Excel;
class Program
{
    static void Main(string[] args)
    {
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        Excel.Range range;

        string str;
        int rCnt = 0;
        int cCnt = 0;

        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Open("Wind_Speed.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        range = xlWorkSheet.UsedRange;

        for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
        {
            for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
            {
                // here I can get a string value... how to make it date/time
                str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Text                                
                Console.WriteLine(str);
                Console.ReadLine();                       
            }
        }

        xlWorkBook.Close(true, null, null);
        xlApp.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);

        Console.ReadLine();
    }

Upvotes: 1

Views: 11022

Answers (2)

NNeugier
NNeugier

Reputation: 1

It's really unclear what you are asking about, but probably you will need to obtain Value, not Text 

//some code here
{
    var rangeValue = (range.Cells[rCnt, cCnt] as Excel.Range).Value
    //do whatever you need 
}

Upvotes: 0

D Stanley
D Stanley

Reputation: 152596

If the raw data is a "date" value in Excel (and not text) you should be able to pull the value and convert it to a DateTime using the DateTime.FromOADate method:

double dateValue = range.Cells[rCnt, cCnt].Value2;  // not "Text"
DateTime dateTime = DateTime.FromOADate(dateValue);

Also note that pulling data cell-by-cell using Office interop is usually much slower than if you pull all of the data in one big array then process the array in C#:

    range = xlWorkSheet.UsedRange;
    object[,] rangeDate = (object[,])range.Value;

    for (rCnt = 0; rCnt <= Array.GetLength(rangeDate, 0) ; rCnt++)
    {
        for (cCnt = 0; cCnt <= Array.GetLength(rangeDate, 1); cCnt++)
        {
           object value = rangeDate[rCnt, cCnt] ;

           ...
        }
    }

Upvotes: 7

Related Questions