How to access Office interop objects

C# has features that simplify access to Office API objects. The new features include named and optional arguments, a new type called dynamic, and the ability to pass arguments to reference parameters in COM methods as if they were value parameters.

In this article, you use the new features to write code that creates and displays a Microsoft Office Excel worksheet. You write code to add an Office Word document that contains an icon that is linked to the Excel worksheet.

To complete this walkthrough, you must have Microsoft Office Excel 2007 and Microsoft Office Word 2007, or later versions, installed on your computer.

To create a new console application

1. Start Visual Studio.
2. On the File menu, point to New, and then select Project. The New Project dialog box appears.
3. In the Installed Templates pane, expand C#, and then select Windows.
4. Look at the top of the New Project dialog box to make sure to select .NET Framework 4 (or later version) as a target framework.
5. In the Templates pane, select Console Application.
6. Type a name for your project in the Name field.
7. Select OK.

The new project appears in Solution Explorer.

To add references

1. In Solution Explorer, right-click your project's name and then select Add Reference. The Add Reference dialog box appears.
2. On the Assemblies page, select Microsoft.Office.Interop.Word in the Component Name list, and then hold down the CTRL key and select Microsoft.Office.Interop.Excel. If you don't see the assemblies, you may need to install them. See How to: Install Office Primary Interop Assemblies.
3. Select OK.

To add necessary using directives

In Solution Explorer, right-click the Program.cs file and then select View Code. Add the following using directives to the top of the code file:

using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;

To create a list of bank accounts

Paste the following class definition into Program.cs, under the Program class.

public class Account
{
    public int ID { get; set; }
    public double Balance { get; set; }
}

Add the following code to the Main method to create a bankAccounts list that contains two accounts.

// Create a list of accounts.
var bankAccounts = new List<Account> {
    new Account {
                  ID = 345678,
                  Balance = 541.27
                },
    new Account {
                  ID = 1230221,
                  Balance = -127.44
                }
};

To declare a method that exports account information to Excel

1. Add the following method to the Program class to set up an Excel worksheet. Method Add has an optional parameter for specifying a particular template. Optional parameters enable you to omit the argument for that parameter if you want to use the parameter's default value. Because you didn't supply an argument, Add uses the default template and creates a new workbook. The equivalent statement in earlier versions of C# requires a placeholder argument: ExcelApp.Workbooks.Add(Type.Missing).

static void DisplayInExcel(IEnumerable<Account> accounts)
{
    var excelApp = new Excel.Application();
    // Make the object visible.
    excelApp.Visible = true;

    // Create a new, empty workbook and add it to the collection returned
    // by property Workbooks. The new workbook becomes the active workbook.
    // Add has an optional parameter for specifying a particular template.
    // Because no argument is sent in this example, Add creates a new workbook.
    excelApp.Workbooks.Add();

    // This example uses a single workSheet. The explicit type casting is
    // removed in a later procedure.
    Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;
}

Add the following code at the end of DisplayInExcel. The code inserts values into the first two columns of the first row of the worksheet.

// Establish column headings in cells A1 and B1.
workSheet.Cells[1, "A"] = "ID Number";
workSheet.Cells[1, "B"] = "Current Balance";

Add the following code at the end of DisplayInExcel. The foreach loop puts the information from the list of accounts into the first two columns of successive rows of the worksheet.

var row = 1;
foreach (var acct in accounts)
{
    row++;
    workSheet.Cells[row, "A"] = acct.ID;
    workSheet.Cells[row, "B"] = acct.Balance;
}

Add the following code at the end of DisplayInExcel to adjust the column widths to fit the content.

workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();

Earlier versions of C# require explicit casting for these operations because ExcelApp.Columns[1] returns an Object, and AutoFit is an Excel Range method. The following lines show the casting.

((Excel.Range)workSheet.Columns[1]).AutoFit();
((Excel.Range)workSheet.Columns[2]).AutoFit();

C# converts the returned Object to dynamic automatically if the assembly is referenced by the EmbedInteropTypes compiler option or, equivalently, if the Excel Embed Interop Types property is true. True is the default value for this property.

To run the project

Add the following line at the end of Main.

// Display the list in an Excel spreadsheet.
DisplayInExcel(bankAccounts);

Press CTRL+F5. An Excel worksheet appears that contains the data from the two accounts.

To add a Word document

The following code opens a Word application and creates an icon that links to the Excel worksheet. Paste method CreateIconInWordDoc, provided later in this step, into the Program class. CreateIconInWordDoc uses named and optional arguments to reduce the complexity of the method calls to