ExcelDataReader Datatype "date" gets transformed

Question

the following code reads the data out of an excel file:

        string path = "testtable.xls";

        FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read);

        IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);

        excelReader.IsFirstRowAsColumnNames = true;
        DataSet result = excelReader.AsDataSet();

        foreach (DataTable Table in result.Tables)
        {

            for (int i = 0; i < Table.Rows.Count; i++)
            {
                for (int j = 0; j < Table.Columns.Count; j++)
                {
                    dataGrid1.ItemsSource = new DataView(Table);
                }
            }

        }

This works for every column just fine, except the column with "dates" in.

In the excel table I specified dates in dd.mm.yyyy but in my gridview I get something like: 42781 instead of 15.02.2017.

Does anybody know a solution for this problem? Thanks

Donohue

how do you prevent the date getting converted to datetime? for some reason my dates are being rendered as datetimes with a 12:00:00 AM suffix! any way to prevent this> – Cordilleras

Answer 1

I found a solution. After changing these two lines it works.

IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream, true);
DataSet result = excelReader.AsDataSet(true);

Donohue

this no longer works with the newest versoin of ExcelDataReader – Cordilleras

@AlexGordon - how we can achieve this? – Lambart

Answer 2

When you are reading excel, you are reading an internal representation of a date. 42781 is the internal representation (days sine 1/1/1900). So you need to convert to a date using DateTime.FromOADate.

In your case, I am afraid you cant bind your table as is. (By the way why are you iterating over columns/rows and setting the binding everytime? You just need to bind the table once.). Anyways you might need to change the table somhow or create a new table with transformed value. Or may be there is some kind of value converter you could use

Libido

You are right, I have cleaned this up. There indeed is some kind of value converter. Posted the code as an answer. – Donohue

are you saying you need to cast it prior to reading it into a dataset? – Cordilleras

Answer 3

          

  DataSet result = excelReader.AsDataSet(true);

     The above line of code is giving the time also after conversion (05/23/2017 12:00 AM)           

Crotty

how do you disable it fmro giving the time? – Cordilleras

Answer 4

You could also calculate the offset like described here.

 public static DateTime FromExcelDate(double value)
 {
     return new DateTime(1899, 12, 30).AddDays(value);
 }

Sociolinguistics

I already using CreateBinaryReader(stream, true) and excelReader.AsDataSet, but for some reasons, some excel files still don't have date converted. this solution worked for me (checked if cell value is double, then convert to date) – Teodoor