richgn
asked on
Problems reading Excel file from ASP.NET
I'm having some problems reading an Excel file form ASP.NET. I'm trying to add a range of excel data to a grid view which will be updated every time I open my application. I would really appreciate the help- this has been really frustrating to fix as I know it's probably something really simple that I'm missing now.
I have named a range of cells in excel 'MyRange'. The error message I am getting says that it is not picking up this range. I have the error message below and my code below that. I'm assuming that I can have the excel file accessed in the same folder as my ASP.NET project.
Here is the error message:
The Microsoft Jet database engine could not find the object 'MyRange'. Make sure the object exists and that you spell its name and the path name correctly.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbExc eption: The Microsoft Jet database engine could not find the object 'MyRange'. Make sure the object exists and that you spell its name and the path name correctly.
Source Error:
Line 59:
Line 60: // Fill the DataSet with the information from the worksheet.
Line 61: objAdapter1.Fill(objDatase t1, "MyRange");
Line 62:
Line 63: // Bind data to DataGrid control.
I have named a range of cells in excel 'MyRange'. The error message I am getting says that it is not picking up this range. I have the error message below and my code below that. I'm assuming that I can have the excel file accessed in the same folder as my ASP.NET project.
Here is the error message:
The Microsoft Jet database engine could not find the object 'MyRange'. Make sure the object exists and that you spell its name and the path name correctly.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbExc
Source Error:
Line 59:
Line 60: // Fill the DataSet with the information from the worksheet.
Line 61: objAdapter1.Fill(objDatase
Line 62:
Line 63: // Bind data to DataGrid control.
protected void Page_Load(object sender, EventArgs e)
{
String excelConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + Server.MapPath("~/AllStocks.xls") + ";" +
"Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(excelConn);
objConn.Open();
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM MyRange", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();
objAdapter1.Fill(objDataset1, "XLData");
GridView3.DataSource = objDataset1.Tables[0].DefaultView;
GridView3.DataBind();
objConn.Close();
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Select * from [Sheet1$]
* Use a range with a defined name (for example, [MyNamedRange]):
Select * from [MyNamedRange]
* Use a range with a specific address (for example, [Sheet1$A1:B10]):
Select * from [Sheet1$A1:B10]
Please go thru the KB article for details:
http://support.microsoft.com/kb/316934