Accessing some SharePoint properties from JavaScript

Tags

, , , , ,

We know now Microsoft is pushing the SharePoint development to client side as much as possible. This means developers should use JavaScript more instead of server side code where is possible and Microsoft made this easier by exposing some SharePoint properties to client side code. There is a JavaScript object called _spPageContextInfo which contains some properties you might use in your code:

  • webServerRelativeUrl
  • webAbsoluteUrl
  • siteAbsoluteUrl
  • serverRequestPath
  • layoutsUrl
  • webTitle
  • webTemplate
  • tenantAppVersion
  • isAppWeb
  • webLogoUrl
  • webLanguage
  • currentLanguage
  • currentUICultureName
  • currentCultureName
  • clientServerTimeDelta
  • siteClientTag
  • crossDomainPhotosEnabled
  • webUIVersion
  • webPermMasks
  • pageListId
  • pageItemId
  • pagePersonalizationScope
  • userId
  • systemUserKey
  • alertsEnabled
  • siteServerRelativeUrl
  • allowSilverlightPrompt

List is long and you can figure out yourself how to use them. Probably the most noticeable property is userId. I remember when people moved to SharePoint 2013, a lot of developers were upset they could not use any more _spUserId. That’s probably they did not know it was replaced with _spPageContextInfo.userId. I would say, Microsoft did not communicate enough new functionalities, but this doesn’t mean they did not offer something to replace old functionality.

Advertisements

“Data cannot be inserted because there is no matching record” error

Tags

, , ,

Microsoft Access and linked tables to SharePoint lists are commonly used to manipulate data from SharePoint sites. Through the Microsoft queries, users can bulk update or insert data into a SharePoint list. I also did the same, but a few days ago I started to receive an error running an update query against a SharePoint list: “Data cannot be inserted because there is no matching record”.

In my case, removing some users from the site was the cause of this. People/group field type is actually a type of lookup field which is pointing to User Information List. But when you remove a user from site, which means removing it from the users list also, people/group field value is not reset to blank and it keeps continue storing the ID of removed user. So, when you try to run an update query you actually force the field to keep the value, but it fails because users is not anymore accessible in users list. If you recently removed users, go to your list and update records replacing removed users with other users, which are not removed from your site.

Date type extensions in Microsoft Ajax library

Tags

, , ,

I have to admit, Microsoft Ajax JavaScript library is not so well known and popular these days. But still if offers a lot of functionalities a developer might use doing some client side work. I believe can be very useful for SharePoint developers, as SharePoint, being a web forms based technology, use it.

For this article, my objective is to explain a little bit more about Ajax Date type extensions, which are a set of methods designed to extend JavaScript built-in Date object.

Format date as string

Converting a date object to a formatted string has never been easier. Ajax is adding a extension method to Date object called “format” which accepts a format string as parameter. As rule this string should contain the following placeholders to show different parts of the date:

  • yyyy – show full year
  • yy – show short format of year (only the last 2 numbers)
  • M – show short month number (doesn’t add leading zero if month number is under 10)
  • MM – show month
  • MMM – show month name, like Oct, Jan
  • MMM – show full month name, like October and January
  • d – show short day number (doesn’t add leading zero if day number is under 10)
  • dd – show day
  • ddd – show day name, like Sat, Sun….
  • dddd – show full day name, like Saturday or Sunday
  • hh- show hours
  • mm – show minutes
  • ss- show seconds
var dt = new Date();

// 2015-10-22T09:18:30Z
dt.format("yyyy-MM-ddThh:mm:ttZ") 

// 2015/10/22
dt.format("yyyy/MM/dd");

// 22-Oct-15
dt.format("dd-MMM-yy");

// Output Thu
dt.format("ddd");

// Output Thursday
dt.format("dddd");

// Output October
dt.format("MMMM");

You can play with this format string parameter and find more date formats. There is not rocket science here and it is straight format.

Convert a formatted string to Date

Let’s now discussed about the reverse process to convert a string to Date object. Microsoft implemented a static method for Date object called “parseInvariant”. It returns a date if string is a valid representation of it, otherwise returns null.

// @value = A string that represents a date.
// @format = A string provided the format information. For me an array of strings did not work, even  Microsoft says this is the correct parameter type
 var a = Date.parseInvariant(value, format);

