CODEWITHSUNDEEP
exceltimeopenxmltype-conversion
devC
devC

Reputation: 1444

Reading excel time value using OpenXML API

I have an excel sheet which has a time column. Its time column is currently in the data type 'Time' (6:00:00PM), however, I've tried with 'Custom' data type (6:00PM) as well.

I read this cell value using openXML library as follows:

row.XCells[9].GetValue()

The value I read is .75. This is the value I see when I change the data type to number. I want to convert this to a timespan in my C# backend. How do I do that?

 var ts = TimeSpan.Parse(row.XCells[9].GetValue());

doesn't work.

Upvotes: 1

Views: 872

Answers (1)

petelids
petelids

Reputation: 12815

Excel dates and times can be converted to C# DateTime using DateTime.FromOADate. Once you have the DateTime you can use the TimeOfDay property to get a TimeSpan (the DateTime will have a date of 30-Dec-1899 which is the OLE automation base date)

TimeSpan t = DateTime.FromOADate(row.XCells[9].GetValue()).TimeOfDay;
Console.WriteLine(t.ToString(@"hh\:mm\:ss")); // prints 18:00:00

Upvotes: 1

Related Questions