How-To Develop Excel Interop Applications with C# | Visual Studio Tools for Office | VSTO

Creating applications with Excel 2007 and C# and the interop services is not always straightforward as many of us have found out. For example you cannot use a macro to write code for you as you would with VB.net (VBA). To add to the dilemma you will find when programming with C# in Excel 2007 or other Office products you must access the APIs differently compared to using VBA. So there is a bit of a learning curve.

That said, Microsoft does provide full support for C#, .net and the Microsoft Office 2007 API, of which Excel is a member through the Visual Studio 2008 Tools for Office 2007 (or 2003). Both are available as separate downloads from the MSDN site.

This article is intended as an entry point for developers who, like me, either need to use C# and the .net framework to programmatic-ally interface with Excel (2007) using interop or because it is their language of choice. This article will outline which references to include; how-to initialize a workbook; access a group of worksheets; or just one in particular; how-to access and change a cell. All the examples will use the VSTO (Visual Studio Tools for Office 2007) project templates.

Create a Excel 2007 or Excel 2003 Workbook Project
Create a Excel 2007 or Excel 2003 Workbook Project

Workbook Project Example

This example is a simple walk through to show you the basics of programming with Microsoft C# with Excel 2007. When programming with the Visual Studio Tools for Microsoft Office 2007 you have to view the Excel 2007 file as a Windows Forms client. You interact with this form as you would with any other Windows Form in .Net.

If you haven’t done so, you are going to need to download and install VSTO 2008. You will find the toolkit on MSDN. The installation is quick and easy. Once you have setup the VSTO, create a project in Visual Studio 2008:

-> File
-> New
-> Project

Alternatively Ctrl+Shift+N. From the New Project Dashboard, Expand the C# node and select the Office node. On the rigth side, you will have several Project Solution template to choose from. For this example, select the Excel 2007 Workbook template. In the appropriate fields, give your application, choose a location and a Solution Name. Make sure the “Create Directory for Solution” is checked.

In the next screen accept the defaults which to use a new workbook. You can also choose to use an existing workbook if you want to add some functionality to an existing application. Keep the default “xlsx” file format and click OK to finish creating the project.

Once the project is created you will have an Excel workbook instead of the usual Windows Form. In the Solution Explorer there will have a C# (cs) file for each Excel worksheet and the Workbook.cs file. This follows the same Excel template when creating a standard Excel Spreadsheet.

At the top of the open ThisWorkbook.cs are the various references that are included in the template. The VSTO Solution template should have added the following two references (see below) when the project was setup. However if it is not the case, you can copy and paste the code references into the code editor of the Workbook.cs file.

...
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;

Most of the code is created from the template when the project is setup. For the examples in this article you are going to add code to the “ThisWorkbook_Startup” which will be called when the application is launched.

If you need to reference the top sheet (Sheet1) when the Application is launched, you would use the following code:

Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)this.ActiveSheet;

Alternatively, if you needed to reference another worksheet when the application is launched, you would use first get the collection of the worksheets

Microsoft.Office.Interop.Excel.Sheets sheets = (Microsoft.Office.Interop.Excel.Sheets)this.Worksheets;

Then you could get a handle on the worksheet in the collection:

Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item("Sheet2");

To access a cell and enter a cell you need to access a Range. This can be one cell or a range of cells:

Microsoft.Office.Interop.Excel.Range afield = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range("A1",System.Reflection.Missing.Value);
afield.set_Value(System.Reflection.Missing.Value, "Hello World");

The complete code for both examples are provided below:

Example on How To Access the Active Sheet

namespace Test
{
public partial class ThisWorkbook
{
private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{
//Example 1 - To get the top sheet (e.g. Sheet1) or the Active Sheet use this syntax
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)this.ActiveSheet;

//To get a cell or group of cells, you can use the following syntax
Microsoft.Office.Interop.Excel.Range afield = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range("A1",System.Reflection.Missing.Value);

//Set the value of the A1 cell equal to "Hello World"
afield.set_Value(System.Reflection.Missing.Value, "Hello World");
}

Example Access a Particular Worksheet

namespace Test
{
public partial class ThisWorkbook
{
private void ThisWorkbook_Startup(object sender, System.EventArgs e)
{

//Example 2 - To get a specific sheet in a workbook
//Get a handle on all the worksheets in the Workbook
Microsoft.Office.Interop.Excel.Sheets sheets = (Microsoft.Office.Interop.Excel.Sheets)this.Worksheets;

//Get a specific sheet in the Workbook
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item("Sheet2");

//To get a cell or group of cells, you can use the following syntax
Microsoft.Office.Interop.Excel.Range afield = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range("A1",System.Reflection.Missing.Value);

//Set the value of the A1 cell equal to "Hello World"
afield.set_Value(System.Reflection.Missing.Value, "Hello World");
}

Conclusion


This bit of information, although minimalistic, is hard to come by and will hopefully be of great help in your quest to develop Excel application with the VSTO in C#.

More by this Author


Comments 2 comments

the QB 4 years ago

Klan, this is a great little tutorial for someone like me who is working on excel add-ins for the first time. Very helpful thanks!


klanguedoc profile image

klanguedoc 4 years ago from Canada Author

Thanks for the feedback. If you need help with something in particular, just let me know and I will be glad to help if I can.

Kevin

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working