从 Excel 表中读取日期时间值

当我试图从 Excel 表中读取 datetime 类型值时，它返回一个 double value。例如，如果想像这样读取值 '2007-02-19 14:11:45.730'，我就会得到一个 double 类型值。我进一步转换这个双重价值使用时间跨度，但没有完成成功，因为我只得到这个值 '2007-02-19 12:00:00 AM'
现在我想要和第一个完全一样的日期时间值。我的代码是这样的:-

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();

救命啊! ! ！ 谢谢。

155713 次浏览
小开

Perhaps you could try using the DateTime.FromOADate method to convert between Excel and .net.

小开
最佳答案

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

double d = double.Parse(b);
DateTime conv = DateTime.FromOADate(d);
小开

Or you can simply use OleDbDataAdapter to get data from Excel

小开

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

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
小开

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

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]);
}
}
}

Hope this helps some1 thx_joxin

小开

Reading Datetime value From Excel sheet : Try this will be work.

string sDate = (xlRange.Cells[4, 3] as Excel.Range).Value2.ToString();


double date = double.Parse(sDate);


var dateTime = DateTime.FromOADate(date).ToString("MMMM dd, yyyy");
小开

You may want to try out simple function I posted on another thread 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 C# not reading excel date from spreadsheet

小开

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


public static DateTime? GetAsDateTimeOrDefault(Range cell)
{
object cellValue = cell.Value;
if (cellValue is DateTime result)
{
return result;
}
return null;
}

提交答案