Code Focused

Automate Microsoft Excel with Visual Studio 2010

Enhancements in the .NET Framework 4 and Visual Studio 2010 make Office automation solutions easier than ever to write and deploy. Here's an Excel automation scenario that reflects solutions that I've seen requested by multiple clients.

• By Joe Kunk
• 06/20/2011

In this article I present an Excel automation scenario that is closely representative of solutions requested by multiple clients. My experience is that there is a strong demand for Excel automation solutions in the finance departments across a wide variety of industries.

Chinook and Northwind Have Merged
Our scenario is that the Chinook company has purchased the Northwind company and the Chinook CFO has hired you to provide automation to create consolidated financial statements. You must provide a solution that allows the Controller to simply pick a financial reporting period and click a button to produce a consolidated financial statement for that period. The statement must list invoices by date, show the Sales to Date for the customer alongside the order total, and highlight the largest sale order for the period in bold font. The Chinook invoice detail must be filled on a new worksheet tab for verification. A sample completed consolidated spreadsheet is shown in Figure 1.

[Click on image for larger view.]

Figure 1. Sample Consolidated Financial Sheet

Your research has shown that adding a custom ribbon tab to Excel will meet the client's requirements. The custom tab will be named "Accounting" and will contain a drop-down list of available financial periods (months) for the selected company or companies as shown in Figure 2. The Period drop-down lists the financial periods available based on which company is checked, as an example of dynamic data interaction based on Ribbon controls status. Once the desired period is selected, the user clicks the Load Data button and the result is a completed consolidated spreadsheet as shown in Figure 1 above.

[Click on image for larger view.]

Figure 2. Custom Accounting Tab in Excel 2010

Database Note: Both sample databases are implemented as SQL Compact 3.5 databases to permit the databases to install with the demo application. The Chinook database is a sample database available for SQL Server and other databases at http://chinookdatabase.codeplex.com/. The Northwind database is taken from the SQL Server Compact 3.5 installation at C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5\Samples. The Northwind Order dates were adjusted to correspond to the same years as Chinook Invoice dates to provide accounting periods with activity from both companies. LINQ to SQL is used to access both databases.

Creating the Excel Solution
Visual Studio 2010 offers a number of Office templates as shown in Figure 3. Choosing the Excel 2010 Workbook template opens a blank Excel workbook template that is the spreadsheet shown when the solution is run. This is a document level customization; the Accounting tab will appear only in this particular template when opened in Excel. Selecting this template offers the choice to create a new document or add to an existing document as shown in Figure 4. Select the option to create a new document.

[Click on image for larger view.]

Figure 3. Available Office 2010 Visual Studio Templates

[Click on image for larger view.]

Figure 4. Option to add customizations to a new or existing Excel document

To provide a spreadsheet with proper formatting that can be filled in by our customization, the blank template is modified as shown in Figure 5. The yellow column A contains keyword markers that identify which rows receive particular data. Column A is hidden after the data is filled on the template. This provides a positive visual cue to the user that the process has completed.

[Click on image for larger view.]

Figure 5. Excel template to be filled by the customization

Adding a new item to a Workbook project offers the templates shown in Figure 6. Choosing the Ribbon Designer produces a blank Ribbon tab. Figure 7 shows the blank Ribbon and controls available to be used on the Ribbon. The customized Ribbon is shown in Figure 2 above.

[Click on image for larger view.]

Figure 6. Office Templates for Excel 2010 Workbook Solutions

[Click on image for larger view.]

Figure 7. The blank Ribbon control to provide a user interface for Excel Workbook customization.

The solution shown in Figure 8 consists of the Data.Chinook project to provide needed methods to query the Chinook sample database, the Data.Northwind project provides the needed methods to query the Northwind database, the Data.Shared provides a few common functions. The Data projects contain some interesting LINQ-to-SQL queries but are otherwise outside the discussion of this article.

[Click on image for larger view.]

Figure 8. Consolidated Reporting Solution.

Almost all of our custom Ribbon code is contained in Ribbon.vb and Utility.vb of the Excel2010_Workbook_VB project. This logic performed when the "Load Data" button is clicked is:

• Write the Accounting period to column C of row that contains "Period" in column A.
• Write the company name with the top sales for the period to column C of row that contains "TopSales" in Column A.
• Write the total sales for the period for Chinook to column C of row that contains "ChinookSales" in column A.
• Write the total sales for the period for Northwind to column C of row that contains "NorthwindSales" in column A.
• Write the combined sales for the period to column C of row that contains "CombinedSales" in column A.
• For each Chinook invoice or Northwind order for the period.
• If the first row then write values into the row that contains "FormatRow" in column A.
• If the last row then write values into the row that contains "DataRow" in column A.
• Otherwise insert a new row at "DataRow" and copy the formatting from the row below and then write the values into the newly inserted row.
• Change the formatting to bold for all columns for the row that contained the highest invoice or order amount. This must be done AFTER the rows are written otherwise the newly inserted rows will carry this bold formatting.
• Hide Column A.
• Create a new worksheet and place the detail Chinook transactions for the period in the new worksheet. A new worksheet tab could also be created for Northwind order detail if desired.

The conditional processing in the loop above is required because Excel copies the formatting of the prior row when a row in inserted. In order for the detail line formatting to be maintained and for the Totals line ranges to be maintained as rows are inserted, there must be two rows between the heading and the Total line.

Listing 1 contains the user-interface code described above. Listing 2 contains almost all the code that interacts with the Excel object model.

• AddChinookInvoicesForPeriod -- Adds the Chinook invoice detail to a blank worksheet.
• AddWorksheet -- Adds a new worksheet with specified name either before or after the current worksheet.
• CopyRowActiveSheet -- Copies a row in the active worksheet to the specified row.
• FindFirstOccuranceRow -- Find the row that contains the specified text in the specified column.
• GetActiveWorksheetDimensions -- Returns the row and column dimensions of the active worksheet based on data present in the worksheet.
• HideColumn -- Hides the specified column. Do this only after all data has been written to it.
• InsertRowActiveSheet -- Insert a blank row in the active spreadsheet at the specified row.
• SetRowBold -- Formats all columns of the specified row with the Bold font attribute.
• WorkSheetIndex -- Returns the numeric worksheet index for the specified name starting at 1.
• WriteValueToSheet -- Writes a string value to the active worksheet at the specified row, column.

Testing the Ribbon
I created a second tab on the ribbon as shown in Figure 9. This Testing tab is used to verify methods as they are being written. It is useful to keep this tab in the template, just change its Visible property to false before deploying the solution.

[Click on image for larger view.]

Figure 9. A custom Testing tab for use in development.

Conclusion
Visual Studio 2010 and .NET Framework 4 makes it much easier to develop Microsoft Office extensions as shown here by the automated spreadsheet to prepare a consolidated financial statement across two different databases. The user simply needs to open the template, select the desired financial period, and click the Load Data button to get a fully prepared financial spreadsheet with information that would otherwise take significant manual effort to prepare.

Developing Office Automation solutions provides useful functionality in an environment in which the user is comfortable and familiar to the end user. Business solution developers will do well to consider taking full advantage of these capabilities in future applications.

• Get Code Download