Cocoa Dev Cocoa Dev - 3 months ago 20
C# Question

Reading an Excel file with C# causes OleDbException to be thrown

The message says


The Microsoft Access database engine could not find the object
'Sheet1$'. Make sure the object exists and that you spell its name and
the path name correctly. If 'Sheet1$' is not a local object, check
your network connection or contact the server administrator.


The name of the sheet in the Worksheet is "Sheet1"

string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES"";", fileName);
string query = String.Format("SELECT [columnName1],[columnName2],[columnName3] from [{0}]", "Sheet1$");
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
DataTable YourTable = dataSet.Tables[0];
listBox1.DataSource = YourTable.Columns["ColumnName1"];

Answer

This works for me:

string filename = @"C:\Book1.xlsm";

        string connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\";", filename);
        string query = String.Format("SELECT * from [{0}$]", "Sheet1");
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
        DataSet dataSet = new DataSet();
        dataAdapter.Fill(dataSet);
        DataTable YourTable = dataSet.Tables[0];

*NOTE: * If your data does not have headers to make HDR=NO

Also noticed that in your question you used

[columnName1],[columnName2],[columnName3]

for your columns to select. Please remember these should be the value(s) of the first cell in the column(s) that you would like to grab.

To get column E Use:

        string connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=NO\";", filename);
        string query = String.Format("SELECT [F5] from [{0}$]", "Sheet1");

Replace 5 With any other column number you need so

F1 = A
F2 = B
F3 = C 

and so on.

The Error you are getting could be because you have the file open and active.

OR you are pointing at the wrong file (Remember you have to include full file path in the filename string. and make sure the sheet is correct. Alos take notice to the fact the i include the $ in my string not in my parameter so rememer to only put just the name of the sheet you are trying to get. If you are still having trouble supply me with the FULL file name for the worksheet you are using i.e. C:\Book1.xlsm and the sheet you are trying to get data from.