Excel Development: 2. Excel Menu System

Before starting Excel Development, you need to set up the shelf. The most direct one is the menu in Excel, which intuitively shows the functions of our plug-in to users. After the menu is displayed, we can implement the events and functions. There are two types of Excel menus: Excel 2003 and earlier traditional menu styles, and Ribbon menus after and after Excel 2007. This article first explains how to create menus in Excel 2007, including Visual Studio Visual design menus, RibbonXml configuration of menus, and then how to create custom menus in Excel 2003. The final demonstration shows how to use SharedAddin technology to combine the two. That is, the original menu style is displayed in version 2003, And the Ribbon menu is dynamically loaded in version 2003 to achieve version compatibility.

To demonstrate how to create a menu, first create a VSTO program and create an Excel external program in:

2.1 Tab Control

RibbonXML can be used to configure Ribbon menus in Office. That is to say, the preceding visual interface design provides us with support for RibbonXML editing. In fact, we can also directly create an XML file for design and then load it in the Code. This function can also be implemented. In some cases, for example, when we create a SharedAddin program, there is no design-time support at all. Therefore, it is particularly important to understand RibbonXML to create a menu system that is compatible with multiple versions of Excel.

The best way to create RibbonXML is to right-click the newly created visualization menu and export the function area to XML. The project will then automatically create the Ribbon. xml and Ribbon. cs files, where Ribbon. xml is the layout file and Ribbon. cs is the event processing code.

CustomerRibbon; Office. CreateRibbonExtensibilityObject () {customerRibbon = (); customerRibbon ;}

Run the program to see the following results:

LoadImage (imageName) {assembly =. GetExecutingAssembly (); stream = assembly. GetManifestResourceStream (+ imageName);. FromStream (stream );}

ImageName is the image name to be set for the image attribute of the Button control. Resource file image, which must be set as an embedded resource. We use the GeneralButton_Click event to process the click events of all buttons. Run the program and we can see the interface for editing the menu when using the designer.

The Ribbon menu is not supported in Excel2003 and earlier versions, so the above programs cannot run in version 03. However, if you develop enterprise-level applications, you still need to create a menu system under 2003 because the majority of customers who use Office 2003 still need. The following describes how to create an Excel menu and toolbar in the Excel2003 system.

To create a plug-in under Excel 03, we can create a Shared Add-in program. For example, first create a Shared Add-in project named YYSharedAddin:

ApplicationObject; OnConnection (application, Extensibility. connectMode, addInInst, System. custom) {applicationObject = application; addInInstance = addInInst; (applicationObject. version =) {(menuDesigner =) {menuDesigner = (applicationObject);} menuDesigner. addMenus (); menuDesigner. addToolBars ();}}

In the OnConnection method, we first determine the Excel Version number, which can be obtained through the Version object. if the Version number is 11, that is, the Excel Version of Version 2003, we need to manually create menus and toolbar dynamically. We created a class named MenuDesigner used to create menus and toolbar, and passed in the applicationObject object in its constructor.

Add menu

We put the Add menu in the AddMenus method of MenuDesigner. In Excel, both a menu item and a sub-menu are an MSOffice. CommandBarPopup object. The menu button in the menu item is a MSOffice. CommandBarButton object. First, we define the menu items and menu buttons. Here we only list some of them.

MSOffice.YYMenu = ;MSOffice.btnQuoteFunctionMenuCommand = ;MSOffice.btnQuoteSinaFunctionMenuCommand = ;MSOffice.btnQuoteYahooFunctionMenuCommand = ;

When creating a menu, you must first create a YYMenu object and then add a menu item to the object. The method for creating the YYMenu menu is as follows:

AddMenus(){    MSOffice.menubar = (MSOffice.)application.CommandBars.ActiveMenuBar;    controlCount = menubar.Controls.Count;    menuCaption = ;    {        YYMenu = (MSOffice.)            application.CommandBars.ActiveMenuBar.FindControl(            MSOffice..msoControlPopup, System..Missing, menuTag, , );    }    { }    (YYMenu != )    {        YYMenu.Delete(.Missing);    }    YYMenu = (MSOffice.)menubar.Controls.Add(MSOffice..msoControlPopup, missing, missing, controlCount, );    YYMenu.Tag = menuTag;    YYMenu.Caption = menuCaption;    YYMenu.BeginGroup = ;    LoginGroup();    FinancialGroup();    MapServiceGroup();    WeatherReportGroup();    AboutGroup();}

Before creating a menu, you must determine whether the same Tag value menu already exists. If yes, delete the previously created menu item and recreate it. Otherwise, the menu item is created again when you display or hide the menu item in the Com add-on. After the main menu is created, we can create a sub menu on the main menu. These methods are written to the last several methods ending with a Group. The FinancalGroup method for creating a financial item menu is used as an example.

