how to Office Developers is called No-PIA

This sample shows how to use the C# 4.0 features called COM Interop, omitting ref, indexed properties and Named and Optional Parameters to create a C# application that communicates with Microsoft Office. C# developers have traditionally had to write relatively verbose code in order to access Microsoft Office applications such as Word or Excel. New C# 4.0 features make it much simpler to call Office APIs.

Consider this declaration for a Microsoft Office method used in this sample:

void PasteSpecial(ref object IconIndex = null, ref object Link = null,
ref object Placement = null, ref object DisplayAsIcon = null,
ref object DataType = null, ref object IconFileName = null,
ref object IconLabel = null);

As you can see, this method takes a fairly large number of parameters. In C#, developers have traditionally had to fill out each parameter, even though the developers of this call had intended to simplify its use by supporting optional parameters. In C# 4.0, the new support for named and optional parameters allows the developer to specify only the parameters of interest, and to take default values for the other parameters:

word.Selection.PasteSpecial(Link: true, DisplayAsIcon: true);

In the call to the PasteSpecial method the Link and DisplayAsIcon parameters are explicitly named, and set to the value true. All the other parameters default to values specified internally by the developers of the Office API, as shown in the above signature.

You can create your own calls that support named and optional paramters. Consider this example:

public void M(int x, int y = 5, int z = 7) { }

In this method, the parameters y and z are assigned default values. Calls to this method might look like this:

M(1, 2, 3); // ordinary call of M
M(1, 2); // omitting z – equivalent to M(1, 2, 7)
M(1); // omitting both y and z – equivalent to M(1, 5, 7)
M(1, z: 3); // passing z by name
M(x: 1, z: 3); // passing both x and z by name
M(z: 3, x: 1); // reversing the order of arguments

A new dynamic feature in C# 4.0 makes Office much easier for C# developers to use. Types used in Office are now presented to C# developers as if they were declared with the type dynamic. Here is the traditionally way to set a Cell property:

((Excel.Range)excel.Cells[1, 1]).Value2 = "ID";

In C# 4.0 developers can now write code that looks like this:

X1.Cells[1, 1].Value = "ID";

A feature called Index Properties allows us to simplify the call further, so that it looks like this:

xl.Cells[1, 1] = "ID";

A final feature of interest to Office Developers is called No-PIA. Primary Interop Assemblies are generated from COM interfaces and provide helpful type support at design time. At runtime, however, they increase the size of your program, and can cause versioning issues. The No-PIA feature allows you to continue to use PIAs at design but omit them at runtime. The C# compiler will bake the small part of the PIA that a program actually uses directly into its assembly. You will no longer need to include PIA's in the distribution of your programs.

// Copyright © Microsoft Corporation.  All Rights Reserved.
// This code released under the terms of the 
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
//

using System;
using System.Collections.Generic;

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

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

public class Program
{
    static void Main(string[] args)
    {
        var checkAccounts = new List<Account> {
                   new Account {
                                      ID = 345,
                                      Balance = 541.27
                               },
                   new Account {
                                      ID = 123,
                                      Balance = -127.44
                               }
               };

        DisplayInExcel(checkAccounts, (account, cell) =>
        {
            // This multiline lambda will set
            // custom processing rules.
            cell.Value = account.ID;
            cell.Offset[0, 1].Value = account.Balance;

            if (account.Balance < 0)
            {
                cell.Interior.Color = 255;
                cell.Offset[0, 1].Interior.Color = 255;
            }
        });

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

    public static void DisplayInExcel(IEnumerable<Account> accounts,
                           Action<Account, Excel.Range> DisplayFunc)
    {
        var xl = new Excel.Application();

        xl.Workbooks.Add();
        xl.Visible = true;
        xl.Cells[1, 1] = "ID";
        xl.Cells[1, 2] = " Balance";
        xl.Cells[2, 1].Select();
        foreach (var ac in accounts)
        {
            DisplayFunc(ac, xl.ActiveCell);
            xl.ActiveCell.Offset[1, 0].Select();
        }

        xl.Range["A1:B3"].Copy();
        //xl.get_Range("A1:B3").Copy();

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