• Article

Exercise 1: Creating a Collection of Business Entities and Inserting their Values into an Excel Worksheet

Business entities are a commonly used pattern to carry data through an application or system. Business entities might reflect a bank account, with a balance and account holder, or they might reflect a shopping cart with a number of items in it, each of those items being its own business entity.

In this exercise, you will learn how to create a collection of business entities and write the values from those entities to an Excel worksheet. Along the way, you will be introduced to new language features in C# 4.0 and Visual Basic 10 that greatly speed the creation of business entities.

Note:
To verify that each step is correctly performed, it is recommended to build the solution at the end of each task.

Task 1 – Creating a New Console Application and Reference the Microsoft Office Interop Assemblies

In this task, you will create a new console application and add in the assemblies needed to begin Office development.

  1. Open Microsoft Visual Studio 2010 from Start | All Programs | Microsoft Visual Studio 2010 | Microsoft Visual Studio 2010.

  2. On the Microsoft Visual Studio Start Page, click the New Project icon.

    Note:
     This lab will be presented in both C# and Visual Basic. Feel free to use which ever language you are more comfortable with. Along the way new features of each language and Visual Studo 2010 will be pointed out.

  3. In either the C# or Visual Basic projects Templates list, select Windows | Console Application.
  4. In the Name field, enter the name “OfficeApplication”.
  5. In the upper right hand corner, ensure that version 4.0 of the .NET Framework is selected.
  6. Accept the default location and Solution Name. Click the OK button. The solution will be created with a single console application project.
  7. Right-click on the OfficeApplication project node and select Add Reference….

  8. Under the .NET tab in the Add Reference dialog box, select Microsoft.Office.Interop.Excel. Hold down the CTRL key and click Microsoft.Office.Interop.Word. Click the OK button.

    Note:
    Since the lab is targeted to Office 2007, make sure version 12.0.0.0 is selected when adding the references.

    When complete, the list of references for your console application should look like this:

    Figure 1

    References for Office development

Task 2 – Creating a Collection of Business Entities

In this task, you will create a collection of business entities that will be pre-populated with data. Pre-populating simulates loading the entities with data from a separate source. We are shortcutting here to focus on things other than data access.