FinancialGroup(){    MSOffice.realTimeButton = AddPopupButton(YYMenu.Controls, .Quote);    realTimeButton.BeginGroup = ;    btnQuoteFunctionMenuCommand = AddCommandButton(realTimeButton.Controls, .Quote, YYSharedAddin.Properties..QuoteReal);    btnQuoteSinaFunctionMenuCommand = AddCommandButton(realTimeButton.Controls, .QuoteSina, YYSharedAddin.Properties..SinaQuote_64);    btnQuoteSinaFunctionMenuCommand.BeginGroup = ;    btnQuoteYahooFunctionMenuCommand = AddCommandButton(realTimeButton.Controls, .QuoteYahoo, YYSharedAddin.Properties..Yahoo_Quote);    MSOffice.historyButton = AddPopupButton(YYMenu.Controls, .QuoteHistory);    realTimeButton.BeginGroup = ;    btnQuoteHistoryFunctionMenuCommand = AddCommandButton(historyButton.Controls, .QuoteHistory, YYSharedAddin.Properties..QuoteHist);    btnQuoteHistorySinaFunctionMenuCommand = AddCommandButton(historyButton.Controls, .QuoteHistorySina, YYSharedAddin.Properties..SinaQuote_64);    btnQuoteHistorySinaFunctionMenuCommand.BeginGroup = ;    btnQuoteHistoryYahooFunctionMenuCommand = AddCommandButton(historyButton.Controls, .QuoteHistoryYahoo, YYSharedAddin.Properties..Yahoo_HistoryQuote);    MSOffice.importButton = AddPopupButton(YYMenu.Controls, .Import);    importButton.BeginGroup = ;    btnImportFromLocalMenuCommand = AddCommandButton(importButton.Controls, .ImportFromLocal, YYSharedAddin.Properties..ImportFromDisk);    btnImportFromWebMenuCommand = AddCommandButton(importButton.Controls, .ImportFromWeb, YYSharedAddin.Properties..ImportFromWeb);}

For finance menus, there are three level-1 menus: Real-time quotations, historical quotations, and import. AddPopupButton code:

MSOffice.AddPopupButton(MSOffice.controls, menu){    tag = menu.ToString();    caption = .Empty;    .menus.TryGetValue(tag, caption);    MSOffice.command = ;    {        command = controls[caption] MSOffice.;    }    { }    (command == )    {        command = controls.Add(MSOffice..msoControlPopup, .Missing, .Missing, .Missing, .Missing) MSOffice.;        command.Caption = caption;    }    command;}

The first parameter indicates all the Controls containers of the Maximum Root Node menu YYMenu object. Therefore, the method for creating the first real-time market level menu is as follows:

MSOffice.realTimeButton = AddPopupButton(YYMenu.Controls, .Quote);realTimeButton.BeginGroup = ;

After obtaining the realTimeButton level-1 menu, we set its BeginGroup attribute to true to add a Seperator control (a horizontal or vertical line) before ). With this realTimeButton level-1 menu, we can create three level-2 menu buttons on this object.

btnQuoteFunctionMenuCommand = AddCommandButton(realTimeButton.Controls, .Quote, YYSharedAddin.Properties..QuoteReal);btnQuoteSinaFunctionMenuCommand = AddCommandButton(realTimeButton.Controls, .QuoteSina, YYSharedAddin.Properties..SinaQuote_64);btnQuoteSinaFunctionMenuCommand.BeginGroup = ;btnQuoteYahooFunctionMenuCommand = AddCommandButton(realTimeButton.Controls, .QuoteYahoo, YYSharedAddin.Properties..Yahoo_Quote);

The method for creating the sub-menu button is encapsulated in the AddCommandButton method. The first parameter of the method is the container class of all controls in the first-level sub-menu.

MSOffice.AddCommandButton(MSOffice.controls, menu, System.Drawing.icon){    tempName = menu.ToString();    tag = .Format(, menu, .Now.ToBinary());    caption = .Empty;    .menus.TryGetValue(tempName, caption);    MSOffice.command = ;    {        command = controls[caption] MSOffice.;        command.Tag = tag;        command.Click += MSOffice.(command_Click);    }    { }    (command == )    {        command = controls.Add(MSOffice..msoControlButton, .Missing, .Missing, .Missing, .Missing) MSOffice.;        command.Style = MSOffice..msoButtonIconAndCaption;        command.Caption = caption;        command.Tag = tag;        command.Picture = .ImageToPictureDisp(icon);        command.Click += MSOffice.(command_Click);    }    command;}

When creating a menu button, you must first determine whether the menu item exists in the current menu. Otherwise, a new object is created. Note that the Tag attribute of CommandBarButton must be added with a unique identifier, such as the current time or GUID, and the name of the menu. The purpose of adding a unique flag is to ensure that the menu is unique every time you create a menu. Otherwise, the menu will only respond to a button click event and other strange issues. Next, set the Style of the button to MSOffice. msoButtonStyle. when msoButtonIconAndCaption includes both images and text, the Picture attribute is the image to be displayed before the button. This object is a stdole. IPictureDisp type object, you need to perform a conversion. Second, directly setting the image will make the background color of the image not transparent. The background color is the default style color of Office. To make the background color transparent, you need to set the Mask attribute. The Mask attribute is also an image. This image is a mask with a display image, that is, the area to be displayed in the original image. It is displayed in black and transparent in other places. For more information, see this article, to simplify the process, we will not proceed with it.

