[原创]VSTO Excel开发（一）：自定义excel 菜单

木头甲

在excelhome潜水也有时日了，学到了很多VBA开发方面的知识；现在由于工作需要，在使用VSTO开发Excel，分享给大家，希望对大家有点帮助

和我的Blog同步发行：http://itblog.spaces.live.com

先看一下效果图：

 我们现在来定义这个菜单“采购系统”：打开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菜单：

 希望那位高人能指点一下，谢谢。

wslfriend

楼主，我用上述代码，制作了一个可以将前台Excel信息保存到后台数据库中的项目，菜单添加维护的代码是模仿了此贴，但发现一个问题，
如果关闭了第一个WorkBook,菜单就会失灵，不触发任何事件，百思不得其解，如您有空，请看看

wslfriend

另外发现个很奇怪的现象，VSTO中，如果和Excel对象操作有关的语句执行异常，并不抛出错误，而是直接跑到finally中关闭数据库，然后大摇大摆的冒充执行成功了。

分明应该返回false  的函数，根本就没机会return false。

tactsong

bar_One.OnAction = "do成果表";//do成果表是宏名
但是如何来加载vsto生成的dll文件?

阿桥的窝

你好，项目最近需要使用vsto对excel进行二次开发。现在遇到一个问题：我新建一个“excel模板”项目，然后通过在任务面板中添加一个按钮来实现自定义的另存为。我另存为的时候，把模板格式另存成普通的excel格式。（即把.xltx另存成.xlsx）。但是我在调用workbook.saveas（）方法时，虽然excel文档存到了指定路径，但是打开后发现excel里面是空的，数据全丢了。急请帮助！！！
环境：win7 X64；vs2010；office2010.