web 2.0

Excel resources using VSTO 2.0

Technologies used
- .NET
- MS-Excel 2003

MS-Excel has been, without a doubt, the best spreadsheet in the market for years. All the available resources within Excel are key in areas such as controlling, finance, accounting, logistics, etc. The integration of Excel with a database has become a functionality which is crucial to manipulate data in a worksheet (Front-End) where the data source can be SQL Server, Access or any other data source available. 

With .NET, the developers’ market has entered a new world of developing languages and developing tools (VS.NET 2003). For those who use Excel, programming in VBA (Visual Basic for Application) will continue to be a valid choice.

If we put the development power of .NET together with Excel what we get is VSTO (Visual Studio Tools for the Microsoft Office System) which is a tool that you need to install separately in VS.NET 2003 (currently on version 1.0)

Nevertheless, in this article I will show you a bit of what can be done with Excel and VSTO 2.0. Version 2.0 can only be installed using VS.NET 2005 and it is worth noting that these are beta versions, at least up to when this article was last updated (Sept/2005). If you have worked with VSTO 1.0, believe me, version 2.0 is way better and easier to develop with.

To get started, open VS.NET 2005 and create a new project with the following characteristics:

Project Types: Visual C# / Office
Templates: Excel Application
Name: ExcelMSDN
Solution Name: ExcelMSDN

Refer to pictures below:

Picture 1: New Project window

Choose the solution as shown in the picture above. Next, click on OK to define the document name. We can either define a new document or copy an existing document. For this article, we will create a new document:

Picture 2: Select a document window

Save your application as follows:

Picture 3: Save Project window

Click on the Save button to save your new solution. You should note that the UI (User Interface) is actually Excel’s UI (which includes toolbars, sheets, etc) within VS.NET 2005:

Picture 4: VSTO 2.0 with Excel's UI

Now, under the Solution Explorer you should add a UserControl to the project as shown in the picture below:

Picture 5: Add New Item window

You now also add the following controls to the project-level control you’ve just added:

Picture 6: Design view of UserControl1

In the Toolbox, drag a bindingSource control to the document and configure the DataSource Property accordingly. At this stage there is no data source; thus, it is necessary that we add one to the project. Click on the Add Project Data Source link and configure the data source. I use the Northwind database as the data source for the project; therefore, you should use the same database and choose the tables Categories and Products.

See picture below:

Picture 7: Adding a data source to the Project

In same instances it is possible to define the Master Details. Go back to the worksheet and drag the controls from the Toolbox onto the active sheet (Sheet1) as shown in the next picture. Note that the controls are embedded in the worksheet (look at the formula bar). Now, you should configure the properties of the controls according to the information in the bindingSource control.

Remember that you do not need to write any code to bind the data to the controls. All you need to do is to configure the controls’ properties.


Picture 8: Adding controls to Sheet1

We are now ready to add a few lines of code to our project. Change ThisWorkbook view to code mode. By adding a few lines of code we can show the UserControl1 in Excel’s Task Pane (referred to within VS.NET as ActionsPane):

private void ThisWorkbook_Startup(object sender, System.EventArgs e)
            this.ActionsPane.ForeColor = Color.White;
            this.ActionsPane.BackColor = Color.Tomato;
            this.ActionsPane.Controls.Add(new UserControl1());

In order to navigate the Internet using the webBrowser control that we added to Sheet1, we will add a bid of code to the “GO” button:

void btnGo_Click(object sender, EventArgs e)

Finally, save your project and press (F5) to execute it. Note that the UserControl1 is shown in the Task Pane (ActionsPane) with the data from the Northwind database. On the worksheet there is also a GridView control that shows the same data shown in the Task Pane. The same goes for the listboxes on the worksheet and Task Pane.

Another new feature is the WebControl which allows you to browse the web within and Excel worksheet:

Picture 9: Up and running project

Excel using VSTO 2.0 will not be the same. All the easy-to-use tools within .NET given to solution developers are reusable within VSTO. What needs to be had in mind is that Excel will become a “Smart Client”, though it has always been one. Thus, it won’t be with VSTO that it will cease being one. It is worth your while to delve deeper into the tools available in the VSTO+VS.NET world and what they will give you.

I wish you good luck on your studies and remember: No Stress, think .NET + VSTO!


Tags: ,

Microsoft Excel | VSTO - Excel