Office components such as Excel or Word, have a powerful GUI, with many options to automate everyday tasks. In addition, there are a lot of excellent Add-Ins such as Power BI. With such functionality and the ability to use VBA, you may ask yourself why we need Office extensions.

Of course, there are many situations in which you might like to extend Office functionality. For example, to create a routine that:

• exports data from SQL Server into Excel with custom formatting
• imports data from Excel into the dataset
• automatizes Pivot Table creation in Excel
• customizes writing documents in Word
• customizes writing e-mail messages in Outlook
• more…

Microsoft provides a mechanism to accomplish all of these tasks. Office developer tools in Visual Studio let you create .NET Framework applications that extend Office. These applications are also named Office solutions. This article talks about how to develop and distribute such solutions.

In order to start developing you have to set up the environment.

Configure a Computer for Developing Office Solutions

The Office Developer Tools are installed by default as part of Visual Studio.

If you customize the Visual Studio installation by selecting which features to install, make sure that Microsoft Office Developer Tools is selected during setup. Alternatively, you can start Visual Studio Installer and select the Office/SharePoint development workload, as shown in the image below.

In addition, you have to Install a version of Office that is supported by the Office developer tools in Visual Studio. Examples in the article are tested with Office 2013/2016. Make sure that you also install the PIAs (Primary Interop Assemblies) for the version of Office that you install. The PIAs are installed with Office by default. If you modify the Office setup, make sure that the .NET Programmability Support feature is selected for the applications you want to target.

To check the installation, start Visual Studio, create a new project and be sure that Office templates are present as shown in the image below.

Choose an Office project type

Office projects are divided into Office and COM Add-ins. The first one usually is marked as ‘new development’, uses WEB templates and JavaScript, requires Office 2016, and targets mainly Office 365. In my opinion, both are worth exploring. This article covers the second one, which is usually divided based on the template you choose. There are Document-level customizations and VSTO Add-ins. VSTO Add-ins are associated with the application itself and Document-level customizations, as the name implies, are associated with a particular document.

Besides that, there is the possibility to include the Office object model in your application by referencing PIA assemblies. The PIA enables the managed code in your solution to interact with the Office application’s COM-based object model. That would be the first example in which we will browsing source code in a Console application. If you would like to create your own startup project, choose C# console application from the list of available templates. After that you can use Visual Studio productivity tool Power Commands to copy references as shown in the image below and paste them in your project.

Exporting Data into Excel

Download this solution and then open it in Visual Studio. Make sure that ExportToExcel is selected as the startup project. The purpose of the first demo is to introduce the Excel object model by accomplishing one common task, exporting data from SQL Server tables into Excel. In order to follow the demo, you have to change the application configuration file app.config. In the file enter your instance name where you have restored the AdventureWorks2016 database.

The most important objects in Excel are:

• Application
• Workbook
• Worksheet
• Range

Microsoft documentation states: “The Application object represents the Excel application. The Application object exposes a great deal of information about the running application, the options applied to that instance, and the current user objects open within the instance.”

In order to instantiate an application object, you have to include the Microsoft.Office.Interop.Excel namespace. An application object is created by using the following code snippet.

If you add a breakpoint on a line after an application object is created (see the image below) and press F5 to start debugging the project, you will notice that nothing changes. The application is started as hidden because the Visible property is set to false.

In order to prove that the Excel application has started, start Task Manager, and find the Excel application on the list, as shown in the image below.

The Workbook object represents a single workbook within the Excel application. The following snippet is used to create the object:

The Worksheet object is a member of the Worksheets collection. Many of the properties, methods, and events of the Worksheet are identical or similar to members provided by the Application or Workbook objects. Excel provides a Sheets collection as a property of a Workbook object. Each member of the Sheets collection is either a Worksheet or a Chart object. The following snippet is used to create the Worksheet.

A range object represents a cell, a row, a column, or any kind of selection that contains at least one cell. In order to create Range object, you have to use a snippet like this one

The purpose of the first example is to take T-SQL query results as an object of type DataSet and transform that object into an Excel workbook. The T-SQL query, as well as the connection string, are found in the application configuration file app.config. The example uses two tables of the AdventureWorks2016 database, both located in the schema Sales: The SalesOrderHeader table which contains 31K rows and SalesOrderDetail which contains 121K rows. On my machine, the Excel file is created in 25 sec. In order to shorten the example, I reduced number of records in both tables to 1000 by applying SELECT TOP in the app.config file.

