DOCX

Microsoft Office Programmability in C# and Visual Basic

By Philip Ortiz,2014-09-19 17:29
12 views 0
Microsoft Office Programmability in C# and Visual Basic

Hands-On Lab

    Microsoft Office Programmability in C# and

    Visual Basic

Lab version: 1.0.0

    Last updated: 9/19/2011

CONTENTS

    OVERVIEW ............................................................................................................................................. 3

    EXERCISE 1: CREATING A COLLECTION OF BUSINESS ENTITIES AND INSERTING THEIR

    VALUES INTO AN EXCEL WORKSHEET .............................................................................................. 5

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

     ........................................................................................................................................................ 5

    Task 2 Creating a Collection of Business Entities ............................................................................ 6

    Task 3 Creating an Excel Workbook and Populate it with Data from the Business Entities ........... 10 Exercise 1: Verification 13

    EXERCISE 2: EMBEDDING AN EXCEL WORKSHEET IN MICROSOFT WORD ................................. 14

    Task 1 Embedding your Excel Worksheet into a New Microsoft Word Document ........................ 15 Exercise 2: Verification 17

    EXERCISE 3: REMOVING THE PIA DEPENDENCE FROM THE APPLICATION ................................. 19

    Task 1 Checking that PIA dependencies are removed by default.................................................. 19 SUMMARY ............................................................................................................................................ 20

Overview

    Microsoft Office provides organizations an environment for rapidly creating business applications in an environment that is very familiar to end users. Building Office Business Applications present a large number of opportunities for organizations to take advantage of, including tying into Customer Relationship Management (CRM) systems, fronting data extracted from line of business applications, hosting business intelligence reports, and a wealth of other possibilities.

    While Office development provides a great number of value opportunities for organizations, previously it also presented a number of challenges for developing and deploying those applications. Earlier versions of managed languages also contributed to the friction involved in creating applications using Office.

    Calling numerous Office API methods was often painful due to the large number of parameters involved, many of which had no use. Visual Basic’s optional parameters feature made this easier, but developers in C# had to suffer through writing significant amounts of useless initializers or null statements to fill out required method signature parameters. If you were writing in Visual Basic, you had no access to an equivalent of C# 3.0’s lambda feature to supply inline methods when invoking delegates.

    In this lab, you will see how new features in Visual Studio 2010, C# 4.0, and Visual Basic 10 eliminate the issues in the previous paragraphs. Additionally, you will see a number of other powerful features, which speed other elements of Office development.

    Objectives

    In this Hands-On Lab, you will learn how to:

    ; Learn how new language features speed creation of business entities

    ; Discover new language features that make it easier to have business entities interact with Excel

    ; Utilize new language features which enable quick interaction between Office applications

    ; See how the new build process allows easier deployment of Office applications to end users

System Requirements

    You must have the following items to complete this lab:

    ; Microsoft Visual Studio 2010

    ; .Net Framework 4

    ; Microsoft Excel 2007

    ; Microsoft Word 2007

Setup

    All the requisites for this lab are verified using the Configuration Wizard. To make sure that everything is

    correctly configured, follow these steps.

    1. Run the Configuration Wizard for the Training Kit if you have not done it previously. To do this,

    browse to Source\Setup folder of this lab, and double-click the Dependencies.dep file. Install

    any pre-requisites that are missing (rescanning if necessary) and complete the wizard.

    Note: The Configuration Wizard is used for checking dependencies and setting up the

    environment. If the Configuration Wizard is not installed on your machine, you must install it

    running the DependencyChecker.msi file located on

    the %VS2010TKInstallationFolder%\Assets\DependencyChecker folder (e.g. by default the

    Training Kit is installed under C:\VS2010TrainingKit).

    For convenience, much of the code you will be managing along this lab is available as Visual

    Studio code snippets. The Dependencies.dep file launches the Visual Studio installer file that

    installs the code snippets.

Exercises

    This Hands-On Lab is comprised of the following exercises:

    1. Creating a collection of account classes and display the account information in Microsoft Excel

    2. Embed the Microsoft Excel worksheet in a Microsoft Word document

    3. Alter the VS project files to enable the program to be deployed without PIA

Estimated time to complete this lab: 60 minutes.

Next Step

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

    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.

    a. 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

    {

    }

    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

    {

     public int ID { get; set; }

     public double Balance { get; set; }

     public string AccountHolder { get; set; }

    }

    (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 String

    End Class

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 CreateAccountList() {

     var checkAccounts = new List {

     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. 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()

    End Sub

C#

    static void Main(string[] args)

    {

     var checkAccounts = CreateAccountList();

    }

    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. 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:

Report this document

For any questions or suggestions please email
cust-service@docsford.com