Excel Reading Problem with Column having multiple types

Hi

I am Reading Excel File in .Net Framework 1.1 , using C# . I am getting problem in reading Excel file when i have column which contains number as well text something like this

1003 1004 1005 Test_1 Test_2

Its not giving me any error, but i am getting null values in first three row. This is the code that i am using to read excel file.

string strcon   = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                                                     path +
                                                     ";Extended Properties=Excel 8.0;";

            System.Data.OleDb.OleDbConnection objConn  = new System.Data.OleDb.OleDbConnection(strcon);

            try
            {
                    objConn.Open ();                                
                    System.Data.OleDb.OleDbDataAdapter objadp = new System.Data.OleDb.OleDbDataAdapter("Select * from [" + sheetName + "$" + "]",objConn);  
                    DataSet ds = new DataSet ();                            
                    objadp.Fill (ds);
                    return ds;                              
            }
            catch(Exception ex)
            {
                    throw ex;
            }
            finally
            {
                    objConn.Close();
                    objConn.Dispose();                              
            }

can anybody tell me what can be a problem? Thanks.

From stackoverflow

• I do not have .NET 1.1 installed on my machine, but was able to reproduce the problem with .NET 2.0 installed. I modified the connectionstring a bit and am able to read text values now.

      string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                                               @"C:\Documents and Settings\johnsond\Desktop\Book1.xls" +
                                               @";Extended Properties=" +  Convert.ToChar(34).ToString() + @"Excel 8.0;Imex=1;HDR=Yes;"
                                               + Convert.ToChar(34).ToString();
  

  Quoting from Microsoft Forums the problem seems to be due to the following :

  The issue you have is pretty common, since Excel is not real database and columns do not have fixed data type. Jet OLEDB was not designed to work specifically with Excel and has those limitations.

  Not very convincing I know :-) Further if you want to write such data to excel, it gets more complicated. Further reading : MSDN Forum Link

  Alternatively you could use the "Excel.Application Object" to manipulate the sheets as disucussed before on SO.

  Check this if you are intrested : Read Excel using Excel.Application

  Cheers!

• Thank very much