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);


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

    OleDbDataAdapter objAdp = new OleDbDataAdapter();

    objAdp.SelectCommand = ObjCommand;

    DataSet myDataSet = new DataSet();


    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)





    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




    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




    Wednesday, July 23, 2008 4:44 PM
  • 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