Expand Minimize

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

Office 2010

Office Visual How To

Summary:  Use strongly typed classes in the Open XML SDK 2.0 for Microsoft Office to add custom content to the ribbon in a Microsoft Excel 2010 or Microsoft Office Excel 2007 workbook, without loading the workbook into Microsoft Excel.

Applies to:  Microsoft Office 2010 | 2007 Microsoft Office system | Microsoft Excel 2010 | Microsoft Office Excel 2007 | Open XML SDK 2.0 for Microsoft Office | Microsoft Visual Studio 2010

Published:  February 2011

Provided by:   Ken Getz

Overview

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.

Code It

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.

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="http://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 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 (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.

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();

Sample procedure

The sample includes the following code.

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();
  }
}
Read It

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.

See It

Watch the video

Watch video

Length: 00:8:41

Click to grab code

Grab the Code

Explore It

 

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.

Community Additions

ADD
Show:
© 2014 Microsoft