blocks|key|840260|text|也许您可以尝试使用DateTime.FromOADate方法在Excel和.net之间进行转换。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|840261|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/system.datetime.fromoadate.aspx^0|9|J|9|J|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@$9|Q|A|R|1|S]]|E|$]]|$1|F|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

Perhaps you could try using the <a href="http://msdn.microsoft.com/en-us/library/system.datetime.fromoadate.aspx" rel="noreferrer"><code>DateTime.FromOADate</code></a> method to convert between Excel and .net.

blocks|key|1055020|text|您需要使用DateTime.FromOADate将日期格式从OLE自动化转换为.net格式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1055021|double+d+=+double.Parse(b);
DateTime+conv+=+DateTime.FromOADate(d);|code-block|syntax|javascript|1055022|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You need to convert the date format from OLE Automation to the .net format by using DateTime.FromOADate.

<pre><code>double d = double.Parse(b);
DateTime conv = DateTime.FromOADate(d);
</code></pre>

blocks|key|840349|text|或者，您可以简单地使用OleDbDataAdapter从Excel中获取数据|type|unstyled|depth|inlineStyleRanges|entityRanges|data|840350|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Or you can simply use OleDbDataAdapter to get data from Excel

blocks|key|840406|text|或者，如果您的单元格已经是一个实际的日期，则只需使用.Value而不是.Value2：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|840407|excelApp.Range[namedRange].Value
{21/02/2013+00:00:00}
++++Date:+{21/02/2013+00:00:00}
++++Day:+21
++++DayOfWeek:+Thursday
++++DayOfYear:+52
++++Hour:+0
++++Kind:+Unspecified
++++Millisecond:+0
++++Minute:+0
++++Month:+2
++++Second:+0
++++Ticks:+634970016000000000
++++TimeOfDay:+{00:00:00}
++++Year:+2013

excelApp.Range[namedRange].Value2
41326.0|code-block|syntax|javascript|840408|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Alternatively, if your cell is already a real date, just use .Value instead of .Value2:

<pre><code>excelApp.Range[namedRange].Value
{21/02/2013 00:00:00}
 Date: {21/02/2013 00:00:00}
 Day: 21
 DayOfWeek: Thursday
 DayOfYear: 52
 Hour: 0
 Kind: Unspecified
 Millisecond: 0
 Minute: 0
 Month: 2
 Second: 0
 Ticks: 634970016000000000
 TimeOfDay: {00:00:00}
 Year: 2013

excelApp.Range[namedRange].Value2
41326.0
</code></pre>

blocks|key|1055168|text|我有过类似的情况，我使用了下面的代码来让它正常工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1055169|Aspose.Cells.LoadOptions+loadOptions+=+new+Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.CSV);

Workbook+workbook+=+new+Workbook(fstream,+loadOptions);

Worksheet+worksheet+=+workbook.Worksheets[0];

dt+=+worksheet.Cells.ExportDataTable(0,+0,+worksheet.Cells.MaxDisplayRange.RowCount,+worksheet.Cells.MaxDisplayRange.ColumnCount,+true);

DataTable+dtCloned+=+dt.Clone();
ArrayList+myAL+=+new+ArrayList();

foreach+(DataColumn+column+in+dtCloned.Columns)
{
++++if+(column.DataType+==+Type.GetType("System.DateTime"))
++++{
++++++++column.DataType+=+typeof(String);
++++++++myAL.Add(column.ColumnName);
++++}
}


foreach+(DataRow+row+in+dt.Rows)
{
++++dtCloned.ImportRow(row);
}



foreach+(string+colName+in+myAL)
{
++++dtCloned.Columns[colName].Convert(val+=>+DateTime.Parse(Convert.ToString(val)).ToString("MMMM+dd,+yyyy"));
}


/*******************************/

public+static+class+MyExtension
{
++++public+static+void+Convert<T>(this+DataColumn+column,+Func<object,+T>+conversion)
++++{
++++++++foreach+(DataRow+row+in+column.Table.Rows)
++++++++{
++++++++++++row[column]+=+conversion(row[column]);
++++++++}
++++}
}|code-block|syntax|javascript|1055170|希望这能帮助some1+thx_joxin|1055171|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

i had a similar situation and i used the below code for getting this worked.. 

<pre><code>Aspose.Cells.LoadOptions loadOptions = new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.CSV);

Workbook workbook = new Workbook(fstream, loadOptions);

Worksheet worksheet = workbook.Worksheets[0];

