Click here to Skip to main content
15,868,164 members
Articles / Programming Languages / C#

Implementing a Custom Action Pane and Custom Ribbon for Excel 2010 using VSTO and C#

Rate me:
Please Sign up or sign in to vote.
4.78/5 (5 votes)
16 Dec 2013CPOL8 min read 60.2K   2.3K   17   7
This article shows how to implement custom action pane and custom ribbon for Excel 2010 using Visual Studio Tools for Office (VSTO) using C#.

Implementing Custom Pane and Ribbon

Background

Since I work in software development for the financial sector, I need to interact with a lot of accountants. You probably know that lots of accountants/finance coordinators love to use Microsoft Excel. So recently I developed a small utility for Excel 2010 using Microsoft Visual Studio Tools for Office (VSTO) using C#. This utility enables users to retrieve the Chart of Accounts dimension details and displays them in an Excel sheet from an OLAP database. Also, it provides users the ability to export this data into XML and CSV format so that it can be used with other applications. In this utility I implemented a custom action pane and custom ribbon for user interaction. I thought this and some of the features I implemented in this utility can also be useful to other users and hence this article.

Introduction 

The purpose of this article is to demonstrate how to implement a custom action pane and custom ribbon for Excel 2010 using Visual Studio Tools For Office (VSTO) using C#. In addition I will be exploring some of the key features of the .NET Framework such as Entity Framework, and C# language features such as LINQ and Lambda Expressions. Both these features make the interaction with the database very easy and elegant. Also this converts an Excel workbook into a powerful application with a nice GUI interface. I have also used here a simple lightweight logging class to show the application of the well known Singleton design pattern. The scenario taken for this article is about a company whose sales department wants to monitor and track their customer's orders and product details for their re-order level. It also enables them to export orders for any particular customer both in CSV and XML formats. The demo application uses the Northwind database which is downloadable from the CodePlex website of Microsoft whose data model fits this application. I have used Visual Studio 2013 Professional for the sample project. I will cover the following key points in this article:  

  • Implementing a custom Action Pane. 
  • Implementing a custom Ribbon using Visual Designer. 
  • Entity Framework 
  • LINQ and Lambda Expressions 
  • Singleton Design Pattern

Design Overview

The Application Model is built using Entity Framework. Logging is implemented using a simple Logger class implemented as a singleton. The basic class diagram of the application is shown in Figure 1.

Class Diagram of Custom Pane and Ribbon

Figure 1: Basic Class Diagram of Demo Application

From figure 1, the CustomerPane class is the GUI class implementing the custom action pane. It derives from the UserControl class. This class is responsible for populating Customer details in a dropdown for user selection. It is also responsible for populating the order details of the selected customer to the workbook and also for exporting these details either in CSV or XML format.

CustomRibbon is the class implementing the Custom Ribbon and is derived from the RibbonBase class. The visual designer in Visual Studio 2013 provides a rich set toolbox which you can use to drag and drop the different controls on the ribbon's design surface. You can group the controls and select all kinds of controls such as button, toggle button, radio or checkbox buttons, and dropdowns. For this example, I am using a simple button to show the product details and the toggle button to show and hide the customer action pane.

Business Layer

BONorthWindFacade is a class which acts as a facade between the different classes in the GUI layer and other entities. Most of the entities are automatically created by Entity Framework. The details of these are covered in a later section. The facade class provides a uniform interface to all the GUI classes and abstracts the Business/Data Layer. This will enable us to modify in future the implementation of the business and data layers without impacting the GUI classes.

Data Layer

The Data layer is implemented using Entity Framework 5.0 and has a key class NorthWindEntities and is derived from the DBContext class This class provides access to other entities and we can query the data from key tables using LINQ. Figure 2 shows the key entities and their relationships.

Entity Relationship Diagram

Figure 2: Entity Relationship Diagram

Implementing the Customer Action Pane

From Visual Studio Solution Explorer, right click on the project and select Add New Item. This will show a dialog and you can select the action pane control. Refer figure 3.

Action Pane Control

Figure 3: Add Action Pane Control

I have added this control with the name CustomerPane. The UI for this control is shown in figure 4.

Action Pane Control

Figure 4: Customer Pane UI

