Reading Datetime value From Excel sheet

Question

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.

score 256 · Accepted Answer · answered Dec 27 '10 at 11:29

256

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

answered Dec 27 '10 at 11:29

Mikael Svenson

39,181
7
73
79

the above snippet works fine if I enter numerals and date value in the cell. How do I check if the user had entered a string in the cell? it is getting converted to some date in the year 1900..instead I need to check before converting. tried using DateTime.Tryparse but no luck. – renuka Dec 09 '13 at 11:40
@renuka, what is your definition of a string? You can check the cell formatting type for one, or create some checking rule if it contains certain characters, or if the date returned is way off what you would expect. – Mikael Svenson Dec 09 '13 at 19:22
Does this take care of the Excel bug with the year 1900? – user3111311 May 21 '14 at 13:06
I get FormatException while converting from excel date-value to String or InvalidCastException while converting from excel date-value to Double. – Vikrant Aug 23 '15 at 09:57
@vikrant try to add CultureInfo to the parsing. Check usage of . and , in the Value being parsed. – Mikael Svenson Aug 23 '15 at 14:21
@MikaelSvenson : `DateTime.FromOADate(Double.Parse(ws.Cells[r, 1].Value.ToString(), CultureInfo.InvariantCulture));` is what I tried, but still getting **FormatException: Input string was not in a correct format** – Vikrant Aug 24 '15 at 05:18
@Vikrant Perhaps write out the value first and inspect it before parsing. That will help you with using the right culture. – Mikael Svenson Aug 24 '15 at 08:47
If I want to add same converted variable to "find elements--> SendKeys" then can anyone please guide me? – Nisarg Patel Aug 04 '17 at 13:07

score 15 · Answer 2 · answered Dec 27 '10 at 11:27

15

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

answered Dec 27 '10 at 11:27

Tom

3,354
1
22
25

score 9 · Answer 3 · edited Jul 02 '14 at 14:19

9

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

edited Jul 02 '14 at 14:19

Pranav

8,563
4
26
42

answered Jul 02 '14 at 14:16

Yashwant Software Developer

217
3
2

4

By the way how your code differ from accepted answer? – Pranav Jul 02 '14 at 14:19
Downvoted. This is inefficient, cause when cell is date, `Value2` returns a boxed `double` value. – dbardakov Nov 29 '19 at 13:09

score 1 · Answer 4 · answered Feb 22 '13 at 05:26

1

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

answered Feb 22 '13 at 05:26

Thomas

1,053
2
11
20

1

I think it's a bit more complicated than that. `Value` will return a `DateTime` if you wrote a `DateTime` to `Value` but a `double` if you wrote a `DateTime` to `Value2`. – jwg May 23 '13 at 12:19

score 1 · Answer 5 · answered Aug 28 '13 at 12:18

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

The use of this answer requires a license and associated installation of the Aspose.Cells product. This is a great product, but not something to which all developers have access. — Zarepheth, Aug 27 '14 at 21:52

score 0 · Answer 6 · edited May 23 '17 at 11:54

0

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

edited May 23 '17 at 11:54

Community

1
1

answered Aug 26 '14 at 08:59

Kasim Husaini

392
3
14

score 0 · Answer 7 · answered Dec 27 '10 at 11:34

0

Or you can simply use OleDbDataAdapter to get data from Excel

answered Dec 27 '10 at 11:34

rsapru

688
14
30

dbardakov · Answer 8 · 2019-12-05T22:22:43.357

0

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

edited Dec 05 '19 at 22:22

answered Nov 29 '19 at 13:23

dbardakov

651
1
8
22

Reading Datetime value From Excel sheet

8 Answers8

Linked

Related