Click here to Skip to main content
15,890,506 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I am trying to read an excel file using this code.

C#
using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;

                // Get all Sheets in Excel File
                DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                // Loop through all Sheets to get data
                foreach (DataRow dr in dtSheet.Rows)
                {
                    string sheetName = dr["TABLE_NAME"].ToString();

                    if (!sheetName.EndsWith("$"))
                        continue;

                    // Get all rows from the Sheet
                    cmd.CommandText = "SELECT * FROM [" + sheetName + "]";

                    DataTable dt = new DataTable();
                    dt.TableName = sheetName;

                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                    da.Fill(dt);

                    ds.Tables.Add(dt);
                }

                cmd = null;
                conn.Close();
            }


But the problem is that while reading if there is any date field, it gets converted to a double value. How can i handle that...

Pls help
Posted

1 solution

Dates are stored in Excel as doubles, and displayed as dates as per the formatting on the excel sheet. You would need to convert this explicitly.

For each cell in the Date column, do this:
C#
DateTime conv = DateTime.FromOADate(d); // where d is the date value fetched


Hope that helps.
 
Share this answer
 
v2
Comments
Arjun Menon U.K 24-Mar-14 0:21am    
SInce i am using adapter, the datatable will be filled automatically na. so either i have to loop the datatable and then convert or while reading data i must do it, but to do the second way i have to find another way rt?
CoderPanda 24-Mar-14 0:46am    
While reading data from the dt, you will have to convert it. You don't have to find another way I think, just use the DateTime.FromOADate method.
Arjun Menon U.K 24-Mar-14 1:01am    
But not column values will be of date format na? so how can i implement this? Do i have to hard code the column names or is there any way to identify that the column value is of date format?
CoderPanda 24-Mar-14 4:00am    
Can you not loop through the columns and convert only those that have dates in them (based on the column name)?
Arjun Menon U.K 25-Mar-14 7:45am    
Hi all,
A funny thing happened. This problem is happening only when the file being read in opened in excel. i mean when i have kept the file opened and then try to read using my win app. DOes this happen?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900