The CustomerPane class has the following key responsibilities:

  • Populate the Customers dropdown list.
  • Event handler to populate the workbook with order details for the selected customer.
  • Event handler to export the order details to XML or CSV format.

The implementation details of the key methods are shown below:

C#
// Below is private field in ThisWorkbook class
private CustomerPane cpane = new CustomerPane();

// This is start up method of ThisWorbook class
// See how the instance of customer pane control is added to ActionPane's controls collection.
private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
    //Initialises the custom customer pane.
        this.ActionsPane.Controls.Add(cpane);
}

// Private field and methods in CustomerPane class
BONorthwindFacade _bs = new BONorthwindFacade();
       
private void InitDropdowns() {

    try
    {
        List<customer> customerList = _bs.GetCustomers();
        drpCustomer.DataSource = customerList;
        drpCustomer.DisplayMember = "CompanyName";
        drpExportFormat.DataSource = new string[] { "CSV", "XML" };
        drpExportFormat.SelectedIndex = 0;
        //Initialise_Customers(customerList);
    }
    catch (Exception ex)
    {
        string message = "Error occured while populating " + 
          "the schedule dropdown and error is " + ex.ToString();
        Logger.Log.Error(message);
    }
}

The Thisworkbook class in its startup event handler adds the instance of CustomerPane to the ActionPane's Controls collection. The CustomerPane class creates an instance of the BONorthwindFacade class. The InitDropdowns() method populates the customer dropdown list by invoking the GetCustomers() method of the facade class. The format dropdown is populated by a string array. Notice the Logger class' Error() method for logginh exceptions. I will cover the implementation of this class in a later section.

C#
private void AddOrdersToWorkbook(List<Order> orders,string customerID) {
    try
    {

        Logger.Log.Information(string.Format(
          "Adding Order of the customer {0} to the workbook", customerID));

        Excel1.Worksheet ws = null;
        foreach(Excel1.Worksheet ws1 in Globals.ThisWorkbook.Worksheets)
        {
            if(ws1.Name.Equals(customerID,StringComparison.InvariantCultureIgnoreCase))
            {
                ws = ws1;
                break;
            }
        }
        if (ws == null)
        {
            ws = Globals.ThisWorkbook.Worksheets.Add();
            ws.Name = customerID;
        }

        Excel1.Range range =ws.Range["A1"];

        range.Value2 = "Order ID";
        range.Offset[0, 1].Value2 = "Order Date";
        range.Offset[0, 2].Value2 = "Required Date";
        range.Offset[0, 3].Value2 = "Shipping Address";
        range.Offset[0, 4].Value2 = "Shipping City";
        range.Offset[0, 5].Value2 = "Shipping Country";
        range.Offset[0, 6].Value2 = "Shipping PostCode";
        range.Offset[0, 7].Value2 = "Shipped Date";
        range.Offset[0, 8].Value2 = "Order Amount";
        
        int rowIndex = 1;
        int colIndex = 0;

       foreach (Order od in orders)
       {
            range.Offset[rowIndex, colIndex].Value2 = od.OrderID;
            range.Offset[rowIndex, colIndex + 1].Value2 = od.OrderDate;
            range.Offset[rowIndex, colIndex + 1].NumberFormat = @"dd/mm/yyyy;@";
            range.Offset[rowIndex, colIndex + 2].Value2 = od.RequiredDate;
            range.Offset[rowIndex, colIndex + 2].NumberFormat = @"dd/mm/yyyy;@";
         
            range.Offset[rowIndex, colIndex + 3].Value2 = od.ShipAddress;
            range.Offset[rowIndex, colIndex + 4].Value2 = od.ShipCity;
            range.Offset[rowIndex, colIndex + 5].Value2 = od.ShipCountry;
            range.Offset[rowIndex, colIndex + 6].Value2 = od.ShipPostalCode;
            range.Offset[rowIndex, colIndex + 7].Value2 = od.ShippedDate;
            range.Offset[rowIndex, colIndex + 7].NumberFormat = @"dd/mm/yyyy;@";
         
            range.Offset[rowIndex, colIndex + 8].Value2 = 
              od.Order_Details.Sum(odet => odet.Quantity * odet.UnitPrice);
            range.Offset[rowIndex, colIndex + 8].NumberFormat = "$#,##0.00";
         
            rowIndex++;
        }
        range =ws.Range["A1:L1"];
        range.Font.Bold=true;
        range = ws.Range["A1:L1"];
        range.EntireColumn.AutoFit();
        Logger.Log.Information(string.Format(
          "Orders for the Customer {0} adding to the workbook completed!", customerID));
    }
    catch (Exception ex) { 
    
        string message = "Error occured while populating orders " + 
          "to the workbook and error is " + ex.ToString();
        Logger.Log.Error(message);
    }
}

