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

Answer 1

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