This walkthrough demonstrates several new features in C# and Visual Basic. The walkthrough focuses on how the new features, when used in conjunction, can greatly simplify Office development, although most of these new features are useful in other contexts.

In this walkthrough, you will create a class that represents a bank account. You will then create collections of objects of that class. Next, you will create an Office Excel worksheet, populate it with data from a collection, and embed the worksheet into an Office Word document. Finally, you will find a reference to the primary interop assembly (PIA) for your program and learn how to turn the PIA dependency on and off. When the PIA dependency is removed, end users can run your program without having the Office PIA installed on their computers.

This walkthrough demonstrates the following new language features:

New features in Visual C#:

• Optional and named parameters
• Optional ref parameter modifier
• Dynamic dispatch on COM calls returning Object

New features in Visual Basic:

• Auto-implemented properties
• Statement lambdas
• Collection initializers
• Implicit line continuations

New feature in both languages:

• No-PIA deployment

Prerequisites: You must have Excel 2007 and Word 2007 installed on your computer to complete this walkthrough.

To create a new console application

1.    On the File menu, point to New and then click Project. In the New Project dialog box, in the Template Categories pane, expand Visual Basic or Visual C#, and then click Windows. At the top of the Templates pane, make sure that .NET Framework 4.0 is selected. Then click Console Application and click OK.

2.    In Solution Explorer, right-click the project node, and then click Add Reference. On the .NET tab, select Microsoft.Office.Interop.Excel, version 12.0. Hold down CTRL and click Microsoft.Office.Interop.Word, version 12.0.

3.    Click OK to close the Add Reference dialog box.

To create the bank account class

In this section, you will create a simple class that represents a bank account.

