Excel催化剂开源第12波-VSTO开发遍历功能区所有菜单按钮及自定义函数清单

Excel催化剂

发布于 2021-08-19 15:01:52

8540

发布于 2021-08-19 15:01:52

文章被收录于专栏：Excel催化剂

在插件开发过程中，随着功能越来越多，用户找寻功能入口将变得越来越困难，在Excel催化剂，将采用遍历所有功能的方式，让用户可以轻松使用简单的查找功能找到想要功能所在位置，查找的范围有：功能按钮的显示名称、功能说明等。

按钮功能查找关键词文本来源

这个遍历功能区按钮属性，不确定在xml功能区中能否实现，在设计器功能区，因其已经被强类型为一个类对象，用反射技术可以将这个Ribbon类下所有的控件及属性给遍历出来。详细可参考功能第78波说明。

功能区效果

同样地，自定义函数也可以用遍历的方式，列出所有自定义函数。前提是自定义函数是ExcelDna框架开发的。

自定义函数效果

代码实现

给用户一个按钮，点击后遍历到工作表中。

        private void btnShowFeatures_Click(object sender, RibbonControlEventArgs e)
        {
            try
            {
                Common.ExcelApp.ScreenUpdating = false;
                Common.ExcelApp.DisplayAlerts = false;

                RibbonMenu ribbonMenu = sender as RibbonMenu;
                GroupVisibleSetting groupVisibleSetting = new GroupVisibleSetting()
                {
                    CurrentRibbon = this
                };
                groupVisibleSetting.ListTabFunctionInfo();
                //遍历自定义函数清单
                UdfListInfo.ListUdfInfo();

            }
            catch (Exception ex)
            {

                Common.OutMsgError(ex);
            }
            finally
            {
                Common.ExcelApp.ScreenUpdating = true;
                Common.ExcelApp.DisplayAlerts = true;
            }
        }

做了个类，用于访问功能区的信息，里面用了反射技术，因笔者水平也是比较菜，是笔者师傅给予帮助下实现的，详细技术要点也讲不清，有兴趣深入的可以自行百度学习。

using Microsoft.Office.Tools.Excel;
using Microsoft.Office.Tools.Ribbon;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
namespace Excel催化剂
{
    class GroupVisibleSetting
    {
        public Ribbon1 CurrentRibbon { get; set; }
        public RibbonMenu MenuSwitch { get; set; }


        public void ListTabFunctionInfo()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("一级分组");
            dt.Columns.Add("二级分组");
            dt.Columns.Add("三级分组");
            dt.Columns.Add("功能名称");
            dt.Columns.Add("功能说明");


