-1

I'm trying to read Excel 2007+ files in c# but all the libraries I have tried so far (OpenXML, ClosedXML and NPOI) seem unable to parse a cell with the time format correctly.

In Excel the data is formatted as Number > Time and uses '*hh:mm:ss' as it's type.

When I look at the raw value in the libraries it is appearing as 0.0416666666666667. I've followed advice from other posts which suggest using DateTime.FromOADate which (correctly) results in '30/12/1899 01:00:00'.

What I'm really stuck on is how to display the datetime object {30/12/1899 01:00:00} as it is displayed in Excel: '01:00:00'. I can see the Style.DateFormat is set to '[$-F400]h:mm:ss\ AM/PM' but how can I use this to format the DateTime object in C# as a string? The ToString() method doesn't recognise it as a valid format.

Barker1889
  • 53
  • 6

1 Answers1

0

A DateTime by definition always has a date and a time. To only have the time you would have to use a TimeSpan. Here is a quick way you can get that.

DateTime originalDateTime = DateTime.Now;
TimeSpan hoursMinutesSeconds = originalDateTime.TimeOfDay;
Tim Freese
  • 435
  • 3
  • 14
  • I understand the concept of the date and time components. What I'm struggling with is how to use the NumberFormat string from the excel file '[$-F400]h:mm:ss\ AM/PM' to change the datetime object to a string. – Barker1889 Dec 19 '15 at 10:42
  • I'm not sure you need to use the format from excel. I'm also not sure there is a way you can. There should be a way for you do get the data looking how you want without it. – Tim Freese Dec 21 '15 at 14:46
  • I think that would be true if we were in control of the format in the files. We are allowing users to upload the excel files so we have no idea what format they intend to send us without looking at the NumberFormat string. I agree that there may not be a (simple) way of using the format string, and since forcing them to use CSV isn't an option, we are considering using google drive/sheets to convert the xlsx to csv for us. – Barker1889 Dec 22 '15 at 15:17
  • Oh that makes sense. I didn't know that's why you needed that format. I will try to think of a way to do that. – Tim Freese Dec 22 '15 at 15:20