Generating an object of the DataSet type is quite a common thing, and I will skip describing this part. In the project, the source code to accomplish that task is located in DataAccess.cs.

After getting an object of the DataSet type, Excel Workbook creation is invoked. This is accomplished in the Export method that is part of static class ExportToExcel. The workbook Is exported to C:\TMP\FirstTest.xlsx. The workbook looks similar to that shown in the image below.

Every table that is part of the DataSet collection becomes an Excel sheet. The first and the second rows contain some information about the data. The third row contains the column names as well as something called ‘auto-filter’ which I will explain in detail later in the article.

When working with the object of type Range, you may find yourself confused by the Range property names that sound very similar, like Value, Value2, and Text.

Besides that, there is a big difference in performance. Text and Value are much slower than Value2. Value2 represents the cells underlying value, unlike Text which gives you the string representation of the cell. Value differs from Value2 when the cell is formatted as date or currency.

In the Export method, there is a parameter named pasteRange used to test performance. Its default value is true which means that the two-dimensional object based on a DataTable is set as the value of the Excel sheet range.

You can change the method call by setting pasteRange to false as shown in the snippet below.

When the value false is passed, it iterates through all columns and rows, setting the values by each cell. It’s is slow and should not be used.

Before setting the range’s property Value2, some formatting is made. This is accomplished by processing the Columns collection of the DataTable object and setting the property to NumberFormat.

DateTime columns are formatted as shown in the snippet below

So, it depends on the CurrentCulture, and on my machine looks similar to the image below:

You can also format numbers. For columns that are System.Decimal type, formatting is accomplished like the snippet below.

On my machine, it looks similar to the image shown below:

One feature that I love when working with Excel is to put columns into the auto-filter mode. That means that all discrete values for one particular column are bound to the combo box that represents the column name.

This is accomplished by referencing the row in which the column names are and by calling the AutoFilter method as shown in the snippet below.

Finally, to conclude the first example, a sentence about disposing of objects. The proper way to dispose objects is to call Marchal.RelaseComObject. This method is used to explicitly control the lifetime of a COM object used from managed code. You should use this method to free the underlying COM object that holds references to resources in a timely manner or when objects must be freed in a specific order.

COM disposing is a very huge and painful topic and one article is not enough to cover it. Talking about COM disposal is out of scope of the article.

Close the console application if still running and remove the first two rows in the Excel workbook created in the example. This is preparation for the second example.

Importing Data from Excel

The source code for this example is located in the ImportFromExcel project. Set up this project as the Startup Project. This example opens the Excel file generated in the first example, but it can be a different Excel file as well. After opening the file, the code tries to determine how many sheets are in the workbook and the name of every sheet.

This example needs a driver installed, microsoft.ace.oledb.12.0. The driver could be downloaded from the link.

After that, by using the OLEDB provider, every sheet is queried and, as a result, an object of type DataSet is formed. When you have a DataSet object, it is easy to update the database or do whatever else you need to do.

The source code that creates an object of type DataSet is displayed in the snippet below.

It is important that the sheet name is put in parenthesis and that dollar sign is appended at the end. For example, you could query the first sheet by issuing select * from [Sales_SalesOrderDetail$];. As a result, the console window displays the number of records for each table.

Now, is time to make a real Add-in for Excel and that is the third example.

Creating an Excel VSTO Add-In

The third example extends Excel functionality by adding a custom tab with two buttons on the Excel Ribbon, as shown in the image below.

Set SimpleTalkExcelAddIn as the Startup project in the solution. This project has the type of Excel VSTO Add-In. When you create such a project for the first time, a class named ThisAddIn is created and two events are raised. Usually, the generated code looks similar to what is shown in the image below.

The Startup event is raised after the VSTO Add-in is loaded and all the initialization code in the assembly has been run. The event is handled by the ThisAddInStartup method in the generated code file named ThisAddin.cs. The code in the ThisAddInStartup event handler is the first user code to run unless your VSTO Add-in overrides the RequestComAddInAutomationService method. In this case, the ThisAddInStartup event handler is called after RequestComAddInAutomationService.

