ExcelDataReader : Import XLSX file without parsing it into {System.DateTime} object

Question

I am using http://exceldatareader.codeplex.com/. I have an Excel file with a column having dates i.e. D/M/Y. I am trying to read this file using this code:

FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = null;
lock (LockToReadExcelFile)
{
       if (FilePath.EndsWith(".xls", true, System.Globalization.CultureInfo.InvariantCulture))
       {
           excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
       }
       else if (FilePath.EndsWith(".xlsx", true, System.Globalization.CultureInfo.InvariantCulture))
       {
           excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
       }
       else
       {
           return;
       }
}

Then adding it to DataSet as follows:

excelReader.IsFirstRowAsColumnNames = false;
DataSet excelDataTable = excelReader.AsDataSet();

It return rows in {System.DateTime} object. I want them to parse the dates data in System.String format.(e.g. Date 7/11/2015 is returned as 7/11/2015 12:00:00 AM {System.DateTime}.)

PS: I have tried excelReader.AsDataSet(false); as well but it yields same.

You can't _parse_ a `DateTime`. You _can_ parse a `string`. How about using `DateTime.ToString()` method instead? Or any those class supports data format that contains? — Soner Gönül, Dec 21 '15 at 12:31
Let me explain. 7/11/2015 is a string in excel file. It is being parsed as `System.DateTime`. I want to parse this string as a `string`. No matter it is a date or else. — Awais, Dec 21 '15 at 12:46

score 0 · Answer 1 · answered Dec 21 '15 at 16:03

If you are using excel right, it keeps numbers as System.Double, dates as System.DateTime and texts as System.String (at least, as you can see it from .net). Sure, Excel user can set the format of a cell, containig number or date as a string, but it's not normal. So, reading Excel file, you have number as double, dates as DateTime and so on. Very useful. you can convert it to whatever you want, to string is the easiest. Here is the documentation page for the function you need. To convert to the format you mentioned, use

dataTimeValue.ToString("G");

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

First of all... How to read an Excel file.. See: https://gist.github.com/Munawwar/924413

Second, this lib reads the Datetime fiels as a Double value. If you wanna read as DateTime, you need to do some casts, as below:

double d = double.Parse(field_from_excel_datatable); DateTime conv = DateTime.FromOADate(d);

Obs: You can read more about the code above here: Reading Datetime value From Excel sheet

Good luck!

ExcelDataReader : Import XLSX file without parsing it into {System.DateTime} object

2 Answers2