Scott Hanselman

Just got a great tweet from Jeremiah Morrill about .NET 4.

I love a challenge! In my first Whirlwind Tour post on ASP I mentioned how COM Interop and Office Interop was fun with 4.

I've done a lot of COM Interop with C# and a LOT of Office Automation. Once upon a time, I worked at a company called Chrome Data, and we created a Fax Server with a Digiboard. Folks would call into a number, and the person who took the order would pick the make/model/style/year of the car and "instantly" fax a complete report about the vehicle. It used VB3, SQL Server 4.21 and Word 6.0 and a magical thing called "OLE Automation."

Fast forward 15 years and I sent an email to Mads Torgerson, a PM on C# that said:

I’m doing a sample for a friend where I’m simply spinning through an Automation API over a Word Doc to get and change some CustomDocumentProperties.

I’m really surprised at how current C# sucks at this. Of course, it makes sense, given all the IDispatch code in Word, but still. Dim != var as they say. Fix it!

Well, everything except "fix it!" is true. I added that just now. ;) I did a post on this a while back showing how scary the C# code was. This is/was somewhere where Visual Basic truly excels. I vowed to only use VB for Office Automation code after this fiasco.

If you want to melt your brain, check out the old code. No joke. I've collapsed the block because it's too scary. See the "ref missings"? The reflection? The Get/Sets? Scandalous!

{
  ApplicationClass WordApp = new ApplicationClass();
  WordApp.Visible = true;
  object missing = System.Reflection.Missing.Value;
  object readOnly = false;
  object isVisible = true;
  object fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"..\..\..\NewTest.doc");
  Microsoft.Office.Interop.Word.Document aDoc = WordApp.Documents.Open(
      ref fileName,ref missing,ref readOnly,ref missing,
      ref missing,ref missing,ref missing,ref missing,
      ref missing,ref missing,ref missing,ref isVisible,
      ref missing,ref missing,ref missing,ref missing);

  aDoc.Activate();

  string propertyValue = GetCustomPropertyValue(aDoc, "CustomProperty1");
  SetCustomPropertyValue(aDoc, "CustomProperty1", "Hanselman");

  foreach (Range r in aDoc.StoryRanges)
  {
     r.Fields.Update();
  }
}

public string GetCustomPropertyValue(Document doc, string propertyName)
{
  object oDocCustomProps = doc.CustomDocumentProperties;
  Type typeDocCustomProps = oDocCustomProps.GetType();
  object oCustomProp = typeDocCustomProps.InvokeMember("Item",
        BindingFlags.Default |
        BindingFlags.GetProperty,
        null, oDocCustomProps,
        new object[] { propertyName });

  Type typePropertyValue = oCustomProp.GetType();
  string propertyValue = typePropertyValue.InvokeMember("Value",
       BindingFlags.Default |
      BindingFlags.GetProperty,
      null, oCustomProp,
      new object[] { }).ToString();

  return propertyValue;
}

public void SetCustomPropertyValue(Document doc, string propertyName, string propertyValue)
{
  object oDocCustomProps = doc.CustomDocumentProperties;
  Type typeDocCustomProps = oDocCustomProps.GetType();
  typeDocCustomProps.InvokeMember("Item",
        BindingFlags.Default |
        BindingFlags.SetProperty,
        null, oDocCustomProps,
        new object[] { propertyName, propertyValue });
}

Fast forward to C# under .NET 4.

var WordApp = new ApplicationClass();
WordApp.Visible = true;
string fileName = @"NewTest.doc";
Document aDoc = WordApp.Documents.Open(fileName, ReadOnly: true, Visible: true);
aDoc.Activate();

string propertyValue = aDoc.CustomDocumentProperties["FISHORTNAME"].Value;
aDoc.CustomDocumentProperties["FISHORTNAME"].Value = "HanselBank";
string newPropertyValue = aDoc.CustomDocumentProperties["FISHORTNAME"].Value

foreach (Range r in aDoc.StoryRanges)
{
   foreach (Field b in r.Fields)
   {
      b.Update();
   }
}

See how all the crap that was originally reflection gets dispatched dynamically? But that's not even that great an example.

Word and Excel Automation with C# 4

That's just an example from Jonathan Carter and Jason Olson that gets running processes (using LINQ, woot) then makes a chart in Excel, then puts the chart in Word.

using System;
using System.Diagnostics;
using System.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Word = Microsoft.Office.Interop.Word;

namespace One.SimplifyingYourCodeWithCSharp
{
    class Program
    {
        static void Main(string[] args)
        {
            GenerateChart(copyToWord: true);
        }

        static void GenerateChart(bool copyToWord = false)
        {
            var excel = new Excel.Application();
            excel.Visible = true;
            excel.Workbooks.Add();

            excel.get_Range("A1").Value2 = "Process Name";
            excel.get_Range("B1").Value2 = "Memory Usage";

            var processes = Process.GetProcesses()
                                        .OrderByDescending(p => p.WorkingSet64)
                                        .Take(10);
            int i = 2;
            foreach (var p in processes)
            {
                excel.get_Range("A" + i).Value2 = p.ProcessName;
                excel.get_Range("B" + i).Value2 = p.WorkingSet64;
                i++;
            }

            Excel.Range range = excel.get_Range("A1");
            Excel.Chart chart = (Excel.Chart)excel.ActiveWorkbook.Charts.Add(
                After: excel.ActiveSheet);

            chart.ChartWizard(Source: range.CurrentRegion,
                Title: "Memory Usage in " + Environment.MachineName);

            chart.ChartStyle = 45;
            chart.CopyPicture(Excel.XlPictureAppearance.xlScreen,
                Excel.XlCopyPictureFormat.xlBitmap,
                Excel.XlPictureAppearance.xlScreen);

            if (copyToWord)
            {
                var word = new Word.Application();
                word.Visible = true;
                word.Documents.Add();

                word.Selection.Paste();
            }
        }
    }
}

Notice the named parameters in C#, like 'Title: "whatever"' and "copyToWord: true"?

PIAs no long stand for Pain in the *ss - Type Equivalence and Embedded Interop Assemblies

Primary Interop Assemblies are .NET assemblies that bridge the gap between a .NET app and a COM server. They are also a PIA, ahem. When there's articles about your technology on the web called "Common Pitfalls With ______" you know there's trouble.

Typically you reference these Interop assemblies in Visual Studio and they show up, predictably, as references in your assembly. Here's a screenshot with the project on the right, and the assembly under Reflector on the left.

This means that those PIAs better be deployed on the end user's machine. Some PIAs can be as large as 20 megs or more! That's because for each COM interface, struct, enum, etc, there is a managed equivalent for marshalling data. That sucks, especially if I just want to make a chart and I'm not using any other types. It's great that Office has thousands of types, but don't make me carry them all around.

However, now I can click on Properties for these references and click Embed Interop Types = true. Now, check the screenshot. The references are gone and new types have appeared.

Just the types I was using are now embedded within my application. However, since the types are equivalent, the runtime handles this fact and we don't have to do anything.

This all adds up to Office and COM Interop that is actually fun to do. Ok, maybe not fun, but better than a really bad paper cut. Huh, Jeremiah? ;)

About Scott

Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.

About   Newsletter

Hosting By