Microsoft Office Excel is the most frequently-used Microsoft application and one of the leading spreadsheet programs available. A spreadsheet program is a program that uses a huge grid to display data in rows and columns. The spreadsheet program can then be used to do calculation, data manipulation, and various similar tasks, against this data.

Programming in Excel

Microsoft Office Excel is one of the powerful Office tools that provides uncomplicated data management. It is loaded with features such as graphing, charting, pivot tables, and calculation. Even though Excel is loaded with numerous features for users, there may be some functionality that cannot be achieved by using the standard Microsoft Excel environment. In Microsoft Office Excel 2007, automation is a great mechanism for populating business documents (for example, reports) with data from backend system. This can be achieved by using VSTO 3.0 and Visual Studio 2008. Microsoft Office Excel 2007 is more programmable than ever before with support for Visual Studio Tools for Office.

VSTO is aimed at Microsoft Office Excel users who want to develop Excel-based applications for other Microsoft Office Excel users. VSTO 3.0 is shipped with all of the newly-enhanced Excel objects, including improved features for building Microsoft Office based solutions.

VSTO 3.0 is loaded with new features, including support for Windows form controls from within Microsoft Office tools customization. It provides the support for using .NET frameworks, objects and classes for Microsoft Office tools customization. For example, System.Data is the .NET frameworks object that can be used inside the Excel solution to process database operations. This new feature is tightly integrated with the Visual Studio 2008 IDE and gives you the comfort of design time customization of Excel documents for data display and UI customization.

Similar to other Microsoft Office Tools, with Microsoft Office Excel 2007 customization using VSTO 3.0, you have two levels of customization— document-level customization and application-level customization. Document-level customization is a solution created for document-level programming and is specific to the document that is part of that particular solution. Application-level customization is a solution created for application-level programming and is specific to the application, and therefore common to all documents based on that application.

In a simple Hello World demonstration, let's learn about the document level customization approach. We'll step through a simple task, showing how to create an Excel document that will display a Hello World message on startup.

Hello World example using Visual Studio 2008

1. Open Visual Studio 2008, and create a new Excel 2007 Workbook project.
2. Select New Project. Under Office select 2007, and then select the Excel 2007 Workbook template and name the project ExcelHelloWorld, as shown in the following image:

   

3. The document selection dialog box is displayed. At this point, you need to choose the template for your design. In this example, you select a new blank template and click on the OK button. Refer to the following screenshot:

   

   The solution will be created with all of the supporting files required for our development of an Excel solution. Each solution is created with three worksheets, with default names: Sheet1, Sheet2, and Sheet3 for the workbook you're going to customize , as shown in the following image. The number of sheets in a new workbook depends on the settings in Excel. The following image also shows you the Excel-supported controls, placed on the leftmost side of the Visual Studio 2008 toolbox panel. You can also see the visual representation of Excel 2007 inside Visual Studio 2008.

   

4. Let's write our Hello World message in a cell when we load the Excel 2007 document. Write the following code inside the ThisWorkbook.cs file.

   // The Startup event of workbook in our Excel solution
   // Startup event common to all Office application
   // Startup event will be fired at the start of the application
   private void ThisWorkbook_Startup(object sender,
   System.EventArgs e)
   {
   // Creating the instance of active WorkSheet of Excel Document
   Excel.Worksheet AuthorWorkSheet = ThisApplication.ActiveSheet
   as Excel.Worksheet;
   // Get the range using number index through Excel cells
   by setting AuthorExchange to an Excel range object starting at
   (1,1) and ending at (1,1)
   Excel.Range AuthorExcelRange = ThisApplication.
   get_Range(AuthorWorkSheet.Cells[1, 1],
   AuthorWorkSheet.Cells[1, 1]);
   // Setting the value in the cell
   AuthorExcelRange.Value2 = "Hello! this is my VSTO program for
   Excel 2007";
   }
   Copy

   The following screenshot results after adding and executing the preceding code:

   

Manipulation

Microsoft Office Excel is one of the most comprehensive data management tools for all kinds of users. It is a tool that can be easily understood and quickly learnt. The most important feature of Microsoft Office Excel is its capability to manipulate data from different sources.

Excel is one of the most powerful and user-friendly data manipulation applications. You could use Excel to predict what's ahead for your business by creating detailed financial forecasts. This powerful application has pivot table functionality that allows you to drop in your data and rearrange it to answer all kinds of business data analysis type questions. Excel helps you to build various useful analytical tools such as your monthly sales report and product sales growth analysis more easily and flexibly. Excel offers you formulae and functions that will help you to perform complex financial calculations without any manual errors. Excel can provide you with a graphical presentation of your business data by using charts and graphs.

Want to know your growth levels for a specific product sales range? Check which parts of your business are performing worse? The pivot table provides more information from your business in depth.

Every application in this computer world works with data. The data can be in any form and can belong to different sources. The key question for data management is where to place the data. You manage the data in two ways: data managed outside the program and data managed inside the program. The data managed outside the program includes data managed in a database, a file system, and so on. Data managed inside the program includes data in different worksheets within the workbook, embedded images, and so on.

Data manipulation

For users who are not satisfied with the default features in Microsoft Office Excel, VSTO programming makes Excel more flexible, and provides a development tool for the creation of custom solutions for data manipulation and data analysis.