Similar to the Startup event, there is a ShutDown event. The event is raised when the application domain that your code is loaded in is about to be unloaded. Usually, it means when you close the Excel application.

Accessing the application, workbook and worksheet objects is accomplished slightly different from earlier examples, as shown in the code below.

You can interact with the Excel application by adding two types of Ribbon as shown in the image below.

In the example, the Ribbon of type Visual Designer is already added and named UsingRibbon. In design mode, the Ribbon looks similar to the image below.

The ribbon control has its own set of properties and events. The control looks similar to any tabbed control and exposes a tabs collection.

When working with Excel VSTO Add-in’s, on the Visual Studio Toolbar there is a special tab in Visual Studio Toolbox. You can access the toolbox, called Office Ribbon Controls, through the View->Toolbox menu. It’s shown in the image below.

In the example, a few controls are already added. Ribbon Tab is a part of Ribbon Control collection. In this example it’s labeled as Simple Talk. After the Label property, the most commonly used property is Position. The property is the type of RibbonPosition which is an interface that holds a string called OfficeId and an enumerator with three possible values:

• Default
• BeforeOfficeId
• AfterOfficeId

If you choose something other than Default, you have to specify the OfficeId as a string value. Usually, this ID is formed from the word ‘Tab’ plus the ‘Label’ shown on Excel main ribbon. For example, if you would like to position the tab before the ‘Insert’ tab, put ‘TabInsert’ as the OfficeId property. Here is an example from this ribbon.

 

The Group control is a classic container that could hold another control. It also exposes the Position property. In this example, every group control has a button on it. The Button control responds to only one event – the click event. The most important code in this example is in buttons event handlers.

However, before writing anything about what the handler does, I have to make a digression. If you create a brand new Excel VSTO Add-In using Visual Studio template, your first build will fail. Visual Studio will report to you that you have to sign the ClickOnce manifest.

In the example, signing is already accomplished and in the next few sentences, I will explain how.

The project is signed by using the Personal Information Exchange (.pfx) file, although it’s possible to select a certificate from the Store. In order to create a .pfx file, first, you have to create a certificate. This is accomplished by using the Windows SDK utility MakeCert.exe as shown in the snippet below.

After that, another Windows SDK tool named Pvk2Pfx is used. Pvk2Pfx (Pvk2Pfx.exe) is a command-line tool that copies public key and private key information contained in .spc, .cer, and .pvk files to a Personal Information Exchange file. The file is created by issuing following command

After the file is created, on the project property Signing tab, choose Select from File and select the newly created pfx file. Your build will work correctly. So, time to go back to the main topic.

The event handlers for both buttons in the example produce the same result. The result is an Excel workbook. The workbook contains three sheets. The first one a contains data that represents the result of the T-SQL query, the second one is a Pivot table based on data in the first sheet. And the last sheet contains a chart that represents Pivot on the second sheet.

The difference between these two handlers is that the first one shows a form in which the user can specify a connection to SQL Server, T-SQL query, some Pivot options. The form looks similar to that shown in the image below. On the first tab named Connection, you have to specify the instance name and select a database from the list box. This example uses the AdventureWorks2016 database.

On the second tab named Query, you have to specify a T-SQL statement. This example, queries the Sales.SalesHeader table. On the third tab named Fields, you have to specify some Pivot options, like Columns, Rows, Values and so on, as shown in the image below.

Several values are already added to the list boxes, as you see in the image above. Using drag & drop, it is possible to move fields between list boxes similar to Excel. Every table or every query result is not suitable to be pivoted. Furthermore, there is a restriction on the Columns list box. For example, when you try to put SalesOrderID in the Columns list box in Excel, you will receive an Excel message like the one shown in the image below.

Finally, on the Options tab, there are possibilities to define Chart Types, Table Styles and Pivot styles as shown in the image below.

The chart type is an enumerator named xlChartType defined in Microsoft.Office.Core. During loading of the custom user form, the enumerator is set as a data source of the form’s combo box labeled Chart Types, so the user can initially set up the type of chart to see.

 

