Microsoft Office Automation API Tips and Tricks
Disclaimer: This blog post assumes that you have some familiarity with C# and .NET.
Microsoft's (MS) Office Automation .NET API provides powerful and easy-to-use capabilities to integrate MS Office documents in business applications. Below is a set of links that could become a useful starting point to get acquainted with the API:
- How to: Access Office Interop Objects by Using Visual C# Features (C# Programming Guide)
- Microsoft.Office.Interop.Word Namespace
- Microsoft.Office.Interop.Excel Namespace
- Microsoft.Office.Interop.PowerPoint Namespace
In this blog post, I will demonstrate how someone could overcome some well-known (or maybe not yet well-known) problems with Office Automation.
Before we begin, let me introduce this article from Microsoft: Considerations for server-side Automation of Office. There are many documented problems when using MS Office Automation for server-side applications, and all of them are confirmed. Although this statement might discourage you from implementing Office Automation: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.
Regardless, the above article should not be a blocker for developing business applications based on MS Office. There are some important aspects that should be considered when designing your application, and I'd like to share them here. The facts prove that it is possible to use server-side Automation of Office. Such a considerable fact is our flagship product – PrizmDoc Viewer with its MSO based rendering engine. Please refer to the following page of our online documentation for more information: Natively Render Microsoft Office Documents.
Tip 1: Use a real user account with administrative rights for installing the MS Office and running your application.
MS Office Automation does not work under a LocalSystem user or any other Microsoft Windows integrated service account. Therefore, if you are going to run your application as a Windows service, it should run under a real user account that should be a member of the local system's administrators.
Tip 2: Use an activated copy of MS Office.
The installed copy of Microsoft Office must be activated in order for Microsoft Office Automation to work properly: not licensed, not activated, expired, or trial versions of Microsoft Office will not work.
Tip 3: Adjust non-interactive desktop's heap size for Windows service-based application.
If you are going to run your application as a Windows service, then please consider the following page of PrizmDoc Viewer's documentation: Registry Changes. The more MSO instances should run simultaneously, the more you will need to increase the heap size.
Tip 4: Have a default printer set on the system.
When MS Excel is used for printing or exporting to PDF, it invokes the system's default printer driver to render the pages. If the default printer is not set, then the Excel Automation API will fail with COMException. Also, if exporting to PDF is being used, then I would recommend using a Microsoft XPS Document Writer printer, because it includes a good set of paper sizes.
Tip 5: Be careful with the automation API's properties; they are not regular C# getters.
Practically all getters of the Automation API are COM objects and are being initialized through Marshalling. So, when getting some property, do not forget to release its value after use. Consider following simple code:
Any property or object, retrieved via a COM API that is not a C# embedded type, is a COM object that must be released to avoid resource leaks.
Tip 6: Use robust and adaptive error handling.
Consistently use try/catch/finally blocks when dealing with the Automation API. The COMException may raise quite frequently. Be ready for it. Depending on the context, an exception does not necessarily indicate an error case. For instance, upon opening the document, you might get the error:
One of the possible reasons? The application is busy with some task(s) and cannot execute your command at this time. In such a case, you might consider using retry logic. If after some reasonable number of retries the error is still occurring, then the failing MSO instance should be restarted. Terminate the corresponding MSO application and initialize it again. Below is the example of how to initialize and terminate the Excel application:
In addition, there are a variety of other application specific tips you might want to consider. Download the rest of my article here to learn more.
Aram Nshanyan, Software Engineer, PrizmDoc Viewer
Aram Nshanyan began his Accusoft career in 2008 as a team lead for ImageGear. He is currently working on PrizmDoc Viewer. Aram received his master’s degree from the Armenian State Engineering University. Aram enjoys working on design and development for new challenging projects using C++/C#. When he’s not working, Aram enjoys spending time with family and friends and learning new technologies.