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