You can see below some examples.

var dt;

// This is valid date and is 1st of december 2015. 
dt = "2015-12-01";
Date.parseInvariant(dt,"yyyy-MM-dd");
dt = "12/01/2015";
Date.parseInvariant(dt,"dd/MM/yyyy");
dt = "01 December 2015";
Date.parseInvariant(dt,"dd MMMM yyyy");

It seems for “parseInvariant” function we use the same placeholders like we used for “format” function.

The functions I have talked about do not depend on the culture, but there are also other two versions which are affected by it: “parseLocale” and “localeFormat”. Documentation about entire Date type extended methods can be found here. I have to admit Microsoft did not provide too much details about this, but I hope my findings will help you work smarter doing client side development on web forms based technology (not only, because Ajax is not depending on server side).

Calculate financial fiscal year and quarter based on some given dates

Tags

, , ,

Is very well known that US financial fiscal year and quarters are not the same as calendar ones. As a result, is pretty much common in US companies to be required to calculate these based on some dates representing accounting information. In general, management needs to present financial reports at the end of each quarter or year.

How is typically solved? In almost 100% of the cases, people are using very long Excel formulas or based on VBA or even calculated SharePoint fields if data is stored into a SharePoint list. I have nothing against, just I do not like to complicate things. There is an easier way to do this with a little help from Power Query as you can create simple custom formulas to extract the information you need.

Let’s take a look on the formula to calculate fiscal quarter.

let
    fiscalQuarter = (fd as nullable date) =>
    let 

     m = Date.Month(Date.From(fd)),    
     fm = if m = 11 or m = 12 or  m = 1 then "Q1" else 
          if m = 2 or m = 3 or m = 4 then "Q2" else 
          if m = 5 or m = 6 or m = 7 then "Q3" else 
          if m = 8 or m = 9 or m = 10 then "Q4" else  ""          
     in fm

in
    fiscalQuarter

I have created a formula which accepts a nullable date as parameter. I get the month number based on it and depending on it I return the quarter name. For 1st, 11th, 12th months the returned results is Q1, for 2nd, 3rd and 4th is Q2, for 5th, 6th and 7th is Q3 and for 8th, 9th and 10th is Q4.

To calculate the fiscal year is even an easier thing to do.

let
    fiscalYear = (fd as nullable date) =>
    
    let m = Date.Month(Date.From(fd)), y = Date.Year(Date.From(fd)),
        year = if m = 11 or m = 12 then y + 1 else y
    in year 

in
    fiscalYear

I get the year value from a date, which is always a number if provided number is not null, and for 11th and 12th months I increase it with 1 unit.

I found this was easier than classical ways which sometimes generate ugly and long code. After all, who wants to work with very very long formulas in Excel formula bar or with calculated SharePoint columns which doesn’t always work as expected. And as a rule I am driven by in my career: a better solution can be much closer to you than you think. 🙂 Just do more research and you will probably find it.

Create custom formula in Power Query

Tags

,

Power Query is a very powerful extension and requires more attention. So, for today, I have decided how to write and use a custom formula. It will be a simple example to show friendly name of the months based on month’s numbers.

Let’s imagine we have this data.
PowerQueryDummyData
And now we want to show friendly name of the month for each date in the “Start Date” column. Of course we can use some VBA formulas as well, but let’s use something more modern. Just go to “Power Query” ribbon to create a blank query, which will contain our custom formula.Actually the sequence is: Power Query -> Get External Data -> From Other Sources -> Blank Query. We name the query “ShowMonthName” and in Advanced Editor we enter the formula (to enter in the editor respect the sequence: “Home” ribbon -> “Query” tab -> “Advanced Editor” button).
Custom formula
What we actually did? We create a formula called “ShowMonthName” which can be used in Power Query connection. And I am going to show how to do this.

Place mouse inside used range containing dates and do the following: Power Query -> Excel Data -> From Table. Excel will select by default entire used range, so you probably do not need to adjust it and you can go to the next screen, which is our query editor window. Here you go to Add Column tab and click on Add Custom Column button from General tab. And for the next step you need to name your column and insert our formula in formula text area control. This is straight forward and there no other explanation needed.
Add custom column
If everything is correct, you will see the results immediately in the query editor window and, if there is no error, you can use the connection you have just created, which includes the custom formula.
Formula results
I really encourage you to start using Power Query. It will open a new world for you and you will love using it, because it is a step forward in reporting and business intelligence. Leave behind ugly VBA formulas and start looking for the future.

