CODEWITHSUNDEEP
c#exceldateexceldatareader
reveN
reveN

Reputation: 642

ExcelDataReader Datatype "date" gets transformed

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

Upvotes: 7

Views: 6993

Answers (4)

Partha G
Partha G

Reputation: 81

          

  DataSet result = excelReader.AsDataSet(true);

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

Upvotes: 2

aggsol
aggsol

Reputation: 2490

You could also calculate the offset like described here.

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

Upvotes: 1

reveN
reveN

Reputation: 642

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

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

Upvotes: 3

dgorti
dgorti

Reputation: 1240

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

Upvotes: 3

Related Questions