Adding Custom UI to Excel 2010 Worksheets by Using the Open XML SDK 2.0.
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.
Last modified: September 12, 2012
Applies to: Excel 2007 | Excel 2010 | Office 2007 | Office 2010 | Open XML | PowerPoint 2010 | VBA | Visual Studio | Word 2010
Published: February 2011
Provided by: Ken Getz
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:
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
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.
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)
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
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
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.
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
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.
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.