Add Toolbar

A toolbar is actually a large level-1 menu. Like creating a menu, we encapsulate the code for creating a toolbar in the AddToolBars method. The code for this method is as follows:

AddToolBars(){    {        YYToolBar = application.CommandBars[];    }    { }    (YYToolBar == )    {        YYToolBar = application.CommandBars.Add(, MSOffice..msoBarTop, , );    }    LoginGroup_ToolBar();    FinancialGroup_ToolBar();    MapServiceGroup_ToolBar();    WeatherReportGroup_ToolBar();    AboutGroup_ToolBar();    YYToolBar.Visible = ;}

First, we need to create a large toolbar, similar to creating a menu. Before creating a toolbar, we need to define the buttons in all the toolbar. Note, this button object cannot be shared with the objects in the menu item. Otherwise, event registration may be aborted.

MSOffice.YYToolBar;MSOffice.btnQuoteFunctionToolBarCommand = ;MSOffice.btnQuoteSinaFunctionToolBarCommand = ;MSOffice.btnQuoteYahooFunctionToolBarCommand = ;

After the YYToolBar object is created, the tool items in the toolbar created based on the object are similar to those in the word menu.

Now let's look at the effects under Excel2003. Because of the SharedAddin program we created, we need to set the Startup Program during debugging. Visual Studio sets the default startup program to Visual Studio itself. Here, we specify the default program as an executable file of Excel 2003, for example:

This section describes how to create Ribbon menus in versions 2003 and later and traditional menu items in versions 2003. A good Excel application should display different menu forms based on different versions. If you use VSTO to create a menu, it may not be supported in version 03, because version 03 does not support the Ribbon menu. To be compatible with all Excel versions, you can create a Shared Add-in project. The second part has explained how to create a traditional menu in SharedAddin. To be compatible with version 03 and later, we only need to load the RibbonXML created in the first part in SharedAddin.

To enable SharedAddin to render the Ribbon menu in Versions later than 03, We need to enable the Connect class to implement the Office. IRibbonExtensibility interface. Since the previously generated Ribbon. cs class has implemented this interface, the simplest method is to copy the previously created Ribbon. xml and Ribbon. cs to the SharedAddin project. Set Ribbon. xml as the embedded resource. Change the namespace of Ribbon. cs to the same namespace as Connect. cs. Then, use the Partial keyword to change the name of the Ribbon class to the partial Connect class. As follows:

[()]: Office. {Office. ribbon; ribbonextensibility member GetCustomUI (ribbonID) {GetResourceText ();} The Ribbon_Load (Office. ribbonUI ){. ribbon = ribbonUI;} LoadImage (imageName) {assembly =. getExecutingAssembly (); stream = assembly. getManifestResourceStream (+ imageName );. fromStream (stream);} GeneralButton_Click (Office. control ){{. show (+ control. id) ;}( ex) {}} help GetResourceText (resourceN Ame) {asm =. getExecutingAssembly (); [] resourceNames = asm. getManifestResourceNames (); (I = 0; I <resourceNames. length; ++ I ){(. compare (resourceName, resourceNames [I],. ordinalIgnoreCase) = 0) {(resourceReader = (asm. getManifestResourceStream (resourceNames [I]) {(resourceReader! =) {ResourceReader. ReadToEnd ();}}}};}}

To set the correct Resource Name, you can use GetManifestResourceNames to view all the resource names in the Assembly.

Now, set the startup project to Excel 2007 or 2010, and now the menu is in the Ribbon style:

This document introduces the menu system in Excel. This section describes how to create Ribbon menus supported during Visual Studio design. By dragging controls and setting properties, you can create custom menus that are comparable to those of the built-in Office dishes. Next, we introduced the basic Ribbon XML file of the Ribbon menu, and then explained how to manually load the Ribbon menu in VSTO. However, Ribbon menus are only supported in Excel versions later than 2003. To solve the problem of menu creation in Excel 2003, this article shows how to create the Excel Shared Add-in program and how to create a traditional menu item and toolbar. Finally, in order to be compatible with all Excel versions, SharedAddin shows how to load Ribbon XML so that our Excel plug-in can display menu items of different styles for different Excel versions.

Now our plug-in shelf has been set up. Next I will explain the Excel object model and introduce several core objects in Excel, such as WorkBook, WorkSheet, and Range objects, no matter what type of Excel Development you are using, these objects are also an important foundation for your Excel Development.

Click here to download the code. I hope this article will help you understand the Excel menu system.