            foreach (var grp in CurrentRibbon.Tabs.Cast<RibbonTab>().FirstOrDefault(s => s.Name == "excelCatalyzer").Groups)
            {
                foreach (var grpItem in grp.Items)
                {
                    if (grpItem is RibbonButton)
                    {
                        var btn = grpItem as RibbonButton;
                        DataRow dr = dt.NewRow();
                        dr["一级分组"] = grp.Label;
                        dr["二级分组"] = grp.Label;
                        dr["三级分组"] = grp.Label;
                        dr["功能名称"] = btn.Label;
                        dr["功能说明"] = btn.SuperTip;

                        dt.Rows.Add(dr);
                    }
                    else if (grpItem is RibbonMenu)
                    {
                        var grpItemMenu = grpItem as RibbonMenu;
                        MenuItemsLoad(grpItemMenu);

                        foreach (var grpItemMenuItem in grpItemMenu.Items)
                        {
                            if (grpItemMenuItem is RibbonButton)
                            {
                                var btn = grpItemMenuItem as RibbonButton;
                                DataRow dr = dt.NewRow();
                                dr["一级分组"] = grp.Label;
                                dr["二级分组"] = grpItemMenu.Label;
                                dr["三级分组"] = grpItemMenu.Label;
                                dr["功能名称"] = btn.Label;
                                dr["功能说明"] = btn.SuperTip;
                                dt.Rows.Add(dr);
                            }
                            else if (grpItemMenuItem is RibbonSeparator)
                            {
                                var btn = grpItemMenuItem as RibbonSeparator;
                                DataRow dr = dt.NewRow();
                                dr["一级分组"] = grp.Label;
                                dr["二级分组"] = grpItemMenu.Label;
                                dr["三级分组"] = grpItemMenu.Label;
                                dr["功能名称"] = btn.Title;
                                dr["功能说明"] = "分隔符，组合以下内容";
                                dt.Rows.Add(dr);
                            }
                            else if (grpItemMenuItem is RibbonGallery)
                            {
                                var itemGal = grpItemMenuItem as RibbonGallery;
                                DataRow dr = dt.NewRow();
                                dr["一级分组"] = grp.Label;
                                dr["二级分组"] = grpItemMenu.Label;
                                dr["三级分组"] = grpItemMenu.Label;
                                dr["功能名称"] = itemGal.Label;
                                dr["功能说明"] = itemGal.SuperTip;
                                dt.Rows.Add(dr);


                            }
                            else if (grpItemMenuItem is RibbonMenu)
                            {
                                var itemMenu = grpItemMenuItem as RibbonMenu;
                                foreach (var itemMenuItem in itemMenu.Items)
                                {

                                    DataRow dr = dt.NewRow();
                                    dr["一级分组"] = grp.Label;
                                    dr["二级分组"] = grpItemMenu.Label;
                                    dr["三级分组"] = itemMenu.Label;
                                    if (itemMenuItem is RibbonButton)
                                    {
                                        var btn = itemMenuItem as RibbonButton;
                                        dr["功能名称"] = btn.Label;
                                        dr["功能说明"] = btn.SuperTip;
                                    }
                                    else if (itemMenuItem is RibbonSeparator)
                                    {
                                        var btn = itemMenuItem as RibbonSeparator;
                                        dr["功能名称"] = btn.Title;
                                        dr["功能说明"] = "分隔符，组合以下内容";
                                    }
                                    dt.Rows.Add(dr);
                                }

                            }

                        }
                    }
                    else if (grpItem is RibbonToggleButton)
                    {
                        var tglBtn = grpItem as RibbonToggleButton;
                        DataRow dr = dt.NewRow();
                        dr["一级分组"] = grp.Label;
                        dr["二级分组"] = grp.Label;
                        dr["三级分组"] = grp.Label;
                        dr["功能名称"] = tglBtn.Label;
                        dr["功能说明"] = tglBtn.SuperTip;
                        dt.Rows.Add(dr);
                    }
                    else if (grpItem is RibbonGallery)
                    {
                        var grpGal = grpItem as RibbonGallery;

                        foreach (var grpGalItem in grpGal.Items)
                        {
                            DataRow dr = dt.NewRow();
                            dr["一级分组"] = grp.Label;
                            dr["二级分组"] = grp.Label;
                            dr["三级分组"] = grp.Label;
                            dr["功能名称"] = grpGalItem.Label;
                            dr["功能说明"] = grpGalItem.SuperTip;
                            dt.Rows.Add(dr);

                        }
                    }


                }
            }

            ListObject listObject = Common.CreateNewVSTOListObject("功能清单",false);
            Common.OutputDataToListObject(listObject, dt, false);
        }

