This project has moved. For the latest updates, please go here.

Getting this to work on Production Server

Jun 28, 2011 at 9:57 PM

Hi everyone,

I have a fully tested application using ClosedXML, which takes an existing template .xlsx file, saves it to ~temp folder, manipulates it as required with ClosedXML, and then prompts the user to save or open the file to their local drive.

When they are ready, it takes that file with their information filled in, save it to ~App_Data folder, queries the names ranges to retrieve the data, and save it to SQL server database.

 

This worked perfect in my local environment, but it is failing on the web server (our web server DOES NOT have Excel installed, as it is not considered best practice to install applications there).

Any ideas of what I need to do to get the upload and download to work?  It is usually not possible to write a file from the web server to a user's local machine, but it I could do that, and manipulate the file there instead of the web server, I would be willing to try it.  Does Excel need to be installed on their machine for ClosedXML to work?

 

Thanks

Coordinator
Jun 28, 2011 at 10:02 PM

You do not need Excel and you don't need to hit the disk to create and serve files on a web server. The only thing I can think of is that you're missing the DocumentFormat.OpenXml.dll dll. What error are you getting?

Jun 29, 2011 at 2:14 AM

Hi Manuel,

 

No, I put all the .dlls, including OpenXML, both in the /bin, and local copy, and also in a Library folder.  I'm having trouble seeing the error even with Cutsom Errors set to false.  The modified (if they even get that far) excel files are not saved in the destination folder.  However, I have it all in Try..Catch, and there doesn't seem to be an error thrown.  What should cause it to work fine on my local machine, and fail on the server?  Tomorrow, I'm going to make sure that write permissions exist on the folder (I assumed they were, using the aspnetUser account), and will try using Tracing on the page.  Last, I may save a simple "save" to the folder just to see if I can do that.  I was pretty sure today that Excel was not required on the server.  Thanks,

 

I'll let you know what I find out.

Jun 29, 2011 at 1:42 PM

I do this all the time in my application using ClosedXML with .Net 3.5, VS 2008 and the MVC framework. Upload a file via the browser from the local machine to the server and downloading a modified file back to the browser. The only thing I can think of is to be sure that your application has write permission to the directory on the server and that you are specifying the directory properly. Remember the directory needs to be somewhere under the web root of your application. Run a test where your application just creates a file in your target directory and writes some text. See if the text file gets created.

By the way, I agree with @MDeLeon. Usually when I am creating an Excel file for downloading to the browser, I do the workbook.SaveAs to a MemoryStream and return the MemoryStream and never save the created Excel file on disk. However, I usually save the uploaded files on disk on the server and then delete them when I am done saving the data to the database.

Jun 29, 2011 at 3:41 PM

Robert,

 

Do you have a small snippet that does these 2 actions (not in MVC ActionRequests, just plain old C#)?  I have the the write permissions on the folder, and it is under the root.

Coordinator
Jun 29, 2011 at 3:48 PM

Check the FAQ on the Documentation tab...

Jun 29, 2011 at 3:53 PM

Where does the filestream save the file?  This looks like it just renders it on the page.

Coordinator
Jun 29, 2011 at 4:05 PM

It doesn't save it, it just returns it to the users for them to download.

Jun 29, 2011 at 4:13 PM

I have to save to template to a temporary file so ClosedXML can modify it.  It is this changed file that I want them to download.  The file is not being saved to the temp location.  Below is the error I'm getting:

 

Access to the path 'C:\Inetpub\wwwroot\HurricaneLosses\temp\HurricaneLossTemplate2007_blather_Test Event_February 28 2011.xlsx' is denied

 

Are you saying I don't have to save it to manipulate it and return it to them?

What about when I upload the file- I'm pretty sure that I have to save it in my application folder to process it, even if I delete it later?

Coordinator
Jun 29, 2011 at 4:21 PM

That's a permissions error, talk to your system/network administrators and they'll help you with that.

As for manipulating the files, you have two options:

1) Start a new workbook, in which case you don't need to save to disk to return it to your users.

2) Open a template, modify it and then return it to your users. Once again, after modifying it you don't need to save it back to disk (unless you want to, of course).

