Office11 Solution using .NET - A White Paper

Table of Contents

• Introduction 
• Office Automation Architecture 
• Office 11 & .NET Support 
• Excel/Word Project Development 
• Creating/Configuring Office Project 
• Understanding Architecture and Managing Events 
• Coding with .NET Libraries 
• Conclusion 
• References

1. Introduction

We all know that today most of the end users information processing is being managed by office tools, in other words end users are highly tied with office tools. But if we closely look at the existing office system, there is lack of interface between people, data and process.

In simple terms, we always present static information and we have limited options to get latest data. Often, we may need accurate or up-to minute information for our business to take effective decision.
The Office 11 goal is to help workers or customers and businesses to succeed by bringing together people, information and processes.

Microsoft "Office 11" allows developer to efficiently build powerful, intelligent, and connected solution within a familiar and powerful user interface.

• Connected applications. "Office 11" enables developers to build applications that leverage Extensible Markup Language (XML) and XML Web services to allow information workers using Office to easily interact with enterprise wide business processes and data.
   
• Intelligent applications. Using the "smart" client capabilities and the new, powerful interface in Office, developers can create applications that allow people to efficiently work the way they want with the information they need. 
  
  
• Innovative enterprise developer tools. Integration with Microsoft Visual Studio(R) using the Visual Studio .NET Tools for Office allows the developer to bring the power of writing managed code in Visual Studio .NET into Office applications.

Hope the above scenarios gives some idea about what Office 11 can do for customer and for the developer community. We are going to discuss more about office 11 developer tools to build the connected application.

2. Office Automation Architecture

Before we get into .NET support on Office11, let us see what is Office automation and what are they trying to address.

Productivity is the biggest challenge for any company or small business. Increasing productivity is Microsoft Office's goal. In that spirit, Microsoft wanted to include features that allow corporate and small business developers to produce quality applications that bind the power and flexibility of Office in order to quickly build customized solutions. Automation is the key to this strategy.

Automating one application from another is often referred to as running code from a host application to automate another application. Automating one Office application from another is generally accomplished in the same way.

Office Automation offers extensive support for most of the technology infrastructure provided by Microsoft as well as interaction with legacy system using web services or similar technologies.

Let us have simple office automation solution architecture.

In the above architecture, Excel Application got enough resource to interact as well as very flexible programming modal to develop the application.

We have discussed details of office automation, but it's important to know the real business needs to choose the office solution, let us explore some business challenges may need office solutions. 

Business Challenges	Solution
Saving Time & Money Customer time is very expensive; they need a system which consumes less time as well as less investment	Office products very interactive and user friendly, which means they need to spend very less time to complete their task. One of the customer says, "Our previous solution required two full-time business analysts," says Robert. "The Microsoft Word-based forms solution requires less than one full-time equivalent. In fact, I typically spend less than two to three hours per week on it."
Up-to-minute Information Business today requires decision makers to have up-to-the-minute information and solutions that shorten the span between knowledge and action.	Office 11 provides excellent support to implement dynamic data presentation with office 11 tools and web services.
Accessible to Everyone Effective collaboration among people, teams, and organizations is a key to achieving and maintaining competitive advantage	Office solution demands very minimal infrastructure in the client, it also enables web-based development using Office 11 Web Components
Interoperability It is also very important that your solution should facilitate interaction with other application	Office products supports XML Data source to manage information; which means the interoperability becomes very simple
Well-Presented Information For an organization to realize maximum benefit, the front-end tools must be exceptionally easy to use and understand	As we know, office tools are very interactive and simple to use. We have verity of tools like Word, Excel, PowerPoint and Info path to present our information effectively

 3. Office 11 & .NET Support

Microsoft Visual Studio 2003 release added Visual Studio .NET Tools for Office technology to deliver office 11 solution. We should also keep in mind that VBA programming model still exists and the .NET Office tools are just another choice.

The key benefits of choosing VS.NET as development environment for Office Solutions are,

