Reading Excel File using C#

Question

I am new to C# and I am trying to read an excel file with the following code

string conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + 
                ";Extended Properties=Excel 12.0;";
using (OleDbConnection connection = new OleDbConnection(conStr))
{
    connection.Open();
    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
    using (OleDbDataReader dr = command.ExecuteReader())
    {
        while (dr.Read())
        {
            var row1Col0 = dr[0];
            Console.WriteLine(row1Col0);
        }
    }
}

I get the following error:

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

Can anyone tell me whats I am doing wrong?

The name of excel sheet is sheet.xlsx

Thanks

Answer 1

The sheet name might not be the same as the filename, you can get the first sheet name by doing the following

First, get the schema

DataTable dtSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

Then get the first sheets name

var sheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();

After you get your command, you can then fill a dataset and work with it's .Rows collection

var myDataSet = new DataSet();
command.Fill(myDataSet);

The sheetname is this