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

在插件开发过程中，随着功能越来越多，用户找寻功能入口将变得越来越困难，在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().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 grpInfos = new List();
 foreach (RibbonCheckBox item in this.MenuSwitch.Items)
 {
 grpInfos.Add(item.Name.Substring(3) + "," + item.Label + "," + item.Checked.ToString());
 }
 Properties.Settings.Default.SwitchSetting = string.Join(";
", grpInfos);
 Properties.Settings.Default.Save();
 }
 /// 

 /// 反射的方式获得组的对象
 /// 
 /// 
 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', '
', '' }),
 GrpCaption: s.Split(',')[1].Trim(new char[] { 'r', '
', '' }),
 GrpVisible: bool.Parse(s.Split(',')[2].Trim(new char[] { 'r', '
', '' }))
 )
 ).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 list = new List();
 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那般拖拉控件即可完成，敏捷开发首选。