• Power of writing managed .NET code that executes behind Word and Excel documents
  
  
• Developers get the full, robust advantages of the Visual Studio .NET environment
  
  
• Allows developers to create applications with a more robust security model, restricting code that can execute only on a fully trusted corporate server.
  
  
• Code-behind .NET projects can be started in .NET with new Office documents, applied to existing Excel spreadsheets or Word documents and templates, and even co-exist with current VBA-based logic.
  
  
• Using VS.NET facilitates Language freedom, easier debugging, better memory management, and a more robust security model.
  
  
• Use of XML and XML Web services, "Office 11" becomes a highly connected platform to share data between applications, among processes, across enterprises and beyond

Hope we got enough understanding of Office 11, and it's a right time to jump into more technical details. We will discuss more details about Word and Excel project introduced in Visual Studio.NET; finally we will explore a case study.

4. Excel/Word Project Development

Every word/excel application developed in VS.NET will have its own assembly, which includes your business process implementation. The advantage is that we can specify our own assembly location, for example, we can deploy word application assembly in remote location (HTTP) and configure word document to use it form that http location.

Even though we implement our business logic in managed code, still the office object model uses couple of unmanaged resources at the runtime. Since the assembly uses unmanaged environment, we need to setup the Security Policy for the assemblies,

A typical execution of office application would like this,

4.1 Creating/Configuring Office Project

VS.NET introduced a new project template to create word/excel project (we will take Excel as our reference for the discussion) like Word Document/ Excel Document project as shown below,

Once we complete this step, VS.NET Development environment does couple steps behind the screen. It's better to understand what exactly they do,

• Creates YourApplication.doc word document file in the destination directory. This will be distributed to the end user to operate.
  
  
• Creates Security Policy to enable assembly to run and have full access to the system resources. This is done by Development environment to enable the execution permission for the assembly (We need to implement the same for the other clients).
  
  
• It creates 'YourApp_Bin' default assembly directory. It's default location created by development environment for assembly, we can change this to any UNC, HTTP or relative path.

A typical development environment of Office 11 project would look like this,

4.2 Understanding Architecture and Managing Events

We are done with creating an office11 project using Visual Studio.NET, let us start interacting with office 11 components and learn about some important events.

The architecture is slightly different from the normal application; the excel project creates two default objects to keep the references of Excel Application (nothing but reference to Excel.exe instance) and Excel Workbook (Excel Workbook with three default sheet).

When the Excel Sheet is getting opened, it loads the respective assembly and makes call to constructor and _Startup event will be notified to initialize the internal 'ThisApplication' and 'ThisWorkbook' objects as mentioned earlier.

The sample code generated by Dev Environment is given below for the better understanding,

Public Class ExcelProject
Friend WithEvents ThisWorkbook As Excel.Workbook
Friend WithEvents ThisApplication As Excel.Application
' Default constructor.
Public Sub New()
End Sub
' Required procedure. Do not modify.
Public Sub _Startup(ByVal application As Object, ByVal workbook As Object)
ThisApplication = CType(application, Excel.Application)
ThisWorkbook = CType(workbook, Excel.Workbook)
End Sub
End Calss 

So far we did understanding the default settings and behavior; now we will look at some important events required for implementing office 11 solutions. Remember that events are used to notify some incident or activity happened in the application.

ThisWorkbook_Open - This event will be called when the workbook is opened. Often we will use this place to update our presentation content by pulling latest information from various data source. 

' Called when the workbook is opened.
Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
End Sub 

ThisWorkbook_BeforeClose - It will be used when the user closes the workbook, you may be interested in doing some logging or cleaning activities.

' Called before the workbook is closed.
' The Cancel parameter has no effect. The workbook will close
' whether Cancel is set to True or False, unless it is kept open
' by user intervention or by code such as a COM add-in or VBA.
Private Sub ThisWorkbook_BeforeClose(ByRef Cancel As Boolean) Handles ThisWorkbook.BeforeClose
End Sub

_Shutdown - The last procedure to execute before closing the application (not the workbook) and has default implementation to remove the objects form memory. 

' Required procedure. Do not modify.
Public Sub _Shutdown()
ThisApplication = Nothing
ThisWorkbook = Nothing
End Sub

