OmegaMan's Musings

With VS2010 and .Net 4, working with the office interops has become a lot easier. This article gives a step by step view of how to open a word document without needing the tools of Visual Studio Tools for Office (VSTO). *

The following topics are demonstrated:

1. Open and properly close a Word Document.
2. Write to a Word document.
3. Remove the Word document’s meta data.
4. Properly close the Word Application and clean up resources opened by the underlying Office Interop calls.
5. Properly cast method calls to specific interops to avoid “Ambiguity between method” issue CS0467 C# compiler warning.
6. Why the developer no longer has to reference null when passing in optional parameters to COM objects thanks to .Net 4.

Steps

1. In VS2010 create a Console Application.
2. In the Solutions Explorer right click on the References folder and select Add Reference.  In the .Net tab search for
   Microsoft.Office.Interop.Word.
   Note you can use version 12 or version 14; but you might as well use the latest 14.  
3. Insert the following usings:

   using Microsoft.Office.Interop.Word;

4. Create an existing Word document. The example code below uses an existing document at C:\TestDoc.docx.
5. Insert this code:

   Application ap = new Application();
   
   try
   {
   
       Document doc = ap.Documents.Open( @"C:\TestDoc.docx", ReadOnly: false, Visible: false );
       doc.Activate();
   
       Selection sel =  ap.Selection;
   
       if ( sel != null )
       {
           switch ( sel.Type )
           {
               case WdSelectionType.wdSelectionIP:
                   sel.TypeText( DateTime.Now.ToString() );
                   sel.TypeParagraph();
                   break;
   
               default:
                   Console.WriteLine( "Selection type not handled; no writing done" );
                   break;
   
           }
   
           // Remove all meta data.
           doc.RemoveDocumentInformation( WdRemoveDocInfoType.wdRDIAll );
   
           ap.Documents.Save( NoPrompt: true, OriginalFormat: true );
   
       }
       else
       {
           Console.WriteLine( "Unable to acquire Selection...no writing to document done.." );
       }
   
       ap.Documents.Close( SaveChanges: false, OriginalFormat: false, RouteDocument: false );
   
   }
   catch ( Exception ex )
   {
       Console.WriteLine( "Exception Caught: " + ex.Message ); // Could be that the document is already open (/) or Word is in Memory(?)
   }
   finally
   {
       // Ambiguity between method 'Microsoft.Office.Interop.Word._Application.Quit(ref object, ref object, ref object)' and non-method 'Microsoft.Office.Interop.Word.ApplicationEvents4_Event.Quit'. Using method group.
       // ap.Quit( SaveChanges: false, OriginalFormat: false, RouteDocument: false );
       ( (_Application)ap ).Quit( SaveChanges: false, OriginalFormat: false, RouteDocument: false );
   
       System.Runtime.InteropServices.Marshal.ReleaseComObject( ap );
   }

Explanation

• Line 1: Open Word application object found in the Microsoft.Office.Interop.Word namespace.
• Line 6: Open up the Word document we are interested in. This is first instance of using named parameters in calling the underlying COM functionality.
• Line 7: Activate makes sure our document has focus in the event that Word is already open.
• Line 9-17: This code is a template to working with a selection and its type. Word distinguishes areas of interest by selections. Our selection is at the beginning of the document. When we get the selection we write the current date and time (line 16).
• Line 27: Remove all of the meta data of the document. This is not required for writing, just as an added bonus of how-to.
• Line 29: Save what we have done.
• Line 37: Close the document.
• Line 48: In line 47 if we call without the cast we get the ambiguity warning message from the compiler. By casting to the Application interface we avoid that warning.
• Line 50: Release any COM handles or resources we may have inadvertently gotten in this process.

* For the record VSTO is only needed when we are creating a smart document or an addin to one of the office applications. This is pure interop programming and not VSTO.

It is a common misconception that one needs the office interops to create Excel documents for the user to download when dealing with Asp.net on a server. Well this article shows one how to create an Excel document from just the data at hand, say from Linq entities, without being tied to any control nor having to use office interops!

How Does one Send the Data to the User?

Here is the shell which will encompass the code which creates the document. Basically one uses the response class to send the data back to the user, say on a button click or some other relevant event. By specifying that this object will be a ms-excel file and it has an .xls extension, that is half the battle right there.

Response.Clear();
Response.Buffer      = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader( "Content-Disposition", "attachment;filename=registrationData.xls" );
this.EnableViewState = false;

//  Code here to generate the xls document.
...

HttpContext.Current.Response.Write( /* String Buffer to Here */ );
HttpContext.Current.Response.End();