Combining Excel worksheets with Power Query

Tags

,

Microsoft released an extension for Excel called Power Query. It makes some tasks easier, like combining Excel worksheets in a single one. This is the subject I am going to talk about, but in the future I will cover more about Power Query.

Let’s assume we have a worksheet containing foods.

Food Worksheet

Food Worksheet


And we also have another worksheet containing electronics.
Electronics Worksheet

Electronics Worksheet


Both worksheets have the same columns and we want to combine them into a single worksheet to display all data. With Power Query this becomes an easy task. You just need to follow these steps.

Creating connection from tables

Click inside a worksheet used range and from Power Query ribbon choose to get data from table.

Get data from table

Get data from table


You are asked to choose the range you want to use for data. By default used range is selected.
Choose range

Choose range


Query editor window is opened. Just give a name to your query (I named it “Table_Food”) and choose “Close & Load To” option from “Close & Load” menu. From the new window choose “Only Create Connection” as we already have data into table.

Load To

Load To

Repeat this operation for the other worksheet, of course giving a different name for the connection (like “Table_Electronics”).

Create the connection to combine the data

To create a connection to show combined data, choose “Show Pane” from Power Query ribbon.

Show Pane

Show Pane


On the “Workbook Queries” pane, right click on any data connection and choose “Append”.
Choose Append Option

Choose Append Option


On the next screen choose source and destination for append query.
Choose source and destination

Choose source and destination


Give a name for your new query (like “Combined Data”) and again choose “Close & Load To” option from “Close & Load” menu, of course only creating connection.

Display data

Now, as you have created all connections, go to “Data” ribbon and choose to get external data from existing connection.

Get data from existing connections

Get data from existing connections


Choose your connection which displays data from both worksheets and click “Open” button.
Choose connection

Choose connection


On the next screen is really up to you what you choose. You can choose to display data into an existing worksheet or you can tell Excel to create a new for you to place the data. You can consider now the task is done. 🙂

There is an interesting part with Power Query. If, for example, you add new data into one of the worksheets you used as a source and refresh data connections, Power Query knows and put new data into combined connection.

Autocomplete textbox with JavaScript CSOM

Tags

, , , , ,

I have searched on the internet about how to create an auto-complete functionality in SharePoint. Of course, jQuery UI was the solution with an Ajax request to REST service. For some reason, I cannot understand it, the examples I have seen are based on synchronous Ajax request. So I simply said no way. I needed something asynchronous to avoid page freeze.

Normally to create an auto-complete is a simple thing.


    jQuery('#txtBox').autocomplete({
        minLength: 3,
        source: function(request, response) {
         // At the and of the async operation call response with obtained results
         }
     });

Asynchronous operation will be placed inside source function, but instead using classic Ajax examples, I will use JavaScript CSOM. It is not better, but I like it more. So is more a personal choice.

For getting data from SharePoint, you can use classical example from MSDN website, but I prefer to reorganize the code a little bit. After all I still have Microsoft Ajax library available so I can put classes in namespaces or I can validate parameters type.

/// <Reference Name="MicrosoftAjax.js" />


Type.registerNamespace('Shp');


Shp.Lists = function () {
    throw 'Cannot instantiate Shp.Lists static class';
}


Shp.Lists.GetItems = function (listName, query, web, success, fail) {
    /// <summary>Get list items based on provided CAML query</summary>
    /// <param name="listName" type="String" optional="false" mayBeNull="false">List name</param>
    /// <param name="query" type="String" optional="false" mayBeNull="false">Query</param>
    /// <param name="web" type="SP.Web" optional="false" mayBeNull="true">Web</param>
    /// <param name="success" type="Function" optional="false" mayBeNull="false">Success callback</param>
    /// <param name="fail" type="Function" optional="true" mayBeNull="false">Fail callback</param>

    var e = Function.validateParameters(arguments, [{ name: 'listName', type: String, mayBeNull: false, optional: false },
                                                   { name: 'query', type: String, mayBeNull: false, optional: false },
                                                   { name: 'web', type: SP.Web, mayBeNull: true, optional: false },
                                                   { name: 'success', type: Function, mayBeNull: false, optional: false },
                                                   { name: 'fail', type: Function, mayBeNull: false, optional: true }], true);
    if (e) throw e;

    var fail = fail || function (error) { alert(error); };
    var ctx = (web === null) ? SP.ClientContext.get_current() : web.get_context();
    var web = (web === null) ? ctx.get_web() : web;


    Shp.Lists._GetItems(listName, query, ctx, web, success, fail);
}

