在转换Excel时格式柱到C＃的DateTime [英] Convert time-formatted column in excel to C# DateTime
问题描述
我目前使用Excel C＃库（Microsoft.Office.Interop.Excel）来读取Excel电子表格到我的C＃应用程序。
I am currently using the Excel C# libraries (Microsoft.Office.Interop.Excel) to read an excel spreadsheet into my C# application.
我最初尝试读取所有单元格作为其原始数据，但发现日期格式单元格给我一个5位数的整数，时间格式的单元格返回小数。所以我发现你可以使用内置到Excel的C＃库中的日期转换方法，像这样：
I initially tried to read all the cells as their raw data, but found that Date-formatted cells were giving me a 5-digit integer, and time-formatted cells were returning a decimal. So I then found out that you can use a date-conversion method built into Excel's C# library, like so:
DateTime excelDate = (DateTime)ExcelCalcValue.ExcelDateToDateTime(workbook, Double.Parse(cell.Value.ToString()));
output = excelDate.ToString("yyyy-MM-dd HH:mm");
通过使用各种测试表调试我的应用程序，我已经能够记录各种格式字符串当它们以不同的方式格式化时返回。如下：
Through debugging my application with various test sheets, I have been able to record the various format strings that cells return when they are formatted in different ways. These are below:
(WorksheetCell.CellFormat.FormatString)
Times
[$-F400]h:mm:ss\\ AM/PM
hh:mm:ss;@
h:mm:ss;@
[$-409]hh:mm:ss\\ AM/PM;@
[$-409]h:mm:ss\\ AM/PM;@
Dates
m/d/yy
[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy
dd/mm/yyyy;@
dd/mm/yy;@
d/m/yy;@
d\\.m\\.yy;@
yyyy\\-mm\\-dd;@
[$-809]dd\\ mmmm\\ yyyy;@
[$-809]d\\ mmmm\\ yyyy;@
使用这些，我现在可以可靠地确定excel中单元格的格式化风格中。使用早期的代码，我可以检测日期格式的单元格，并以DateTime格式返回正确的数据。
Using these, I can now reliably determine the formatting style of a cell in excel. Using the earlier code, I can detect a date-formatted cell and return the proper data in DateTime format. However, I cannot see an equivalent function for converting time-formatted cells.
我得到一个 0.58368055555555554 的结果我读取一个格式为 [$ - F400] h：mm：ss\\ AM / PM 的单元格。我完全不知道如何将这转换为 DateTime ，或者这个float代表什么。
I get a result of 0.58368055555555554 when I read a cell time-formatted as [$-F400]h:mm:ss\\ AM/PM. I have absolutely no idea how to convert this into a DateTime, or indeed what this float represents.
建议一种将时间格式的excel单元格（存储为一个奇怪的浮点数）转换为正确的 DateTime 变量的方法？
Can anyone suggest a method of converting time-formatted excel cells (which are stored as a strange float) into the correct DateTime variable?
推荐答案
正如FrankPI所说，使用 DateTime.FromOADate（）。您将使用此函数与Excel单元格的原始数据 - 不需要解析格式。
As FrankPI said, use DateTime.FromOADate(). You would use this function with the raw data from an Excel cell - there is no need to parse the format.
Excel以双精度编码其日期和时间。积分部分表示1900年1月1日以后的天数。分数部分表示从所参考的那天的午夜起的时间。例如：
Excel encodes its dates and times in a double. The integral portion represents the days after January 1, 1900. The fraction part represents the time since midnight of the day referenced. For example:
1.5是1900年1月1日@中午
和
41507.25 = 2013年8月21日@ 6:00 am
有关详细信息，请参阅此函数的MSDN文档：
Refer to the MSDN docs on this function for more information:
http://msdn.microsoft.com/en-us/library/system.datetime.fromoadate.aspx
这篇关于在转换Excel时格式柱到C＃的DateTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