That was pretty easy to send the information to the user using a few C# classes which are globally exposed to the code-behind of our web page.

Generate an Excel document from Data in C#

Our goal is not to be tied to any control on the web page, though some controls such as the GridView have the ability to easily export a table which can be passed on. But for our purposes we want to simply extract the data from a linq query and generate the document. By keeping this example generic, one can adapt it to many different situations.

// We will be creating html on the fly via the
// string and html writers to create a table on the fly.
using ( StringWriter sw = new StringWriter() )
    using ( HtmlTextWriter htw = new HtmlTextWriter( sw ) )
    {
        //  Create a table to contain the grid
        Table table = new Table();

        //  Gridlines to box the cells
        table.GridLines = System.Web.UI.WebControls.GridLines.Both;

        // We are going to add the header row to the data first.
        List columns = new List() {
        "Sent", "Sent Date", "User Name", "Last Name", "First Name",
        "Work Phone", "Work Email",    "Number of Tickets", "Street Address 1",
        "Street Address 2",    "City", "State", "Zip Code" };

        // Each row will need to be declared upfront and cells added later.
        TableRow tRow = new TableRow();
        string value;

        foreach ( string name in columns )
            tRow.Cells.Add( new TableCell() { Text = name } );

        table.Rows.Add( tRow ); // Done! Header row added.


        // Note TableCell has a property BackColor. Once can set the
        // that within the cell such as BackColor = System.Drawing.Color.Blue
        // and that will carry into the Excel Document!

        // UserData is our Linq entity list and we will enumerate it to fill the into cells of the row
        // and subsequently into the table.
        foreach ( var usr in UserData )
        {
            tRow = new TableRow();

            value = ( usr.ticketsSent == null ) ? "N" : usr.ticketsSent.Value.ToString();
            tRow.Cells.Add( new TableCell() { Text = value } );

            value = ( usr.ticketsSentDate == null ) ? string.Empty : usr.ticketsSentDate.Value.ToShortDateString();

            tRow.Cells.Add( new TableCell() { Text = value } );
            tRow.Cells.Add( new TableCell() { Text = usr.userName } );
            tRow.Cells.Add( new TableCell() { Text = usr.lastName } );
            tRow.Cells.Add( new TableCell() { Text = usr.firstName } );
            tRow.Cells.Add( new TableCell() { Text = usr.workNumber } );
            tRow.Cells.Add( new TableCell() { Text = usr.workEmail } );
            tRow.Cells.Add( new TableCell() { Text = usr.ticketsRequested.Value.ToString() } );
            tRow.Cells.Add( new TableCell() { Text = usr.address1 } );
            tRow.Cells.Add( new TableCell() { Text = usr.address2 } );
            tRow.Cells.Add( new TableCell() { Text = usr.city } );
            tRow.Cells.Add( new TableCell() { Text = usr.state } );
            tRow.Cells.Add( new TableCell() { Text = usr.zipCode } );

            table.Rows.Add( tRow ); // We add each row to the table.
        }

        //  Translate/Render the table into the htmlwriter
        table.RenderControl( htw );

        //  Response needs the Htmlwriter
        HttpContext.Current.Response.Write( sw.ToString() );
        HttpContext.Current.Response.End();
       }

By simply creating a mock table we can load it and ship it out to the user as an automatic download.

Full Code

Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader( "Content-Disposition", "attachment;filename=registrationData.xls" );
this.EnableViewState = false;

using ( StringWriter sw = new StringWriter() )
    using ( HtmlTextWriter htw = new HtmlTextWriter( sw ) )
    {
        //  Create a table to contain the grid
        Table table = new Table();

        //  Gridline to box the cells
        table.GridLines = System.Web.UI.WebControls.GridLines.Both;

        List columns = new List() {
        "Sent", "Sent Date", "User Name", "Last Name", "First Name",
        "Work Phone", "Work Email",    "Number of Tickets", "Street Address 1",
        "Street Address 2",    "City", "State", "Zip Code" };

        TableRow tRow = new TableRow();
        string value;

        foreach ( string name in columns )
            tRow.Cells.Add( new TableCell() { Text = name } );

        table.Rows.Add( tRow );


        // BackColor = System.Drawing.Color.Blue
        foreach ( var usr in UserData )
        {
            tRow = new TableRow();

            value = ( usr.ticketsSent == null ) ? "N" : usr.ticketsSent.Value.ToString();
            tRow.Cells.Add( new TableCell() { Text = value } );

            value = ( usr.ticketsSentDate == null ) ? string.Empty : usr.ticketsSentDate.Value.ToShortDateString();

            tRow.Cells.Add( new TableCell() { Text = value } );
            tRow.Cells.Add( new TableCell() { Text = usr.userName } );
            tRow.Cells.Add( new TableCell() { Text = usr.lastName } );
            tRow.Cells.Add( new TableCell() { Text = usr.firstName } );
            tRow.Cells.Add( new TableCell() { Text = usr.workNumber } );
            tRow.Cells.Add( new TableCell() { Text = usr.workEmail } );
            tRow.Cells.Add( new TableCell() { Text = usr.ticketsRequested.Value.ToString() } );
            tRow.Cells.Add( new TableCell() { Text = usr.address1 } );
            tRow.Cells.Add( new TableCell() { Text = usr.address2 } );
            tRow.Cells.Add( new TableCell() { Text = usr.city } );
            tRow.Cells.Add( new TableCell() { Text = usr.state } );
            tRow.Cells.Add( new TableCell() { Text = usr.zipCode } );

            table.Rows.Add( tRow );
        }

        //  Htmlwriter into the table
        table.RenderControl( htw );

        //  Htmlwriter into the response
        HttpContext.Current.Response.Write( sw.ToString() );
        HttpContext.Current.Response.End();
   }

