Reading Datetime Value from Excel Sheet

Reading Datetime value From Excel sheet

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

How to get a datetime value from an excelsheet in java?

public static String getFormatedDate(final Date date) {
SimpleDateFormat myFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
return myFormat.format(date);
}

Use SimpleDateFormat

Get DateTime value from excel cell with C#

You need to parse the string to Datetime.

date = DateTime.Parse(worksheet.Cells[row, 1].Value.ToString());

you can also use the DateTime.TryParse method to make sure you get a value and not an exception.

DateTime.TryParse(worksheet.Cells[row, 1].Value.ToString(), out date)

How to read in DateTime in Excel Interop C#

There yo go bud. I modified the code for you a little to reflect the usage of the helper conversion function. The conversion happens in this line: DateTime dt = DateTime.Parse(ConvertToDateTime(dtString)); Feel free to modify the helper function as needed to return a DateTime variable instead of a string, but this is essentially the same thing. Hope this helps!

public void addTime(Microsoft.Office.Interop.Excel.Workbook workbook)  
{  
Excel.Worksheet ws = (Excel.Worksheet)workbook.Worksheets.get_Item("Time Series");  
Excel.Range range = ws.UsedRange;  
int num = 0;  
for (int row = 1; row <= range.Rows.Count; row++ )  
{  
    String dtString = ((Excel.Range)ws.Cells[row, "C"]).Value2.ToString();  
    DateTime dt = DateTime.Parse(ConvertToDateTime(dtString));  

    this.addEdgeInstance(dt);  
}  
}  


public static string ConvertToDateTime(string strExcelDate)
{
    double excelDate;
    try
    {
        excelDate = Convert.ToDouble(strExcelDate);
    }
    catch
    {
        return strExcelDate;
    }
    if (excelDate < 1)
    {
        throw new ArgumentException("Excel dates cannot be smaller than 0.");
    }
    DateTime dateOfReference = new DateTime(1900, 1, 1);
    if (excelDate > 60d)
    {
        excelDate = excelDate - 2;
    }
    else
    {
        excelDate = excelDate - 1;
    }
    return dateOfReference.AddDays(excelDate).ToShortDateString();
}

Get Date column value from Excel

Hi techstack excel usees OLE Automation Date.

use DateTime.FromOADate(Double) Method

It Returns a DateTime equivalent to the specified OLE Automation Date.

C# getting excel value date

I located some code from here and modified it: open xml reading from excel file

I am thinking the same thing that Hambone is thinking, namely the Excel cell has something else in it, or you are not reading the cell you think you are.

Here is the code I am using, and it works for me:

using System;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;



namespace ConsoleApplication1
{
    class Program
    {

        private static void Main(string[] args)
        {
            var filePath = @"c:\xyz\stack_c_Sharp.xlsx";
            using (var document = SpreadsheetDocument.Open(filePath, false))
            {
                var workbookPart = document.WorkbookPart;
                var workbook = workbookPart.Workbook;

                var sheets = workbook.Descendants<Sheet>();
                foreach (var sheet in sheets)
                {
                    var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
                    var sharedStringPart = workbookPart.SharedStringTablePart;

                    string text;
                    var rows = worksheetPart.Worksheet.Descendants<Row>();
                    foreach (var row in rows)
                    {
                        Console.WriteLine();
                        int count = row.Elements<Cell>().Count();

                        foreach (Cell theCell in row.Elements<Cell>())
                        {

                            text = theCell.CellValue.InnerText;

                            double d = double.Parse(theCell.InnerText);
                            DateTime conv = DateTime.FromOADate(d).Date;

                            Console.Write(text + " ");
                            Console.Write(conv + " ");

                        }
                    }
                }
                Console.ReadLine();
            }


        }
    }
}

How to represent a DateTime in Excel

The underlying data type of a datetime in Excel is a 64-bit floating point number where the length of a day equals 1 and 1st Jan 1900 00:00 equals 1. So 11th June 2009 17:30 is about 39975.72917.

If a cell contains a numeric value such as this, it can be converted to a datetime simply by applying a datetime format to the cell.

So, if you can convert your datetimes to numbers using the above formula, output them to the relevant cells and then set the cell formats to the appropriate datetime format, e.g. yyyy-mm-dd hh:mm:ss, then it should be possible to achieve what you want.

Also Stefan de Bruijn has pointed out that there is a bug in Excel in that it incorrectly assumes 1900 is a leap year so you need to take that into account when making your calculations (Wikipedia).

---

Related Topics