asked on

Read from Excel file

HI, i am trying to read data from an excel file and insert it to the a table in the sql db.

i am using the following code to open the excel file:

Excel.Application excelApp = new Excel.ApplicationClass();
string workbookPath = FileName;                   //strFilePath;

Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);

Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Sheet1";
Excel.Worksheet excelWorksheet =
            (Excel.Worksheet)excelSheets.get_Item(currentSheet);
Excel.Range excelCell =
            (Excel.Range)excelWorksheet.get_Range("A1", "A1");

I get an Access denied error when i get to the first line of this code.

Error:
Access is denied.
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.UnauthorizedAccessException: Access is denied.

ASP.NET is not authorized to access the requested resource. Consider granting access rights to the resource to the ASP.NET request identity. ASP.NET has a base process identity (typically {MACHINE}\ASPNET on IIS 5 or Network Service on IIS 6) that is used if the application is not impersonating. If the application is impersonating via <identity impersonate="true"/>, the identity will be the anonymous user (typically IUSR_MACHINENAME) or the authenticated request user.

To grant ASP.NET write access to a file, right-click the file in Explorer, choose "Properties" and select the Security tab. Click "Add" to add the appropriate user or group. Highlight the ASP.NET account, and check the boxes for the desired access.

Any idea what i am doing wrong?

AGBrown

To fix that you need to give ASP.NET read access to the directory that you are saving the file in. In your case, this is the temp directory i think, though you can check that by looking at FileName. Grant read access to that directory to the ASPNET account, and see if that helps.

Andy

GoldenJag

ASKER

Andy!

I accessed my c: and clicked on properties -- security--Add. I "looked in my computer (goldenjag) clicked on ASPNET. appied and clicked ok.

I tried to run the code again and got the same error.

What did i do wrong?

AGBrown

Ok, here are my questions:
-Where is this code running (your computer, a server etc.)?
-Did you set up IIS?
-Which version of .NET are you using?

GoldenJag

ASKER

i am using source safe and the code is on another server.

No i did not set up IIS

version 1.1

quoclan

When you use :
Excel.Application excelApp = new Excel.ApplicationClass();
it will access your Microsoft Excel and make Excel do many things for you ...
So, you must have ensure :
1. your web server has Microsoft Excel program
2. you have full right to access Microsoft Excel program.

It has difficult to do this .... Microsoft XP PIAs is suitable for local system, winform ... With server, from now, with Office 2003, you can use this (convert your document in Office 2003 format, process it like XML ..)

My English is not good, hope you know this.

AGBrown

GoldenJag,

quoclan is right - you might end up with horrible problems using Excel on the server like this. The main problem is that it is not intended to be a server application. It runs in a user space, so it will actually throw up dialog boxes and things at inappropriate points, but they will appear in the user space of the ASP.NET user. You _might_ get away with doing it like this, but if you have an option then you should look at a commercial/free Excel file handler assembly for .NET; there are a few around.

When I said "where is the code running" I mean, is the web server you are developing on your local machine, or is it a remote server? I am assuming it is the local machine, as you indicated that the temp directory was on your local machine that the Excel file was being saved into. Can you confirm that?

Andy

GoldenJag

ASKER

Andy,

I am developing on my local machine.

GoldenJag

ASKER

Ok Guys,

i talked with my boss and there has been a change. Now he wants me to us ODBC to read from the excel file.

I am not sure how to do that. So any help would be nice.

Thanks!

GoldenJag

ASKER

Ok i found a code snipplet that may help me with reading excel from ODBC:

string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
      "Data Source= //C:\\temp.xls;" +
      "Extended Properties=Excel 8.0;";

//You must use the $ after the object you reference in the spreadsheet
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "ExcelInfo");
DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
DataGrid1.DataBind();

When i run the code i get the following error:

Cannot update. Database or object is read-only.
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.OleDbException: Cannot update. Database or object is read-only.

Source Error:
Line 328:                  myCommand.Fill(myDataSet, "ExcelInfo");

========
What is ExcelInfo?

ASKER CERTIFIED SOLUTION

AGBrown

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GoldenJag

ASKER

Andy, the myCommand.Fill(myDataSet) gives me an error:

Cannot update. Database or object is read-only.
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.OleDbException: Cannot update. Database or object is read-only.

Any idea what's going on with that error?
Below is the segment of code that i have that generates that error:
=========================

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                        "Data Source= //C:\\temp.xls;" +
                        "Extended Properties=Excel 8.0;";
//You must use the $ after the object you reference in the spreadsheet
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

                  DataSet myDataSet = new DataSet();
                  myCommand.Fill(myDataSet);
                  DataGrid1.DataSource = myDataSet.Tables[0].DefaultView;
                  DataGrid1.DataBind();

GoldenJag

ASKER

I found the solution to my problem. Andy the link you provided me with guided me to the solution therefore i will award you the points.
Thanks for all of your help.

This section of code works perfectly:

string strConn;

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
      "Data Source= C:\\temp.xls;" +
      "Extended Properties=Excel 8.0;";

//You must use the $ after the object you reference in the spreadsheet
OleDbConnection objConn = new OleDbConnection(strConn);
objConn.Open();
OleDbCommand myCommand = new OleDbCommand("SELECT * FROM [Sheet1$]",objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

objAdapter1.SelectCommand = myCommand;

DataSet myDataSet = new DataSet();

objAdapter1.Fill(myDataSet, "XLData");
DataGrid1.DataSource = myDataSet.Tables[0].DefaultView;
DataGrid1.DataBind();

AGBrown

Glad you got it working.

Andy