Shp.Lists._GetItems = function (listName, query, ctx, web, success, fail) {

    var oList = web.get_lists().getByTitle(listName);
    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml(query);
    var oListItems = oList.getItems(camlQuery);
    ctx.load(oListItems);
    ctx.executeQueryAsync(function () {
        success(oListItems);
    }, function (sender, args) {
        fail(args.get_message());
    });
}

Shp.Lists.registerClass('Shp.Lists');

“Shp.Lists.GetItems” should be called with the following parameters:

  • List name, as string, not optional and cannot be null.
  • CAML query as string, not optional and cannot be null.
  • Web as SP.Web, not optional but can be null. In this case web associated with the current context is used.
  • Success as function, not optional and cannot be null. It is executed if operation is a success.
  • Fail function, optional and cannot be null. If not specified and operation fails, code will alert the error message.

Now as I created a reusable function for reading list items, everything should be much easier. I just need to call “Shp.Lists.GetItems” with correct parameters inside auto-complete source and, if operation is successfully, to add suggestions based on list items.

jQuery('#txtBox').autocomplete({
    minLength: 3,
    source: function (request, response) {
       
        var term = request.term;
        var query = '<View><Query><Where><Contains><FieldRef Name="Title" /><Value Type="Text">' + term + '</Value></Contains></Where></Query></View>';
        Shp.Lists.GetItems("list name", query, null, function (items) {
            var suggestions = [];
            var listItemEnumerator = items.getEnumerator();
            while (listItemEnumerator.moveNext()) {
                suggestions.push(listItemEnumerator.get_current().get_item('Title'));
            }
            // Add suggestions
            response(suggestions);

        });

    }
});

This was my approach of creating the auto-complete functionality. As I said, is a personal option to use JavaScript CSOM because looks for me more organized and structured. Of course code can be extended and you can even create an Ajax client side control to incorporate this functionality.

Thank you for reading my post!

Send list items to recycle bin with JavaScript CSOM

Tags

, , , ,

Delete operations examples provided by Microsoft include how to delete list items using JavaScript CSOM. And everything is fine, with a small exception. “deleteObject” operation is completely deleting items and you cannot restore them from recycle bin. But instead you can use “recycle” method of SP.ListItem object. My article will show how to sent multiple items to recycle bin using JavaScript CSOM.

As usual, I am using Microsoft Ajax to organize my code in namespaces and classes:

/// <Reference Name="MicrosoftAjax.js" />
/// <Reference Path="jquery.js" />
Type.registerNamespace('Shp');

Shp.Lists = function () {
    throw 'Cannot instantiate Shp.Lists static class';
}

Shp.Lists.registerClass('Shp.Lists');

I will have a static class called “Lists” inside “Shp” namespace and with some help from jQuery (I will need it for deferred object) I will add my methods to complete the operations. In my case method will be called “RecycleItems”.

/// <Reference Name="MicrosoftAjax.js" />
/// <Reference Path="jquery.js" />

Type.registerNamespace('Shp');


Shp.Lists = function () {
    throw 'Cannot instantiate Shp.Lists static class';
}