The above code creates a new worksheet and adds to the workbook if it does not exist, else updates the same worksheet. Most of the code above is self explanatory. But see the use of lambda expressions to compute the order amount.

Both the Export methods are shown below.

C#
private void SaveOrdersAsCSVFile(List<Order< orders, string customerID)
{
    string filePath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + 
      FILE_PATH + customerID + "_Orders.csv";
    using (StreamWriter sw = new StreamWriter(filePath, false))
    {
        string header = "'Order ID','Order Date','Required Date'," + 
          "'Order Amount','Shipped Date', 'Address','City','Country','Zip'";
        sw.WriteLine(header);
        orders.ForEach(od=>sw.WriteLine(string.Format(
          "'{0}','{1}','{2}','{3:C}','{4}','{5}','{6}','{7}','{8}'", 
          od.OrderID,od.OrderDate,od.RequiredDate,
          od.Order_Details.Sum(odet=>odet.Quantity*odet.UnitPrice),
          od.ShippedDate,od.ShipAddress,od.ShipCity,od.ShipCountry,od.ShipPostalCode)));
      
    }
}

private void SaveOrderAsXMLFile(List<Order> orders, string customerID)
{
    try
    { 
        string filePath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + 
          FILE_PATH + customerID + "_Orders.xml";
        XElement orderXML=
           new XElement("Orders",
               new XAttribute("CustomerID",customerID),
            from o in orders
            select 
              new XElement("Order",
                new XAttribute("OrderID",o.OrderID),
                new XElement("OrderDate",o.OrderDate),
                new XElement("RequiredDate",o.RequiredDate),
                new XElement("Amount",string.Format("{0:C}",
                     o.Order_Details.Sum(odet=>odet.UnitPrice*odet.Quantity))),
                new XElement("ShippingDetails",
                        new XElement("Address",o.ShipAddress),
                        new XElement("City",o.ShipCity),
                        new XElement("Country",o.ShipCountry),
                        new XElement("PostCode",o.ShipPostalCode)
                    )

              )
              );
        
        orderXML.Save(filePath);
    }catch(Exception ex){

        string message = "Error occured while saving the orders in xml format and error is " + ex.ToString();
        Logger.Log.Error(message);

    }
}

From the above code, please note the use of lambda expressions to write to the CSV file. Also see how the XML is generated using LINQ query syntax, and a new version of the XElement class. Also see the use of nested constructors of the XElement and XAttribute classes. LINQ is used to build all the nested order elements. This is a very compact and simplified version to generate XML compared to old traditional methods in earlier versions of .NET framework.

Implementing Custom Ribbon

From Visual Studio Solution Explorer, you can add a Ribbon control (Visual Designer). Figure 5 shows the CustomRibbon control. It contains simple button and toggle button controls. You can add any control from the Ribbon Toolbox using drag and drop. Also, you can set the properties of these controls to display as large or small buttons. Also, you can specify Office Image IDs provided by the Microsoft Office Image Gallery. I have included the resource for that in the code download.

Action Pane Control

Figure 5: Customer Ribbon UI

The CustomRibbon control class implements two tasks:

  1. Display Product List in a Dialog window.
  2. Handler to Show or Hide the CustomerPane action pane.
C#
//This is method in Thisworkbook class
// And is invoked via show/hide handler by passing the checked status.
// of toggle button
public void ShowHide_ActionPane(bool flag)
{
    this.Application.DisplayDocumentActionTaskPane = flag;
    cpane.Visible = flag;

}

private void btnProduct_Click(object sender, RibbonControlEventArgs e)
{
    BONorthwindFacade bs = new BONorthwindFacade();
    List<ProductEntity> productList = bs.GetProducts();
    frmProducts form = new frmProducts(productList);
    form.ShowDialog();
}

The code above is self explanatory. The frmProducts is a WinForm and populates the ProductEntity collection and displays in the DataGridView code. Please refer to the code for a detailed implementation.

