It is sometimes difficult to remember the corporate office of the recent past. Think back about 10 or maybe 15 years. For many of us, that isn't so long ago. However, from a technology perspective, it's an era as distant as the dark ages. Sure, personal computing was taking off and the Internet was in its infancy, but most companies did not yet have a web site, and the average business user had little exposure to the technologies that seem commonplace today. Remember when only the technically proficient used spreadsheets? When e-mail was a productivity tool and didn't require labor-intensive filing and sorting? The point of this trip down memory lane is to lend some perspective as to how far workers have come in embracing the technology solutions IT offers. The fact is, the amount of information workers have to interact with increases regularly, as does the proliferation of often-siloed software systems built to cope with that information. Today, many organizations find themselves in a state where information and labor are duplicated. Often, workers take more time finding and constructing information than analyzing it and making decisions. This is where technology now has an opportunity—the opportunity to provide smarter tools that focus on the work corporate business users actually need to accomplish. It is with this in mind that this book will explore common challenges and scenarios and their solutions.
Companies often believe the average business user isn't going to find new technologies or solutions easy enough to use. Though there is some truth in that, history has shown that the information worker will adapt if the solution delivers a true value. What is more interesting is the behavior of the next generation of workers. After Hurricane Katrina, we had the opportunity to perform some community service in the New Orleans area. Some of our team helped restore houses, others helped local services restore computer networks, and we got to visit area schools to provide something similar to a career day for the students. Before we walked into the classrooms, the principals told us that most of the families who had the means left the area and didn't come back and that we should not have high expectations about how much technology the students had been exposed to. Of course, we didn't listen and asked the same questions we always ask students:
How many of you have cell phones?
Almost every student had one. And most were comfortable discussing the phone as a multipurpose device. I quickly learned to turn off my Internet browser access when the students were playing with my phone. Most of the students had sent text messages and used their phones for music.
How many of you can type faster than you can write?
Again, almost every hand in the room was raised.
How many of you use a computer daily?
The hands remained raised. During our time there, many students asked us questions about their home networks. Even with middle-school kids, they were the ones setting up the networks for their families.
This past year, I picked up my son at the bus stop after the first day of third grade. Most of the older kids walked off the bus with cell phones in hand, responding to text messages. And this was an elementary school bus! Our school experience is evidence that not only will the average corporate business user pick up a new technology if we can provide a solution that delivers value, but that our workforce will more and more include users who have been exposed to a fair amount of technology outside of the office. Still, we do have to be aware that many workers don't want to learn something completely new. Because of this, this book will focus on solutions that customize tools that have been a staple on the corporate desktop. The solutions in this book will extend the most familiar Microsoft Office tools: Word, Excel, Outlook, PowerPoint, Access, and Visio.
Why focus on Microsoft Office? For the information worker, Microsoft Office has a proven value that few other technologies can compare with. Word, Excel, Outlook, and PowerPoint have been on the corporate desktop for more than a decade and users are comfortable working with them. Microsoft itself is using this strength to address the needs of enterprises, relating to how they create, organize, and search information. At the heart of these enterprise application servers are Microsoft SharePoint products and technologies.
Specifically, the name SharePoint is attached to two server-side applications: SharePoint Foundation is a Windows Server component that provides collaboration features designed to deliver Web-based sites for teams and groups of users. These sites provide a focal point for activities, such as planning a meeting, reviewing a document, or completing a business process. Microsoft SharePoint Server extends this foundation to provide enterprise-level features such as search, records management, content management, personalization, application integration, social networking, data visualization, and so on. There is a reason that SharePoint is a part of the Microsoft Office platform—these products extend the Microsoft Office desktop applications to provide services that an organization needs beyond document creation. The server applications integrate seamlessly into the Microsoft Office desktop applications. As a result, organizations can connect information workers, service their collaboration needs, and help them locate information—all from within the same document, spreadsheet, presentation, or e-mail they are working on.
The latest versions of the Microsoft Office desktop tools and application servers provide an incredible range of features and functionality out of the box, and they integrate more completely than any previous release. However, as organizations apply these technologies to their business processes or to solve specific problems, there may still be some gaps because of the nuances inherent in the way a company wants to use them. This is where the application developer enters center stage. The mistake often made here is that the application developer builds a solution completely removed from the user's familiar environment. To be successful, a solution needs to be highly integrated with the Office desktop tools, and to leverage SharePoint's services as well. There is no reason, for example, to build a custom database application that stores documents and their metadata, and provides versioning capabilities. Such features are available with any SharePoint document library. And as for the interface, why ask a user to close the document he is working on to go to some other thick Windows client or custom web page?
Historically, developers have avoided customizing the Office tools for several reasons. The first is that such solutions are notoriously difficult to construct. This is in large part due to the lack of a sophisticated development environment. Developers focused on C++, Visual Basic, or C# are typically not exposed to VBA, the development model within Office, and therefore lack the comfort level needed to build effective solutions. Microsoft Visual Studio Tools for Office (VSTO) bridges this chasm. VSTO is a runtime library that provides a layer of abstraction between the .NET developer and the Microsoft Office primary interop assemblies. This layer helps hide some of the COM particulars from.NET developers and enables them to build .NET solutions for Office using Visual Studio, focusing on their solutions, not on interop plumbing. In addition to making the code simpler, VSTO provides a designer experience. For example, when building an Excel-based solution, Excel is loaded into Visual Studio and the spreadsheet becomes the design surface. This means a developer can drag and drop her familiar Windows forms controls right onto the spreadsheet, set their properties, and add a code-behind. This is the expected experience for the Windows or Web developer. Many of the examples in this book will utilize Visual Studio Tools for Office.
Another milestone that promotes new development opportunities is the switch from Office documents that rely on proprietary binary file formats to those using formats that are open and built on XML. This change first arrived with Microsoft Office 2007, whose files rely on an Open XML format. Very often, developers find themselves in a situation where a solution requires the generation of a document, spreadsheet, or presentation based on data in a SQL database, web service, or other external application. Previously, most solutions relied on automating Office, which required the application to be installed on the server. Not only were these solutions extremely difficult to scale, but in most circumstances they were not recommended by Microsoft. With the Open XML file format, developers can build server-side document-generation solutions that don't require having the Office application on the server.
So this book is about building solutions on top of the Microsoft Office platform, meaning that the solutions will incorporate SharePoint, Office, and VSTO. This is a book for the developer community. We assume an average level of experience building .NET applications and some familiarity with Office and SharePoint.
The three chapters following this one (Chapters 2, 3, and 4) provide an overview of SharePoint, the new SharePoint 2010 development tools, and Office development topics. Almost all of our chapters have a "Further Reading" section at the end in case you want more information on the topics covered there.
This book is not meant to be a reference manual that teaches you every feature of these technologies; instead it shows you common solution patterns through scenarios that could apply to any organization. If you are an expert in these technologies, feel free to skim the overview chapters or even skip them and jump straight to the scenario/solution ones (Chapter 5 and onward). We tried to make each of our solution chapters capable of standing on its own, so you can read them in any order, focusing on the scenarios that most interest you.
You might think that, with all of this technology, we will be building solutions never dreamed of before. However, that really isn't the case. The solutions we will construct are ones that developers have been struggling to deliver for some time. What has changed over time are the tools available for constructing these solutions. With the 2010 releases, you will see how much easier it is to build the solutions and how much less code it takes.
The solutions we will construct have their humble beginnings in custom VBA applications. Many VBA solutions are brought into businesses by a technology-savvy business user who loves to record macros and see the code they emit. Such users are able to put together a document or spreadsheet that turns into a mission-critical application. Unfortunately for the organization, such applications become difficult to manage and deploy. They typically haven't been designed with performance and security in mind, and debugging has been largely a process of trial and error. Even though these VBA applications are rather primitive, they tend to be a huge success. The reason is that they are designed to make the information worker's job easier and they can be deployed rather quickly. They are integrated into the Office applications and were built by someone intimately involved with the challenges faced in the workplace.
A level above VBA applications are solutions developed using the COM interfaces exposed by Office. These tend to be application add-ins that run within an Office tool or an application that automates Office. An add-in is essentially a custom library that Office loads as the application launches. The add-in can then extend the Office tool to provide additional functionality. An example could be an Excel add-in that puts a button on the toolbar that, when pressed, loads data from an external source into the spreadsheet. The add-in model is a powerful one that continues into the managed code solutions created by VSTO. The biggest differences between COM and VSTO add-ins are the improvements in the development experience and the benefits of .NET code bases over COM.
Developers have also built solutions that rely on automating an Office tool. An example of this would be custom code that loads the Word application (Word.Application) and automatically begins to construct a document based on a data source. Such applications tend to be deployed on servers where this work can be done on behalf of the user. The main problem with these solutions is that they will not scale, because automating Word in this manner is equivalent to each user logging on to the server and opening the application directly. Moreover, this kind of server-based automation of Office is something Microsoft has always advised against.
Microsoft Office 2003 presented an application type called smart documents, which were supposed to solve the information integration and duplication challenge. Smart documents took advantage of the task pane and Office 2003's support for XML. An example of such an application would be a Word document that, through a custom task pane, allows a user to select data from another application. That data is then placed appropriately into the document based on the document's XML schema. Smart documents were first presented as COM applications in which the task pane was HTML with an XML file detailing the controls that were to be loaded. Later, the first version of VSTO for Visual Studio 2003 provided a .NET programming option, but only for Word, Excel, Outlook, and InfoPath. These types of applications got "smart" in their name because of their similarity to smart clients. In smart client applications, a thick client relies on external resources such as web services. This model has many appealing advantages, including deployment scenarios and support for disconnected clients. Visual Studio 2010 and .NET continue to evolve solutions of this type, and expose this development model to many more Office applications.
Solutions developed on the Microsoft Office platform are termed Office Business Applications, or OBA. The OBA team at Microsoft has documented common patterns that solutions incorporate to deliver integration, a rich experience, and data reduction for the information worker. These patterns can be grouped into the categories displayed in Table 1-1. As we introduce you to the scenarios in the book, we will describe which of these patterns the solutions implement. (Often there is not a single pattern used, but rather a combination.) For more information on these patterns and OBA, visit the OBA developer portal at http://msdn2.microsoft.com/en-us/office/aa905528.aspx, and read an article in MSDN Magazine on the solution patterns at http://msdn.microsoft.com/en-us/magazine/ cc337889.aspx.
Table 1-1. Categories of Office Business Application Patterns
Pattern Category | Description |
|---|---|
Office Applications as a Reach Channel | Using Office to present data from other systems in an effort to simplify access or reduce duplication of effort |
Automating the generation of documents with data from another system or processing the documents to extract data | |
Composite User Interface | Bringing together data from disparate resources into a single tool for the end user |
Providing the ability to incorporate ad-hoc workflow into other line-of-business processes | |
Providing the ability to search and navigate through data of other systems | |
Collaborative Site | Using a SharePoint site to represent an instance of a structured process |
Application-Generated Tasks and Notifications | Consolidating task requests from systems into Microsoft Outlook |
You've just read about the need for these solutions, the technologies we have access to, and how we may have built them in the past. Now let's focus on the information-worker problems we will solve in this book. We've designed each solution not to rely on code presented in another chapter. Therefore, you will be able to read these chapters in any order depending on how appealing you find the problems.
Chapter 5: Beyond the Spreadsheet—Organizations often have large sets of spreadsheets. Some of these spreadsheets get dedicated teams to update them and distribute them on a regular basis. Other spreadsheets just get lost on file shares. In this chapter, we show you how to breathe new life into to Excel spreadsheets making them more interactive with automatically up to date data. This will include automatically generating new spreadsheets with data, providing an effective way to navigate a large number of data visualizations, and new methods of collaborating on portions of the spreadsheet.
Related OBA patterns: Document Integration, Composite User Interface, Discovery Navigation
Chapter 6: Merging SharePoint List Data into Word Documents—Organizations often have sets of document templates that are used throughout their enterprise. It is not unusual for several of these templates to share common data elements. At the same time, the list structures in SharePoint sites are increasingly being used to store records of data that might have previously been in local spreadsheets or Access databases. In this chapter we show you how to leverage the Open XML file format to insert a list item into a Word document so that its fields are displayed in designated locations.
Related OBA pattern: Document Integration
Chapter 7: Automating Document Assembly—By storing a document in a SharePoint library, users are able to collaborate and benefit from features such as versioning, metadata, and security. What if an organization has a document consisting of several fragments that need to be completed by different users concurrently and go through different approval processes or processing? In this chapter we will build a proposal tool that enables a user to search the SharePoint profile data to insert requests for résumés When the proposal is saved, each résumé request becomes an assigned task for the corresponding employee. When each one completes the task and attaches his résumé, it is merged back into the larger proposal document. We will also show how you can then take the final proposal and automatically convert it to a PDF on the server.
Related OBA patterns: Document Integration, Discovery Navigation, Office Application as a Reach Channel, Application Generated Tasks and Notifications
Chapter 8: Extending PowerPoint to Build a Presentation Based on Site Content—SharePoint Foundation supports collaboration for a particular team of users or a business process by providing team sites. Often, the users involved with such processes have to present their progress to the organization or to upper management. In this chapter, we will extend Microsoft PowerPoint with a wizard that allows the user to have presentation slides containing site content inserted into their presentation automatically.
Related OBA patterns: Office Applications as a Reach Channel, Document Integration, Composite User Interface
Chapter 9: Building a Presentation Server-Side within a Web Part—When an organization uses a team site to represent an instance of a business process, it often has to report the status of that process in the form of a presentation. That presentation usually includes content from the lists within the site. For types of presentations that happen frequently, the organization has likely adopted a template that is always used and orders the slides and information in a specific manner. In this chapter, we will build a web part that takes a PowerPoint template and populates the slides with content from a SharePoint site. This all happens on the server with a simple click of a button.
Related OBA pattern: Document Integration
Chapter 10: Surfacing Line-of-Business Data in Outlook—Information workers are often put into an environment where they need several tools that contain silos of duplicate data. They often have to jump in and out of these tools, copying and pasting data from one screen to the next. In this chapter we will show you how to leverage the Business Connectivity Services (BCS) functionality of Microsoft SharePoint Server to seamlessly integrate a line-of-business system with SharePoint. This will allow the data to be used in web parts and columns of lists, and will support search indexing. We will also show you how to extend this integration into Microsoft Outlook, providing the end-user with the external data in a familiar tool. We will then extend Outlook's functionality so that it provides single place for decision-making and acting on the data. This customization will include an Excel chart in the Outlook form for visualizing the data that will be generated by leveraging SharePoint's Excel Services functionality. The customization will also enable the user to create follow-up tasks from the Outlook form.
Related OBA patterns: Office Applications as a Reach Channel, Discovery Navigation, Composite User Interface, Application-Generated Tasks and Notifications
Chapter 11: Site Provisioning Workflows—The ability for a user to self-provision a site or site collection is one of the more powerful enabling features of SharePoint. It is also one of the most frequent features that organizations try to insert governance policies. In this chapter, we show you how an organization can utilize workflows to add controls to the site creation process. We will start with envisioning a process as a flow diagram in Visio and then import it into SharePoint Designer where the workflow will be constructed. We will also show you how you can build new custom workflow actions for SharePoint Designer in Visual Studio. Once the workflow is complete, we will take the solution full-circle and use a Visio diagram to visualize the status of the workflow.
Related OBA patterns: Office Applications as a Reach Channel, Complementary Document Workflow, Application-Generated Tasks and Notifications
Chapter 12: Rapid SharePoint Application Development with Access—Microsoft Access has always been an excellent Rapid Application Development (RAD) tool for building desktop data-centric applications, but it has been challenged when the need arises to move the application to the Web or to support a shared multi-user environment. With the release of Office 2010, the combination of SharePoint, Access, and Access Services now provides the ability to publish an Access database application to the Web. In this chapter, we show how, with Access, you can quickly create a SharePoint site for tracking assets.
Related OBA pattern: Collaborative Site
Chapter 13: Using Visio Services to Visualize Data—Visio has long been used by information workers as a canvas to create drawings that represent a wide range of ideas and systems. However, after implementation, the Visio drawing usually gets discarded. In this chapter, we show you how to breathe more life and achieve even greater value from Visio drawings by using them for data visualization. We will show you how to connect Visio drawings to information stored across disparate repositories, how to conditionally format a drawing based on values found there, and how to share the result with a wide audience through SharePoint's Visio Services feature.
Related OBA patterns: Composite User Interface, Discovery Navigation
Chapter 14: Building Mashups—Mashups are a Web 2.0 technique of taking data from multiple sources a combining them into a single presentation. The result provides greater insight than viewing each of the data sources individually. In this chapter, we use Bing Maps as the surface for the mashup. We then bring together data from a SharePoint list, a KML file, and a geocoded RSS feed all onto the surface of the map. This solution also includes an extension of SharePoint's out-of-the-box contact list so that when new items are added, their address is automatically geocoded.
Related OBA patterns: Discovery Navigation, Composite User Interface
Finally, Chapter 15 entitled Realizing the Vision will sum things up revisiting the importance of the solutions.
There are many options for a development environment and through the course of writing this book we used almost all of them at one point in time. When we first started, we relied on a virtualized environment. This gives the developer a sandbox to implement the solutions presented in the book. Because Microsoft SharePoint Server 2010 is a 64-bit application, we chose to host this virtual environment on a Windows Server 2008 machine with its Hyper-V virtualization features. We allocated 5GB of RAM to the machine and used an external E-SATA drive to host the virtual disk independent of the host machine. This does create quite the hardware requirement. As the Office 2010 and SharePoint 2010 bits neared release, we simplified our environment by installing SharePoint 2010 directly onto a 64-bit workstation using Windows 7 (could also use Windows Vista). This technique is discussed in Chapter 3. This is also why you see several different styles of Urls in the code references.
The reason we used a variety of approaches is that we began writing this book well before the final release of Office and SharePoint 2010, which meant we were constantly loading new versions and juggling multiple images. Hyper-V in Windows Server 2008 simplified the management of all of this. If you are going to match the Hyper-V configuration, you should have a recent machine that supports virtualization with about 8GB of RAM, and an external drive for the virtual machines is recommended. The virtual machine was our domain controller and included Microsoft SQL Server 2008. The virtual machine ran Microsoft SharePoint Server 2010 with its enterprise features enabled. This was also our development environment, and Microsoft Office Enterprise 2010, Microsoft SharePoint Designer 2010, and Microsoft Visual Studio 2010 Ultimate were also installed. The Windows 7 environment was similar in that it contained Office, SharePoint, Visual Studio, and SQL Server, but we used the domain our laptop was already joined to. In SharePoint, we used a single web application to host the intranet and enabled all of the shared service applications. In addition to these core products, we installed several SDKs, toolkits, and the like, including:
SharePoint Server 2010 SDK
The .NET Framework (versions 2.0, 3.5 SP1, and 4.0)
All SharePoint 2010 prerequisites (installed as part of the SharePoint installation process)
Word Content Control Toolkit from Codeplex -
http://www.codeplex.com/dbeOpen XML SDK 2.0 March 2010 -
http://msdn.microsoft.com/en-us/library/bb448854(office.14).aspx