Caspol is used to setup trusts for .Net applications. But the command line version to view what has been done is not user friendly. A way to view trusts that have been setup can be found in the Microsoft .Net Framework Configuration 2.0 tool. That tool is installed with the .Net SDK. Note: this tool will not be listed in the SDK folder but a shortcut will be added to the Administrative Tools folder on the Program Menu.

Once it is running, check for your trusts that you have setup by looking at this folder

.Netframework 2.0 Configuration\My Computer\Runtime Security Policy\Machine
\ Code Groups

That should list all remote assembly(ies) that one has caspol setting up a trusts for… Note those don’t have to be assemblies but can be a full directory to trust.

By looking at All_Code\LocalIntranet_Zone one can see the pathing to your server as an URL. If you named the trust, it should be easy to find.

Debugging Trust Issues

If when debugging a trust issue, one needs to find the assembly in question as a listing such as \\Server\directory\MyAssembly.dll or within a listing such as \\Server\Directory\* . If that is not found, then trust needs to be setup using CasPol.

Suggestions

1. One can temporarily turn off caspol security by opening up a DOS shell and typing caspol -s off. While the window is open, caspol will be turned off for debugging purposes. Once the window is closed it will be turned on.
2. If one has remote assemblies on an intranet directory, one can set the whole directory to be trusted by specify a * when using caspol instead of an assembly or executable. That way if there are multiple assemblies within that directory, you don’t have to specify trust for each one.

(Updated 11/22/2011 removed yellow font color)

Most people when working in C# or VB.Net using the VSTO package confuse a VSTO problem with an Interop problem. They are actually quite different. Most of the problems a developer will encounter are actually interops problems with the document they are working on. The problem with the interops is that the documentation of them is sparse and sometimes incomplete. But there is a way to get free fast examples of interop usage and this is the Tribal Knowledge of working with interops:

One way to divine the inner workings of any office document and how to manage it via the interops is to record a macro of the process needed. Once done examine the vba code, it will show settings changes and other items of interest that can lead the way through the tribal knowledge of the interops. Most the object calls are the same under the covers…

If that fails try posting a question to the Microsoft interop groups such as Discussions in Office Development or Discussions in Automation.

I discovered that you can use the Application Settings feature to persist data between application execution sessions for VSTO addins/smart documents. For those not familiar with the Settings object see MSDN’s Using Settings in C#, this is a good place to start. I discovered that a settings file can co-exist with the assemblies installed.

Below is an example where a generic VSTO Excel document is created. A user setting is created and named ColdStorage. It is important in Visual Studio to have that as a user setting so the value can be propagated between user sessions; Otherwise it is an application setting value which is readonly and will fail for our purposes. So set that value.

Steps

1. Create a VSTO Excel document in VS2005.
2. Create a user setting named ColdStorage as type string.
3. Copy the below code into the Workbook startup and shutdown method

Startup

private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{

// Write current value from storage to a sheet

Excel.Range rangeB2 = Globals.Sheet1.get_Range("B2", missing);

rangeB2.Value2 = ExcelWorkbookTestSettings.Properties.Settings.Default.ColdStorage;

}

Shutdown

private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
{
    // Read from the sheet and save
    Excel.Range rangeB = Globals.Sheet1.get_Range("B2", missing);

    ExcelWorkbookTestSettings.Properties.Settings.Default.ColdStorage = rangeB.Value2.ToString();

    ExcelWorkbookTestSettings.Properties.Settings.Default.Save();

}