        private void MenuItemsLoad(RibbonMenu grpItemMenu)
        {
            if (grpItemMenu.Name == "menuNumberFormatSetting")
            {
                CurrentRibbon.menuNumberFormatSetting_ItemsLoading(grpItemMenu, null);
                var panel = Utilities.TaskPanelUtility.GetCusTomTaskPaneByTitle(Utilities.TaskPanelUtility.NumberFormatPanelTitle);
                panel.Visible = false;

            }
            else if (grpItemMenu.Name == "menuVisualData")
            {
                CurrentRibbon.menuVisualData_ItemsLoading(grpItemMenu, null);
                var panel = Utilities.TaskPanelUtility.GetCusTomTaskPaneByTitle(Utilities.TaskPanelUtility.DataVisualPanelTitle);
                panel.Visible = false;
            }
            else if (grpItemMenu.Name == "menuInsertPicture")
            {
                CurrentRibbon.menuInsertPicture_ItemsLoading(grpItemMenu, null);
                var panel = Utilities.TaskPanelUtility.GetCusTomTaskPaneByTitle(Utilities.TaskPanelUtility.BatchPicInsertPanelTitle);
                panel.Visible = false;
            }
            else if (grpItemMenu.Name == "menuBarCode")
            {
                CurrentRibbon.menuBarCode_ItemsLoading(grpItemMenu, null);
                var panel = Utilities.TaskPanelUtility.GetCusTomTaskPaneByTitle(Utilities.TaskPanelUtility.QrCodePanelTitle);
                panel.Visible = false;
            }
        }

        public void GrpVisibleSetting()
        {
            var grpInfos = GetGrpInfosSetting();
            foreach (var item in grpInfos)
            {
                var grp = Globals.Ribbons.Ribbon1.GetGrpByGroupName(item.GrpName);
                if (grp != null)
                {
                    grp.Visible = item.GrpVisible;
                }

            }
        }

        public void menuSwitch_ItemsLoading()
        {

            this.MenuSwitch.Items.Clear();



            //从反射中得到grp的集合
            RibbonGroup[] grpfields = GetGrpFields();
            //从setting里提取grp信息

            var grpInfosSetting = GetGrpInfosSetting();

            foreach (var item in grpfields)
            {
                var grpInfo = grpInfosSetting.FirstOrDefault(s => s.GrpName == item.Name);
                RibbonCheckBox control = Globals.Ribbons.Ribbon1.Factory.CreateRibbonCheckBox();
                control.Name = "btn" + item.Name;
                control.Label = item.Label;
                control.Checked = grpInfo.GrpName != null ? grpInfo.GrpVisible : true;  //当setting信息里有保存的话，用setting的信息显示关闭与否，否则用true
                control.Click += Control_Click;
                this.MenuSwitch.Items.Add(control);
            }
        }



        private void Control_Click(object sender, RibbonControlEventArgs e)
        {
            RibbonCheckBox checkBox = sender as RibbonCheckBox;
            string grpName = checkBox.Name.Substring(3);//去除btn字符

            RibbonGroup grp = Globals.Ribbons.Ribbon1.GetGrpByGroupName(grpName);
            grp.Visible = checkBox.Checked;

            SaveSwithSetting();
        }

        private void SaveSwithSetting()
        {
            List<string> grpInfos = new List<string>();
            foreach (RibbonCheckBox item in this.MenuSwitch.Items)
            {
                grpInfos.Add(item.Name.Substring(3) + "," + item.Label + "," + item.Checked.ToString());
            }
            Properties.Settings.Default.SwitchSetting = string.Join(";\n", grpInfos);
            Properties.Settings.Default.Save();
        }

        /// <summary>
        /// 反射的方式获得组的对象
        /// </summary>
        /// <returns></returns>
        private RibbonGroup[] GetGrpFields()
        {
            BindingFlags bf = BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.GetField;
            Type t = typeof(Ribbon1);
            return t.GetFields(bf)
                                    .Where(s => s.Name.StartsWith("grp") && s.Name != "grpSwitch" && s.Name != "grpAbout")
                                    .Select(s => Globals.Ribbons.Ribbon1.GetGrpByGroupName(s.Name))
                                    .ToArray();
        }

        private (string GrpName, string GrpCaption, bool GrpVisible)[] GetGrpInfosSetting()
        {
            string switchSettingString = Properties.Settings.Default.SwitchSetting;

            return switchSettingString.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries)
                                                    .Select(s =>
                                                    (
                                                        GrpName: s.Split(',')[0].Trim(new char[] { '\r', '\n', '\0' }),
                                                        GrpCaption: s.Split(',')[1].Trim(new char[] { '\r', '\n', '\0' }),
                                                        GrpVisible: bool.Parse(s.Split(',')[2].Trim(new char[] { '\r', '\n', '\0' }))
                                                    )
                                                    ).ToArray();
        }

    }
}

