1

Is it possible in any way to edit an excel sheet through an ASP.net page that contains macro. I have tried to open the Excel sheet and it seems to just hang rather than load the excel. Testing on a page without macros works perfectly fine?

Community
  • 1
  • 1
karlstackoverflow
  • 3,298
  • 6
  • 30
  • 41
  • You cannot use Office Automation from a server process. – John Saunders Feb 29 '12 at 03:23
  • Is there anyway I can just edit the excel, like remove the macro and reapply it through code?? – karlstackoverflow Feb 29 '12 at 03:24
  • 1
    @JohnSaunders Yes, he can, it just needs to be done with extreme care and it takes a lot of configuration to get it run with the right privileges. I didn it myself with Excel 2000, 2003 and 2007. – xxbbcc Feb 29 '12 at 03:24
  • @xxbbcc: no, he can't, not if he expects it to actually work reliably, be supported, and not violate his Office license. Office Automation is designed for desktop applications, does not take multiple threads into account, assumes the presence of a message loop, and will cause far more subtle problems than it's worth. – John Saunders Feb 29 '12 at 03:28
  • @JohnSaunders I don't know the legal angle but I guarantee you that it works. I haven't done it with Excel 2010 but I made it work with multiple versions of Excel. I did Excel workbook parsing/generation in multiple web projects. – xxbbcc Feb 29 '12 at 03:30
  • @xxbbcc: what about testing under load, with multiple threads in the code at the same time? Doesn't work quite as well. – John Saunders Feb 29 '12 at 03:34
  • @kjt15 Give me a few minutes to look up some of the details. As I said, I didn't do this with 2010, and I don't remember all the steps anymore. The COM configuration to get it working was pretty complicated and took us quite some time to figure out. A Microsoft article helped eventually but I need to see if I can find it. – xxbbcc Feb 29 '12 at 03:35
  • possible duplicate of [asp.net web service using office 2010 COM](http://stackoverflow.com/questions/7382704/asp-net-web-service-using-office-2010-com) – John Saunders Feb 29 '12 at 03:39
  • @JohnSaunders Note that this solution is not necessarily a violation of Office license terms. See my comment below regarding intranet sites and enterprise licenses. – xxbbcc Feb 29 '12 at 15:51

2 Answers2

4

Disclaimer: I don't know the Excel license agreement and I don't know if utilizing Excel in a server process violates it or not. This is purely a technical description of how to get it working. The reader is advised to check the license agreement to see if it's allowed to do so or not. Different Office versions may have different license agreements. I used this method at several Fortune 100/500 companies and they didn't seem to care. Go figure.

This solution works but it has some limitations and require a fair amount of control over the server where it runs. The server also needs to have lots of memory.

To start, make sure that you perform a complete installation of every single Office feature on the server so that Excel won't try to install something if you attempt to use a feature that's not present.

You also need to create a dedicated user account on the server that has the right privileges. I can't tell you what exactly because in my case we controlled the server and we gave admin rights to this user.

When you have the user account, you need to log in as that user and run Excel (preferably all Office applications) at least once so that it can create its settings.

You also need to configure Excel to run under this user account when it's created as a COM object. For this, you need to go into DCOM Config on the server and configure Launch and Activation Permissions for the Excel.Application object to use your new user account. I'm not sure if I remember correctly, but I think after this step, running Excel as an interactive user was slightly problematic.

By default, Office applications try to display various messages on the screen: warnings, questions, etc. These must be turned off because when you utilize an Office application from a web application, it runs on the server so a human user won't be around to dismiss these messages - the Office program will just sit around indefinitely, waiting for the message to be dismissed.

You need to set (at the minimum) these properties:

DisplayAlerts = false
AskToUpdateLinks = false
AlertBeforeOverwriting = false
Interactive = false
Visible = false
FeatureInstall = 0 'msoFeatureInstallNone

to disable UI messages from Excel. If you use Excel 2010, there may be more, but I'm not familiar with that.

If you have Excel files with macros in them, you may have to disable macro security in Excel - that can't be done programmatically, for obvious reasons.

To access Excel services, implement a manager object that will actually hold the Excel reference - don't try to hold the Excel.Application object in the page because your page code will become very complicated and you may not be able to properly clean things up.

The object that holds the Excel reference may be a separate DLL or an out-of-process server. You must make sure, however, that when you acquire an instance of Excel on a given thread you always create a new Excel instance. The default is that an already running Excel instance will also serve other requests but that won't work for you because the same Excel instance cannot be shared among multiple threads. Each request-processing thread in IIS must have its own Excel instance - if you share instances, you'll have all kinds of problems. This means that your server will need to have quite a bit of memory to have many instances of Excel running. This was not an issue for me becasue we controlled the server.

If you can, try to create an out-of-proc (.exe) COM server because this way you can hold the Excel reference in a separate process. It's possible to get it working using an in-proc (.dll) COM object but it'll be more risky to your application pool - if Excel crashes, it'll crash your app pool as well.

When you have an .exe server, you can pass parameters in several possible ways:

  1. Make your manager objet a COM object and pass parameters as properties.
  2. Pass parameters as command-line parameteres to the .exe as it starts up.
  3. Pass parameters in a text/binary file; pass the name of the file on the command-line.

I used all these and found the COM object option the cleanest.

In your manager object, follow these guidelines:

  • Wrap every single function that uses Excel in a try..catch block to capture any possible exception.
  • Always explicitly release all Excel objects by calling Marshal.ReleaseComObject() and then setting your variables to null as soon as you don't need them. Always release these objects in a finally block to make sure that a failed Excel method call won't result in a dangling COM object.
  • If you try to use any formatting features in Excel (page header, margins, etc.) you must have a printer installed and accessible to the user account that you use to run Excel. If you don't have an active printer (preferably attached to the server), formatting-related features may not work.
  • When an error happens, close the Excel instance that you're using. It's not likely that you can recover from Excel-related errors and the longer you keep the instance, the longer it uses resources.
  • When you quit Excel, make sure that you guard that code against recursive calls - if your exception handlers try to shut down Excel while your code is already in the process of shutting down Excel, you'll end up with a dead Excel instance.
  • Call GC.Collect() and GC.WaitForPendingFinalizers() right after calling the Application.Quit() method to make sure that the .NET Framework releases all Excel COM objects immediately.

Edit: John Saunders may have a point regarding the license agreement - I can't advise about that. The projects that I did using Word/Excel were all intranet applications at large clients and the requirement to use Word/Excel was a given.

The link he provided also has some tools that may be useful, although those libraries won't have full Excel functionality and if that's what you need, you don't have a choice. If you don't need full Excel functionality, check out those libraries - they may be much simpler to use.

A few links that may be useful to people trying to implement this approach:

Community
  • 1
  • 1
xxbbcc
  • 16,930
  • 5
  • 50
  • 83
  • Can you tell me the properties that I can use to disable the UI messages :):):) – karlstackoverflow Feb 29 '12 at 03:28
  • +1. Please back on topic, to disabling the UI messages. I tried the application.visible but it doesnt seem to reach that code after the .open – karlstackoverflow Feb 29 '12 at 03:33
  • I penalize your answer because you have _not_ got it working in a way that will help the OP. Have you done so in an ASP.NET application? Have you tested under load, with multiple threads hitting the Office Automation code at the same time? Do you know that the Office Automation code was not created to handle multiple threads in the code at the same time, and may not even have been _tested_ that way? – John Saunders Feb 29 '12 at 03:34
  • @JohnSaunders it was absolutely under load. (Although not millions of users.) All sites were intranet sites and they typically parsed/generated somewhere around 2000 Word/Excel documents a day, from about 150 concurrent users. We had to use multiple instances of Word/Excel to make it working, exactly because of the threading issue. In one setup we had a COM out-of-proc server that took input through its parameters and then saved output into a database. The .exe server then held the reference to Excel. In other setups, the business object (called from the page) held the reference. – xxbbcc Feb 29 '12 at 03:41
  • Note that what you did can work. What the OP is talking about is far simpler, and cannot work.In your case, you could even have run the per-instance processes with a message loop if necessary. – John Saunders Feb 29 '12 at 03:44
  • If you post your comment in your answer, so that future readers know they need to use per-instance, separate processes, then I'll remove the downvote. – John Saunders Feb 29 '12 at 03:45
  • @JohnSaunders sure, I'll do that. We also had to setup a specific user account and force Excel to run under that. To get that to work, we had to login as that user and run Excel/Word that way so they had a chance to create their settings, registry, etc. – xxbbcc Feb 29 '12 at 03:49
  • Please mention the out-of-proc server, how you can't access the DLL from multiple threads, etc. – John Saunders Feb 29 '12 at 03:51
  • I've tried all these property changes and it still won't open the excel document. hmmm – karlstackoverflow Feb 29 '12 at 04:08
  • @kjt15 Those properties won't make Excel open the document by default. They're the bare minimum to get Excel working in a server process. Give me some more time to finish the answer. – xxbbcc Feb 29 '12 at 04:27
  • Please do not advocate solutions that violate Microsoft's license agreements regarding Office. – Jared Shaver Feb 29 '12 at 04:30
  • I'm not actually sure how i am violating anything please explain? I just want to open my excel document through asp.net, and for some reason macros wont let me? – karlstackoverflow Feb 29 '12 at 04:38
  • @kjt15 Ok, I think it's done. It's a fairly lengthy process but it describes in detail how we got this working. As for the legality of this: I used this method to get Office applications run on servers at several Fortune 100 companies. Go figure. – xxbbcc Feb 29 '12 at 04:56
  • @JaredShaver The linked Microsoft article specifically mentions that all clients using the server must have a license. If that's the case, I don't see why this would be a violation of the license terms. If a company has an enterprise-wide Office license and the website (using Excel) is an intranet site, it's probably perfectly legal to use Excel from the web application - only intranet users can access it and they all have a license. – xxbbcc Feb 29 '12 at 05:34
  • Note that this configuration is in no way supported: http://support.microsoft.com/kb/257757 Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment. – ta.speot.is Mar 02 '12 at 22:39
  • I ended up just working with the macros and putting ALL the code into the macros (VBA format). Annoying indeed. – karlstackoverflow Mar 06 '12 at 05:27
  • Awarding xxbbcc +1 and a tick for amazing amount of effort. gg! – karlstackoverflow Mar 06 '12 at 05:28
  • xxbbcc, you saved the day! Thanks for sharing. – Alexander van Trijffel Jan 10 '16 at 18:40
  • @AlexandervanTrijffel I'm glad if this was useful. – xxbbcc Jan 10 '16 at 22:13
0

The story changed a little while ago, with HPC Services for Excel.

With that, you can do Office Automation on a web server. I'm still trying to determine how it fits my situation, but you may want to check it out.

Glen Little
  • 6,951
  • 4
  • 46
  • 68