Adding Custom UI to Excel 2010 Worksheets by Using the Open XML SDK 2.0.

The Open XML file formats enable you to add custom content to the ribbon in a Microsoft Excel 2010 or Microsoft Office Excel 2007 document. The Open XML SDK 2.0 adds strongly typed classes to simplify access to the Open XML file formats. The SDK also simplifies the addition of custom content to the ribbon, and the code sample that is included with this article describes how to use the SDK to add custom ribbon content to an Excel 2010 or Excel 2007 document.

To use the code sample, install the Open XML SDK 2.0 by using the link listed in the Explore It section. The code sample is modified from code that is included as part of a set of code examples for the Open XML SDK 2.0. The Explore It section also includes a link to the full set of code examples, although you can use the code sample without downloading and installing the code examples. The sample application adds a simple customization to the Excel ribbon, using a workbook that you supply.

The code sample that accompanies this article includes code that shows how to add custom ribbon content to an Excel 2010 or Excel 2007 workbook.

Setting up references

To use the code from the Open XML SDK 2.0, you must add several references to your project. The sample project includes these references, but in your own code, you must explicitly reference the following assemblies:

• WindowsBase. This reference might be set for you, depending on the kind of project that you create.

• DocumentFormat.OpenXml. Installed by the Open XML SDK 2.0.

You should also add the following using or Imports statements to the top of your code file.

Imports DocumentFormat.OpenXml.Office.CustomUI
Imports DocumentFormat.OpenXml.PackagingImports DocumentFormat.OpenXml.Packaging

using DocumentFormat.OpenXml.Office.CustomUI;
using DocumentFormat.OpenXml.Packaging;using System;
using DocumentFormat.OpenXml.Packaging;

Creating custom UI

Before you use the Open XML SDK 2.0 to customize the ribbon in an Excel workbook, you must first create the customization content. Describing the XML that is required to create a ribbon customization is beyond the scope of this article. In addition, it is far easier to use the Ribbon Designer in Visual Studio 2010 to create the customization for you. See the Explore It section for links to articles that describe how to create a ribbon customization by using the Visual Studio 2010 Ribbon Designer. For this demonstration, you need an XML file that contains a customization, and the following code provides a simple customization. (If you prefer, you can create your own customization by using the Visual Studio 2010 Ribbon designer, and then right-click it to export the customization to an XML file). Copy the following XML content into a text file that is named XLCustomUI.xml. The XML describes a ribbon customization that includes a button labeled Click Me! in a group named Group1 on the Add-Ins tab in Excel. When you click the button, it attempts to run a macro named SampleMacro in the host workbook.

<customUI xmlns="https://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab idMso="TabAddIns">
                <group id="Group1" label="Group1">
                    <button id="Button1" label="Click Me!" showImage="false" onAction="SampleMacro"/>
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

Create the macro

Now that you have the customization XML, you must create a macro in a sample workbook for the custom button's Click action to call. Create a new workbook, open the VBA Editor (press Alt+F11), select Insert -> Module to create a new module, and then add the following code to the new module.

Sub SampleMacro(button As IRibbonControl)
  MsgBox "You Clicked?"
End Sub

Save the workbook as an Excel Macro-Enabled Workbook named CustomUI.xlsm.

Examine the procedure

The XLAddCustomUI procedure accepts two parameters: a string that contains a file name to modify, and a string that contains the custom content (that is, the XML markup that describes the customization). To call the sample procedure, use code that resembles the following example.

Const SAMPLEXML As String = "XLCustomUI.xml"
Const DEMOFILE As String = "CustomUI.xlsm"

Dim content As String = System.IO.File.OpenText(SAMPLEXML).ReadToEnd()
XLAddCustomUI(DEMOFILE, content)

const string SAMPLEXML = "XLCustomUI.xml";
const string DEMOFILE = "CustomUI.xlsm";