Excel exposes 21 ‘Light’ styles, 28 ‘Medium’ styles, and 11 ‘Dark’ styles by default, and you can define your own styles for the table and pivot. When the user has filled everything on the form, and clicks Execute in the current Excel workbook, three sheets are added.

In the first one, the result of the T-SQL query, is displayed very similarly to in the first example, as shown in the image below.

The only difference is that the result is formatted as a table rather than range. This is accomplished by calling function FormatAsTable that is implemented in the project’s General.cs class. The function is implemented as shown in the snippet below.

The benefits of formatting ‘as table’ are that column names are frozen in the first row with applied auto-filtering, and there are extra functionalities such as adding Total row by clicking on checkbox, as shown in the image below.

The second sheet contains a pivot table with a slicer object.

You can manipulate fields, applied a filter, and so on. One cool thing is a slicer object, introduced with Excel 2010, which makes it easy to apply a filter.

 

The pivot table is added by calling the AddPivot function defined in the General.cs static class. The signature of the function looks similar to that shown in the image below.

The AddPivot function uses the PivotTableWizard function which is part of the _Worksheet interface defined in the Microsoft.Office.Interop.Excel namespace. The function call is accomplished as in the snippet below.

 

Finally, on the third tab, there is a pivot-chart, which is basically the pivot table represented as a chart.

The pivot-chart is added to the workbook by using a wrapper method AddChart defined in the static class General. The most important part of this method is calling the ChartWizard method, which is part of the Chart interface defined in Microsoft.Office.Interop.Excel namespace.

In the introduction of the section I mentioned that there are two buttons on the ribbon form. Both do the same thing. The difference is that information needed to accomplish the task in the second event handler is read from the application configuration file app.config shown in the image below.

Reading from app.config is quite common, and it is accomplished by using ConfigurationManager defined in System.Configuration as shown in the image below.

 

Changing the values in app.config before and after publishing the result is a little bit different. In order to explain how to achieve this, I have to explain how to publish the result of the project VSTO.

Publishing the project result could be accomplished by using ClickOnce or by creating a Setup project. On the project properties, there is a tab named Publish as shown in the image below.

In addition to specifying the publish and installation folders, you can also specify Prerequisites, Updates, and Options.

 

When you click on the Prerequisites button, you see a set of options similar to the image below.

When you click on the Update button, you can see a set of options for updates as shown in the image below.

Depending on the options you choose, and after choosing the Publish command from the project context menu, your publishing/installation folder will look to that shown in the image below.

When you browse Application Files, you will notice that the files are separated by the version number. One common thing that you will need to do is to change configuration values, e.g. to change the connection string. This could be done, but after that, you should update the manifest file. On the image below configuration file and manifest file are highlighted.

 

When you explore the manifest file by using Visual Studio, you will notice that the file is basically an XML file. Part of that file is a description of the configuration file, as shown in the image below.

The result files are protected by hashing. In order to change some values in the configuration file, you have to determine a new hash value. This might be accomplished by using ComputeHashGUI that is part of the article solution. If you set the project as a startup, you will see a simple GUI as shown in the image below.

Usage is easy, first, you have to pick a file and that will be the configuration file. After that, the hash value is calculated by using the hash algorithm selected in the combo box. That hash value is then pasted in the manifest file. And there are a just a few more steps.

 

First, you have to copy the pfx file to the installation folder as well as the Windows SDK utility called mage.exe. Then you have to sign the manifest file located in the last installation folder by executing

After that, you have to update the application VSTO file by executing the following command

As a final step, you have to copy the VSTO file to the last installation folder, in this example SimpleTalExcelAddin_1_0_0_0. Of course, all these steps could be automated, but it is out of the scope of the article.

In the solution, there are two other projects. One to define a Word add-in by using the XML Ribbon control, and the other by using Outlook Add-in.

Set TestWordAddIn as the Startup project in Visual Studio. That is the next example.

Working with Word Add-ins

Unlike the previous example in which a Ribbon Designer Control is used, in this section, I will take another approach by utilizing Ribbon XML. So, what is the difference? The Ribbon Designer does not incorporate all facets provided by the OfficeFluentUI model. For example, the Ribbon Designer provides no way to re-purpose built-in commands or work with the BackStage. These things can only be done using Ribbon XML.

