Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All
Expand Minimize

Taking Advantage of C# Language Improvements in Office 2010 Projects

Office 2010

Office Visual How To

Summary:  C# 2010 adds several language improvements that make it easier to interact with Office object models. Reduce the size and complexity of your C# code by using these new language features.

Last modified: September 12, 2012

Applies to: Excel 2010 | Office 2010 | Open XML | PowerPoint 2010 | VBA | Word 2010

Published:  September 2010

Provided by:  MVP ContributorKen Getz, MCW Technologies | About the Author

Overview

In earlier versions of C#, writing code that interacted with Microsoft Office applications required handling many difficulties imposed by the interaction between the C# language and Component Object Model (COM) components. C# 2010 adds several language features, including named and optional parameters, better support for COM interop, and more.

The code sample that is included with this article contains a Console application that includes a single procedure, GenerateChart. The procedure uses COM interop to interact with both Microsoft Excel and Microsoft Word. The code retrieves information about how to run processes on the host computer, and creates a chart in Excel that displays information about processes and memory consumption. The code then copies the chart and pastes it into a Microsoft Word document. The code, although very simple, shows off several of the new C# language features that focus on how to improve COM interop coding. Figure 1 shows the results of running the code sample, in either of its versions (Microsoft .NET Framework 3.5 or Microsoft .NET Framework 4.

Figure 1. Code sample creates this chart first in Microsoft Excel, and then pastes it into Microsoft Word

Sample cope adds a chart added to Word from Excel

Code It

Follow these steps to convert the GenerateChart procedure in the code sample project so that it uses new C# 2010 features. (You can also investigate the WorkWithOffice40 project, which already includes all these changes.)

To test the original code

  1. In Microsoft Visual Studio 2010, load the original sample project, WorkingWithOffice35\WorkingWithOffice.sln.

  2. Examine Program.cs. Run the application, and step through the code. Note that the project includes references set to the Word and Excel type libraries, and includes using statements at the top of the code file for both interop assemblies.

  3. Note that the GenerateChart procedure includes a parameter that determines whether it copies the chart to Microsoft Word.

    static void GenerateChart(bool copyToWord)
    
  4. Note that the calling procedure, Main, passes true as a parameter to the GenerateChart procedure. It would be nice if, as in a Visual Basic or VBA procedure, the calling code treats the parameter as an optional parameter in some simple way, with the called procedure assuming the value of true for the GenerateChart(true); parameter.

  5. In the Solution Explorer window, right-click the project node, select Properties from the context menu, and in the Properties window, set the Target framework property to .NET Framework 4 Client Profile.

  6. In the Solution Explorer window, right-click the project node and select Add Reference. In the Add Reference dialog box, in the .NET tab, select Microsoft.CSharp. Click OK to add the reference.