BONorthWindFacade Implementation

This class provides a uniform interface between the data layer and other GUI classes. Please see the code below for the key method implementation details.

C#
class BONorthwindFacade
{
    private NORTHWNDEntities _context = new NORTHWNDEntities();
    
    public BONorthwindFacade() {
       
    }

    /// <summary>
    /// Method:GetCustomers
    /// Purpose:Returns collection of Customers from NorthWind database
    /// </summary>
    /// <returns></returns>
    public List<Customer> GetCustomers() {

        var customers = from c in _context.Customers
                        select c;
        return customers.ToList<Customer>();
    }

    /// <summary>
    /// Method:GetOrders
    /// Purpose:Returns orders for the customer selected.
    /// </summary>
    /// <param name="customerID"></param>
    /// <returns></returns>
    public List<Order>GetOrders(string customerID)
    { 
        var orders= from  od in _context.Orders
                    where od.CustomerID.Equals(customerID)
                    select od;
        return orders.ToList<Order>();
    }

    /// <summary>
    /// Method:GetProducts
    /// Purpose:Returns products from NorthWind database
    /// and projects as collection of Custom class ProductEntity.
    /// </summary>
    /// <returns></returns>
    public List<ProductEntity> GetProducts()
    {
        var products =from p in _context.Products
                      select new ProductEntity {
                      
                        ProductID =p.ProductID,
                        ProductName=p.ProductName,
                        ProductCategory =p.Category.CategoryName,
                        QuantityPerUnit=p.QuantityPerUnit,
                        ReorderLevel=p.ReorderLevel,
                        UnitPrice=p.UnitPrice
                        
                      };
        return products.ToList<ProductEntity>();
    }
}

The key methods implemented in the above code are:

  • GetCustomers() to retrieve the list of customers.
  • GetOrders() for a particular customer.
  • GetProducts() to retrieve the products with its category.

In the GetProducts() method I am projecting the product details as a collection of custom ProductEntity classes.

Data Layer Implementation

The Data Layer is implemented using Entity Framework. You can add the Entity Framework Data Model edmx file using Visual Studio. You can they define the connectionstring and define the Table or View from the underlying database. I have selected only key entities as shown in figure 2 above. NorthwindEntities and other entity classes are auto generated. Please refer to the source code for full details. Also modify the connection string from the App.Config file.

XML
<connectionStrings>
    <add name="NORTHWNDEntities" 
      connectionString="metadata=res://*/Models.NorthWindEntities.csdl|
        res://*/Models.NorthWindEntities.ssdl|res://*/Models.NorthWindEntities.msl;
        provider=System.Data.SqlClient;provider connection string="data source=servername;initial 
        catalog=NORTHWND;user id=northwind;password=pw here; 
      MultipleActiveResultSets=True;App=EntityFramework"" 
      providerName="System.Data.EntityClient" />
</connectionStrings>

Implementation of the Logger class

The purpose of this class is just to demonstrate the application of the Singleton design pattern. You can use the Logging Application Block provided by Enterprise Library available for download on Microsoft's Patterns and Practices Website.

C#
namespace DemoCustomActionPaneAndRibbon.Utilities
{
    public enum LogLevel
    { 
        DEBUG =1,
        INFORMATION,
        WARNING,
        ERROR,
        FATAL
       
    }
    // Logger class implemented as a Singleton.
    public class Logger
    {
        private static readonly Logger _instance = new Logger();
        private string _logFilePath = string.Empty;
        private const long MAX_FILESIZE = 5000 * 1000;
        private const string CATEGORY = "DemoCustomActionPaneAndRibbon";
        private Logger() {

            InitLogFilePath();
            this.CurrentLogLevel = LogLevel.DEBUG;
            this.IsEnabled = true;
        }

        public LogLevel CurrentLogLevel { get; set; }
        public bool IsEnabled { get; set; }
        private void InitLogFilePath()
        {
            string filePath = Environment.GetFolderPath(
              Environment.SpecialFolder.MyDocuments)+ @"\NorthWind\Logs\";
            filePath += string.Format("NorthWind_log_{0}_{1}_{2}_{3}_{4}.csv", 
              DateTime.Now.Day, DateTime.Now.Month, DateTime.Now.Year, 
              DateTime.Now.Hour, DateTime.Now.Minute);
            _logFilePath = filePath;
        }

