文章 / 公众号历史文章
Excel催化剂开源第12波-VSTO开发遍历功能区所有菜单按钮及自定义函数清单
在插件开发过程中，随着功能越来越多，用户找寻功能入口将变得越来越困难，在Excel催化剂 ，将采用遍历所有功能的方式，让用户可以轻松使用简单的查找功能找到想要功能所在位置，查找的范围有：功能按钮的显示名称、功能说明等。 按钮功能查找关键词文本来源 这个遍历功能区按钮属性，不确定在xml功能区中能否实
在插件开发过程中，随着功能越来越多，用户找寻功能入口将变得越来越困难，在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那般拖拉控件即可完成，敏捷开发首选。