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

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 2

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...

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

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