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. Many computer users live in the Microsoft Office suite, using Word, Excel and Outlook as the core tools to perform the majority of their daily computer tasks. I am amazed at the number of serious.NET business software developers that fail to exploit this familiar and comfortable environment to deliver easy-to-use solutions. Enhancements in the.NET Framework 4 and Visual Studio 2010 make Office automation solutions easier than ever to write and deploy, in either C# or Visual Basic.
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. 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. 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.
When trying to activate Microsoft Office 2013, I cannot get it to activate. The installer path to your setup.exe and then clicking change channel.
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. Available Office 2010 Visual Studio Templates 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. 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.
Office Templates for Excel 2010 Workbook Solutions 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. Consolidated Reporting Solution. Almost all of our custom Ribbon code is contained in Ribbon.vb and Utility.vb of the Excel2010WorkbookVB 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.
Data Import Properties. File: Choose the required document. Table / Sheet: Define the Table’s or Sheet’s name. Column(,Col.): Set one ore more column names to use (comma separated). Area Input: Excel also accepts areas. Column Input: Used column.
Row Input: Used row. Data Delim.: Delimiter after each row. Column Delim.: Column separator sign(s). Shared Mem.: Set Scene-, Global- or Distributed-Shared Memory for data transport.
Use Inactive memory to not forward any values via Shared Memory. Key: Shared Memory key name. GetIt: Reads the document, sends the required data and shows it in the text box. You must close the document before importing it to Viz Artist. Start Viz Artist and create a new scene. Add a group container to the scene tree.
Drag and drop the and Data Import plug-in to the group container. Add a material to the same group container. Open the and set Position X to -200.0 and Position Y to -100.0. Set Shared Mem.
To Scene with MyDataY as Key name for both plug-ins. Set Data Delim.
To # for both plug-ins. For the Line Chart plug-in set ChartWidth to 500.0. Activate DataY Fit, DataY Auto Scale and DataY Detect Limits. Adjust DataY Stop to 200.0. Activate Const. Thickness to give the chart a constant line width.
For the Data Import plug-in, set the stored Excel file as the File parameter. Enter MyTable for the Table/Sheet parameter and ExcelDataY for Column (,Col). Click the GetIt button and the chart shows.