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.
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.
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.
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.
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:
private CustomerPane cpane = new CustomerPane();
private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
this.ActionsPane.Controls.Add(cpane);
}
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;
}
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.
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.
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.
Figure 5: Customer Ribbon UI
The CustomRibbon control class implements two tasks:
- Display Product List in a Dialog window.
- Handler to Show or Hide the
CustomerPane action pane.
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.
class BONorthwindFacade
{
private NORTHWNDEntities _context = new NORTHWNDEntities();
public BONorthwindFacade() {
}
public List<Customer> GetCustomers() {
var customers = from c in _context.Customers
select c;
return customers.ToList<Customer>();
}
public List<Order>GetOrders(string customerID)
{
var orders= from od in _context.Orders
where od.CustomerID.Equals(customerID)
select od;
return orders.ToList<Order>();
}
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.
<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.
namespace DemoCustomActionPaneAndRibbon.Utilities
{
public enum LogLevel
{
DEBUG =1,
INFORMATION,
WARNING,
ERROR,
FATAL
}
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.
