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

That's the name of the file. What are the names on the tabs inside the file? IIRC that's what the sheets are. — CaffGeek
– CaffGeek, Commented Aug 4, 2016 at 14:46

CaffGeek · Accepted Answer · 2016-08-04 15:03:48Z

3

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

edited Aug 4, 2016 at 15:03

answered Aug 4, 2016 at 14:50

CaffGeek

22.2k18 gold badges106 silver badges186 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

CaffGeek Over a year ago

I'd put the data in a dataset with command.Fill then work with the dataset like normal

Ragnar Over a year ago

Can you please provide some psudocode I am very new to C#

Collectives™ on Stack Overflow

Reading Excel File using C#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related