Shp.Lists.RecycleItems = function (listName, listItems, web) {
    ///	 <summary>Recycle items from the list</summary>
    ///  <param name="listName" type="String" optional="false" mayBeNull="false">List name</param>
    ///  <param name="listItems" type="Array" elementType="Number" elementInteger="true" elementMayBeNull="false" optional="false" mayBeNull="false">Array with string representation of list items id</param>
    ///  <param name="web" type="SP.Web" optional="true" mayBeNull="false">Web</param>
    ///  <returns type="jQuery.deffered" />
    var e = Function.validateParameters(arguments, [{ name: 'listName', type: String, optional: false, mayBeNull: false },
                                                   { name: 'listItems', type: Array, elementType: Number, elementInteger: true, elementMayBeNull: false, optional: false, mayBeNull: false },
                                                   { name: 'web', type: SP.Web, optional: true, mayBeNull: false }], true);
    if (e) throw e;

    // Depending if I provided web parameter, I build internal variable to call internal method
    var ctx = (typeof web === 'undefined' || web === null) ? SP.ClientContext.get_current() : web.get_context();
    var web = (typeof web === 'undefined' || web === null) ? ctx.get_web() : web;

    // Call and return internal method result, which is a jQuery.deffered
    return Shp.Lists._DefferedRecycleItems(listName, listItems, ctx, web);

}

Shp.Lists._DefferedRecycleItems = function (listName, listItems, ctx, web) {
    // Implementation will go here
}

Shp.Lists.registerClass('Shp.Lists');

My method is validating parameters types and called an internal method if parameters are in expected format. Last parameter, which is SP.Web, is optional. If you do not provide it, code will use website that is associated with the client context. And entire implementation will go into my internal method.

/// <Reference Name="MicrosoftAjax.js" />
/// <Reference Path="jquery.js" />

Type.registerNamespace('Shp');


Shp.Lists = function () {
    throw 'Cannot instantiate Shp.Lists static class';
}

Shp.Lists.RecycleItems = function (listName, listItems, web) {
    ///	 <summary>Recycle items from the list</summary>
    ///  <param name="listName" type="String" optional="false" mayBeNull="false">List name</param>
    ///  <param name="listItems" type="Array" elementType="Number" elementInteger="true" elementMayBeNull="false" optional="false" mayBeNull="false">Array with string representation of list items id</param>
    ///  <param name="web" type="SP.Web" optional="true" mayBeNull="false">Web</param>
    ///  <returns type="jQuery.deffered" />
    var e = Function.validateParameters(arguments, [{ name: 'listName', type: String, optional: false, mayBeNull: false },
                                                   { name: 'listItems', type: Array, elementType: Number, elementInteger: true, elementMayBeNull: false, optional: false, mayBeNull: false },
                                                   { name: 'web', type: SP.Web, optional: true, mayBeNull: false }], true);
    if (e) throw e;

    // Depending if I provided web parameter, I build internal variable to call internal method
    var ctx = (typeof web === 'undefined' || web === null) ? SP.ClientContext.get_current() : web.get_context();
    var web = (typeof web === 'undefined' || web === null) ? ctx.get_web() : web;

    // Call and return internal method result, which is a jQuery.deffered
    return Shp.Lists._DefferedRecycleItems(listName, listItems, ctx, web);

}

Shp.Lists._DefferedRecycleItems = function (listName, listItems, ctx, web) {

    var deferred = jQuery.Deferred();
    var oList = web.get_lists().getByTitle(listName);
    var recycledItems = [];

    // For each provided list item id we perform recycle method
    for (var i = 0; i < listItems.length; i++) {
        var oListItem = oList.getItemById(listItems[i]);
        var recycleItem = oListItem.recycle();
        recycledItems.push(recycleItem);
    }

    ctx.executeQueryAsync(function () {

        // In case of success, we refine the results into an array of GUID and use it with deferred.resolve
        Array.forEach(recycledItems, function (element, index, array) {
            array[index] = element['m_value']['_m_guidString$p$0'];
        }, null);

        deferred.resolve(recycledItems);

    }, function (sender, args) {

        // In case of fail, we use error message with deferred.reject
        deferred.reject(args.get_message());

    });

    return deferred.promise();

}

Shp.Lists.registerClass('Shp.Lists');

In my development tasks, code above provided an easy way to remove items from a list, being also capable to restore them. Without entering into details, I can show a basic example.

// Array containing ID of the list items to be sent to recycle bin
var deleteIds = ["1", "2", "3", "6"];

// Define delete operation
var deleteOps = Shp.Lists.RecycleItems('List Name', deleteIds);

// In case of fail we show error message
deleteOps.fail(function (error) {
    alert(error)
});

