Excel Add-In Guide for Beginners
Hello% username%!
Recently, I had to write a small Excel add-in on duty. By the way, before that, my experience with MS Office from under .NET was reduced to simple utilities that generated reports in Excel using Office Primary Interop Assemblies. And when the office bought licenses for Aspose.NET, interaction with the office through COM was forgotten like a bad dream. So, starting to understand the topic, I was unpleasantly surprised by the lack of sane Russian-language tutorials for beginners. Now I want to fill this gap. As an example, we will create an add-in that, at the click of a button, generates a list of days of the current month on the current sheet and highlights the days off. Interested please ask for a cut!
I want to make a reservation in advance that I myself am not a professional in the field of development for MS Office, and there may be people who criticize my decision to the smithereens. Well, I will be glad to any constructive criticism. Also, this article is not an exhaustive guide to writing Excel Add-ins, does not set out to describe in detail the architecture of Excel Add-ins or the contents of the Microsoft.Office.Core namespace. This is an introductory article, which, I hope, will help a beginner to navigate a topic new to him and will guide you through the process of creating an add-in that does something meaningful. So let's get started!
Let's open the studio and create a new project like Excel 2010 Add-in. Let's call it SampleAddIn:
If everything turned out right (and what could have happened incorrectly here? ..), you will see a solution with the following structure:
The only file that the studio generated for us is ThisAddIn.cs:
Everything is simple here - we just register event handlers for the start and end of our add-in. Now, of course, our Add-in does nothing, but you can already start it as a normal project, by pressing F5. What is especially nice, at this stage you can not bother with deployment, because the studio itself launches Excel with the necessary security settings and palm off on it our Add-in. If everything worked out, then when you start Excel, you will see the following inscription:
Let's add an element of type Ribbon to the solution and call it SampleRibbon.
In the designer, add a button there:
And set the names for the tab, group and button:
And also create a button click handler:
Let's try to run our project. If you did everything correctly, then in Excel there is a new tab with a button, by clicking on which a message box appears.
At the moment, we are faced with two problems: firstly, if the ThisAddIn class has an Application field that gives access to the running Excel instance, then the Ribbon class has nothing of the kind, and therefore it is not clear how to interact with Excel worksheets. Secondly, as we all know, a mixture of display logic and business logic is an excellent preparation for a good portion of spaghetti in your code. Therefore, we will change the code in order to get rid of these two problems, and at the same time we will understand the “magic” that built our Ribbon into Excel without any action on our part.
The CreateRibbonExtensibilityObject method from the AddInBase class, from which our ThisAddIn class is actually inherited, is responsible for creating custom feeds in the Add-in. Thank God this method was prudently made virtual, so overloading it would not be a problem. First, add the code to the ThisAddIn class:
And run the project. Voila! The tab has disappeared. Now change the SampleRibbon class:
And the ThisAddIn class:
Run the project. If you did everything correctly, then the tab reappeared, and by pressing the button, as before, a message box is displayed.
Now the time has come for what all this fuss was actually started for - the generation of the table. First, create a couple of helper methods for decorating cells:
This method makes the cell border bold. By the way, pay attention to the argument type border - dynamic - starting with .NET 4.0 it is a very convenient way to work with COM objects! If suddenly you are not in the know, I recommend that you read, for example, this article. For our convenience, we’ll create a couple more methods:
Now we’ll write a code to get a list of days of the month:
And bring it all together:
If you haven’t made a mistake anywhere, then starting the project and clicking on the button on our tab, you will see something similar:
That's all, I hope, after reading this article you got the basic idea of creating Excel Add-ins for .NET. For further reading, I can recommend, of course, MSDN and this blog - it helped me a lot when studying this topic. Good luck to everyone and thank you for your attention!
Recently, I had to write a small Excel add-in on duty. By the way, before that, my experience with MS Office from under .NET was reduced to simple utilities that generated reports in Excel using Office Primary Interop Assemblies. And when the office bought licenses for Aspose.NET, interaction with the office through COM was forgotten like a bad dream. So, starting to understand the topic, I was unpleasantly surprised by the lack of sane Russian-language tutorials for beginners. Now I want to fill this gap. As an example, we will create an add-in that, at the click of a button, generates a list of days of the current month on the current sheet and highlights the days off. Interested please ask for a cut!
Instead of the foreword
I want to make a reservation in advance that I myself am not a professional in the field of development for MS Office, and there may be people who criticize my decision to the smithereens. Well, I will be glad to any constructive criticism. Also, this article is not an exhaustive guide to writing Excel Add-ins, does not set out to describe in detail the architecture of Excel Add-ins or the contents of the Microsoft.Office.Core namespace. This is an introductory article, which, I hope, will help a beginner to navigate a topic new to him and will guide you through the process of creating an add-in that does something meaningful. So let's get started!
We prepare solutions
Let's open the studio and create a new project like Excel 2010 Add-in. Let's call it SampleAddIn:
If everything turned out right (and what could have happened incorrectly here? ..), you will see a solution with the following structure:
The only file that the studio generated for us is ThisAddIn.cs:
public partial class ThisAddIn
{
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
}
private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
}
#region VSTO generated code
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
private void InternalStartup()
{
this.Startup += new System.EventHandler(ThisAddIn_Startup);
this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
}
#endregion
}
Everything is simple here - we just register event handlers for the start and end of our add-in. Now, of course, our Add-in does nothing, but you can already start it as a normal project, by pressing F5. What is especially nice, at this stage you can not bother with deployment, because the studio itself launches Excel with the necessary security settings and palm off on it our Add-in. If everything worked out, then when you start Excel, you will see the following inscription:
Draw an interface
Let's add an element of type Ribbon to the solution and call it SampleRibbon.
In the designer, add a button there:
And set the names for the tab, group and button:
And also create a button click handler:
public partial class SampleRibbon
{
private void SampleRibbon_Load(object sender, RibbonUIEventArgs e)
{
}
private void button1_Click(object sender, RibbonControlEventArgs e)
{
MessageBox.Show("Hello, world!");
}
}
Let's try to run our project. If you did everything correctly, then in Excel there is a new tab with a button, by clicking on which a message box appears.
Digging a little deeper
At the moment, we are faced with two problems: firstly, if the ThisAddIn class has an Application field that gives access to the running Excel instance, then the Ribbon class has nothing of the kind, and therefore it is not clear how to interact with Excel worksheets. Secondly, as we all know, a mixture of display logic and business logic is an excellent preparation for a good portion of spaghetti in your code. Therefore, we will change the code in order to get rid of these two problems, and at the same time we will understand the “magic” that built our Ribbon into Excel without any action on our part.
The CreateRibbonExtensibilityObject method from the AddInBase class, from which our ThisAddIn class is actually inherited, is responsible for creating custom feeds in the Add-in. Thank God this method was prudently made virtual, so overloading it would not be a problem. First, add the code to the ThisAddIn class:
protected override Microsoft.Office.Core.IRibbonExtensibility CreateRibbonExtensibilityObject()
{
return null;
}
And run the project. Voila! The tab has disappeared. Now change the SampleRibbon class:
public partial class SampleRibbon
{
public event Action ButtonClicked;
private void SampleRibbon_Load(object sender, RibbonUIEventArgs e)
{
}
private void button1_Click(object sender, RibbonControlEventArgs e)
{
if (ButtonClicked != null)
ButtonClicked();
}
}
And the ThisAddIn class:
protected override Microsoft.Office.Core.IRibbonExtensibility CreateRibbonExtensibilityObject()
{
var ribbon = new SampleRibbon();
ribbon.ButtonClicked += ribbon_ButtonClicked;
return Globals.Factory.GetRibbonFactory().CreateRibbonManager(new IRibbonExtension[] { ribbon });
}
private void ribbon_ButtonClicked()
{
MessageBox.Show("Hello, world!");
}
Run the project. If you did everything correctly, then the tab reappeared, and by pressing the button, as before, a message box is displayed.
We work with cells
Now the time has come for what all this fuss was actually started for - the generation of the table. First, create a couple of helper methods for decorating cells:
public static void MarkBold(dynamic border)
{
border.Weight = XlBorderWeight.xlMedium;
}
This method makes the cell border bold. By the way, pay attention to the argument type border - dynamic - starting with .NET 4.0 it is a very convenient way to work with COM objects! If suddenly you are not in the know, I recommend that you read, for example, this article. For our convenience, we’ll create a couple more methods:
public enum Border
{
Left = 1,
Right = 2,
Top = 3,
Bottom = 4
}
private void MarkCell(dynamic cell, DateTime day)
{
if (day.DayOfWeek == DayOfWeek.Saturday)
{
MarkBold(cell.Borders[Border.Left]);
MarkBold(cell.Borders[Border.Top]);
MarkBold(cell.Borders[Border.Bottom]);
}
if (day.DayOfWeek == DayOfWeek.Sunday)
{
MarkBold(cell.Borders[Border.Right]);
MarkBold(cell.Borders[Border.Top]);
MarkBold(cell.Borders[Border.Bottom]);
}
cell.Columns[1].ColumnWidth = 4;
}
Now we’ll write a code to get a list of days of the month:
private static IEnumerable GetDaysOfCurrentMonth()
{
var today = DateTime.Today;
var dayIter = new DateTime(today.Year, today.Month, 1);
while (dayIter.Month == today.Month)
{
yield return dayIter;
dayIter = dayIter.AddDays(1);
}
}
And bring it all together:
private void ribbon_ButtonClicked()
{
GenerateTable(Application.ActiveWorkbook.ActiveSheet, 1, 1);
}
private void GenerateTable(dynamic wSheet, int row, int column)
{
var currentMonth = GetDaysOfCurrentMonth().ToArray();
for (int i = 0; i < currentMonth.Length; i++)
{
wSheet.Cells[row, column + i] = currentMonth[i].Day;
MarkCell(wSheet.Cells[row, column + i], currentMonth[i]);
}
}
If you haven’t made a mistake anywhere, then starting the project and clicking on the button on our tab, you will see something similar:
Instead of a conclusion
That's all, I hope, after reading this article you got the basic idea of creating Excel Add-ins for .NET. For further reading, I can recommend, of course, MSDN and this blog - it helped me a lot when studying this topic. Good luck to everyone and thank you for your attention!