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

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> — Alex Gordon, Nov 21 '18 at 17:01

score 3 · Answer 1 · answered Jan 16 '17 at 07:52

3

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

answered Jan 16 '17 at 07:52

dgorti

1,130
2
12
22

You are right, I have cleaned this up. There indeed is some kind of value converter. Posted the code as an answer. – reveN Jan 16 '17 at 08:19
are you saying you need to cast it prior to reading it into a dataset? – Alex Gordon Nov 21 '18 at 17:08

score 3 · Accepted Answer · answered Jan 16 '17 at 08:19

3

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

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

answered Jan 16 '17 at 08:19

reveN

552
2
8
20

1

this no longer works with the newest versoin of ExcelDataReader – Alex Gordon Nov 21 '18 at 17:07
@AlexGordon - how we can achieve this? – Sanjeevi Subramani Feb 22 '22 at 06:35

score 2 · Answer 3 · edited Jun 24 '19 at 09:54

2

  DataSet result = excelReader.AsDataSet(true);

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

edited Jun 24 '19 at 09:54

Mohan Singh

1,142
3
15
30

answered May 23 '17 at 15:19

Partha G

81
2
5

1

how do you disable it fmro giving the time? – Alex Gordon Nov 21 '18 at 17:00

score 1 · Answer 4 · answered Mar 13 '17 at 14:46

1

You could also calculate the offset like described here.

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

answered Mar 13 '17 at 14:46

aggsol

2,343
1
32
49

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) – Roberto Correia Sep 25 '17 at 18:14

ExcelDataReader Datatype "date" gets transformed

4 Answers4