1.    In Solution Explorer, right-click the project node, point to Add, and then click Class to open the Add New Item dialog box. Name the file Account.vb (for Visual Basic) or Account.cs (for C#) and click Add.

2.    Add the following code to the new class. Note that when you declare a property, it is no longer necessary to also create an explicit backing field because the compiler will add one automatically. This is called an auto-implemented property, and it is new to Visual Basic 10.0:

Public Class Account
    Property ID As Integer = -1
    Property Balance As Double
End Class

Note: Be sure to delete any namespace declarations before pasting in this code. To simplify the rest of the walkthrough, the Account class should be outside of any namespace.

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

To import the Office namespaces

There is nothing new in this step. You are just adding Imports statements or using directives to those provided by default so that you do not have to fully qualify the names of the Excel and Word objects each time you reference them.

• At the top of the Module1.vb or Program.cs file, add the following code:

Imports Microsoft.Office.Interop

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

To add data to the account class

This step demonstrates collection initializers, which provide a convenient and expressive way to populate a collection like a list or array with elements when you first create the object. This feature was introduced in C# in Visual Studio 2008 and is introduced in Visual Basic in Visual Studio 2010.

In the Main method of your application, add the following code:

Dim checkAccounts As New List(Of Account) From {
            New Account With {
                                  .ID = 345,
                                  .Balance = 541.27
                             },
            New Account With {
                                  .ID = 123,
                                  .Balance = -127.44
                             }
            }

var checkAccounts = new List<Account> {
                    new Account {
                                      ID = 345,
                                      Balance = 541.27
                                },
                    new Account {
                                      ID = 123,
                                      Balance = -127.44
                                }
               };

To display the account data in Excel

This step demonstrates how to create a new Excel workbook and populate it with data from the List<Account> or List (Of Account) that was initialized in the previous step. Action is a delegate type; several Action delegates are defined that have differing numbers of input parameters, but they all return void. In a later step, you will use a statement lambda when calling DisplayInExcel to supply the inline method that matches the Action delegate signature.

1.    Add the following method to Module1 (for Visual Basic) or the Program class (for C#).

Sub DisplayInExcel(accounts As IEnumerable(Of Account),
                   DisplayFunc As Action(Of Account, Excel.Range))
    With New Excel.Application
        .Workbooks.Add()
        .Visible = True
        .Range("A1").Value = "ID"
        .Range("B1").Value = "Balance"
        .Range("A2").Select()
        For Each ac In accounts
            DisplayFunc(ac, .ActiveCell)
            .ActiveCell.Offset(1, 0).Select()
        Next
        .Range("A1:B3").Copy()
    End With
End Sub

public static void DisplayInExcel(IEnumerable<Account> accounts,
                                  Action<Account, Excel.Range> DisplayFunc)
{
    var xl = new Excel.Application();
    xl.Workbooks.Add();
    xl.Visible = true;
    xl.get_Range("A1").Value2 = "ID";
    xl.get_Range("B1").Value2 = "Balance";
    xl.get_Range("A2").Select();
    foreach (var ac in accounts)
    {
        DisplayFunc(ac, xl.ActiveCell);
        xl.ActiveCell.get_Offset(1, 0).Select();
    }
    xl.get_Range("A1:B3").Copy();
}

2.    At the bottom of the Main method, call the DisplayInExcel method by using the following code. Note the use of the statement lambda, which colors the Excel cell red if the balance is negative.

DisplayInExcel(checkAccounts, Sub(account, cell)
                            ' This multiline lambda will set
                            ' custom processing rules.
                            cell.Value2 = account.ID
                            cell.Offset(0, 1).Value2 = account.Balance
                            If account.Balance < 0 Then
                                cell.Interior.Color = RGB(255, 0, 0)
                                cell.Offset(0, 1).Interior.Color =
                                                      RGB(255, 0, 0)
                            End If
                        End Sub)

DisplayInExcel(checkAccounts, (account, cell) =>
                  {
                      // This multiline lambda will set                      // custom processing rules.
                      cell.Value2 = account.ID;
                      cell.get_Offset(0, 1).Value2 = account.Balance;
                      if (account.Balance < 0)
                      {
                          cell.Interior.Color = 255;
                          cell.get_Offset(0, 1).Interior.Color = 255;
                      }
                  });

3.    To automatically adjust the width of these columns to fit their contents, insert the following code at the end of the DisplayInExcel method:

' Insert these lines at the end of the With statement.
.Columns(1).AutoFit()
.Columns(2).AutoFit()

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

Notice that the AutoFit method is being called on the result of the indexed call to Columns, which has a type of Object. Return values of type Object from COM hosts such as Office are automatically treated as type dynamic in C# 4.0 when property Embed Interop Types is set to True, the default value for the property. This allows dynamic dispatch (late binding), and avoids the casts that would be required in C# 3.0. For more information, see section "To restore the PIA dependency."

// C# 3.0 code. Not necessary in C# 4.0 when Embed Interop Types is set to True.
((Excel.Range)xl.Columns[1]).AutoFit();
((Excel.Range)xl.Columns[2]).AutoFit();

To embed the Excel spreadsheet into a Word document

In this step, you will create an instance of Word and paste a link to the Excel worksheet into the document. There is nothing new in the Visual Basic code, because Visual Basic has supported named and optional parameters for a long time. Note, however, that C# 4.0 now supports this feature. The PasteSpecial method actually has seven parameters, but they are all optional, so in C# it is no longer necessary to supply arguments for all parameters.

• Insert the following code at the end of the Main method:

Dim word As New Word.Application
word.Visible = True
word.Documents.Add()
word.Selection.PasteSpecial(Link:=True, DisplayAsIcon:=True)

var word = new Word.Application();
word.Visible = true;
word.Documents.Add();
word.Selection.PasteSpecial(Link: true, DisplayAsIcon: true);

Finally, in the definition for PasteSpecial, note that all of its parameters are ByRef (ref in C#). C# 4.0 allows you to make calls to COM components without having to specify ref in front of each parameter. What can now be done in one line of code used to take about 15 (for this particular function) in C# 3.0:

// C# 3.0 code. Not necessary in C# 4.0
object iconIndex = System.Reflection.Missing.Value;
object link = true;
object placement = System.Reflection.Missing.Value;
object displayAsIcon = true;
object dataType = System.Reflection.Missing.Value;
object iconFileName = System.Reflection.Missing.Value;
object iconLabel = System.Reflection.Missing.Value;
word.Selection.PasteSpecial(ref iconIndex,
                            ref link,
                            ref placement,
                            ref displayAsIcon,
                            ref dataType,
                            ref iconFileName,
                            ref iconLabel);

To run the application

• Press F5 to run the application. First, Excel will open and display a worksheet. Next, Word will open and display a document that contains an embedded link to the Excel worksheet. It should look something like this:

To find the PIA reference

1.    Start a Visual Studio 2010 Command Prompt (from the Visual Studio Tools folder on the Start menu). Type ildasm and press ENTER. Click File and then open your assembly. It will be in your project’s bin\Debug directory by default: My Documents\Visual Studio 10\Projects\project name\project name\bin\Debug\project name.

2.    Notice that you see namespaces for Microsoft.Office.Interop.Excel and Microsoft.Office.Interop.Word. This is because in Visual Studio 2010 the Embed Interop Types option is set to True by default, and only the members of the types that are used from the PIAs are added to your assembly.

3.    Double-click Manifest. You should not see an entry for Microsoft.Office.Interop.Excel or Microsoft.Office.Interop.Wordin the manifest.  Since the types that were used from the PIAs were already embedded into your assembly, there is no need for the assembly to reference the PIA. This means that the PIAs no longer need to be deployed with your application to the end user’s machine.

      The No-PIA feature enables you to compile your application in such a way that references to a PIA are not required; the compiler will import whatever types you use from the PIA into your own assembly. This results in easier deployment; the PIAs do not have to be present on the user's computer. Also, this application can work with multiple versions of Office, because it does not require a specific version of a PIA.

4.    Close the manifest window and the assembly window.

To restore the PIA dependency

5.    In Solution Explorer, click the ShowAll Files button. Expand the References folder and select Microsoft.Office.Interop.Excel. Press F4 to display the Properties window.

6.    Change the Embed Interop Types property from True to False.

7.    Repeat steps 5 and 6 for Microsoft.Office.Interop.Word.

8.    In C#, you must add explicit type casting to the following two statements in DisplayInExcel:

Replace those lines with the following, which provide the necessary type casting

9.    Press F5 to rebuild the project. Verify that everything still runs correctly.

10.  Repeat step 1 to open the assembly in ildasm.

11. Double-click Manifest. You should see the following entry for Excel in the list. There will be a similar entry for Word.

This is an assembly reference to the Excel primary interop assembly (PIA).  Since this assembly is referenced by your application, it needs to exist on the end user’s machine.