Click here to Skip to main content
15,850,919 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Updated 11-Apr-20 5:53am
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.

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.
 
Share this answer
 
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!
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!
 
Share this answer
 
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??

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900