A very common scenario that keeps sprouting new heads like a hydra is Office Automation. Let me start by saying that this is not supported.

There is a KB-article number 257757 discusses this, and clearly states the following:

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.

Why is this not supported?

The crucial thing to consider is the fact that Microsoft Office is designed to be an end-user, single client product. Automating Microsoft Office in a client application, using the identity and security context of the logged on client is supported, but unattended execution is not.

There are a number of things that can go wrong and article 257757 lists most of them. For arguments sake, let's consider the following scenario:

Your server application starts up and uses CreateObject to create an instance of MS Word. Word tries to read the settings of the current client and since your application is using the Network Service account this presents an immediate problem. There are, for example, certain methods that rely on a default printer being installed. All this can lead to serious problems. You then need to monitor the server, since Office might show a modal dialogue for some reason. The "install on first use" feature of MSI might also kick in, prompting the client to install additional features. All this would hang the current thread. Also, running one instance of Word, might be okay, but what happens when you get 100 more or less concurrent requests and each request starts up an instance of Word? MS Word is not a server, it's an excellent piece of single-client software.

So what should I do instead?

Actually, most of your problems can be resolved using HTML. I've seen applications where lines and lines of complex code were used for generating an Excel document, when it could just as well have been made using a standard HTML-table. Excel would have no problems whatsoever reading a table like the one below:

<table>
    <tr>
        <td><b>Person</b></td>
        <td><b>Age</b></td>
    </tr>
    <tr>
        <td>Pete</td>
        <td>30</td>
    </tr>
    <tr>
        <td>Claire</td>
        <td>40</td>
    </tr>
    <tr>
        <td>Average age</td>
        <td>=AVERAGE(B2:B3)</td>
    </tr>
</table>

As you can see all formatting, and even the functions, would be interpreted correctly.

Obviously Word is great with HTML as well, so most formatting issues can quite easily be resolved using this approach.

So how do I do this?

The solution is quite easy. In order to generate an Excel document all you need to do is to create a table with the data you want, and add the following two lines to the Page_Load event of your page:

Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=Data.xls;");

The first line, sets the returned ContentType to Excel, and the second changes the filename of the returned file. This means that the client will be prompted to Open/Save the file, and the filename will be set to "Data.xls" rather than "Default.aspx" (or whatever your original document may be called.)

If you wanted to return a word document you'd set the ContentType to "application/msword" instead.

One little thing to consider is the fact that by default you're probably adding a lot of redundant information to your webpage. You might want to remove all excessive HTML from the page so that you remove all unnecessary headers, stylesheets, viewstate, etc. For an example, please consider the code below:

Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=Data.xls;");
this.EnableViewState = false;
System.IO.StringWriter oSw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtml = new System.Web.UI.HtmlTextWriter(oSw);
Table oTable = new Table();
for (int ctr = 1; ctr <= 10; ctr++)
{
    TableRow oRow = new TableRow();
    TableCell oCell1 = new TableCell();
    oCell1.Text = ctr.ToString();
    TableCell oCell2 = new TableCell();
    oCell2.Text = (ctr*10).ToString();
    oRow.Cells.Add(oCell1);
    oRow.Cells.Add(oCell2);
    oTable.Rows.Add(oRow);
}
oTable.RenderControl(oHtml);
Response.Write(oSw.ToString());
Response.End();

 

Security issues in Office 2007

If you're running Office 2007 you might come across the following error message:

"The file you are trying to open, 'Data.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

<Yes> <No> <Help>

This is completely by design. In this example Excel has noticed that though the file is named .xls it does in fact contain html, so it's warning us about this inconsistency. We might bypass this by actually changing the file extension to htm or, perhaps saving the data as comma separated values (csv) which Excel also supports, but both options would be opened directly in the browser rather than passed on to Excel, so this is a design consideration we have to take into account.

There is really no way of bypassing this security feature other than manually disabling it, client-side, using a registry hack. (The key is HKCU\Software\Microsoft\Office\12.0\Excel\Security\ Add a DWORD named xtensionHardening and set it to 0.) For obvious reasons this is not something that is generally recommended.

Other options

There are also third-party products available for generating MS Office-compatible documents, but there are also some articles in the knowledge base on the subject. For example this little gem which can easily be adopted to ASP.NET as well:

How to use ASP to generate a Rich Text Format (RTF) document to stream to Microsoft Word

/ Johan