Tags

, , ,

This article is describing how to add a custom task pane in an Excel add-in application. Custom task panes are very powerful and represent the right way to enhance Excel with custom functionality. So in case you want to create input form to collect data from the user, you just need to add a task pane which includes a user control (typically these are used in Windows forms application).

Because I did not want to repeat the code each time I am requested to add a custom task pane, I have add it to a static method.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using Microsoft.Office.Core;

namespace ExcelApplication
{
    public static class ExcelUtils
    {
        public static Microsoft.Office.Tools.CustomTaskPane AddCustomTaskPane(System.Windows.Forms.UserControl userControl, string taskPaneTitle)
        {
            Microsoft.Office.Tools.CustomTaskPane pane = Globals.ThisAddIn.CustomTaskPanes.Cast<Microsoft.Office.Tools.CustomTaskPane>().FirstOrDefault(taskPane => taskPane.Title == "Forecast Report") ??
                                                         Globals.ThisAddIn.CustomTaskPanes.Add(userControl, taskPaneTitle) as Microsoft.Office.Tools.CustomTaskPane;
            return pane;
        }
    }
}

Each time I call this method, a custom task pane is added to the application with specified title. In case a custom task pane having specified title already exists, method is returning existing one. Of course in this case it doesn’t matter what user control you have specified and you should pay attention not to create a conflict.
Let’s see how we can use this in a concrete case.

           UserControl forecastReport = new UserControl();
           Microsoft.Office.Tools.CustomTaskPane pane = ExcelUtils.AddCustomTaskPane(forecastReport, "Forecast Report");

           pane.VisibleChanged += ((object s, EventArgs ee) =>
           {
               if (pane.Visible == false)
               {
                   Globals.ThisAddIn.CustomTaskPanes.Remove(pane);
               }
           });
           pane.Visible = true;

So, not only I just showed how to use my static method. I also showed also how to release resources when user hides the pane. But to be honest, if you want to even forward, you can adapt the static method a little bit.

        public static Microsoft.Office.Tools.CustomTaskPane AddCustomTaskPane(System.Windows.Forms.UserControl userControl, string taskPaneTitle, bool autoDisposeOnHide = false)
        {
            Microsoft.Office.Tools.CustomTaskPane pane = Globals.ThisAddIn.CustomTaskPanes.Cast<Microsoft.Office.Tools.CustomTaskPane>().FirstOrDefault(taskPane => taskPane.Title == taskPaneTitle) ??
                                                         Globals.ThisAddIn.CustomTaskPanes.Add(userControl, taskPaneTitle) as Microsoft.Office.Tools.CustomTaskPane;

            // Retun pane if auto dipose on close is false
            if (autoDisposeOnHide != true) return pane;

            // If auto dispose is set to true, we continue the code and define handler for visible change event
            EventHandler disposeOnClose = (object sender, EventArgs e) =>
            {
                Microsoft.Office.Tools.CustomTaskPane pn = sender as Microsoft.Office.Tools.CustomTaskPane;
                if (pn.Visible != false) return;
                Globals.ThisAddIn.CustomTaskPanes.Remove(pn);
                pn.Dispose();
            };

            // Bind the event handler to task pane visible change event
            pane.VisibleChanged -= disposeOnClose;
            pane.VisibleChanged += disposeOnClose;
            return pane;
        }

I have introduced a parameter called autoDisposeOnHide. If is set to true, when user hides the task pane, it is disposed and remove from task panes collection.

Happy Coding !!!

Advertisements