dt = worksheet.Cells.ExportDataTable(0, 0, worksheet.Cells.MaxDisplayRange.RowCount, worksheet.Cells.MaxDisplayRange.ColumnCount, true);

DataTable dtCloned = dt.Clone();
ArrayList myAL = new ArrayList();

foreach (DataColumn column in dtCloned.Columns)
{
 if (column.DataType == Type.GetType("System.DateTime"))
 {
 column.DataType = typeof(String);
 myAL.Add(column.ColumnName);
 }
}


foreach (DataRow row in dt.Rows)
{
 dtCloned.ImportRow(row);
}



foreach (string colName in myAL)
{
 dtCloned.Columns[colName].Convert(val =&gt; DateTime.Parse(Convert.ToString(val)).ToString("MMMM dd, yyyy"));
}


/*******************************/

public static class MyExtension
{
 public static void Convert&lt;T&gt;(this DataColumn column, Func&lt;object, T&gt; conversion)
 {
 foreach (DataRow row in column.Table.Rows)
 {
 row[column] = conversion(row[column]);
 }
 }
}
</code></pre>

Hope this helps some1
thx_joxin

blocks|key|1055239|text|您可能想要尝试我在另一个thread上发布的与从excel工作表中读取日期值相关的简单函数。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1055240|它只是将单元格中的文本作为输入，并将DateTime作为输出。|1055241|我很高兴看到我为.Net开发社区提供的示例代码的改进。|1055242|下面是线程C#+not+reading+excel+date+from+spreadsheet的链接|1055243|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/17927250/c-sharp-not-reading-excel-date-from-spreadsheet/25501735#25501735|1^0|C|6|0|0|0|0|5|16|1|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|X|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|J|3|-4|5|6|7|12|8|@]|9|@]|C|$]]]|K|$L|$5|M|N|O|C|$P|Q]]|R|$5|M|N|O|C|$P|Q]]]]

You may want to try out simple function I posted on another <a href="https://stackoverflow.com/questions/17927250/c-sharp-not-reading-excel-date-from-spreadsheet/25501735#25501735">thread</a> related to reading date value from excel sheet.

It simply takes text from the cell as input and gives DateTime as output. 

I would be happy to see improvement in my sample code provided for benefit of the .Net development community.

Here is the link for the thread <a href="https://stackoverflow.com/questions/17927250/c-sharp-not-reading-excel-date-from-spreadsheet/25501735#25501735">C# not reading excel date from spreadsheet</a>

blocks|key|840543|text|另一种选择是:如果在编译时单元类型未知，并且单元格格式化为日期，则Range.Value将返回所需的DateTime对象。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|840544|public+static+DateTime?+GetAsDateTimeOrDefault(Range+cell)
{
++++object+cellValue+=+cell.Value;
++++if+(cellValue+is+DateTime+result)
++++{
++++++++return+result;
++++}
++++return+null;
}|code-block|syntax|javascript|840545|entityMap^0|X|B|1E|8|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

Another option: when cell type is unknown at compile time and cell is formatted as Date <code>Range.Value</code> returns a desired <code>DateTime</code> object.

<pre class="lang-cs prettyprint-override"><code>
public static DateTime? GetAsDateTimeOrDefault(Range cell)
{
 object cellValue = cell.Value;
 if (cellValue is DateTime result)
 {
 return result;
 }
 return null;
}
</code></pre>

when am trying to read datetime type value from excel sheet it is returning a double value.for example if want to read value <code>'2007-02-19 14:11:45.730'</code> like this, i am getting a double type value .further i am converting this double value using timespan,but not complete successfully because i am getting only this value <code>'2007-02-19 12:00:00 AM'</code> 
now i want exact same datetime value as first one. My code is like :-

<pre><code>TimeSpan datefromexcel = new TimeSpan(Convert.ToInt32((range.Cells[rCnt, cCnt] as Excel.Range).Value2), 0, 0, 0);

 DateTime inputdate = new DateTime(1900, 1, 1).Add(datefromexcel);

 arrrow2[cCnt - 1] = inputdate.ToString();
</code></pre>

Please help!!!
Thanks.

Reading Datetime value From Excel sheet

当我试图从excel工作表中读取datetime类型值时，它返回了一个double value.for示例如果想像这样读取值'2007-02-19 14:11:45.730'，我得到了一个双精度类型值.further我正在使用timespan转换这个双精度值，但没有成功完成，因为我只得到了这个值'2007-02-19 ...

问从Excel工作表中读取日期时间值
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从Excel工作表中读取日期时间值EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问从Excel工作表中读取日期时间值
EN