ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 高效办公必会的Office实战技巧 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 13703|回复: 5

[原创]VSTO Excel开发(一):自定义excel 菜单

[复制链接]

TA的精华主题

TA的得分主题

发表于 2007-7-14 14:10 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:VSTO开发

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

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

先看一下效果图:item1

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

item2

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

item3

右击“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);

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-7-14 14:12 | 显示全部楼层

接上

       //添加“物品表”菜单命令
                    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菜单:

item4

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

TA的精华主题

TA的得分主题

发表于 2008-12-10 13:56 | 显示全部楼层

楼主在么?求助

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

TA的精华主题

TA的得分主题

发表于 2008-12-10 13:58 | 显示全部楼层
另外发现个很奇怪的现象,VSTO中,如果和Excel对象操作有关的语句执行异常,并不抛出错误,而是直接跑到finally中关闭数据库,然后大摇大摆的冒充执行成功了。

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

TA的精华主题

TA的得分主题

发表于 2009-10-11 21:48 | 显示全部楼层

知道如何加载"宏"

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

TA的精华主题

TA的得分主题

发表于 2011-9-29 15:19 | 显示全部楼层
你好,项目最近需要使用vsto对excel进行二次开发。现在遇到一个问题:我新建一个“excel模板”项目,然后通过在任务面板中添加一个按钮来实现自定义的另存为。我另存为的时候,把模板格式另存成普通的excel格式。(即把.xltx另存成.xlsx)。但是我在调用workbook.saveas()方法时,虽然excel文档存到了指定路径,但是打开后发现excel里面是空的,数据全丢了。急请帮助!!!
环境:win7 X64;vs2010;office2010.
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关闭

最新热点上一条 /1 下一条

关注官方微信,高效办公专列,每天发车

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-8-26 00:37 , Processed in 0.130025 second(s), 15 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表