VSTO Excel开发（一）：自定义excel 菜单

 

先看一下效果图：

 我们现在来定义这个菜单“采购系统”：打开visual studio 2005点击“File”--"New"---"Project"选择“Office”如下图：

点击“OK”后在右边你会看到：

右击“ThisWorkbook.cs”，选择"View Code"则进入代码编写模式，现在是重点了（代码的编写），其实也就是从MSDN上的帮助来模仿了（红色的是需要我们自己编写的）；

    public partial class ThisWorkbook
    {
        //**自定菜单
        //定义菜单变量
        //supplierCommand---供应商输入；questBuyBillCommand---申购单；

         //quotationCommand-----报价单；purchaseOrderCommand--采购单；
        //materialCommand---物品表；

        private Office.CommandBarButton supplierCommand;
        private Office.CommandBarButton materialCommand;
        private Office.CommandBarButton questBuyBillCommand;
        private Office.CommandBarButton quotationCommand;
        private Office.CommandBarButton purchaseOrderCommand;
        //定义菜单Tag，MSDN中说Office菜单是靠Tag来识别的，我们做删除这个自定义菜单得靠他；
        private string menuTag = "A unique tag";

        // 如果菜单存在则删除它.
        public void CheckIfMenuBarExists()
        {
            try
            {
                Office.CommandBarPopup foundMenu = (Office.CommandBarPopup)
                    this.Application.CommandBars.ActiveMenuBar.FindControl(
                    Office.MsoControlType.msoControlPopup, System.Type.Missing, menuTag, true, true);

                if (foundMenu != null)
                {
                    foundMenu.Delete(true);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        // 如果菜单不存在则创建它.
                private void AddMenuBar()
        {
            try
            {
                Office.CommandBarPopup cmdBarControl = null;
                Office.CommandBar menubar = (Office.CommandBar)Application.CommandBars.ActiveMenuBar;
                int controlCount = menubar.Controls.Count;
                string menuCaption = "采购系统(&P)";
                // Add the menu.
                cmdBarControl = (Office.CommandBarPopup)menubar.Controls.Add(Office.MsoControlType.msoControlPopup, missing, missing, controlCount, true);
                cmdBarControl.Tag = menuTag;
                if (cmdBarControl != null)
                {
                    cmdBarControl.Caption = menuCaption;

                    // 添加“供应商资料”菜单命令.
                    supplierCommand = (Office.CommandBarButton)cmdBarControl.Controls.Add(Office.MsoControlType.msoControlButton, missing, missing, missing, true);
                    supplierCommand.Caption = "供应商资料(&S)";
                    supplierCommand.Tag = "supplierCommand";
                    supplierCommand.FaceId = 0162;
                    supplierCommand.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(supplierCommand_Click);

                    //添加“物品表”菜单命令
                    materialCommand = (Office.CommandBarButton)cmdBarControl.Controls.Add(Office.MsoControlType.msoControlButton, missing, missing, missing, true);
                    materialCommand.Caption = "物品表(&M)";
                    materialCommand.Tag = "materialCommand";
                    materialCommand.FaceId = 0162;
                    materialCommand.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(materialCommand_Click);

                    //添加“申购单”菜单命令
                    questBuyBillCommand = (Office.CommandBarButton)cmdBarControl.Controls.Add(Office.MsoControlType.msoControlButton, missing, missing, missing, true);
                    questBuyBillCommand.Caption = "申购单输入(&Q)";
                    questBuyBillCommand.Tag = "questBuyBillCommand";
                    questBuyBillCommand.FaceId = 0162;
                    questBuyBillCommand.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(questBuyBillCommand_Click);

                    //添加“报价单”菜单命令
                    quotationCommand = (Office.CommandBarButton)cmdBarControl.Controls.Add(Office.MsoControlType.msoControlButton, missing, missing, missing, true);
                    quotationCommand.Caption = "报价单输入(&U)";
                    quotationCommand.Tag = "quotationCommand";
                    quotationCommand.FaceId = 0162;
                    quotationCommand.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(quotationCommand_Click);

                    //添加"采购单"菜单命令
                    purchaseOrderCommand = (Office.CommandBarButton)cmdBarControl.Controls.Add(Office.MsoControlType.msoControlButton, missing, missing, missing, true);
                    purchaseOrderCommand.Caption = "采购单输入(&P)";
                    purchaseOrderCommand.Tag = "purchaseOrderCommand";
                    purchaseOrderCommand.FaceId = 0162;
                    purchaseOrderCommand.Click += new Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(purchaseOrderCommand_Click);
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        //点击菜单事件
        private void supplierCommand_Click(Microsoft.Office.Core.CommandBarButton Ctrl, ref bool CancelDefault)
        {

             //点击“供应商”菜单后，会在excel 的sheet1的A1单元格写入"The menu command.";
            Globals.Sheet1.Range["A1", missing].Value2 = "The menu command .";
        }

        void materialCommand_Click(Microsoft.Office.Core.CommandBarButton Ctrl, ref bool CancelDefault)
        {
            Globals.Sheet2.Activate(); //点击“物品表”会激活sheet2;
        }

        void questBuyBillCommand_Click(Microsoft.Office.Core.CommandBarButton Ctrl, ref bool CancelDefault)
        {
            Globals.Sheet1.Range["A3", missing].Value2 = "The menu command was clicked.";
        }

        void quotationCommand_Click(Microsoft.Office.Core.CommandBarButton Ctrl, ref bool CancelDefault)
        {
            Globals.Sheet1.Range["A2", missing].Value2 = "The menu command was clicked.";
        }

        void purchaseOrderCommand_Click(Microsoft.Office.Core.CommandBarButton Ctrl, ref bool CancelDefault)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        private void ThisWorkbook_Startup(object sender, System.EventArgs e)
        {
            //当工作簿启动时初始化菜单
            CheckIfMenuBarExists();
            AddMenuBar();  

        }

        private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
        {
        } 
        #region VSTO Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisWorkbook_Startup);
            this.Shutdown += new System.EventHandler(ThisWorkbook_Shutdown);
        }

        #endregion

    }

 个人水平有限，具体语句的功能可以参考MSDN；而且我还有一个问题就是怎么将菜单分级和分组，例如下面的Ofice菜单：