When you add a Ribbon (XML) item to a VSTO project from the Add New Item dialog box, Visual Studio automatically adds

• A Ribbon XML file. This file is used to defined UI elements
• A Ribbon code file. This file contains the Ribbon class.

In the solution, these two files are named as MyRibbon.xml and MyRibbon.cs.

If you open the MyRibbon.cs file, you will notice that, unlike the previous example in which a Ribbon Designer Control is used, here the class implements an interface called IRibbonExtensibility. The interface is defined as shown in the image below. The interface contains only one method named GetCustomUI. (NOTE : The image is NOT from the project code, it is generated(displayed) with a help of R#-Resharper).

The interface implementation is in the code file and looks similar shown in the following snippet

The content of the XML file, returned by GetCustomUI, is used to construct a GUI representation of the ribbon control. You can respond to user actions, such as clicking a button on the ribbon, by creating callback methods. Callback methods resemble events in Windows Forms controls, but they are identified by an attribute in the XML of the UI element and displayed surrounded with red in the image below. You might define an image for a button, as shown highlighted in yellow.

Before exploring the Word Add-in’s functionality, take a look at the Word object model. Similar to Excel, at the top of the object hierarchy, is the Application object. It represents a current instance of Word.

Next in the hierarchy is the Document object, which is basically the most frequently used object. Application objects have a collection named Documents of currently open or created documents.

The Selection object represents the area that is currently selected. When you perform an operation in the Word user interface, such as bolding text, you select, or highlight the text and then apply the formatting.

The Range object represents a contiguous area in a document and is defined by a starting character position and an ending character position. It is not visible, unlike Selection, which is always visible.

Now it is time to start exploring the Add-in functionality. By pressing F5, Word will launch. On the Add-ins tab, there are three buttons, as shown in the image below.

The first button, labeled with Calculate, calculates the number of characters (including hidden) in the current document.

The second button, with the Insert label, inserts a table at the current cursor position. This is accomplished by adding a new table in the ActivDocument.Tables collection, as shown in the snippet below.

The third button performs spell checking and reports the result by showing a MessageBox dialog. Most of the things that I write about Excel VSTO could be applied to Word VSTO or Outlook VSTO as well.

Now it is time to review the last example. In order to do that set OutLookAddIn as the startup project.

Working with Outlook Add-ins

Most things we are talking about Office VSTO could be applied to Outlook VSTO’s as well. It means that signing the project, then working with ThisAddIn class is almost the same as with Excel or Word VSTO’s.

However, unlike Word or Excel VSTO’s, Outlook VSTO does not raise a ShutDown event. When you create a new Outlook VSTO project, the generated code tells you exactly that.

There is another difference, unlike Excel or Word VSTO’s which allows you to have running instances when you try to debug an OutLook VSTO by running the Outlook client, you receive a message like shown in the image below

At the top of Outlook’s object model hierarchy is the Application object. It represents the Outlook application. The application object has two collection properties, Explorers, and Inspectors. Part of the Explorers collection is the object of type Explorer.

The Explorer object represents a window that displays the contents of a folder that contains items such as e-mail messages, tasks, or appointments. The Inspector object represents a window that displays a single item such as an e-mail message, task, or appointment.

In this example, the Inspector object is used. In the VSTO StartUp event-handler, the following snippet is added in order to respond to the creation of the new e-mail message.

When you create a new task, meeting, contact or e-mail or view existing once, the event is fired. In the event, handler following code is added.

If the item is a newly created e-mail, then a text is added to e-mail subject and e-mail body.

Finally few words about cleaning up the environment. By choosing File->Options from the main menu, and choosing Add-ins from tabbed menu, you can get the form like shown in the image below.

If you would like to disable an Add-in, just uncheck checkbox that is with Add-ins name. If you would like to remove the Add-in, choose Remove button.

Summary

Browsing Microsoft documentation about Office solutions is a tedious task. There are no pictures and a lot of mistakes. My aim was in writing this article is to provide a set of practical examples that could be easy to follow. The Office solution is a huge topic. In the article, I cover a small part, but I hope quite enough to encourage readers to start exploring the topic.

The solution source code can be found on GitHub here.