Automating Excel using ASP.NET

Question

Background

We are developing some in-house utilities using ASP.NET 2.0. One of which is extracting some information from databases and building an Excel workbook containing a number of spreadsheets with data based on queries into the database.

Problem

The proof-of-concept prototype (a simple ASP.NET page that queries a single item from the database and opens Excel to add data to a worksheet) is working well when run locally on the development machines, happily creating and displaying an Excel spreadsheet as requested. However, when run on our server, we get the following error upon trying to instantiate Excel .

Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel._Application'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

Solution?

We are using the PIA for Excel 2003 and we have Excel 2003 and the PIA installed on the server. Can anyone explain why this isn't working or give us some tips on how we might track the problem down?

Thanks for any assistance you can provide.

Answer 1

From Microsoft, (emphasis in original source):

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.

With a list of reasons why you shouldn't do it:

• ... Many services run under accounts that have no user profiles (such as the SYSTEM account or the IWAM_[servername] accounts). Therefore, Office may not initialize correctly on startup. In this situation, Office returns an error on the CreateObject function or the CoCreateInstance function. Even if the Office application can be started, other functions may not work correctly if no user profile exists.
• If an unexpected error occurs, or if an unspecified parameter is needed to complete a function, Office is designed to prompt the user with a modal dialog box that asks the user what the user wants to do. A modal dialog box on a non-interactive desktop cannot be dismissed. Therefore, that thread stops responding (hangs) indefinitely. Although certain coding practices can help reduce the likelihood of this issue, these practices cannot prevent the issue entirely. This fact alone makes running Office Applications from a server-side environment risky and unsupported.
• Server-side components need to be highly reentrant, multi-threaded COM components that have minimum overhead and high throughput for multiple clients. Office applications are in almost all respects the exact opposite. Office applications are non-reentrant, STA-based Automation servers that are designed to provide diverse but resource-intensive functionality for a single client.

And your code might throw the following errors:

• CoCreateInstance

  • Run-time error '429': ActiveX component cannot create object
  • Run-time error '70': Permission denied
  • CO_E_SERVER_EXEC_FAILURE (0x80080005): Server execution failed
  • E_ACCESSDENIED (0x80070005): Access denied
  • hangs
  • returns with no error, but didn't work

And finally:

Because of the limitations to Office's design, changes to Office configuration are not enough to resolve all issues. Microsoft strongly recommends a number of alternatives that do not require Office to be installed server-side, and that can perform most common tasks more efficiently and more quickly than Automation. Before you involve Office as a server-side component in your project, consider alternatives.

Answer 2

I think the problem is that once you deploy your application to IIS you are suddenly running inside an MTA COM Apartment. I believe that Excel is an STA component and therefore cannot be created inside the MTA. You will need to set the aspcompat option in the page you are using

<%@ page aspcompat=true %>

More Info Here

Answer 3

Consider working with XLSX files (new in Office 2007, but there is a plugin for Office 2003), which are just ZIP files containing XML files which you can manipulate without the need for Excel. The (XML-based) SpreadsheetML is well documented and not too complicated to program against (you might even find a LINQ to SpreadsheetML somewhere on the web).

As was pointed out above, Excel is not really a server product and you might run into all kinds of issues when using it on a server.

Answer 4

Can the user that the ASP.NET Application Pool runs under have access to the application? Try logging in as that user (or change the Application Pool to run as that user) and opening Excel. If that works try running a WinForms application on the server as that user with the code that is failing.

Not sure but I think the PIA assemblies might need to be registered via regsvr32.

I suspect that if you run as Network Service, you will not be able to start Excel (no interactive login, restricted account, etc). Your ASP.NET code runs inside the application pool. You can change the user the application pool runs as through the IIS manager. If you want to check what your code is currently running as look for the w3wp process in Task Manager.

For testing, change the Application Pool to run as the user you know works with Excel.

Answer 5

We use Aspose (commercial). Office on a server is not much fun.

• You have to be careful about the licencing.
• Once in a while you need to kill a hanging process.
• Getting the rights right takes some effort.

It is called PI(t)A for a reason...