Date format lost when Import Excel file in asp.NET C#

Question

Date format is 1:55:25 AM in the excel file, after reading using ExcelReaderFactory.CreateBinaryReader, I get the 0.0451388888888889.

Answer 1

Internally Excel stores the data in that decimal format and applies logic to the value to display the date. Unfortunately, the transformation from that format into the date format is not always straightforward.

What I would recommend is opening the Excel file using OleDb to read it into a dataset. Doing it that way preserves the data in the format you see in the spreadsheet.

There are a number of great articles on how to read Excel using OleDb.

You can start with this one: http://codehill.com/2009/01/reading-excel-2003-and-2007-files-using-oledb/

Cheers!

Answer 2

Use OleDb to read the data in, like so:

string dsn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
              + xlsPath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"

OleDbConnection newConn = new OleDbConnection(dsn);

string tableName= "MyGlobalNamedRange";    
string sqlText = String.Format("SELECT * FROM [{0}]", tableName);
IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = sqlText;

IDataReader res = cmd.ExecuteReader();

For info on using DataReaders, see the KB article

Answer 3

When Excel recognizes a value as a time value, it stores the time portion in the decimal portion of a number and stores the day portion of the date as the whole number portion of a number. Excel stores all numeric values as double precision floating point values which is why you are getting non-exact results. However, why you are getting 0.0451388888888889 instead of something like 0.080150462962963 is a mystery. You might try looking the underlying value in Excel by putting a simple formula in a blank cell that points to one of your mystery date values (e.g. =A3). Then format that cell as a numeric value with decimal places and see what number it provides. If the underlying value is also 0.04513... then the problem is in the source data and not the ExcelDataReader library.