CODEWITHSUNDEEP
c#exceldatedatetime
Raymond Dumalaog
Raymond Dumalaog

Reputation: 353

C# getting excel value date

I need to retrieve an excel column of date. My first column 'A' values are formatted like this6/20/2016 10:44. I have no problem of retrieving column 'A' with this format using 

using DocumentFormat.OpenXml;

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

My second column 'B' is formatted as 6/20/2016. With no time, just date. but my problem is when i tried this code below:

using DocumentFormat.OpenXml;

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

theCell.InnerText value is 1455

I am having a different value. the value changes into 12/25/1903 12:00:00 AM

How can I retrieve excel values with this kind of date format 6/30/2016 ?

Upvotes: 2

Views: 5868

Answers (2)

rlm96
rlm96

Reputation: 193

I had opened the file while I was debugging on VS2022 and I had your same problem. Try closing the file.

Upvotes: 0

dev1998
dev1998

Reputation: 892

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


        }
    }
}

Upvotes: 4

Related Questions