同样地在自定义函数方面，通过在自定义函数里作了一个自定义函数，专门用于遍历自定义函数的属性，已经开源在自定义函数项目中。

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Tools.Excel;
namespace Excel催化剂
{
    class UdfListInfo
    {
        private static string shtName = "功能清单";
        internal static void ListUdfInfo()
        {
            try
            {


                Excel.Worksheet sht = Common.ExcelApp.ActiveWorkbook.Worksheets[shtName];
                DataTable dt = new DataTable();
                dt.Columns.Add("函数类别");
                dt.Columns.Add("函数名称");
                dt.Columns.Add("函数注释");

                var result = Common.ExcelApp.Run("ListUdfDesc");

                if (result is Array)
                {
                    var arrResult = Utilities.ArrayUtility.ConvertExcelArrayToDotNetArray(result as Array);
                    foreach (var item in arrResult)
                    {
                        var strSplits = item.ToString().Split(new string[] { "#|#" }, StringSplitOptions.RemoveEmptyEntries);
                        if (strSplits.Length == 3)
                        {
                            DataRow dr = dt.NewRow();
                            dr[0] = strSplits[0];
                            dr[1] = strSplits[1];
                            dr[2] = strSplits[2];
                            dt.Rows.Add(dr);
                        }
                    }
                    Excel.Range listRange = sht.ListObjects[shtName].Range;
                    Excel.Range firstCell = listRange.Offset[0, listRange.Columns.Count + 2].Cells[1, 1];

                    Worksheet vstoSht = Globals.Factory.GetVstoObject(sht);
                    var listObject = vstoSht.Controls.AddListObject(firstCell, "自定义函数清单");

                    Common.OutputDataToListObject(listObject, dt, false);


                }
            }
            catch (Exception)
            {

            }


        }
    }
}

上述代码中，在VSTO项目里，仅需用Application.Run就可以访问到xll里的自定义函数ListUdfDesc，同时有个小要点是自定义函数返回的数组，下标是从1开始的Excel特有的，和.net的0为下标的不一样，需要作下转换。 var arrResult = Utilities.ArrayUtility.ConvertExcelArrayToDotNetArray(result as Array);

 public static object ListUdfDesc()
        {
            List<string> list = new List<string>();
            var funcs = ExcelRegistration.GetExcelFunctions();
            foreach (var funcInfo in funcs)
            {
                string name = funcInfo.FunctionAttribute.Name;
                string desc = funcInfo.FunctionAttribute.Description.Replace("Excel催化剂出品，必属精品！", "");
                string catalog = funcInfo.FunctionAttribute.Category;
                if (!string.IsNullOrEmpty( catalog))
                {
                    list.Add($"{catalog}#|#{name}#|#{desc}");
                }
            }
            if (list.Count>0)
            {
                return list.ToArray();
            }
            else
            {
               return string.Empty;
            }

        }

        public static object[] ConvertExcelArrayToDotNetArray(Array arr)
        {
            int lb = arr.GetLowerBound(0);
            var ret = new object[arr.GetUpperBound(0) - lb + 1];
            for (int ix = 0; ix < ret.Length; ++ix)
            {
                ret[ix] = arr.GetValue(ix + lb);
            }
            return ret;
        }

结语

使用遍历的方式，让用户可以更轻松地查找相应功能，将极大地提升查找功能的友好度，本篇用到反射技术，比较高级的技术，若代码不甚明白，可自行进行相关知识点的学习补充。

再一次验证了VSTO给一般开发者带来的便利性，特别是在设计器功能区上，带来了强类型的Ribbon类，可轻松访问此类里的所有对象，而增加此类的内容，仅需类似Winform那般拖拉控件即可完成，敏捷开发首选。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-01-10，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法

本文分享自 Excel催化剂微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

编程算法

登录后参与评论

0 条评论

热度