Hi,
Most probably the sheet name is incorrect,
Take a look at this code, it does read an excel without any problem, let me
know if it worked for you
string srcConnString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
sourceFile + @";Extended Properties=""Excel 8.0;HDR=YES;""";
string srcQuery = "Select * from [" + GetExcelSheetNames(
sourceFile)[0] + "]";
OleDbConnection srcConn = new OleDbConnection( srcConnString);
srcConn.Open();
OleDbCommand objCmdSelect =new OleDbCommand( srcQuery, srcConn);
readerExcel = objCmdSelect.ExecuteReader(
CommandBehavior.CloseConnection);
Here is how you get the name of the sheets
static String[] GetExcelSheetNames(string excelFile)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
// Connection String. Change the excel file to the file you
// will search.
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString);
// Open connection with the database.
objConn.Open();
// Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables , null);
if(dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach(DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
return excelSheets;
}
catch(Exception ex)
{
return null;
}
finally
{
// Clean up.
if(objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if(dt != null)
{
dt.Dispose();
}
}
}
<ba**********@yahoo.comwrote in message
news:11**********************@a26g2000pre.googlegr oups.com...
Hi All,
I need to read data from a Excel spreadsheet, but I got the problem
when I tried the code below:
StringBuilder sbConn = new StringBuilder();
sbConn.Append(@"Provider=Microsoft.Jet.OLEDB.4.0;D ata Source=" );
sbConn.Append(excelFile);
sbConn.Append(";Extended Properties=");
sbConn.Append(Convert.ToChar(34));
sbConn.Append("Excel 8.0;HDR=Yes;IMEX=2");
sbConn.Append(Convert.ToChar(34));
//open spreadsheet and query data
OleDbConnection cnExcel = new OleDbConnection(sbConn.ToString());
cnExcel.Open();
OleDbCommand cmdExcel = new OleDbCommand("Select * From
[Sheet1]",cnExcel);
try
{
OleDbDataReader drExcel = cmdExcel.ExecuteReader();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
The exception message is "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."
I checked the Sheet name, it is "Sheet1", what am I doing wrong?
Thanks for the help!
