C# getting excel value date

Question

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 ?

Is the value in column B formatted as a date or is it actually a string? — dev1998, Aug 02 '16 at 02:05
Hi @dev1998 , thanks for checking my question. Column B are formatted as date. — Raymond Dumalaog, Aug 02 '16 at 18:49
It is all working for me, and I'm using the same 2 lines you are using. For the value that is wrong, what is the debugger showing for theCell.InnerText ? — dev1998, Aug 02 '16 at 23:58
if you put a breakpoint on the second set of code, what are the values of `theCell.InnerText` and `d`? 12/3/1909 is 3,265. Somehow the text in that cell is rendering that value. This sounds oversimplified, but it looks like you're not reading the cell you think you are reading. I don't know OpenXml, but if you were using COM I'd have some suggestions to debug. — Hambone, Aug 03 '16 at 03:21
Hi @Hambone, i edited my question. Thanks for looking at it. I have done some research and couldnt find a solution to this problem using OpenXml. — Raymond Dumalaog, Aug 03 '16 at 17:31
Hi @dev1998, i have edited my question. Thanks for looking at it. — Raymond Dumalaog, Aug 03 '16 at 17:33
I posted my code. But I suspect @Hambone may be correct, you might be looking at the wrong cell. — dev1998, Aug 03 '16 at 22:02
Just a question... can you use COM (interop)? The only reason I would think no is if you don't have Excel on the machine. — Hambone, Aug 04 '16 at 02:55

score 4 · Accepted Answer · edited May 23 '17 at 12:33

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


        }
    }
}

score 0 · Answer 2 · answered Jun 27 '22 at 07:03

0

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

answered Jun 27 '22 at 07:03

rlm96

193
1
15

C# getting excel value date

2 Answers2