Custom programming using VSTO 3.0 improves most part of the Microsoft Office Excel solution. Custom programming using VSTO 3.0 provides a wide range of advantages, including saving time by automating most of the frequently-performed tasks, reducing errors due to manual operations, as well as enforcing standards for data processing, and building the capability to seamlessly integrate with other applications seamlessly.

Reading worksheet cells

There are many ways to manipulate data and write to the cells in an Excel worksheet. Let's see some of these ways. We can read worksheet cells directly through the Cells property of the sheets, rows, and columns, and can set a value directly by using the cell's row and column index. Open Visual Studio 2008 and create a new Excel solution. Refer to the previous example for full instructions of how to do this. Write the following code inside the ThisWorkbook.cs file. In this sample explanation, you are writing data into the worksheet by using the Cells object.

// The Startup event of workbook in our Excel solution
private void ThisWorkbook_Startup(object sender,
System.EventArgs e)
{
// Set value for Cells row and column index
// Text data in Sheet1 cells
Globals.Sheet1.Cells[3, 3] = "Set my data";
}
Copy

We can also read the worksheet and write data to the cells by using the Range object. In this case, you are creating the range and setting the text data for the range in the Excel worksheet.

Open Visual Studio 2008 and create a new solution, as before. Write the following code inside the ThisWorkbook.cs file . In this demonstration, you read the worksheet through the range of cells and set the value by reading through cell ranges.

private void ThisWorkbook_Startup(object sender,
System.EventArgs e)
{
// Setting value in ExcelSheet cells through reading range object
Excel.Range AuthorExcelSheetRange = Globals.Sheet1.Range["A2",
"B2"];
// Text data for the range A2 to B2
AuthorExcelSheetRange.Value2 = "Set my data";
}
Copy

Let's see a demonstration of how to read data from an external data file and display this inside our Excel cells. In this demonstration, you will see how the data from the text (.txt) file is displayed in the spreadsheet.

Opening a text file as a workbook using VSTO

We'll now see how to open the text file as a workbook by using VSTO and C# programming. This saves time and makes the user more comfortable in accessing the text file while processing the data. Open Visual Studio 2008 and create a new solution, as before. Write the following code inside the ThisWorkbook.cs file:

// Opening Text file as workbook
private void ThisWorkbook_Startup(object sender,
System.EventArgs e)
{
// In the workbook objects, file path as parameter in Opentext
property this.Application.Workbooks.OpenText(@"C:TechBooks.txt",
// Value 1 is, the row from which it will read data in the text
file missing, 1,
// Checks for delimits for text parsing
Excel.XlTextParsingType.xlDelimited,
// Text Enumeration value
Excel.XlTextQualifier.xlTextQualifierNone,
missing, missing, missing, true, missing, missing, missing,
missing, missing, missing, missing, missing, missing);
}
Copy

Connecting with Microsoft SQL Server 2008 database

Microsoft SQL Server 2008 is a relational database management system developed by Microsoft Corporation. Microsoft SQL Server is used to manage a huge volume of data along with relation and Metadata information for this data. VSTO provides support for manipulating the data from your database inside Excel using ADO.NET classes.

The preceding figure demonstrates how an Excel 2007 object is used to interact with the Microsoft SQL Server database. Let's see how to connect with a relational database management system, retrieve data from the database, and finally display it in our Excel spreadsheet. This demonstration shows you how to retrieve data from a Microsoft SQL Server 2008 database and place the retrieved data into the worksheet cells.

Open Visual Studio 2008 and create a new solution, as usual. Write the following code inside the ThisWorkbook.cs file.

// Namespace for SQL Server connection
using System.Data.SqlClient;
// Startup event of the workbook
private void ThisWorkbook_Startup(object sender,
System.EventArgs e)
{
// Opening SQL connection for Microsoft SQL Server 2008
// WINNER the database server contains the databse called Products
SqlConnection MySQLConnection = new SqlConnection(@"Data
Source=WINNER;Initial Catalog=Products;
Integrated Security=True");
// Passing SQL command text
SqlCommand MySQLCommand = new SqlCommand("SELECT * FROM
Books", MySQLConnection);
MySQLConnection.Open();
// SQL reader to read through data from Database
SqlDataReader MySQLReader = MySQLCommand.ExecuteReader();
// Get the active sheet of current application
Excel.Worksheet MyWorkSheet = this.Application.ActiveSheet as
Excel.Worksheet;
// Header for the columns set in the Value2 properties
((Excel.Range)MyWorkSheet.Cells[1, 1]).Value2 = "Book Name";
((Excel.Range)MyWorkSheet.Cells[1, 2]).Value2 = "Author Name";
// Indexer
int i = 2;
// Loop to read through the database returned data
while (MySQLReader.Read())
{
// Writing the data from the database table column BookName
((Excel.Range)MyWorkSheet.Cells[i, 1]).Value2 =
MySQLReader["BookName"];
// Writing the data from the database table column Author
((Excel.Range)MyWorkSheet.Cells[i, 2]).Value2 =
MySQLReader["Author"];
i++;
}
// Dispose the SQL command
MySQLCommand.Dispose();
// Closing SQL connection after using it.
MySQLConnection.Close();
}
Copy

The following screenshot displays data retrieved from Microsoft SQL Server 2008 database and the data being displayed in the worksheet cells.

In this demonstration, you learned how to connect with a Microsoft SQL Server 2008 database in order to get data and populate it in a workbook. This is just one of the ways of manipulating data outside of a workbook.