c# - Excel VSTO 自定义任务窗格可用性
问题描述
我正在编写一个 Excel 应用程序级 VSTO。但是有一点我不明白。我创建的自定义功能区选项卡在应用程序级别可用，这意味着所有打开的工作簿。
我使用功能区设计器创建了功能区选项卡，并通过文件setRibbonControlState中的函数调用它的一个实例ThisAddIn.cs。该函数由我处理的 3 个事件调用ThisAddIn_startup：
((Excel.AppEvents_Event)Application).NewWorkbook += new Excel.AppEvents_NewWorkbookEventHandler(App_NewWorkbook);
Application.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(App_WorkbookBeforeClose);
Application.WorkbookOpen += new Excel.AppEvents_WorkbookOpenEventHandler(App_NewWorkbook);
在这个级别上，一切正常。
然后我创建了 3 个用户选项卡控件（.cs每个课程都有一个文件）以显示在 3 个 CustomTaskPanes 中。I created the 3 CustomTaskPanes in the loadevent handler of the ribbon tab and wrote functions to display the appropriate Task Pane and the appropriate Tab of the inner control. 这是ribbon.cs的部分代码，展示了我如何创建和显示CustomTaskPane：
private CustomTaskPane tsPane;
private void tsFinStRibbon_Load(object sender, RibbonUIEventArgs e)
{
// Custom task pane
tsPane = Globals.ThisAddIn.CustomTaskPanes.Add(new tsDataControl(), "Data", Globals.ThisAddIn.Application.ActiveWindow);
tsPane = Globals.ThisAddIn.CustomTaskPanes.Add(new tsStControl(), "Statements", Globals.ThisAddIn.Application.ActiveWindow);
tsPane = Globals.ThisAddIn.CustomTaskPanes.Add(new tsPubControl(), "Publishing", Globals.ThisAddIn.Application.ActiveWindow);
tsPane.DockPosition = Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionRight;
tsPane.Visible = false;
}
// Données
private tsDataControl setDataPane()
{
tsPane = Globals.ThisAddIn.CustomTaskPanes[0];
tsDataControl control = tsPane.Control as tsDataControl;
if (!tsPane.Visible)
{
tsPane.Width = 320;
tsPane.Visible = true;
}
return control;
}
// Etats
private tsStControl setStPane()
{
tsPane = Globals.ThisAddIn.CustomTaskPanes[1];
tsStControl control = tsPane.Control as tsStControl;
if (!tsPane.Visible)
{
tsPane.Width = 320;
tsPane.Visible = true;
}
return control;
}
// Publications
private tsPubControl setPubPane()
{
tsPane = Globals.ThisAddIn.CustomTaskPanes[2];
tsPubControl control = tsPane.Control as tsPubControl;
if (!tsPane.Visible)
{
tsPane.Width = 320;
tsPane.Visible = true;
}
return control;
}
// Balance générale
private void tsBtn1A_Click(object sender, RibbonControlEventArgs e)
{
if(Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsDataControl control = setDataPane();
control.selectTab(0);
}
}
// balance tiers
private void tsBtn1B_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsDataControl control = setDataPane();
control.selectTab(1);
}
}
// Calcul des états
private void tsBtn2A_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
string btnId = btn.Name;
control.selectTab(0, btnId);
}
}
// -- Calcul des états avec notes annexes
private void tsBtn2A1_Click(object sender, RibbonControlEventArgs e)
{
if(Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonButton btn = (RibbonButton)sender as RibbonButton;
string btnId = btn.Name;
control.selectTab(0, btnId);
}
}
// -- Calcul des états sans notes annexes
private void tsBtn2A2_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonButton btn = (RibbonButton)sender as RibbonButton;
string btnId = btn.Name;
control.selectTab(0, btnId);
}
}
// Bilan
private void tsBtn2B_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonButton btn = (RibbonButton)sender as RibbonButton;
string btnId = btn.Name;
control.selectTab(1, btnId);
}
}
// -- Bilan actif
private void tsBtn3A_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
string btnId = btn.Name;
control.selectTab(1, btnId);
}
}
// -- Bilan Passif
private void tsBtn3B_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
string btnId = btn.Name;
control.selectTab(1, btnId);
}
}
当我打开 Excel 时，我只能从初始电子表格访问内部控件中相应的任务窗格和选项卡。我打开或创建的任何其他电子表格都提供了功能区选项卡，但是当我单击其任何按钮时，自定义任务窗格不会显示。
我试图从ThisAddIn.cs文件中创建任务窗格，它有效地创建了它们，但只有当我单击初始电子表格中的功能区时它们才会做出反应。
在我的阅读中，我找不到有效地将功能区附加到任务窗格以使其正常运行的方法。
有谁知道如何解决它？
感谢。
编辑
这是完整的ThisAddIn.cs代码：
public partial class ThisAddIn
{
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
// Events handlers
((Excel.AppEvents_Event)Application).NewWorkbook += new Excel.AppEvents_NewWorkbookEventHandler(App_NewWorkbook);
Application.WorkbookBeforeClose += new Excel.AppEvents_WorkbookBeforeCloseEventHandler(App_WorkbookBeforeClose);
Application.WorkbookOpen += new Excel.AppEvents_WorkbookOpenEventHandler(App_NewWorkbook);
}
private void setRibbonControlState(ref bool isEnabled)
{
int workbookCount = Application.Windows.Count;
if (workbookCount > 1 && !isEnabled)
return;
tsFinStRibbon ribbon = Globals.Ribbons.tsFinStRibbon;
int tabCount = ribbon.Tabs.Count;
for (int i = 0; i < tabCount; i++)
{
RibbonTab tab = ribbon.Tabs[i];
int grpCount = tab.Groups.Count;
for (int j = 0; j < grpCount; j++)
{
RibbonGroup grp = tab.Groups[j];
int itCount = grp.Items.Count;
for (int k = 0; k < itCount; k++)
{
grp.Items[k].Enabled = isEnabled;
}
}
}
if (!isEnabled)
{
int paneCount = CustomTaskPanes.Count;
for (int i = 0; i < paneCount; i++)
CustomTaskPanes[i].Visible = false;
}
}
private void App_NewWorkbook(Excel.Workbook Wb)
{
// Set the ribbon
bool isEnabled = true;
setRibbonControlState(ref isEnabled);
}
private void App_WorkbookBeforeClose(Excel.Workbook Wb, ref bool Cancel)
{
bool isEnabled = (Cancel) ? false : true;
setRibbonControlState(ref isEnabled);
}
private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
}
#region VSTO 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(ThisAddIn_Startup);
this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
}
#endregion
}
这是完整的tsFinRibbon.cs代码：
public partial class tsFinStRibbon
{
//static private Dictionary<string, CustomTaskPane> tsDPanes = new Dictionary<string, CustomTaskPane>();
private CustomTaskPane tsPane;
private void tsFinStRibbon_Load(object sender, RibbonUIEventArgs e)
{
// Custom task pane
Globals.ThisAddIn.CustomTaskPanes.Add(new tsDataControl(), "Data");
Globals.ThisAddIn.CustomTaskPanes.Add(new tsStControl(), "Statements");
Globals.ThisAddIn.CustomTaskPanes.Add(new tsPubControl(), "Publishing");
}
// Data task pane call
private tsDataControl setDataPane()
{
tsPane = Globals.ThisAddIn.CustomTaskPanes[0];
tsDataControl control = tsPane.Control as tsDataControl;
if (!tsPane.Visible)
{
tsPane.Width = 320;
tsPane.Visible = true;
}
return control;
}
// Statement task pane call
private tsStControl setStPane()
{
tsPane = Globals.ThisAddIn.CustomTaskPanes[1];
tsStControl control = tsPane.Control as tsStControl;
if (!tsPane.Visible)
{
tsPane.Width = 320;
tsPane.Visible = true;
}
return control;
}
// Publications task pane call
private tsPubControl setPubPane()
{
tsPane = Globals.ThisAddIn.CustomTaskPanes[2];
tsPubControl control = tsPane.Control as tsPubControl;
if (!tsPane.Visible)
{
tsPane.Width = 320;
tsPane.Visible = true;
}
return control;
}
// Balance générale
private void tsBtn1A_Click(object sender, RibbonControlEventArgs e)
{
if(Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsDataControl control = setDataPane();
control.selectTab(0);
}
}
// balance tiers
private void tsBtn1B_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsDataControl control = setDataPane();
control.selectTab(1);
}
}
// Calcul des états
private void tsBtn2A_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
string btnId = btn.Name;
control.selectTab(0, btnId);
}
}
// -- Calcul des états avec notes annexes
private void tsBtn2A1_Click(object sender, RibbonControlEventArgs e)
{
if(Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonButton btn = (RibbonButton)sender as RibbonButton;
string btnId = btn.Name;
control.selectTab(0, btnId);
}
}
// -- Calcul des états sans notes annexes
private void tsBtn2A2_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonButton btn = (RibbonButton)sender as RibbonButton;
string btnId = btn.Name;
control.selectTab(0, btnId);
}
}
// Bilan
private void tsBtn2B_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonButton btn = (RibbonButton)sender as RibbonButton;
string btnId = btn.Name;
control.selectTab(1, btnId);
}
}
// -- Bilan actif
private void tsBtn3A_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
string btnId = btn.Name;
control.selectTab(1, btnId);
}
}
// -- Bilan Passif
private void tsBtn3B_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
string btnId = btn.Name;
control.selectTab(1, btnId);
}
}
// -- Bilan N-1
private void tsBtn3C_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
string btnId = btn.Name;
control.selectTab(1, btnId);
}
}
// -- Haut du bilan
private void tsBtn3D_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonButton btn = (RibbonButton)sender as RibbonButton;
string btnId = btn.Name;
control.selectTab(1, btnId);
}
}
// -- Bas du bilan
private void tsBtn3E_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonButton btn = (RibbonButton)sender as RibbonButton;
string btnId = btn.Name;
control.selectTab(1, btnId);
}
}
// Compte de résultat
private void tsBtn2C_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonButton btn = (RibbonButton)sender as RibbonButton;
string btnId = btn.Name;
control.selectTab(2, btnId);
}
}
// -- Compte de résultat charges
private void tsBtn4A_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
string btnId = btn.Name;
control.selectTab(2, btnId);
}
}
private void tsBtn4B_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
string btnId = btn.Name;
control.selectTab(2, btnId);
}
}
private void tsBtn4C_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
string btnId = btn.Name;
control.selectTab(2, btnId);
}
}
private void tsBtn4D_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
string btnId = btn.Name;
control.selectTab(2, btnId);
}
}
// Flux de trésorerie
private void tsBtn2D_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonButton btn = (RibbonButton)sender as RibbonButton;
string btnId = btn.Name;
control.selectTab(3, btnId);
}
}
private void tsBtn5A_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
string btnId = btn.Name;
control.selectTab(3, btnId);
}
}
private void tsBtn5B_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonButton btn = (RibbonButton)sender as RibbonButton;
string btnId = btn.Name;
control.selectTab(3, btnId);
}
}
private void tsBtn5C_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
string btnId = btn.Name;
control.selectTab(3, btnId);
}
}
private void tsBtn5D_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
string btnId = btn.Name;
control.selectTab(3, btnId);
}
}
// Annexes
private void tsBtn6A_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsStControl control = setStPane();
RibbonSplitButton btn = (RibbonSplitButton)sender as RibbonSplitButton;
string btnId = btn.Name;
control.selectTab(4, btnId);
}
}
private void tsBtn7A_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsPubControl control = setPubPane();
control.selectTab(0);
}
}
private void tsBtn7B_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsPubControl control = setPubPane();
control.selectTab(1);
}
}
private void tsBtn7C_Click(object sender, RibbonControlEventArgs e)
{
if (Globals.ThisAddIn.CustomTaskPanes.Count != 0)
{
tsPubControl control = setPubPane();
control.selectTab(2);
}
}
}
我稍微更改了自定义功能区选项卡的加载事件处理程序。财产的使用tspane无关紧要。自定义功能区选项卡的加载事件处理程序创建三个自定义任务窗格。它们中的每一个都与一组特定的功能区按钮相关。单击组中的按钮时，将调用相应的任务窗格并激活任务窗格中的相应 tabControl。
正如我之前所说，当我打开 Excel 时，当我单击功能区按钮时，只有初始工作簿会返回自定义任务窗格。
我还注意到，当我打开 Excel、创建/打开第二个工作簿并单击自定义功能区选项卡的功能区按钮时，初始工作簿上可见的任务窗格会做出反应并激活正确的选项卡或任务窗格。
解决方案
我终于想通了。必须为其需要出现的每个窗口创建自定义任务窗格。因为我认为自定义任务窗格绑定到应用程序而不是任何特定的工作簿，所以我只创建了其中的 3 个。
因此，我创建了一个字典来跟踪我创建的任务窗格及其在 CustomTaskPaneCollection 中的位置，以便在切换 Excel 窗口时可以调用/或创建（如有必要）任何任务窗格。以下是关键代码：
private CustomTaskPane tsPane;
private int Wn;
private Dictionary<string, int> dict;
private void tsFinStRibbon_Load(object sender, RibbonUIEventArgs e)
{
// Create a dictionary of indexes
dict = new Dictionary<string, int>();
}
// Données
private tsDataControl setDataPane()
{
// Initialize the window ID and pane name
Wn = Globals.ThisAddIn.Application.Hwnd;
string paneName = "Data" + Wn.ToString();
// Check if the CTP already exists and create it otherwise
if (!dict.ContainsKey(paneName))
{
tsPane = Globals.ThisAddIn.CustomTaskPanes.Add(new tsDataControl(), paneName);
dict.Add(paneName, Globals.ThisAddIn.CustomTaskPanes.Count - 1);
}
else
{
tsPane = Globals.ThisAddIn.CustomTaskPanes[dict[paneName]];
}
// Get the tab control
tsDataControl control = tsPane.Control as tsDataControl;
if (!tsPane.Visible)
{
tsPane.Width = 320;
tsPane.Visible = true;
}
return control;
}
// Etats
private tsStControl setStPane()
{
// Initialize the window ID and pane name
Wn = Globals.ThisAddIn.Application.Hwnd;
string paneName = "Statement" + Wn.ToString();
// Check if the CTP already exists and create it otherwise
if (!dict.ContainsKey(paneName))
{
tsPane = Globals.ThisAddIn.CustomTaskPanes.Add(new tsStControl(), "Statement");
dict.Add(paneName, Globals.ThisAddIn.CustomTaskPanes.Count - 1);
}
else
{
tsPane = Globals.ThisAddIn.CustomTaskPanes[dict[paneName]];
}
// Get the tab control
tsStControl control = tsPane.Control as tsStControl;
// Display the pane
if (!tsPane.Visible)
{
tsPane.Width = 320;
tsPane.Visible = true;
}
return control;
}
// Publications
private tsPubControl setPubPane()
{
// Initialize the window ID and pane name
Wn = Globals.ThisAddIn.Application.Hwnd;
string paneName = "Publishing" + Wn.ToString();
// Check if the CTP already exists and create it otherwise
if (!dict.ContainsKey(paneName))
{
tsPane = Globals.ThisAddIn.CustomTaskPanes.Add(new tsPubControl(), paneName);
dict.Add(paneName, Globals.ThisAddIn.CustomTaskPanes.Count - 1);
}
else
{
tsPane = Globals.ThisAddIn.CustomTaskPanes[dict[paneName]];
}
// Get the tab control
tsPubControl control = tsPane.Control as tsPubControl;
// Display the pane
if (!tsPane.Visible)
{
tsPane.Width = 320;
tsPane.Visible = true;
}
return control;
}
谢谢！
推荐阅读
- javascript - 从 json 解析数组的 java 脚本
- c# - EF CORE datetime 保存时间为 0
- reactjs - 如何在渲染后更新状态
- git - Github 桌面/VsCcode 身份验证失败
- javascript - 是否有更简洁的方法来检查变量是否具有类型编号？
- javascript - jQuery这个元素
- typescript - 为什么 tsc 忽略 tsconfig.json 中的 include 和 exclude？
- sql-server - SQL Server 中 where 子句列的多个条件
- c - 子字符串表示——长度还是指向最后一个字节的指针？
- sql-server - 即使基础表中存在数据，SQL Server 视图也是空的