// In case of success, we show  identifier (GUID type) of the new recycle bin item
deleteOps.done(function (listItemsGuid) {
    for(var i = 0; i < listItemsGuid.length; i++)    {
        alert(listItemsGuid[i]);
    }
});

Hoping my code will help you, I will let you know continue your work. And thank you for ready my post. 🙂

Getting list items with C# CSOM in chunks

Tags

, , ,

I am not expert in C#, but because C# CSOM is more more used in client applications, I had to use it. Of course, the first operation a developer is performing with CSOM is to retrieve data. There is nothing complicated in this, but CSOM cannot retrieve thousands and thousands of items at once. Imagine you want to get a list with 50.000 items and you want to populate an Excel sheet with data you obtained. It is simply not possible. But you can write the code in a way to retrieve data in chunks and execute an operation for each successfully data retrieval request.

In my case I created a class called “SharePointOperation”.

    class SharePointOperation : IDisposable
    {
        private ClientContext oContext;
        private Web oWeb;

        public SharePointOperation(string webUrl)
        {
            oContext = new ClientContext(webUrl);
            oWeb = oContext.Web;
        }

        public void Dispose()
        {
            oContext.Dispose();
        }
    }

I pass the web URL to the constructor and upon class initialization context and web are created. I have implemented IDisposable interface to be able to use my instance in a “using” statement.

The method to get list items is a simple one.

        /// <summary>
        /// Get list items
        /// </summary>
        /// <param name="listName"></param>
        /// <param name="camlQuery"></param>
        /// <param name="callback"></param>
        public void GetListItems(string listName, string camlQuery, Action<ListItemCollection> callback)
        {
            ListItemCollectionPosition position = new ListItemCollectionPosition {PagingInfo = ""};
            ListItemCollection oItems;
            List oList = oWeb.Lists.GetByTitle(listName);
 
            do
            {
                CamlQuery oQuery = new CamlQuery { ViewXml = camlQuery };
                oQuery.ListItemCollectionPosition = position;
  
                oItems = oList.GetItems(oQuery);
                oContext.Load(oItems);
                oContext.ExecuteQuery();
                callback(oItems);
                position = oItems.ListItemCollectionPosition;
            } while (position != null);       
      
           
        }

My method accepts 3 parameters: list name, CAML query string and and action which accepts “ListItemCollection” object type as parameter. Every time I get a new chunk, my action is executed.


string caml = "<View><RowLimit>10</RowLimit><Query><Where><Eq><FieldRef Name='Title' /><Value Type='Text'>Title value</Value></Eq></Where>/Query></View>";

 using (SharePointOperation op = new SharePointOperation("https://url"))
{
                op.GetListItems("List name",caml, (ListItemCollection items) =>
                {
                   // Code not extended for  brevity
                    MessageBox.Show("Done");
                });
}

I don’t pretend this code is perfect, but it might give an idea about how to organize your code and make your life easier. I do not think you want to create complicated code each time you have to deal with large lists from C# CSOM. And to be sure my message is clear, see below entire code for my class.

using System;
using Microsoft.SharePoint.Client;

    class SharePointOperation : IDisposable
    {
        private ClientContext oContext;
        private Web oWeb;

        public SharePointOperation(string webUrl)
        {
            oContext = new ClientContext(webUrl);
            oWeb = oContext.Web;
        }

        /// <summary>
        /// Get list items
        /// </summary>
        /// <param name="listName"></param>
        /// <param name="camlQuery"></param>
        /// <param name="callback"></param>
        public void GetListItems(string listName, string camlQuery, Action<ListItemCollection> callback)
        {
            ListItemCollectionPosition position = new ListItemCollectionPosition {PagingInfo = ""};
            ListItemCollection oItems;
            List oList = oWeb.Lists.GetByTitle(listName);
 
            do
            {
                CamlQuery oQuery = new CamlQuery { ViewXml = camlQuery };
                oQuery.ListItemCollectionPosition = position;
  
                oItems = oList.GetItems(oQuery);
                oContext.Load(oItems);
                oContext.ExecuteQuery();
                callback(oItems);
                position = oItems.ListItemCollectionPosition;
            } while (position != null);
         
      
           
        }

        public void Dispose()
        {
            oContext.Dispose();
        }
    }

Adding a custom task in Excel add-in VSTO application

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 !!!