CODEWITHSUNDEEP
c#exceldatetimetime
Nguyễn Văn Quang
Nguyễn Văn Quang

Reputation: 123

Read hh:mm:ss in excel file using c#

I have a excel file contain hour:minute:second (example:12:20:00)
I set format cells is hh:mm:ss in custom catelogy.
I am using C# to read hour:minute:second in this file.
But I received a string contain "12/30/1899 12:20:00"

Why? Can anybody answer?
How to resolve this problem?

My code:

string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileExcelCapture.Text + ";Extended Properties='Excel 8.0;HDR=Yes;'";
using (OleDbConnection connection = new OleDbConnection(con))
{
    connection.Open();
    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
    using (OleDbDataReader rows = command.ExecuteReader())
    {
        while (rows.Read())
        {
            var nameChannel = rows[0];
            string HMS = rows[1].ToString();//Result is "12/30/1899 12:20:00"
        }
    }
}

Thank
Quang

Upvotes: 0

Views: 3730

Answers (7)

Usama Tariq
Usama Tariq

Reputation: 336

Format hh:mm:ss is considered as DateTime field in excel, so when you read it in c#, instead of string you get a DateTime object. And since DateTime object also has date with it, even if not specified, ToString() function provides complete date and time converted to string. Try this to solve your problem:

string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileExcelCapture.Text + ";Extended Properties='Excel 8.0;HDR=Yes;'";
using (OleDbConnection connection = new OleDbConnection(con))
{
    connection.Open();
    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
    using (OleDbDataReader rows = command.ExecuteReader())
    {
        while (rows.Read())
        {
            var nameChannel = rows[0];
            string HMS = (rows[1] as DateTime).TimeOfDay.ToString();
        }
    }
}

Upvotes: 1

Matthias
Matthias

Reputation: 280

The value is interpreted as DateTime-Object. Simply parse it as such and call datetime.ToLongTimeString()

The reason why you get a date with year 1899 is that C# is interpreting the value as time and treating it like a DateTime object. But since there is no date privided, he takes the earliest date possible.

Upvotes: 0

Nguyễn Văn Quang
Nguyễn Văn Quang

Reputation: 123

I can resolve my problem using following code 

DateTime dt = DateTime.Parse(rows[1].ToString());
string HMS = String.Format("{0:HH:mm:ss}",dt);

but I don't understand ,
Why contain in excel file is different string in C#?.

Upvotes: 2

michal.pawlowski
michal.pawlowski

Reputation: 54

In the cell there was no information about the date, so c# filled it with the day 0

What you should do is use

String.Format("HH:mm:ss",cellValue);

or

DateTime time;
time.TimeOfDay.ToString("HH:mm:ss")

Upvotes: 0

ígor
ígor

Reputation: 1164

Try this:

double d = double.Parse(workSheet.Cells[1, 1].value(););
DateTime conv = DateTime.FromOADate(d);

Upvotes: 0

Oscar Bralo
Oscar Bralo

Reputation: 1907

Only add a new step! If you receive "12/30/1899 12:20:00", then add at the end .Split(' ')[1] and this will return "12:20:00".

Upvotes: 0

Sameer
Sameer

Reputation: 2171

Format cell value using String.Format. When you read from excel it contains DateTime value so format as per you requirement.

String.Format("HH:mm:ss",cellValue);

In your case use as below to get only time part from cell.

rows[1].ToString("HH:mm:ss");

Upvotes: 0

Related Questions