Building applications with Excel 2007 and C # is not always straightforward, as many of us have discovered. For example, you can’t use a macro to write code like you would with VB.net (VBA). To add to the dilemma that you will encounter when programming with C # in Excel 2007 or other Office products, you have to access the APIs differently compared to using VBA. So there is a little learning curve.

That said, Microsoft provides full support for C # in the Excel 2007 API, through Visual Studio 2008 Tools for Office 2007 (or 2003). Both are available as separate downloads from the MSDN site.

This article is intended to be an entry point for developers who, like me, need to use C # to interact programmatically with Excel (2007) or because it is their preferred language. This article will describe what 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 samples will use VSTO (Visual Studio Tools for Office 2007) project templates.

Workbook project example

This example is a simple walkthrough to show you the basics of programming with Microsoft C # with Excel 2007. When programming with VSTO 2007, you should view the Excel 2007 file as a Windows Forms client. Interact with this form as you would any other Windows form in .Net.

If you have not already done so, you will need to download and install VSTO 2008. You can find the toolkit on MSDN. Installation is quick and easy. Once you’ve configured VSTO, create a project in Visual Studio 2008:

-> File
-> New
-> Project

Alternatively, click Ctrl + Shift + N.

From the New Project Panel, expand the C # node and select the Office node. On the right side, you will have several project solution templates to choose from. For this example, select the Excel 2007 workbook template. In the appropriate fields, enter your request, choose a location, and a Solution Name. Make sure the “Create directory for solution” option is checked.

On the next screen, accept the defaults 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 Solution Explorer, there will be 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 set up. However, if this 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 configured. For the examples in this article, you will add code to “ThisWorkbook_Startup”, which will be called when the application starts.

If you need to reference the top sheet (Sheet1) when the application starts, you should use the following code:

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

Alternatively, if you need to reference another worksheet when the app starts, you would first use Get Worksheet Collection

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

Then I could control the collection worksheet:

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. It can be a 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 is provided below:

Example of how to access the active sheet

namespace test
{
ThisWorkbook public partial class
{
private void ThisWorkbook_Startup (sender of the object, System.EventArgs e)
{
// Example 1: to get the top sheet (for example 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 cell A1 equal to “Hello world”
afield.set_Value (System.Reflection.Missing.Value, “Hello world”);
}

Example Access a particular worksheet

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

// Example 2: get a specific sheet in a workbook
// Check all 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 cell A1 equal to “Hello world”
afield.set_Value (System.Reflection.Missing.Value, “Hello world”);
}

Conclution

This information, while minimalist, is hard to come by and will hopefully help you in your quest to develop an Excel application with VSTO in C #.