Excel to csv error after deployment 80070005 access is denied.

Question

0.00/5 (No votes)

See more:

, +

I am using below mentioned code to save my file as csv from xls, I studio code works fine but if I host the application it gives error on button click.
Error details are mentioned below....plz help

HTML

Server Error in '/NPCI' Application.
Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).
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: Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).

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 and IIS 7, and the configured application pool identity on IIS 7.5) 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 access to a file, right-click the file in File 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.

What I have tried:

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Configuration
Imports ClosedXML.Excel

Dim xlsApp As New Excel.Application
        Dim xlsWorkBook As Excel.Workbook
        Dim xlsWorkSheet As Excel.Worksheet
        xlsApp = New Excel.Application
        xlsWorkBook = xlsApp.Workbooks.Open(excelPath)
        xlsWorkSheet = xlsWorkBook.Worksheets(1) ' You could also do it by name: "sheet1"
        'xlsWorkSheet.Rows("1:5").Delete()
        'xlsWorkSheet.Rows("37:100").Delete()
        xlsApp.DisplayAlerts = False
        xlsWorkBook.SaveAs(csvPath, FileFormat:=Excel.XlFileFormat.xlCSV, CreateBackup:=False)
        xlsWorkBook.Close()

Posted 10-Apr-20 21:48pm

Hemil Gandhi

Updated 11-Apr-20 4:53am

Add a Solution

Comments

F-ES Sitecore 11-Apr-20 11:31am

What you're doing isn't supported and won't work. Use something intended for managing Excel files from asp.net like XDoc, EPPlus, Excel ODBC Driver etc.

Hemil Gandhi 13-Apr-20 11:48am

I have tried all other ways but got the same error of "data corrupted". Please suggest something else....

Hemil Gandhi 11-Apr-20 13:02pm

actually i want to import excel data to database but not able to upload excel file, hence i have decided it to save as csv & then import it to DB, please suggest what should i do to full fill my requirement.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2020-04-11T04:54:00

Solution 2

What nobody has mentioned yet is that you cannot use Office Interop, in your case Excel, in a web application. It may work fine on your machine, but in a multi-user app and deployed to a web server, Office is not supported.

On the "Access Denied", the account the web app is running under does not have permissions to write to the filesystem it's running from. This for security reasons. Whatever account your site is running under has to be given permissions to create files and write to them in the folder you're trying to write your data to.

Posted 11-Apr-20 4:54am

Dave Kreskowiak

Comments

Hemil Gandhi 11-Apr-20 12:59pm

You want me to do some setting changes in application pool??

will you please tell me exactly what i should do ??
Thanks in advance

Dave Kreskowiak 11-Apr-20 13:09pm

No, these are NTFS permissions. Right click the folder you're trying to write to, click Properties. On the Security tab, those are the NTFS permissions for each account that has something setup. If the account you're running the site under isn't listed, it's going to be ReadOnly for the account. You have to add the account and setup the permissions for it.

AND AGAIN, YOU CAN NOT USE OFFICE INTEROP IN A ASP.NET APPLICATION! IT WILL NOT WORK IN A MULTIUSER ENVIRONMENT!

OriginalGriff · Answer 2 · 2020-04-10T22:11:00

Solution 1

Read the error message - it's pretty clear:

failed due to the following error: 80070005 Access is denied.

Some part of the path you are trying to save to is not shared to the user that is executing the application with sufficient permission to create or modify the file you specify.

Check the content of the excelPath and csvPath variables, and check what the folder access rights are. If this used to work in dev but fails in prod, it's very likely that you are using the app executable folder to store your data, and that won't work.

We can't do any of that for you - we have no access to your systems!

Posted 10-Apr-20 22:11pm

OriginalGriff

Comments

Hemil Gandhi 11-Apr-20 4:17am

Dim Upath As String = "~/Uploads/" + CDate(Today.Date).ToString("yyyyMMdd")
Dim excelPath As String = Server.MapPath(Upath + "/BKP/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
Dim csvPath As String = Server.MapPath(Upath) + "/" + Path.GetFileNameWithoutExtension(FileUpload1.PostedFile.FileName) + ".csv"

I am using above paths first i am copying original file to uploads/yyyymmdd/bkp folder & then saving csv file to uploads/yyyymmdd/ path, i have checked that system is able copy files to bkp folder but while saving csv it is throwing error. i have already provided necessary rights to folder.

Richard MacCutchan 11-Apr-20 4:22am

Are you sure the directory yyyymmdd exists?

Hemil Gandhi 11-Apr-20 4:29am

Protected Sub CreateDirectory()
Dim Upath As String = "~/Uploads/" + CDate(Today.Date).ToString("yyyyMMdd")
Dim folderPath As String = Server.MapPath(Upath)
'Check whether Directory (Folder) exists.
If Not Directory.Exists(folderPath) Then
'If Directory (Folder) does not exists. Create it.
Directory.CreateDirectory(folderPath)
End If

Dim bkppath As String = Upath + "/" + "BKP"
Dim bfolderPath As String = Server.MapPath(bkppath)
'Check whether Directory (Folder) exists.
If Not Directory.Exists(bfolderPath) Then
'If Directory (Folder) does not exists. Create it.
Directory.CreateDirectory(bfolderPath)
End If
End Sub

I have made sure that they exist.

OriginalGriff 11-Apr-20 4:31am

The chances are that the path you have selected - which is under your website, and thus "owned" by IIS (the app that runs your site) is not accessible by the use that Excel is running under.
When you create an ExcelApplication, it start an instance of Excel (assuming Office is installed on the webserver, which is by no means a given) and it doesn;t necessarily run under the same user as IIS - in fact it's very unlikely to.
See here:
https://support.microsoft.com/en-gb/help/257757/considerations-for-server-side-automation-of-office

Hemil Gandhi 11-Apr-20 5:19am

how shall i record log so that i came to know that system is facing error at which line??

OriginalGriff 11-Apr-20 5:36am

Up to you - File.AppendText is the most primitive, but it works ...