Can't read from my Excel file using C#

Question

I have an Excel 2007 file "my.xlsx" and a sheet named "States", and I have the following code

 using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\my.xlsx;Extended Properties='Excel 12.0 Xml;HDR=NO'"))
        {
            OleDbCommand cmd = new OleDbCommand("select * from [States]", con);

            con.Open();
            OleDbDataReader reader = cmd.ExecuteReader();
            while(reader.Read())
                Console.WriteLine(reader[0]);
        }

It keeps throwing exception saying "The Microsoft Office Access database engine could not find the object 'States'. Make sure the object exists and that you spell its name and the path name correctly.".

Could someone help to see what's wrong with my code please?

Answer 1

I think that you need to open the connection before creating the command - not sure if it's a big deal...

Then add a $ at the end of the sheet name:

OleDbCommand cmd = new OleDbCommand("select * from [States$]", con);

Basically [Name] refers to a named range, whereas [Name$] refers to a sheet.

For more information see this KB: http://support.microsoft.com/kb/316934

Answer 2

I just got it working by visiting this page

http://www.davidhayden.com/blog/dave/archive/2006/05/26/2973.aspx

For my requirement, I only need to read an Excel file. Most of the solutions I searched show you to use some kind of library, which is overkill for me. I was really looking for someone to just post a code snippet on how to read the file, but I only found that on the page I put the link with.

Answer 3

I know this may be not exactly what you want to hear, but you're in a long line of people who have had struggles trying to read excel files by using oledb...

I've had a lot more luck using libraries such as NPOI to read Excel files from C#:

http://npoi.codeplex.com/ (recommended)

http://nexcel.sourceforge.net/

http://sourceforge.net/projects/koogra/