        public static Logger Log
        {
            get {
                return _instance;
            }
        }

        public void Debug(string message, string category = CATEGORY) {

            if ((this.CurrentLogLevel <= LogLevel.DEBUG) &&( this.IsEnabled))
            {

                SaveToLogFile(category, message,LogLevel.DEBUG);
            }

        }
        public void Warn(string message, string category = CATEGORY) {

            if ((this.CurrentLogLevel <= LogLevel.WARNING) &&( this.IsEnabled))
            {

                SaveToLogFile(category, message,LogLevel.WARNING);
            }
        }

        public void Information(string message, string category = CATEGORY) {

            if ((this.CurrentLogLevel <= LogLevel.INFORMATION) &&( this.IsEnabled))
            {

                SaveToLogFile(category, message,LogLevel.INFORMATION);
            }
        }

        public void Error(string message, string category = CATEGORY) {

            if ((this.CurrentLogLevel <= LogLevel.ERROR) &&( this.IsEnabled))
            {

                SaveToLogFile(category, message,LogLevel.ERROR);
            }
        }

        public void Fatal(string message, string category = CATEGORY) {

            if ((this.CurrentLogLevel <= LogLevel.FATAL) &&( this.IsEnabled))
            {

                SaveToLogFile(category, message,LogLevel.FATAL);
            }
        }

        private void SaveToLogFile(string category, string message, LogLevel curLevel)
        {
            try
            {
                if (_logFilePath.Length > MAX_FILESIZE)
                {
                    InitLogFilePath();
                }
                using (StreamWriter sw = new StreamWriter(_logFilePath,true))
                {
                    sw.WriteLine(string.Format("{0}-{1}\t{2}\t{3}\t{4}", 
                      DateTime.Now.ToShortDateString(), 
                      DateTime.Now.ToShortTimeString(),category, curLevel, message));
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
    }
}

The constructor of the Logger class is private so that it can't be instantiated by other classes. It has a static readonly field to which is assigned the  instance of the Logger class. This instance is returned as a static Log property. By default, LogLevel is set to log DEBUG level. So it can log all types of  messages. It creates the log file in the logs folder under the MyDocuments folder. Most users are not an administrator on their desktop and hence writing to the Mydocuments folder is a better approach to avoid permission issues.

Points of Interest

You can use suitable images for different buttons used in a custom ribbon from Office ImageIDs. You can download the add-in for this image gallery from here. You can also choose custom image icons if you don't find suitable icons from this gallery.

Conclusion

Using Microsoft Visual Studio Tools for Office (VSTO), we can now develop and build powerful Office solutions using C# or VB.NET or any .NET compliant language. You can leverage the powerful features of the latest .NET Framework and language features. I hope the example application in this article provides you an idea of how to convert an Excel Workbook into a powerful application. Also, the code is compiled and you don't need to write macros using VBA. But I found the record macro feature of the Excel very useful when I need to implement a particular Excel feature in C#. So I can then view the code generated by the macro and then port it to C#. Please let me know your comments and suggestions. You can also e-mail me for any queries or clarifications about the implementation. Thank you.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
United Kingdom United Kingdom
I am Solution Architect with 20+ years of IT experience in the field of real time,embedded,client/server and web based applications and Business Intelligence . I am currently working as Senior Consultant for Infor.

Comments and Discussions

 
Questionribbon not shown Pin
umesh akbari25-Mar-15 22:04
umesh akbari25-Mar-15 22:04 
QuestionNorthwind database Pin
Alvyn13-Feb-15 3:26
Alvyn13-Feb-15 3:26 
AnswerRe: Northwind database Pin
umesh akbari25-Mar-15 22:10
umesh akbari25-Mar-15 22:10 
Questionthank you so much...it's really helpful :) Pin
Member 1058478826-Jan-15 22:58
Member 1058478826-Jan-15 22:58 
Generalwonderful ! Pin
colde fire9-Dec-14 18:16
colde fire9-Dec-14 18:16 
QuestionNice example. Pin
Duke Carey6-Feb-14 2:27
professionalDuke Carey6-Feb-14 2:27 
AnswerRe: Nice example. Pin
Gokuldas6-Feb-14 7:29
Gokuldas6-Feb-14 7:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.