To modify the Excel code

  1. C# 2010 makes it easy for a procedure to accept an optional parameter. To modify the GenerateChart parameter so that it is optional, modify the declaration so that it resembles the following code. Adding the default value for the parameter automatically makes it optional.

    static void GenerateChart(bool copyToWord = true)
    
  2. Modify the call to GenerateChart, in the Main procedure so that it takes advantage of the new optional parameter, assuming that the call intends the copyToWord parameter to be true.

    GenerateChart();
    
  3. Examine the first three lines of code in GenerateChart. These lines create a new instance of the Excel application, set it to be visible, and add a new workbook.

    var excel = new Excel.Application();
    excel.Visible = true;
    excel.Workbooks.Add(Type.Missing);
    
  4. The Workbooks.Add method accepts an optional parameter that indicates the template to use. Earlier versions of C# did not support passing optional parameters, but C# 2010 handles this correctly. Modify the call to the WorkBooks.Add method, removing the Type.Missing parameter. This simplifies the code, and makes it look more like VBA or Visual Basic code as in the following example.

    excel.Workbooks.Add();
    
  5. Examine the next two lines of code. These lines set the value of two cells in the current worksheet to "Process Name" and "Memory Usage". Note several things about this code:

    • The code calls the get_Range method, instead of simply referring to the Range property, because the Range property in Excel requires a parameter. Prior versions of C# did not support parameterized properties.

    • The code sets the Value2 property of the Range object returned by the get_Range method, instead of setting the simpler Value property. The Value property of a range in Excel accepts an optional parameter, and earlier versions of C# did not support this feature. The Value2 property does not accept a parameter, and so that it works with C#.

      excel.get_Range("A1", Type.Missing).Value2 = "Process Name";
      excel.get_Range("B1", Type.Missing).Value2 = "Memory Usage";
      
  6. C# 2010 supports parameterized properties so that modify the previous two lines of code so that they retrieve the Range property of the Excel object, and set the Value property, instead of the Value2 property.

    excel.Range["A1"].Value = "Process Name";
    excel.Range["B1"].Value = "Memory Usage";
    
  7. Examine the following example, which sets up a LINQ query to retrieve the ten processes that currently consume the most memory. Because this code has nothing to do with COM interop, you can leave this code as is.

    var processes =
      Process.GetProcesses()
            .OrderByDescending(p => p.WorkingSet64)
            .Take(10);
    
  8. The following code copies values from the list of processes into cells in the worksheet. As before, this code calls the get_Range method and sets the Value2 property because of limitations in earlier versions of C#.

    int i = 2;
    foreach (var p in processes)
    {
      excel.get_Range("A" + i, Type.Missing).Value2 = p.ProcessName;
      excel.get_Range("B" + i, Type.Missing).Value2 = p.WorkingSet64;
      i++;
    }
    
  9. Replace the code in the loop with the following code, taking advantage of the same C# 2010 features you have seen already.

    excel.Range["A" + i].Value = p.ProcessName;
    excel.Range["B" + i].Value = p.WorkingSet64;
    i++;
    
  10. The next two lines of code auto-fit the two columns. Note that the code casts Excel.Columns[] to Excel.Range.

    ((Excel.Range)excel.Columns[2]).AutoFit();
    
  11. Although it is not the case in this example, one common case for using COM interop with C# is when you copy code from a VBA solution into C#, and convert it to managed code. VBA is far more loosely typed language and does not require explicit casts. In this case, if you had code in VBA that calls the AutoFit method of a column, the code does not require the cast. When you copy it into C#, however, you need to determine the exact kind of the object so that you can perform the required cast. C# 2010 provides the dynamic type, which enables you to defer type specification until runtime. As an example, you can modify the previous code to use the dynamic type, and avoid the need to determine the exact type when you copy the VBA code into your C# class. This step is not required, but demonstrates how it might be simpler to copy code directly from VBA into a C# project.

    ((dynamic)excel.Columns[1]).Autofit();
    ((dynamic)excel.Columns[2]).Autofit();
    
  12. The following example retrieves a reference to cell A1. This code calls the get_Range method (and by now, you know how to improve this code).

    Excel.Range range = excel.get_Range("A1", Type.Missing);
    
  13. Modify the line of code, replacing the call to the get_Range method.

    Excel.Range range = excel.Range["A1"];
    
  14. The following example adds a new chart to the workbook, in the active sheet. The Charts.Add method requires four parameters. Even though they are all optional, earlier versions of C# required you to supply values for all the parameters.

    Excel.Chart chart = (Excel.Chart)excel.
      ActiveWorkbook.Charts.Add(
      Type.Missing, excel.ActiveSheet, Type.Missing, Type.Missing);
    
  15. Modify the call to Charts.Add, taking advantage of C# 2010's ability to work with optional parameters. In this case, the code accepts all the default values. Note also that the Charts.Add method now returns a dynamic object. Therefore no cast is required.

    Excel.Chart chart = excel.ActiveWorkbook.Charts.Add();
    
  16. The following example calls the ChartWizard method to create a new chart based on the data contiguous to cell A1. This block passes the ChartRegion method many optional parameters, but specifies a few of the parameters explicitly.

    chart.ChartWizard(
      range.CurrentRegion,
      Type.Missing, Type.Missing, Type.Missing, Type.Missing,
      Type.Missing, Type.Missing,
      "Memory Usage in: " + Environment.MachineName,
      Type.Missing, Type.Missing, Type.Missing);
    
  17. C# 2010 enables you to use named parameters so that you can specify a subset of the total number of parameters in a complex method call. Modify the code, fixing up the calls to the get_Range method and the ChartWizard method.

    // Note the use of named parameters:
    chart.ChartWizard(
      Source: range.CurrentRegion,
      Title: "Memory Usage in: " + Environment.MachineName);
    
  18. The following example formats the chart and then copies it to the Clipboard. You cannot improve on this code:

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

