richgn
asked on
Connecting to Excel From ASP.NET
I'm trying to connect to an excel sheet from ASP.NET. I'm battling to connect to it.
Please see the code attached and the errors (below) that I'm getting.
I would really appreciate any help you could offer!
Thanks
Errors:
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.
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 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.
Source Error:
Line 51: // Fill the DataSet with the information from the worksheet.
Line 52:
Line 53: dataAdapt.Fill(ds, "AllStocks");
Line 54: // Build a table from the original data.
Line 55:
Please see the code attached and the errors (below) that I'm getting.
I would really appreciate any help you could offer!
Thanks
Errors:
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.
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 51: // Fill the DataSet with the information from the worksheet.
Line 52:
Line 53: dataAdapt.Fill(ds, "AllStocks");
Line 54: // Build a table from the original data.
Line 55:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Data.OleDb;
public partial class Portfolio : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=AllStocks.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
OleDbConnection connection = new OleDbConnection(excelConnectionString);
connection.Open();
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
OleDbDataAdapter dataAdapt = new OleDbDataAdapter();
dataAdapt.SelectCommand = cmd;
DataSet ds = new DataSet();
dataAdapt.Fill(ds, "AllStocks");
GridView3.DataSource = ds.Tables[0].DefaultView;
connection.Close();
}
I assume you do have a SHEET1$ tab?
ASKER
I do have a Sheet1 tab.
Please try to change
Data Source=AllStocks.xls "
to
Data Source=server.mappath("~/A llStocks.x ls")
Data Source=AllStocks.xls "
to
Data Source=server.mappath("~/A
ASKER
Hi Kishoreb123,
I changed the data source you recommended. The new line now looks like:
string excelConnectionString = @"Provider=Microsoft.Jet.O LEDB.4.0;D ata Source=server.mappath("~/A llStocks.x ls"); Extended Properties=""Excel 8.0;HDR=YES;""";
I am gettign an error for this part of that line:
~/AllStocks.xls"); Extended Properties=""Excel 8.0;HDR=YES;""";
I am being told that a ; is expected.
I changed the data source you recommended. The new line now looks like:
string excelConnectionString = @"Provider=Microsoft.Jet.O
I am gettign an error for this part of that line:
~/AllStocks.xls"); Extended Properties=""Excel 8.0;HDR=YES;""";
I am being told that a ; is expected.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Kishoreb123,
I'm not getting an error for that. I am still getting an error when trying to run it. 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 latest code. 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'm not getting an error for that. I am still getting an error when trying to run it. 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 latest code. 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();
i used the same coding what you have pasted. I'm able to retrieve data without any error.
Have u checked whether issue with any permissions on file..
2010-04-24-152843.gif
Have u checked whether issue with any permissions on file..
2010-04-24-152843.gif