locked
Export to Excel does not happen on live site! RRS feed

  • Question

  • User743508062 posted

    Hi, I have been working on exporting webgrid data to excel using Mike's tutorial. This has worked well on my development machine, it also works well on the production server (running win server 2008 r2, IIS 7.5) when I am on localhost site but when I run the website from my own machine pointing to the site being served by the web server, the code runs (well the prgress bar runs) but nothing is downloaded!!, any ideas why and how I can resolve this? Thanks.

    Thursday, June 27, 2013 8:57 AM

Answers

All replies

  • User-431249759 posted

    is excel installed on that production server? In most cases (I am unaware of your particular solution) it should be installed to be able to manipulate excel. There are 3d party solutions which do not require Excel itself, but then you would have to talk to the vendor for support. 

    Thursday, June 27, 2013 8:59 AM
  • User743508062 posted

    OH! No there is no Excel or office installed on the production server (waste of a license as no user is going to be using this software) so that explains why the site is not downloading the data to Excel.

    How can I get around this issue?

     

    Thursday, June 27, 2013 9:03 AM
  • User-1980594115 posted

    I use EPPLUS on a production server with no Microsoft Office/Excel.  It generates Excel spreadsheets for download fine.

     

    Thursday, June 27, 2013 9:50 AM
  • User743508062 posted

    To overcome this issue, our admin has kindly installed Excel on the production server! We have Excel but still no download from the live site?

    The progress bar runs so I know the js related to calling the gnerating page runs and when I run the gnerating pages from the localhost site on the prod server the download occurs but nothing when a user uses the live site!! Need help. Thanks.

    Thursday, June 27, 2013 10:04 AM
  • User-431249759 posted

    now, your xls files need to be generated and stored somewhere. Thus, it might be that you don`t have proper Permissions on the server file system for the web application to write to. Check that out with your sys admin. 

    You may also want to ask the admin to provide you with the event log on the server. Most likely you will have some application exceptions there which will give you more info. 

    Thursday, June 27, 2013 10:53 AM
  • User743508062 posted
    Server Error in '/' Application.
    
    Access to the path 'C:\inetpub\wwwroot\LensCatalogueSystem\App_Data\8fe85aeb-a769-4d24-994f-d5bbc9657521.xls' is denied.
    
    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 to the path
     'C:\inetpub\wwwroot\LensCatalogueSystem\App_Data\8fe85aeb-a769-4d24-994f-d5bbc9657521.xls' 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 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.

    I hrink you are right and folowing your siggestion, I have changed the permissions for the users allocated to this site but even after giving write/modify rights to them it still does not compile and download the excel file.

    Any other suggestions or do I need to add another use type?

     

    Thursday, June 27, 2013 12:20 PM
  • User-431249759 posted

    It`s not to the users that you have to give permission but to the app (unless you have Impersonate=true). Depending on your IIS config and your application web.config your permissions would vary.  CHeck this: http://www.asp.net/web-forms/tutorials/deployment/deployment-to-a-hosting-provider/deployment-to-a-hosting-provider-setting-folder-permissions-6-of-12

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 27, 2013 2:03 PM
  • User743508062 posted

    Hi, I am having trouble finding any of the app-pools in the add user dialog for security, however as you can see I have the following app-pools:

    http://imageshack.us/scaled/medium/21/cg7.png

    This is what I keep getting when I search for default..., asp..., lenscat...,

    http://imageshack.us/photo/my-images/89/u3fu.png/ 

    Any suugestion on how I can overcome this?

    Friday, June 28, 2013 5:14 AM
  • User743508062 posted

    OK some progress but not much, I managed to add the apps to the folder permissions. Now when I click the icon to generate the file for export to excel I am getting blank excel SS in the app_data folder and have long names such as:

    02092d9a-3fb4-4240-8253-bf606ef1b426

    I have many of these so I know that the js is running and the corresponding generating page is creating these temp excel files.

    I am not getting any further than this and for some reason cannot get any info regarding the generating pages in F12 of IE9.

    Any ideas what could be happening now? thanks. 

    Friday, June 28, 2013 8:04 AM
  • User-431249759 posted

    We`re almost there. 

    First of all, go to your particular site and when you have it selected, in IIS7 (or 7.5) on the right hand side you`ll see all actions and what not. Choose "advanced settings". There you`ll be able to see what app pool your site is using. (Most likely its the ASP.NET v4.0.)

    Then go back to your application pools, choose that one and on the right hand side click on advanced parameters. Among the properties in the window, which will popup, you`ll have "Identity". There choose "NetworkService" and then you`ll be able to give it permissions to the folder. 

    AppPoolIdentity is there by default and it will pass user`s identity, which is not suitable in many cases... 

    see this also: http://technet.microsoft.com/en-us/library/cc771170(WS.10).aspx

    Friday, June 28, 2013 8:06 AM
  • User743508062 posted

    Hi, thanks for the reply. The app pool being used by the site is called lenscat.org, and this app-pool did have networkservice as the identity.

    I have given both lenscat.org and netwrokservice read and write permissions for the app_data folder.

    Now I do get the temp excel file being created (the ones with the long random letters and numbers for the name) and this is expected before the genrating code delivers the data and renames it, while deleting the temp excel file.

    FYI, this is the code for the generating page.

    @{    
          Layout = null;    
          
          var appData = Server.MapPath("~/App_Data");    
          var originalFileName = "orgLens.xls";    
          var newFileName = string.Format("{0}.xls", Guid.NewGuid().ToString());    
          var originalFile = Path.Combine(appData, originalFileName);    
          var newFile = Path.Combine(appData, newFileName);    
          File.Copy(originalFile, newFile);
    
          var lenscat = Database.Open("A-LensCatFE-01SQL");
    
          var sql = "Select * FROM dbo.qryExpLens ORDER by CatID, LensCode, StyleID";
          var lensdata = lenscat.Query(sql);
    
          var connString = string.Format(@"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0}/{1};Extended Properties='Excel 8.0;HDR=Yes;'", appData, newFileName);    
          var provider = "System.Data.OleDb";    
          using (var excel = Database.OpenConnectionString(connString, provider)){
              sql = @"INSERT INTO [Sheet1$] (LensCode,CatName,BrandSN,Style,Design,Material,Colour,AR,Description,Requirements,[Index],Density,Abbe,UVA,UVB,OM,Ref,Cost)
                    VALUES (@0,@1,@2,@3,@4,@5,@6,@7,@8,@9,@10,@11,@12,@13,@14,@15,@16,@17)";
    
        foreach(var lens in lensdata){            
            excel.Execute(sql, 
           lens.LensCode,                
           lens.CatName,
           lens.BrandSN,                
           lens.Style,                 
           lens.Design,                 
           lens.material,                 
           lens.Colour,                 
           lens.AR,                 
           lens.Description,
           lens.Requirements,
           lens.Index,
           lens.Density,
           lens.Abbe,
           lens.UVA,
           lens.UVB,
           lens.OM,
           lens.Ref,
           lens.Cost);        
            }    
            }    
            Response.AddHeader("Content-disposition", "attachment; filename=Lens.xls");    
            Response.ContentType = "application/octet-stream";    
            Response.TransmitFile(newFile);    
            Response.Flush();    
            File.Delete(newFile);    
            Response.End();
        
    }

    when I run this page from the localhost on the web server, it compiles and produces the excel file.

    It is when I use the live site that is being served by IIS7.5 that I get this problem. It would be good to see what is happening to the gnerating page when run from the live site but can't fathom how to view this since the page does not load per se but runs in the 'background'.

    FYI, I did also give read/write permissions to asp.net too.

    Any further ideas? Thanks.

     

    Friday, June 28, 2013 8:38 AM
  • User-431249759 posted

    What is exactly happening now?

    Also, are your files unique for each user? If yes, then Lens.xls will not work. That`s why your files have GUID as their name when they are generated so each one stays unique. 

    Friday, June 28, 2013 8:58 AM
  • User743508062 posted

    When I click the icon to run the relevant generating page, the progress bar runs but nothing else is happening, no dowloads options.

    When I look at the app_data folder I see that a new unique file has been created. This is blank other than for the column names as per the original excel file that is used to populate with data.

    The ans to your q are your files unique? I don't know, I have used this sample from Mike's tutorial (found here: http://www.mikesdotnetting.com/Article/207/Exporting-The-Razor-WebGrid-To-Excel-Using-OleDb )

    However, I think that it cannot be unique otherwise no-one would implement am export solution as above. I am lost as to what is happening, so any suggestion would be very useful. Thanks.

    Friday, June 28, 2013 9:31 AM
  • User-431249759 posted

    Your last line that executes properly that we know of is: 

     File.Copy(originalFile, newFile);

    So, look into your Database.Open stuff. Where is that DB? Is it as reachable from the prod server as from your local machine? 

    Also, keeep looking at your event log each time there is a small progress. I`d bet now you have a different kind of error there. 

    Friday, June 28, 2013 10:03 AM
  • User743508062 posted

    Thansk for the event logger heads up, I have found this error is there:

    Log Name:      Application
    Source:        ASP.NET 4.0.30319.0
    Date:          28/06/2013 15:24:27
    Event ID:      1309
    Task Category: Web Event
    Level:         Warning
    Keywords:      Classic
    User:          N/A
    Computer:      GBHOFSAPP0007.xxxx.xxx.xxx
    Description:
    Event code: 3005 
    Event message: An unhandled exception has occurred. 
    Event time: 28/06/2013 15:24:27 
    Event time (UTC): 28/06/2013 14:24:27 
    Event ID: 1ce57f36d1c448ba83871a9c4f60c903 
    Event sequence: 3 
    Event occurrence: 1 
    Event detail code: 0 
     
    Application information: 
        Application domain: /LM/W3SVC/3/ROOT-1-130169030508678236 
        Trust level: Full 
        Application Virtual Path: / 
        Application Path: C:\inetpub\wwwroot\LensCatalogueSystem\ 
        Machine name: GBHOFSAPP0007 
     
    Process information: 
        Process ID: 4392 
        Process name: w3wp.exe 
        Account name: NT AUTHORITY\NETWORK SERVICE 
     
    Exception information: 
        Exception type: InvalidOperationException 
        Exception message: The 'Microsoft.Jet.OleDb.4.0' provider is not registered on the local machine.
       at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
       at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
       at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.OleDb.OleDbConnection.Open()
       at WebMatrix.Data.Database.EnsureConnectionOpen()
       at WebMatrix.Data.Database.Execute(String commandText, Object[] args)
       at CallSite.Target(Closure , CallSite , Database , String , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object )
       at CallSite.Target(Closure , CallSite , Database , String , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object , Object )
       at ASP._Page_GenLens_cshtml.Execute() in c:\inetpub\wwwroot\LensCatalogueSystem\GenLens.cshtml:line 23
       at System.Web.WebPages.WebPageBase.ExecutePageHierarchy()
       at System.Web.WebPages.WebPage.ExecutePageHierarchy()
       at System.Web.WebPages.WebPageBase.ExecutePageHierarchy(WebPageContext pageContext, TextWriter writer, WebPageRenderingBase startPage)
       at System.Web.WebPages.WebPageHttpHandler.ProcessRequestInternal(HttpContextBase httpContext)
    

    I hope you can understand this because I can't Cry

    Thanks.

    Friday, June 28, 2013 10:36 AM
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 28, 2013 11:06 AM
  • User743508062 posted

    Once again thanks for the help. I need to go but will be back on monday to finally resolve this, have a good weekend.

    Friday, June 28, 2013 11:59 AM
  • User743508062 posted

    I trust you had a good weekend and now back to the grind of sorting out issues.

    I read the links you have provided and the most promising advice seemed to be:

    "just right click on the project -> properties -> build -> change from target any cpu to target x86 -> f5 and it will work"

    Except I don't know where I am supposed to right click my project? Do you have any ideas? Please do let me know.

    Thanks.

    Monday, July 1, 2013 5:25 AM
  • User743508062 posted

    I finally understood what that meant, and in case someone else comes across the same issue here is how I got my project working.

    I gave permission to the app-pools to read amd write to the app_data folder. Initially I had problems but hen I relaised you have to also add the name:

     IIS Apppool\name of app <---------- name of app you want to give permission to.

    After this I went back to IIS 7 / IIS 7.5 and went to the app-pool and changed the enable 32-bit Application to TRUE in the Appplication Pool Defalts settings. Once done waited for a few minutes, refreshed app-pool and then opened IE9 and voila download has started occuring!!

    Thanks to everyone and esp @fimine. Hats off.

    Monday, July 1, 2013 6:21 AM