To modify the Word code

  1. The following example checks whether the caller wants to copy the chart to Microsoft Word, and if so, creates a new instance of the Microsoft Word application and makes it visible.

    if (copyToWord)
    {
      var word = new Word.Application();
      word.Visible = true;
      // The following method call isn't really safe: because the variable
      // missing is passed by reference, its value could be changed.
      // To do it correctly would require creating an individual variable
      // for each parameter.
      // Code removed here…
    }
    
  2. The following example calls the Word.Documents.Add method, but Microsoft Word generally passes all its parameters by reference, and this complicates the method call. Earlier versions of C# require you to pass all parameters, even if they are optional, and in this case, not only must you pass all parameters, you must pass all parameters by reference. The sample code does this in an unsafe manner, because it's possible that the Word.Documents.Add method might modify one of its ref parameters. This code works, but certainly is not good code. To be safe, you must create a separate variable for each parameter, and pass each by reference to the Word method.

    object missing = Type.Missing;
    word.Documents.Add(ref missing, ref missing, ref missing, ref missing);
    
  3. Because C# 2010 handles optional parameters, you do not have to pass the variables for optional parameters for the Documents.Add method. Modify the code, simplifying it to use the default values for all the parameters.

    word.Documents.Add();
    
  4. The code is completed by calling the Selection.Paste method, placing the chart that is on the Clipboard into the Word document. There is no need to modify this code.

    word.Selection.Paste();
    
  5. Save and run the project, and verify that it runs the same as it did originally, although it is with significantly simpler code.

Read It

C# 2010 makes it far easier to copy and paste code directly from a VBA solution into C# than ever before. You must, of course, set references to the appropriate type libraries (Microsoft Word and Microsoft Excel, in this case), but given the new features in C# 2010, you can often then copy code directly from VBA into a C# project and add little more than semi-colons to delimit the lines of code.

Using the new C# 2010 features extend beyond their applicability to interact with COM interop. You can create your own methods with optional parameters. You can pass parameters by name together with using optional parameters. You can create your own dynamic classes, and can interact with dynamic languages such as Iron Python and Ruby. Take the time to investigate the new language features in C#. You'll find it easier than ever to create COM interop code, and can use the new features in pure C# solutions, also. (All these features exist in Visual Basic. Most have been available since the first version of the language. The latest version of Visual Basic adds support for dynamic languages, also.)

See It

Watch the video

Watch video

Length: 00:09:25

Click to grab code   
Grab the Code

Explore It

About the Author

Ken Getz (MVP) is a developer, writer, and trainer, working as a senior consultant with MCW Technologies, LLC. In addition to writing hundreds of technical articles over the past fifteen years, he is lead courseware author for AppDev. Ken has co-authored several technical books for developers, including the best-selling ASP.NET Developer's Jumpstart, Access Developer's Handbook series, and VBA Developer's Handbook series. Ken is a member of the INETA Speakers Bureau, and speaks regularly at a large number of industry events, including 1105 Media's VSLive, and Microsoft's Tech-Ed.

Show:
© 2015 Microsoft