Reading Datetime value from Excel worksheet

Question

when I try to read a datetime value from an excel sheet, it returns a double value. For example, if you want to read the value '2007-02-19 14:11:45.730' like this, I get a double type value. Next, I convert this double value using timespan, but not completely successfully, because I only get this value '2007-02-19 12:00:00 AM'
Now I want the exact same date and time value as the first. My code is like: -

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

Please, help!!! Thank.

Answer 1

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

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

Answer 2

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

Answer 3

Reading the Datetime value from an Excel worksheet: try it, it will 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"); 

Answer 4

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 

Answer 5

Or you can just use OleDbDataAdapter to get data from Excel

Answer 6

I had a similar situation, and I used the code below to make this work.

 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 thanks_joxin

Answer 7

You might want to try a simple function that I posted on another topic related to reading a date value from an excel sheet.

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

I would be happy to see an improvement in my code sample provided for the .NET developer community.

Here is a link to a C # stream not reading an Excel date from a table