Date changed into number when i fetch value from excel

Question

I have an excel file which contains a lot of fields. I use the dataset to store the data fetched from this excel file. In excel sheet I have a date column that is set to the correct format. But when retrieved, the format is changed to a number in the dataset..

I don't know the reason for this issue.

Excel Value: 01/12/2015, changed to 42016 in the dataset.

Any pointers to this issue will be appreciated.

My code is

  private static DataSet GetExcelDataAsDataSet(string path)
        {

            return GetExcelDataReader(path).AsDataSet();
        }

        private static IExcelDataReader GetExcelDataReader(string path)
        {
            FileStream stream = System.IO.File.Open(path, FileMode.Open, FileAccess.Read);


            IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

            var data = excelReader.ResultsCount;
            DataSet result = excelReader.AsDataSet();


            excelReader.IsFirstRowAsColumnNames = true;

            return excelReader;
        }

can you please provide the code snippet where the data from excel is populated into the dataset? — Krishna Kumar N, Feb 26 '16 at 11:15
The reason is that you retrieve the values as their numeric representation of VBA, not as date values. But without your code we cannot advice how to change this behaviour. — Gustav, Feb 26 '16 at 11:22

score 1 · Answer 1 · edited May 23 '17 at 11:45

As explained in this answer it's as easy as using:

public static DateTime FromExcelSerialDate(int SerialDate)
{
    if (SerialDate > 59) SerialDate -= 1; //Excel/Lotus 2/29/1900 bug   
    return new DateTime(1899, 12, 31).AddDays(SerialDate);
}

The number you are getting is the number of days passed since 1/1/1900 so that function is your answer :)

You can use as well DateTime dt = DateTime.FromOADate(NUMBER); and I think it will work better as extracted from this answer.

Date changed into number when i fetch value from excel

1 Answers1