Jun 29, 2011 at 4:40 PM

Now I am getting "Thread was being aborted"

 

Here is the code- am I missing something?

                var savedName = template + v + "_" + SessionManager.CurrentUser.NAICCode + "_" +
                                   this.ddEvent.SelectedItem.Text + "_" +
                                   string.Format("{0:MMMM dd yyyy}"DateTime.Parse(this.ddlYear.SelectedItem.Text)) + ext;
 
                //var outputFile = Server.MapPath("~/temp") + "\\" + savedName;
                //filepath = outputFile;
 
                // Prepare the response
                HttpResponse httpResponse = Response;
                httpResponse.Clear();
                httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                httpResponse.AddHeader("content-disposition""attachment;filename=\"" + savedName + "\"");
 
                // Flush the workbook to the Response.OutputStream
                using (MemoryStream memoryStream = new MemoryStream())
                {
                    workbook.SaveAs(memoryStream);
                    memoryStream.WriteTo(httpResponse.OutputStream);
                    memoryStream.Close();
                }
 
                httpResponse.End();
Jun 29, 2011 at 5:33 PM

I have done a sample located here that will help you fulfilling your goal. Ciao!

Jun 29, 2011 at 5:57 PM

Hi akshayakrsh,

Your project has a lot of great helper methods.  I'm on a pretty tight deadline getting mine into production, so I'm not going to be able to use a lot of that now, but I will definitely in the future.  One thing- what kind of project is Excel.Cloud?  It would not load as a recognized project for me (I'm using VS 2010 Ultimate SP1).

 

I copied the sample in FAQ exactly, and it works fine- I must have something in my code, perhaps the name(?) that doesn't work on the stream or server.

Many thanks

Jun 29, 2011 at 7:57 PM

 I am still getting 'Thread Aborted' error.  Here is the basic code:  I would appreciate some help, since I just can't get this to work. (all the permissions are correct)

                var v = ddlVersion.SelectedValue;
                var ext = ".xlsx";
                var template = config.HLRSTemplateName;
 
                //should retrieve  'HurricaneLossTemplate2007.xlsx' from ~/templates"
                var filepath = Server.MapPath("~/templates"+ "\\" + template + "2007" + ext;
                var workbook = new XLWorkbook(filepath);
                // Prepare the response
                HttpResponse httpResponse = Response;
                httpResponse.Clear();
                httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                httpResponse.AddHeader("content-disposition""attachment;filename=\"HelloWorld.xlsx\"");
 
                // Flush the workbook to the Response.OutputStream
                using (MemoryStream memoryStream = new MemoryStream())
                {
                    workbook.SaveAs(memoryStream);
                    memoryStream.WriteTo(httpResponse.OutputStream);
                    memoryStream.Close();
                }
 
                httpResponse.End();
Coordinator
Jun 29, 2011 at 7:59 PM

When you debug it, on which line does it blow up?

Jun 29, 2011 at 8:07 PM
httpResponse.End();
Coordinator
Jun 29, 2011 at 8:11 PM

http://wiki.asp.net/page.aspx/721/using-responseredirect-and-responseend-in-trycatch-block/

Jun 29, 2011 at 9:16 PM

Thanks Manuel, it worked, though I'm not sure why it was needed and is not on your FAQ. I have another requirement- that is to save the file as 2003.xls if they require it.  I am saving the file I manipulated above, but it is crashing  when I try to use

var app = new Microsoft.Office.Interop.Excel.Application();  Is there another way to output the stream to the 2003 format?  Here's the error:

Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

               if (v == "2003")
               {
                  
                    workbook.SaveAs(filepath);
                    SessionManager.FileSubmitted = "saved";
                    var changed = Path.ChangeExtension(filepath, ".xls");
                    SessionManager.FileSubmitted = "changed";
                    var app = new Microsoft.Office.Interop.Excel.Application();
                    SessionManager.FileSubmitted = "OPd";
                    var wb = app.Workbooks.Open(filepath);
                    SessionManager.FileSubmitted = "opened";
                    wb.SaveAs(changed, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8);
                    SessionManager.FileSubmitted = "savedas";
                    wb.Close();
                    SessionManager.FileSubmitted = "closed";
                    app.Quit();
                    SessionManager.FileSubmitted = "quit";
                    var fileName = Path.GetFileName(changed);
                    SessionManager.FileSubmitted = "getpath";
                    //Response.Redirect("~/temp" + "\\" + fileName);
                    //Response.Redirect("AddEvents.aspx", true);
                }
Coordinator
Jun 29, 2011 at 9:31 PM

"I'm not sure why it was needed and is not on your FAQ."

Because this is the first time anyone asked about this here (it's not a ClosedXML issue).

As for your Interop error, my guess is that you haven't installed Excel on the machine where the code is running.

Jun 29, 2011 at 9:40 PM

Yes you are correct.  Why am I the first to encounter it?  The issue is that the application runs on the web server, and no Office products are installed.  The code does work on my local machine, where Office is installed.  The file that is saved is already processed with ClosedXML, and saved.  I just need to downgrade it, so users can open it if the don't have Excel 2003 or greater.  Is there any way to convert the stream  to the lower version?  Or any other way to make this work?  I wish I could set the requirements, but I am just the developer.

Coordinator
Jun 29, 2011 at 9:53 PM

To save Excel 2003 files you need to install Excel on the server or get a commercial spreadsheet component.

Jun 30, 2011 at 6:30 AM
SteveMets wrote:

Hi akshayakrsh,

Your project has a lot of great helper methods.  I'm on a pretty tight deadline getting mine into production, so I'm not going to be able to use a lot of that now, but I will definitely in the future.  One thing- what kind of project is Excel.Cloud?  It would not load as a recognized project for me (I'm using VS 2010 Ultimate SP1).

 

I copied the sample in FAQ exactly, and it works fine- I must have something in my code, perhaps the name(?) that doesn't work on the stream or server.

Many thanks

The cloud project is of Windows Azure, you dont need to worry about it, it doesnt contain anything extra. I had done this prototype to check if this closed xml operation works on both Asp.Net and Windows Azure. 

Jul 6, 2011 at 9:17 PM

Hi akshayakrsh, and Robert.  I think you have done some posts on the NetOffice project here on CodePlex.  I have an open thread about trying to save an existing .xlsx file as 2003 .xls  I have used the following Excel.XlFileFormat, with results:

xlWorkbookNormal - fail, hangs,locks .xlsx
xlExcel7 = 39, fail, hangs,locks  .xlsx
xlExcel5 = 39, fail, hangs,locks .xlsx
xlExcel9795 = 43, fail, hangs,locks .xlsx
xlXMLSpreadsheet = 46, fails, page not found on redirect to file, no file created
xlExcel12 = 50,fail, hangs
xlOpenXMLWorkbook = 51,fail, hangs
xlWorkbookDefault = 51, - fail, hangs
xlExcel8 = 56, fail, hangs

Here's the line that is failing:

oWB.SaveAs(newFileName, Excel.XlFileFormat.xlOpenXMLWorkbook, nullnullfalsefalse,
                           Excel.XlSaveAsAccessMode.xlShared, falsefalsenullnullnull);

Sebastian (NetOffice creator) advised me to try to open and Save As .xls on the server, and both worked when I did this manually.  I think this is proof that the Excel install on the server is working.  Can you direct me to anyone, or a link, that has the Save As working on their server?  If I could output it to FileStream rather than saving the .xls to disk, that would be a plus, but right now I just want to save the damn thing!

 

Thanks

Jul 6, 2011 at 9:55 PM

Hi akshayakrsh, and Robert and Manuel.

I finally found out what the problem is, and it is not my code.  For some reason, there is an extra layer of permissions when you want to Save As.  That is why the Save worked, but the Save As did not, at least via the AspNet worker thread.

This has been a painful, but informative experience, and I want to thank each of you for playing along.  I will tell you that ClosedXML is superb, and I will use it for all Excel work that I use.  It's a good skill to have since the users don't want to give up their spreadsheets.  I love my DBA, but he could have told me about this last week!

 

Cheers

Jul 7, 2011 at 2:53 PM

Glad it's working for you. Yeah!