when am trying to read datetime type value from excel sheet it is returning a double value.for example if want to read value '2007-02-19 14:11:45.730'
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 '2007-02-19 12:00:00 AM'
now i want exact same datetime value as first one. 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!!! Thanks.
This question is related to
c#
excel
office-interop
Or you can simply use OleDbDataAdapter to get data from Excel
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");
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;
}
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
Perhaps you could try using the DateTime.FromOADate
method to convert between Excel and .net.
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
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
Source: Stackoverflow.com