ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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

Updated on October 20, 2011

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#.

Comments

    0 of 8192 characters used
    Post Comment

    • klanguedoc profile imageAUTHOR

      Kevin Languedoc 

      6 years ago from Canada

      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

    • profile image

      the QB 

      6 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!

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)