Building applications with Excel 2007 and C# isn’t always easy, 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 you’ll encounter when programming with C# in Excel 2007 or other Office products, you need to access the APIs differently compared to using VBA. So there’s a bit of a learning curve.
That being said, Microsoft does provide full support for C# in the Excel 2007 API, 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, need to use C# to programmatically interact with Excel (2007) or because it is their language of choice. This article will describe 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 examples will use the VSTO (Visual Studio Tools for Office 2007) project templates.
Sample Workbook Project
This example is a simple walkthrough to show you the basics of Microsoft C# programming with Excel 2007. When programming with VSTO 2007, you should view the Excel 2007 file as a Windows Forms client. You interact with this form as you would any other Windows Form in.Net.
If you haven’t already, you’ll need to download and install VSTO 2008. You’ll find the toolkit on MSDN. Installation is quick and easy. Once you have configured the VSTO, create a project in Visual Studio 2008:
->File
->New
->Project
You can also click Ctrl+Shift+N.
In the new Project Control Panel, expand the C# node and select the Office node. On the right hand 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 application, choose a location, and a Solution Name. Make sure “Create solution directory” 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 file format “xlsx” 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 spreadsheet 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 set up. For the examples in this article, you’ll add code to “ThisWorkbook_Startup”, which will be called when the application starts.
If you need to reference the top sheet (Sheet1) when the app starts, you would 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 the worksheets collection
Microsoft.Office.Interop.Excel.Sheets = (Microsoft.Office.Interop.Excel.Sheets)this.Worksheets;
Then you could get a handle to the worksheet in the collection:
Microsoft.Office.Interop.Excel.Worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(“Sheet2”);
To access a cell and enter a cell, you must 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 full code for both examples is provided below:
Example of 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 (eg 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
{
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 worksheets in the Workbook
Microsoft.Office.Interop.Excel.Sheets = (Microsoft.Office.Interop.Excel.Sheets)this.Worksheets;
//Get a specific sheet in the Workbook
Microsoft.Office.Interop.Excel.Worksheet = (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”);
}
conclusion
This information, while minimalist, is hard to come by, and we hope it will be helpful in your quest to develop an Excel application with VSTO in C#.