Prerequisites
- Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
- Create Excel Addin in C# code style (Visual Studio Tools for Office)
- Excel 2010 or above
CustomTaskPanel
CustomTaskPanel is a component of Microsoft.Office.Tools library which provides easy side by side navigation during processing the tasks in Office Applications. Let’s take a look how a standard task pane looks like:
We will create a simple custom task panel having some controls over it and it will load on each new workbook upon opening it.
Step 1: Open ThisAddin.cs with default methods like ThisAddin_Startup and ThisAddin_Shutdown
Step 2: create Workbook Open and new Workbook event on which we will load our CustomTaskPanel. High level events must be declared under ThisAddin_Startup which initialize them for life time.
//Event declaration for Workbook Open this.Application.WorkbookOpen += new Excel.AppEvents_WorkbookOpenEventHandler(OpenworkbookHandler); //Event declaration for New Workbook ((Excel.AppEvents_Event)Application).NewWorkbook += ThisAddIn_NewWorkbook;
//Implementation Open workbook handler which take workbook as parameter
private void OpenworkbookHandler(Excel.Workbook wb)
{
//Here we will create our CustomTaskPanel
}
//Implementation New workbook handler which take workbook as parameter
private void ThisAddIn_NewWorkbook(Excel.Workbook wb)
{
//Here we will create our CustomTaskPanel
}
Step 3: Add a class and name it as “TaskPaneManager.cs” in your project. Refer code below (note you must add reference of Microsoft.Office.Tools in the header using:
public class TaskPaneManager
{
static Dictionary<string, CustomTaskPane> _createdPanes = new Dictionary<string, CustomTaskPane>();
/// <summary>
/// Gets the taskpane by name (if exists for current excel window then returns existing instance, otherwise uses taskPaneCreatorFunc to create one).
/// </summary>
/// <param name="taskPaneId">Some string to identify the taskpane</param>
/// <param name="taskPaneTitle">Display title of the taskpane</param>
/// <param name="taskPaneCreatorFunc">The function that will construct the taskpane if one does not already exist in the current Excel window.</param>
public static CustomTaskPane GetTaskPane(string taskPaneId, string taskPaneTitle, Func<WinForm> taskPaneCreatorFunc)
{
string key = string.Format("{0}({1})", taskPaneId, Globals.ThisAddIn.Application.Hwnd);
if (!_createdPanes.ContainsKey(key))
{
var pane = Globals.ThisAddIn.CustomTaskPanes.Add(taskPaneCreatorFunc(), taskPaneTitle);
_createdPanes[key] = pane;
}
return _createdPanes[key];
}
}
As you see in above class we have one method name as “GetTaskPane” which takes one argument type Func<WinForm> and this is the real trick that we are telling the function that bind Panel on the UserControl.
Step 4: Add a win form. Right click in your project and add a UserControl name it as WinForm.
Step 5: Create a XAML control by adding it in your project as per your wish. In my example I have created it and named as “CategoryPanelControl.xaml”
Step 6: On the user control drag and drop a ElementHost control from control toolbox as shown below:
UserControl
ElementHost Control
Toggle the code behind of your Usercontrol form (WinForm) by pressing F7 and on the form Load event right followings to bind your XAML control:
private void WinForm_Load(object sender, EventArgs e)private void WinForm_Load(object sender, EventArgs e)
{
var frm = new CategoryPanelControl(); elementHost1.Child = frm;
}
Now your control (CategoryPanelControl) is ready to hold XAML control which you design later.
Step 7: Come back to ThisAddin.cs, here we will create two methods one to create our custom task panel from scratch and another to get the instance of it which we have created under TaskPaneManager.cs. Creating CustomTaskPanel from scratch method that you need to put under ThisAddin.cs.
public void CreateSidePane(Excel.Workbook context)
{
try
{
if (context.Windows.Count > 0)
{
var myCustomTaskPane = CurrentSidePane;
if (myCustomTaskPane == null)
{
Excel.Window window = context.Windows[1];
myCustomTaskPane =this.CustomTaskPanes.Add(new WinForm(), SidePaneTitle, window);
if (window != null) Marshal.ReleaseComObject(window);
}
myCustomTaskPane.Width = 360;
myCustomTaskPane.Visible = false;
}
}
finally
{
//Your cleanup code goes here
}
}
Put following code to get existing instance of CustomTaskPanel in ThisAddin.cs.
public CustomTaskPane CurrentSidePane
{
get
{
return TaskPaneManager.GetTaskPane("", "My Custom Side Panel Title Here", () => new WinForm());
}
}
Step 8: Initializing your CustomTaskPanel in your New workbook or Open workbook methods so, when you add a new workbook or open an existing workbook your CustomTaskPanel (Side Panel) will be available. So go back to Step 2 and revised those methods as below:
private void OpenworkbookHandler(Excel.Workbook wb)
{
try
{
wbs.Add(wb);
CreateSidePane(wb);
this.CurrentSidePane.Visible = true;
}
finally
{
if (wb != null) Marshal.ReleaseComObject(wb);
}
}
//Implementation New workbook handler which take workbook as parameter
private void ThisAddIn_NewWorkbook(Excel.Workbook wb)
{
try
{
wbs.Add(wb);
CreateSidePane(wb);
this.CurrentSidePane.Visible = true;
}
finally
{
if (wb != null) Marshal.ReleaseComObject(wb);
}
}
Step 9: Hit F5 and you will enjoy your Side Panel or CustomTaskPanel.