You will start by creating an individual entity.

  1. In the Solution Explorer, right click on the node for the OfficeApplication project and select Add… | Class from the menu.
  2. The Add New Item dialog box appears with the Class template already selected. Change the name to Account.cs (for C#) Account.vb (for Visual Basic) and click the Add button.

    1. If you are doing the lab in C#, change the definition of the Account class to be public, as shown below:

      C#

      public class Account
      {
      FakePre-5dcefa872f0743e0a1c38be59f6631f0-f843f9fcf0df47e28c327550c6fcaa20

  3. Add public properties for ID (integer), Balance (double) and AccountHolder (string) to the Account as shown:

    (Code Snippet – Office Programmability Lab - Ex1 Account properties CSharp)

    C#

    public class Account
    FakePre-3fc26bd683594b4bbfdb8fe9a89916af-396a438612ec4d11957b62e13481c07d public int ID { get; set; } public double Balance { get; set; } public string AccountHolder { get; set; }FakePre-af2c2c4b97234148a71b1daf9a452121-f790e57918074f48beac2ec80c858b59

    (Code Snippet – Office Programmability Lab - Ex1 Account properties VB)

    Visual Basic

    Public Class Account
    Property ID As Integer Property Balance As Double Property AccountHolder As StringFakePre-8a5fd6812e864b8fb23c858cd7d228c7-e4abc367ce50441ab1e48879b7d466b3

    Note:
    A new feature in Visual Basic is the ability to declare a property without declaring a separate field to store the value in. These automatically-implemented properties work the same as if the developer had declared a private field to hold the data and implemented simple “getters” and “setters.” They are called the same way a traditional property is called. If a more robust logic is required for property access, traditional properties that store data in private fields can still be created. Comparable Visual Basic code from VS 2008 would look like the following example:

    Visual Basic

    ' This is the old style using backing variables instead of ' auto-implemented properties Public Class Account Private _id As Integer Private _balance As Double Private _accountHolder As String Property ID() As Integer Get Return _id End Get Set(ByVal value As Integer) _id = value End Set End Property Property Balance() As Double Get Return _balance End Get Set(ByVal value As Double) _balance = value End Set End Property Property AccountHolder() As String Get Return _accountHolder End Get Set(ByVal value As String) _accountHolder = value End Set End Property End Class

  4. The Account class represents your individual entity type. Normally an application would create some sort of collection of these entities and populate them with data from a data store of some sort. To keep this lab concise and focused you will populate your list by pushing values into the individual objects at creation. In the Module1.vb (VB) or Program.cs (C#) file, create a new method called CreateAccountList as shown in the following code:

    (Code Snippet – Office Programmability Lab - Ex1 CreateAccountList VB)

    Visual Basic

    Private Function CreateAccountList() As List(Of Account) Dim checkAccounts As New List(Of Account) From { New Account With { .ID = 1, .Balance = 285.93, .AccountHolder = "John Doe" }, New Account With { .ID = 2, .Balance = 2349.23, .AccountHolder = "Richard Roe" }, New Account With { .ID = 3, .Balance = -39.46, .AccountHolder = "I Dunoe" } } Return checkAccounts End Function
    

    

    

    
Note:

    

    
The code above highlights another new feature in Visual Basic: collection initializers. This enables developers to save large amounts of time by declaring the list and initializing the list’s contents directly at that point. There is no need to instantiate the list and then individually “new” up items and add them to the list.

    

    

    

    (Code Snippet – Office Programmability Lab - Ex1 CreateAccountList CSharp)
    

    

    

    C#
    

    
private static List<Account> CreateAccountList() { var checkAccounts = new List<Account> { new Account{ ID = 1, Balance = 285.93, AccountHolder = "John Doe" }, new Account { ID = 2, Balance = 2349.23, AccountHolder = "Richard Roe" }, new Account { ID = 3, Balance = -39.46, AccountHolder = "I Dunoe" } }; return checkAccounts; }
    

    5. 

  5. Call the CreateAccountList method from the Main method in the Module1.vb (VB) or Program.cs (C#) file as show:
    

    

    

    

    Visual Basic
    

    

    Sub Main()
    
 Dim checkAccounts = CreateAccountList()FakePre-bd8de9df297e47dbaa6d3670d4bac85f-fae47afe0a224d27a0de27578b619592

    

    

    

    

    C#
    

    

    static void Main(string[] args)
    
FakePre-f1af74303a564aebac308733637160bb-91d0539496704f10a404872bf7929101 var checkAccounts = CreateAccountList();FakePre-6674fe8ebf7e452dba17eba46e45ed3f-863d0d845a3e4f9894faf8b9c5145d67

    

    6. 




Task 3 – Creating an Excel Workbook and Populate it with Data from the Business Entities


In this task, you will create a new Excel workbook. You will then loop through the collection of business objects and insert the data into the Excel workbook. Finally, you will format the worksheet.


    

  1. The first step is to import the Office namespaces with Imports (VB) or using (C#) statements. Enter the following code at the top of the Module1.vb (VB) or Program.cs (C#) file:
    

    (Code Snippet – Office Programmability Lab - Ex1 Namespaces VB)
    

    

    

    Visual Basic
    

    
Imports Microsoft.Office.Interop Imports System.Runtime.CompilerServices

    

    

    (Code Snippet – Office Programmability Lab - Ex1 Namespaces CSharp)
    

    

    

    C#
    

    
using Microsoft.Office.Interop; using Excel = Microsoft.Office.Interop.Excel; using Word = Microsoft.Office.Interop.Word;

    

    2. 

  2. The code to insert the data into Excel is implemented as an Extension method.
    

    The first parameter of this (and all) extension methods indicates the type that the method operates on (your list of accounts). The second argument, DisplayFunc, is a type of delegate called Action. Action delegates can take any number of arguments, but always return void.
    

    This block of code creates a new Excel application with a workbook, injects some header text into the workbook, calls a helper method to display the values of your Account objects, then sets each column to AutoFit mode to better display each column’s values.
    

    Enter the following code in Module1.vb (vb) or Program.cs (C#) after the Main method:
    

    (Code Snippet – Office Programmability Lab - Ex1 DisplayInExcel VB)
    

    

    

    Visual Basic
    

    
<Extension()> Sub DisplayInExcel(ByVal accounts As IEnumerable(Of Account), ByVal DisplayFunc As Action(Of Account, Excel.Range)) With New Excel.Application .Workbooks.Add() .Visible = True .Range("A1").Value = "ID" .Range("B1").Value = "Balance" .Range("C1").Value = "Account Holder" .Range("A2").Select() For Each ac In accounts DisplayFunc(ac, .ActiveCell) .ActiveCell.Offset(1, 0).Select() Next .Columns(1).AutoFit() .Columns(2).AutoFit() .Columns(3).AutoFit() End With End Sub
    FakePre-76229824d5754e69a335c091b352d065-faaa13a66a6049508f39cde97c02ee43

    

    

    

    
Note:

    

    
In C #, it is necessary to change the declaration of your Program class in Program.cs to be static.

    

    

    

    (Code Snippet – Office Programmability Lab - Ex1 DisplayInExcel CSharp)
    

    

    

    C#
    

    
static void DisplayInExcel(this IEnumerable<Account> accounts, Action<Account, Excel.Range> DisplayFunc) { var x1 = new Excel.Application(); x1.Workbooks.Add(); x1.Visible = true; x1.get_Range("A1").Value2 = "ID"; x1.get_Range("B1").Value2 = "Balance"; x1.get_Range("C1").Value2 = "Account Holder"; x1.get_Range("A2").Select(); foreach (var ac in accounts) { DisplayFunc(ac, x1.ActiveCell); x1.ActiveCell.get_Offset(1, 0).Select(); } ((Excel.Range)x1.Columns[1]).AutoFit(); ((Excel.Range)x1.Columns[2]).AutoFit(); ((Excel.Range)x1.Columns[3]).AutoFit(); }
    FakePre-48724ef30e594d419751b55ba683e9d0-e13cf3c5ad244e8c9a5e2efdb376d2a5static void DisplayInExcel(this IEnumerable<Account> accounts, Action<Account, Excel.Range> DisplayFunc) { var x1 = new Excel.Application(); x1.Workbooks.Add(); x1.Visible = true; x1.get_Range("A1").Value2 = "ID"; x1.get_Range("B1").Value2 = "Balance"; x1.get_Range("C1").Value2 = "Account Holder"; x1.get_Range("A2").Select(); foreach (var ac in accounts) { DisplayFunc(ac, x1.ActiveCell); x1.ActiveCell.get_Offset(1, 0).Select(); } ((Excel.Range)x1.Columns[1]).AutoFit(); ((Excel.Range)x1.Columns[2]).AutoFit(); ((Excel.Range)x1.Columns[3]).AutoFit(); }FakePre-cdd1f55165cb4b12afa94027bfe72717-5a5887ddd36f4c5395887a0a1c625eb7FakePre-54ab78a9a4144ed5a4bcc53d1a123f4b-c41dbc4e77694fce8586763f1e30aeba

    

    

    

    
Note:

    

    
The statement x1.Workbooks.Add(); reflects an important change in the .NET Framework 4.0 and C# 4.0: optional parameters. Previous versions of the Microsoft.Office.Interop.Excel.Workbook COM interop assembly required passing in a parameter. Besides, C#’s previous versions had no support for optional parameters, something Visual Basic developers have enjoyed for years. Earlier the statement would have appeared as x1.Workbooks.Add(Missing.Value); The new interop assemblies in the .NET Framework 4.0, coupled with C# 4.0’s new support for optional parameters lets you use a more concise, clearer syntax.

    

    

    3. 

  3. The next step is to call your extension method from the Main method of your program. Add the following code to the bottom of the Main method:
    

    (Code Snippet – Office Programmability Lab - Ex1 DisplayInExcel call VB)
    

    

    

    Visual Basic
    

    
checkAccounts.DisplayInExcel(Sub(account, cell) cell.Value2 = account.ID cell.Offset(0, 1).Value2 = account.Balance cell.Offset(0, 2).Value2 = account.AccountHolder If account.Balance < 0 Then cell.Interior.Color = RGB(255, 0, 0) cell.Offset(0, 1).Interior.Color = RGB(255, 0, 0) cell.Offset(0, 2).Interior.Color = RGB(255, 0, 0) End If End Sub)

    

    

    

    
Note:

    

    
Here you see another new feature in Visual Basic; statement lambdas. A statement lambda is an anonymous block of code that is treated like a variable in that it can be passed as an argument to a method, as you are doing in this example. The syntax for a lambda is to “declare” a series of arguments, in this case account and cell, and then to supply the logic for that lambda. When using statement lambdas function or sub names are not required and argument types are inferred.

    

    

    

    (Code Snippet – Office Programmability Lab - Ex1 DisplayInExcel call CSharp)
    

    

    

    C#
    

    
checkAccounts.DisplayInExcel((account, cell) => { cell.Value2 = account.ID; cell.get_Offset(0, 1).Value2 = account.Balance; cell.get_Offset(0, 2).Value2 = account.AccountHolder; if (account.Balance < 0) { cell.Interior.Color = 255; cell.get_Offset(0, 1).Interior.Color = 255; cell.get_Offset(0, 2).Interior.Color = 255; } });
    

    

    The DisplayInExcel extension method takes one argument, an Actiondelegate which in turn takes two arguments. The Action delegate’s arguments are an instance of your Account object and an instance of a cell in an Excel worksheet. This extension method is in reality a unit of functionality that will be called for each Account object in your list by the for each loop in the body of the DisplayInExcel function.
    

    The DisplayInExcel function contains the logic for creating and performing setup of the Excel workbook. The delegate you are providing contains the logic to take the individual values from the Account object and place them in the Excel worksheet.
    4. 





Next Step


Exercise 1: Verification






< previous | next >














Downloads


Download The Offline Training Kit

Download Hands-on Lab Document (DOCX)

Download Hands-on Lab Source Files


Contents