Reading from column custom value from Excel and parsing to DateTime or Time C#?-CodePudding

I have problem with reading specific column from Excel, type of that column is custom and if I change type it changes values. So, I am not sure how to convert in code this to can use same value like in Excel.

For example in Excel this column looks like this:

And now I want this to use in my code when I read excel file. But there is problem to compare with other hour, cause type of this column in excel is custom and if I change it to DateTime this change complete value?

I read value from Excel like this:

var loadTimeCell = (rangeSheet2.Cells[rCnt2, 2] as Excel.Range).Value2;

CodePudding user response：

TimeValue Function should do it for you. This converts a text string to a time value. Something like this would do the job:

y = rangeSheet2.Cells[rCnt2, 2].text
loadTimeCell = TimeValue(y)

Or you can also use this:

 var loadTimeCell = TimeValue(cstr((rangeSheet2.Cells[rCnt2, 2] as Excel.Range).Value2));

CodePudding user response：

I don't know what library you're using for this, but you could try this alternate library that I maintain: Sylvan.Data.Excel, which should be able to read this data. Here is a complete C# 10 sample console app showing how to read values out of a time-only formatted column:

using Sylvan.Data.Excel;

var edr = ExcelDataReader.Create("data.xlsx");
// assumes a header row with column names.
var timeIdx = edr.GetOrdinal("Time");
while (edr.Read())
{
    // read the value as a DateTime. Date component will be 0001-01-01.
    var dt = edr.GetDateTime(timeIdx);
    // Get the time of day.
    var time = dt.TimeOfDay;
    Console.WriteLine(time);
}