string content = System.IO.File.OpenText(SAMPLEXML).ReadToEnd();
XLAddCustomUI(DEMOFILE, content);

Interact with the workbook

The sample procedure, XLAddCustomUI, starts by opening the requested workbook in read/write mode.

Using document As SpreadsheetDocument = 
 SpreadsheetDocument.Open(fileName, True)

  ' Code removed here.
End Using

using (SpreadsheetDocument document = 
 SpreadsheetDocument.Open(fileName, true))
{
  // Code removed here.
}

Work with the ribbon extensibility part

Next, the sample procedure attempts to retrieve a reference to the single ribbon extensibility part. If the part doesn't yet exist, the code creates it and stores a reference to the new part, as shown in the following code.

Dim part = document.RibbonExtensibilityPart
If part Is Nothing Then
  part = document.AddRibbonExtensibilityPart
End If

var part = document.RibbonExtensibilityPart;
if (part == null)
{
  part = document.AddRibbonExtensibilityPart();
}

Add the customization

Given a reference to the ribbon extensibility part, the code finishes by setting the part's CustomUI property to a new CustomUI object that contains the supplied customization. Once the customization is in place, the code saves the custom UI.

part.CustomUI = New CustomUI(customUIContent)
part.CustomUI.Save()

part.CustomUI = new CustomUI(customUIContent);
part.CustomUI.Save();

Sample procedure

The sample includes the following code.

Public Sub XLAddCustomUI(ByVal fileName As String, 
  ByVal customUIContent As String)
  Using document As SpreadsheetDocument = 
   SpreadsheetDocument.Open(fileName, True)
    ' You can only have a single ribbon extensibility part.
    ' If the part doesn't exist, add it.
    Dim part = document.RibbonExtensibilityPart
    If part Is Nothing Then
      part = document.AddRibbonExtensibilityPart
    End If
    part.CustomUI = New CustomUI(customUIContent)
    part.CustomUI.Save()
  End Using
End Sub

static public void XLAddCustomUI(string fileName, string customUIContent)
{
  using (SpreadsheetDocument document = 
   SpreadsheetDocument.Open(fileName, true))
  {
    // You can only have a single ribbon extensibility part.
    // If the part doesn't exist, create it.
    var part = document.RibbonExtensibilityPart;
    if (part == null)
    {
      part = document.AddRibbonExtensibilityPart();
    }
    part.CustomUI = new CustomUI(customUIContent);
    part.CustomUI.Save();
  }
}

You can create very complex customizations to the ribbon by using the Visual Studio 2010 Ribbon designer. After you create your customizations, you can export those customizations to an XML file, and then use that XML with the code examples in this article.

The code examples in this article include several of the issues that you encounter when you work with the Open XML SDK 2.0. Each example is slightly different. However, the basic concepts are the same. Unless you understand the structure of the part that you are trying to work with, even the Open XML SDK 2.0 does not make it possible to interact with the part. Take the time to investigate the objects that you are working with before you start to write code. You will save time.

Length: 00:8:41

Grab the Code

• Excel 2010: Adding Custom UI to Excel 2010 Workbooks by Using Open XML SDK 2.0

• Ribbon Designer

• Walkthrough: Creating a Custom Tab by Using the Ribbon Designer

• Open XML SDK 2.0 for Microsoft Office

• Open XML Format SDK 2.0 Code Snippets for Visual Studio 2010

• Brian Jones & Zeyad Rajabi

• Open XML Developer Center

• Office Development with Visual Studio (VSTO)

About the Author

Ken Getz is a developer, writer, and trainer, working as a senior consultant with MCW Technologies, LLC, a Microsoft Solution Provider. He has co-authored several technical books for developers, including the best-selling ASP.NET Developer's Jumpstart, the Access Developer's Handbook series, and the VBA Developer's Handbook series. Ken is a lead courseware author for AppDev, and has authored many of their most popular titles. Ken has spoken for many years at technical conferences, including Microsoft TechEd.