CODEWITHSUNDEEP
c#.netexcelc#-4.0oledb
Cocoa Dev
Cocoa Dev

Reputation: 9541

can't figure out why Im getting System.Data.OleDb.OleDbException for Excel file

    OpenFileDialog openFileDialog1 = new OpenFileDialog();
    openFileDialog1.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
    openFileDialog1.FilterIndex = 2;
    openFileDialog1.RestoreDirectory = true;

    // Show the dialog and get result.
    DialogResult result = openFileDialog1.ShowDialog();
    if (result == DialogResult.OK) // Test result.
    {
        labelFilePath.Text = openFileDialog1.FileName;
        string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + labelFilePath.Text.Trim() + ";Extended Properties=\"Excel 8.0;HDR=YES\"";
        using (var conn = new System.Data.OleDb.OleDbConnection(connString)) 
        { 
            conn.Open(); 
            try
            {
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "Select * From [Sheet1]";
                    using (OleDbDataReader reader = cmd.ExecuteReader())
                    {
                        int firstNameOrdinal = reader.GetOrdinal("First Name");
                        int lastNameOrdinal = reader.GetOrdinal("Last Name");
                        while (reader.Read())
                        {
                            Console.WriteLine("First Name: {0}, Last Name: {1}", reader.GetString(firstNameOrdinal), reader.GetString(lastNameOrdinal));
                        }
                    }
                }
            }
            catch (OleDbException odbe)
            {
                Console.WriteLine(odbe.Errors.ToString());
                Console.WriteLine(odbe.Message.ToString());
            }
        } 

    }
    Console.WriteLine(result); // <-- For debugging use only.

}

I get the error at OleDbDataReader reader = cmd.ExecuteReader()

Here is the output

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll System.Data.OleDb.OleDbErrorCollection The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly. OK

Upvotes: 1

Views: 5643

Answers (2)

Jon Egerton
Jon Egerton

Reputation: 41549

Try changing the following line (note the $):

cmd.CommandText = "Select * From [Sheet1$]";

In this case you are selecting everything from the sheet, however this notation extends to select named ranges as follows:

cmd.CommandText = "Select * From [Sheet1$NamedRange]";

Upvotes: 2

Dimitri
Dimitri

Reputation: 7013

Just a thought: I have had oledb exceptions in Excel drivers when i was using them in a project that had 64 bit platform target selected. Try going to project settings and changing platform target on Build tab to x86.

Upvotes: 0

Related Questions