Hope we have got enough understating about office projects implementation; in the next section we will explore how to use .NET libraries and Web services.

4.3 Coding with .NET Libraries

We will create simple application to pull data form SQL database and display the data in a tabular format in Excel workbook.

Before we start, let us take quick walkthrough about Excel Object Model; the complete Excel Object model is little complicated, so we will focus on few objects required for our sample application,

Application object is the controller object of all other subsystems in the Excel Application. As we discussed earlier, the application object will be returned to 'ThisApplication' object variable.

Each application can have multiple Workbooks; there will be one default workbook for each application, the default workbook is returned to the 'ThisWorkbook' object variable.

Each Workbook will have 3 Worksheets (actual data presentation area). Developer can present data in any one of these sheets or they can create their own additional worksheets.

For example, look at the piece of code which displays "Welcome to Office11" in the first row of the first worksheet, 

Get the active Worksheet, i.e. First Worksheet in the default Workbook
Dim objWs As Excel.Worksheet = ThisWorkbook.ActiveSheet
'Display Welcome data at 1st Row, 1st Column
objWs.Cells(1, 1) = "Welcome to Office 11" 

We have got the simple application which displays (textual presentation) some data in Excel Sheet; similarly we can graphically display (for analysis) our data using Charts and also we can easily implement sorting, filtering etc.

The next step is to interact with Database and present the data in Excel sheet; the implementation is similar to our conventional data-driven programming. Couple of steps to implement the excel data display with database is given below,

• Open the SQL Connection and Execute the SQL query and return the data to Datareader or DataSet
• Get reference to ActiveWorksheet form the default Workbook 
• Loop the dataset or data reader and fill the Excel Sheet

The complete source code which interacts with db and populates excel worksheet is listed here, 

Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
' Office integration attribute. Identifies the startup class for the workbook. Do not modify.
<Assembly: System.ComponentModel.DescriptionAttribute("OfficeStartupClass, Version=1.0, Class=ExcelProject.ExcelProject")>
Public Class ExcelProject
Friend WithEvents ThisWorkbook As Excel.Workbook
Friend WithEvents ThisApplication As Excel.Application
#Region "Generated initialization code"
' Default constructor.
Public Sub New()
End Sub
' Required procedure. Do not modify.
Public Sub _Startup(ByVal application As Object, ByVal workbook As Object)
ThisApplication = CType(application, Excel.Application)
ThisWorkbook = CType(workbook, Excel.Workbook)
End Sub
#End Region
' Called when the workbook is opened.
Private Sub ThisWorkbook_Open() Handles ThisWorkbook.Open
Try
'Open SQL Connection
Dim objCon As New SqlConnection("integrated security=SSPI;data source=EC2reT002031;persist security info=False;initial catalog=pubs")
objCon.Open()
'Create SQL Command to retrive data form EMP Table
Dim objCommand As New SqlCommand("SELECT * FROM EMP", objCon)
'Get the Data into DataReader
Dim objReader As SqlDataReader = objCommand.ExecuteReader
'Get the active work Sheet
Dim objWs As Excel.Worksheet = ThisWorkbook.ActiveSheet
Dim intRow = 2
'Set Header
objWs.Cells(1, 1) = "Employee ID"
objWs.Cells(1, 2) = "Employee Name"
objWs.Cells(1, 3) = "Salary"
'Fill Employee Details
While objReader.Read
objWs.Cells(intRow, 1) = objReader(0)
objWs.Cells(intRow, 2) = objReader(1)
objWs.Cells(intRow, 3) = objReader(2)
intRow += 1
End While
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Class

Excel Output of the above code would look like this, 

5. Conclusion

Microsoft Office 11 Tools are very flexible and powerful to create efficient Office 11 Solutions quickly. The above discussion is clearly proving that, people, information and process are integrated well. We have discussed only about 'Connected Applications'; and there are lot many solutions like 'Smart Tags' which allows customization of office products to meet our own business needs.

6. References

http://www.microsoft.com/office/

http://msdn.microsoft.com/

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoffdev/html/vsofficedev.asp