none
Reading Excel File Using C#.net RRS feed

  • Question

  •  

    Hi i was trying read the excel through c#,net and finally update to respective table in sql server 2005

    i have come a cross one complicate error which not able to track that error

     

    please find the source code below

     

    string filename = @"C:\testdata.xls";

    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +

    "Data Source=" + filename + ";" +

    "Extended Properties=Excel 8.0;";

    OleDbConnection objConn = new OleDbConnection(connectionString);

    objConn.Open();

    OleDbCommand ObjCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", objConn);

    OleDbDataAdapter objAdp = new OleDbDataAdapter();

    objAdp.SelectCommand = ObjCommand;

    DataSet myDataSet = new DataSet();

    objAdp.Fill(myDataSet);

    DataTable dataTable = myDataSet.Tables["Sheet1$"];

    var query = from r in dataTable.AsEnumerable()

    select new

    {

    RelationNr = r.Field<double>("FirstName"),

    ClientName = r.Field<string>("LastName"),

    };

    foreach (var item in query)

    {

    Console.WriteLine(item.ClientName);

    }

     

    rror Message is :

     

    'Sheet1$' is not a valid name.  Make sure that it does not include invalid characters or punctuation and that it is not too long.

     

    what this error means

     

    Orcas

     

    Tuesday, July 22, 2008 1:34 PM

All replies

  • If you have a Worksheet in the Workbook called Sheet1 (w/no $) then make sure that there isn't an Excel Range in the Workbook called Sheet1.
    Tuesday, July 22, 2008 3:36 PM
  • Which line of code generates that error? If it is when you call myDataSet.Tables["Sheet1$"]; then table name inside of DataTable is not populated (by-default) with the actual table name from database. In this case you could access it using index, like

     

    myDataSet.Tables[0];

     

    Wednesday, July 23, 2008 4:44 PM
    Moderator
  • Does your excel workbook has a sheet named "Sheet" ? When you open your excel file, it shows 3 tabs (default) at bottom left. You should see "Sheet" on one of the tabs. If that is not the case then replace "Sheet" with whatever is written on a tab in your code.
    Thursday, November 24, 2011 10:57 AM