Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

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?
0
GoldenJag
Asked:
GoldenJag
  • 7
  • 5
1 Solution
 
AGBrownCommented:
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
0
 
GoldenJagAuthor Commented:
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?
0
 
AGBrownCommented:
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?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

No i did not set up IIS

version 1.1
0
 
quoclanCommented:
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.
0
 
AGBrownCommented:
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
0
 
GoldenJagAuthor Commented:
Andy,

I am developing on my local machine.
0
 
GoldenJagAuthor Commented:
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!
0
 
GoldenJagAuthor Commented:
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?

0
 
AGBrownCommented:
You're going to have a lot of fun with Excel either way. From what I can remember, Jet makes a decision on the format of the column based on the contents of the first 10 rows - you cannot change this. That means if you have a character column, but it contains numbers or dates in the majority of the first ten rows, you are in a lot of trouble.

I take it you've read this? http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934 It talks about exactly what you are now trying to do.

In your code above, "ExcelInfo" refers to the name of the table in that overload of Fill(DataSet, string). I think you might want to try:
    myCommand.Fill(myDataSet);
instead of
    myCommand.Fill(myDataSet, "ExcelInfo");

Does that work?

Andy
0
 
GoldenJagAuthor Commented:
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();
0
 
GoldenJagAuthor Commented:
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();
0
 
AGBrownCommented:
Glad you got it working.

Andy
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now