The Office Developer Conference finished on Wednesday and it was unbelievable! Jay Paulus, Director of the Office Platform Strategy team kicked it off to a great start.
Following that, Bill Gates talked saying: "Making Office into a platform is very important to us...Office will be taking on a very ambitious agenda in the future...Our investment in the Office platform today is greater than it's ever been." I think that was proven by some pretty great platform demos, especially this one by FedEx. He talked quite a bit about S+S as well. My team was lucky enough to meet Bill Gates, shake his hand and get our picture taken with him! Check it out: I'm the one right beside him in the glasses:
Kurt DelBene, Senior Corporate Vice President of the Office Business Platform Group, was next and talked about how SharePoint is the glue which ties people, processes and data together, what that means to developers with the investments in the platform, tooling with Visual Studio 2008 and SharePoint Designer as well as new resources for developers and architects. He announced the BizTalk Adapter Pack RTM, VS extensions for WSS v 1.1. There were also some great demos of OBAs, VS 2008 with VSTO and CRM Live.
There were also a great line-up of sessions including the "un-conference" where people could suggest anything they wanted to hear about, create their own sessions. Topics included things like "How to handle paper documents in SharePoint", "Using OBA for automating ISO 9000 document approval process", and "Designing a SharePoint Intranet - Developing the Information Architecture".
Needless to say, it was awesome.
There were several resources announced at the Office Developer Conference on Monday that if you are a developer or architect, are just going to knock your socks off.
The great line-up consists of:
- OBA Sample Application Kits for SAP and PeopleSoft
- OBA Platform Scenarios for Customer Service Request Management and Sales Proposal Management
- Financial Services OBA Component Library
- OBA RAP for Loan Origination
- OBA Composition Reference Toolkit
OBA Sample Application Kits for SAP and PeopleSoft
These Kits are comprised of two new exciting pieces of guidance for Office developers: the OBA Sample Application Kit for SAP v2 and a “Sneak Peak” of the OBA Sample Application Kit for PeopleSoft. They highlight Office 2007 and SharePoint with Visual Studio 2008 Professional and Visual Studio Tools for Office v 3.0. Each kit shows a model Office Business Application but specifically, the OBA for PeopleSoft shows a Human Resources Recruiting scenario and the OBA for SAP shows a Business Travel Package Management scenario.
The Kit for SAP includes:
- Sample code in Visual Studio 2008 solution files
- Installation guidance
- Whitepaper
The Sneak Peak of the Kit for PeopleSoft includes
- A white paper showing a sneak peek of the Sample Application Kit for PeopleSoft
All attendees of the Office Developer Conference received a CD of the kits and the download will be coming soon.
OBA Platform Scenarios
Two important scenarios, Customer Service Request Management and Sales Proposal Document Management, to give Office developers, architects and the sales field the guidance they need to think about the Office platform in terms of collaborative, BI-driven enterprise applications. Both scenarios are built on Office 2007 and SharePoint using Visual Studio 2008 Professional with Visual Studio Tools for Office v 3.0 making use of features such as Open XML, Excel Services, Enterprise Search, the Business Data Catalog, UI customization, Forms Services and more. Each OBA Platform Scenario shows a model Office Business Application which includes the following deliverables:
- White paper
- Sample code in Visual Studio 2008 solution files
- Installation guidance
- MSI’s
- Click-through demos
Financial Services OBA Component Library
The Financial Services(FS) OBA Component Library is large set of OBA components that aim to solve industry concerns in the Banking, Insurance and Capital Market verticals. All OBA components are based on both common financial schemas and standard web service protocols. These production grade OBA components accelerate both custom development and partner purchased solutions as they are in line with FS business process payloads. The Library consists of:
• Architecture Guidance
• Web Casts
• How To Articles
• Scenario based HOLs
• MSDN Virtual Lab
Available as a download on the MSDN Financial Services Portal
OBA RAP for Loan Origination
This OBA Reference Application Pack (RAP) for Loan Origination Systems in Financial Services is a reusable loan architecture factory. The goals of this RAP are to simulate bridging the gap between disparate business capabilities across a heterogeneous banking environment, centralize business rules decoupled with computation logic, and a scalable and robust lending platform. It contains the following pieces of guidance:
ú Architecture Guidance – Whitepapers educating architects how to design, build, deploy OBA’s for LOS.
ú Building Sustainable Banking Architectures
ú Architecting Enterprise Loan Orchestrations and Workflows
ú Architecting Regulatory Complaint Banking Solutions
ú Rich Media – Web Casts and Pod Casts on Reference Architecture
ú Hands on Lab – Scripted application scenario, self paced online training
OBA Composition Reference Toolkit
The OBA Composition Reference Toolkit brings to surface the underlying composition capabilities of the 2007 Microsoft Office system. It integrates seamlessly with the Microsoft Office 2007 System to enable a prescriptive application composition experience for Information Workers to build and deploy Office Business Applications (OBAs) using semantically related OBA Components like Workflows, SharePoint document libraries and lists, SharePoint Pages, Web Parts, VSTO Office Client Add-ins, BI Reports, BI Dashboards etc. The toolkit comprises an OBA Composition Server and an OBA Composer. The OBA Composition Server implements metadata and provisioning services to enable cataloging OBA components, defining semantic relationships between components, defining the bindings between components and LOB systems, and to support deploying OBAs composed by users. The OBA Composer is a WPF application that consumes and uses the services of the OBA Composition Server to enable a rich client and prescriptive user experience for composing and deploying OBAs.
Well I haven't written in a while because I've been deep in preparation for the Office Developer Conference which I personally think is going to be a blast! If you guys haven't signed, you better soon, because it's going to be an event you definitely don't want to miss. We have:
- An incredible set of keynotes by senior Microsoft executives such as Bill Gates and Kurt DelBene
- 5 technical tracks, ~90 breakout sessions and hands-on labs.
- An Executive Track where analysts, Microsoft and industry executives will get together to learn the how and why of Office applications and the competitive advantage offered by them.
The tracks are unique in that they are really on the cutting edge of enterprise technologies and forward thinking. Take a look for yourself:
Check out the ODC Blog for the latest updates. I'll be there doing a session on Building S+S Based Solutions on Office 2007: Integrating Web Services into your Office Business Applications. It'll be based on a solution I wrote about in a book I co-authored called Programming Microsoft Office Business Applications due out in the summer of '08.
On another exciting note, the book I co-authored with Steve Fox, Rob Barker, Adam Buenz, Bhusan Nene, John Holliday and Karthik Ravindran has been published! It's called 6 Microsoft® Office Business Applications for Office SharePoint® Server 2007 and it's a great book written by MS personnel and MVPs showing you how to write OBAs with expert guidance. Pick it up!
Collection 6071: Developing Solutions with Microsoft Office SharePoint Server 2007 and Visual Studio 2005
In this online collection composed of six courses, you will learn how to develop customized Microsoft Office SharePoint Server 2007 solutions. Topics covered within the collection include Enterprise Content Management, Single-Sign On, and InfoPath and Excel services. Additionally, the courses will cover the Business Data Catalog, and Search and Audience Targeting features. These courses will help you prepare for the 70-542 TS: Microsoft Office SharePoint Server 2007 exam.
This offer includes the following:
E-Learning
Course 6072: Managing Enterprise Content by using Microsoft® Office SharePoint® Server 2007 Course 6073: Creating Business Intelligence Solutions by using Office SharePoint® Server 2007 Course 6074: Integrating Business Data by using Microsoft® Office SharePoint® Server 2007 Course 6075: Accessing Application Platform Services by Using Office SharePoint® Server 2007 Course 6076: Searching Data by Using Microsoft® Office SharePoint® Server 2007 Course 6077: Personalizing a Portal by Using Microsoft® Office SharePoint® Server 2007
What's the single best conference to go to where you'll get up to speed on OBAs and Office 2007 products and technologies?
Microsoft Office System
Developer Conference 2008
Feb 10-13 2008
San Jose Convention Center
San Jose, California
This conference is made for developers and architects who build solutions on the Office platform. For more info, check out www.odc2008.com.
Hope to see you all there!
There is a lot of really powerful integration between SAP and MOSS and the awareness about this is really, for some reason, quite low. We've talked about integrating LOB data into SharePoint with the Business Data Catalog but there are some specific capabilities to SAP when brought together with SharePoint that make an enterprise solution rich, user-friendly, and seamless.
To give you a quick overview of the BDC, it's a way to bring LOB data into SharePoint without writing any code. You just define your entities with an Application Definition File (ADF – of type XML file) which you upload into the SharePoint Central Administration and then reuse throughout your portal as a shared service. You can then surface that LOB data as SharePoint lists, web parts, enterprise search, lists, user profiles and custom applications. It uses either SOAP to connect to the LOB system if it's a traditional one like SAP, or ADO.NET if it's a database system like SQL. In my previous blog post, I mentioned that the latest version of the SDK made available a tool for authoring the ADF file so that you no longer have to work with straight XML.
How does this register with the SAP web services? The ADF corresponds to the WSDL file of the SAP web service and it must also reference a Single Sign-On application that will manage the credentials of the users. The ADF is then imported into the BDC and once the application is registered, the data can be surfaced in the ways I mentioned above, displaying data from the backend SAP system.
So what's so special about SharePoint integration with SAP? First of all, SharePoint has special page redisplay so you can display SAP data inside WebParts residing in SharePoint or custom ASP pages. iViews in SAP are hosted in WSS iView WebParts.
One application here would be to show employees in their mySites a view of pending SAP expenses reports.
A few other integration options:
- You can use WSRP if you want to create a custom look and feel to your view. It's preferable to URL wrapping since it's a true web service and not simply another system's HTML.
- You can display entire BSPs in web parts. HTTP or HTTPS is used to access the application across the network so firewalls and proxy servers can be used. SAP portal is not required to execute BSPs.
- Accessing SAP data using services and bringing it into WSS or the client (e.g. through Enterprise Search, lists, in the client in a custom task pane or custom apps)
- Surfacing SAP data for analysis – this involves exporting SAP data into a warehouse and then consuming the warehouse using Excel pivotCharts, Excel Services and dashboards
If you'd like to look into a few more resources on this topic, check out the following:
Resources for Interoperability with MOSS
White paper on Integrating MOSS and SAP
White paper on Integrating SAP BSP into SharePoint
White paper on How to integrate WSS and Exchange into SAP Enterprise Portal
The latest and greatest MOSS SDK release just went LIVE with an exciting new tool: The BDC Application Definition Editor! This tool abstracts the way you create entities in the Application Definition File by giving you an easy to use UI.
Features include:
- Underlying XML is abstracted by the design surface and properties window
- Drag and drop web methods, tables, or views to create line of business (LOB) connections.
- Entities and methods are created automatically from database metadata and WSDLs.
- Additional method instances can be added to further enhance the DB or WS connection.
- Method instances can be tested from within the tool, enabling incremental development of LOB connections
Currently, writing an application definition to connect the BDC to a LOB system is a manual process. This requires an understanding of both how the LOB system is configured and what must be included in the XML to satisfy the BDC. Having a tool to simplify this process not only lowers the initial knowledge threshold for administering the BDC, it also lessens the required work of the user (such as testing, making modifications, etc.).
The tool has been designed to assist in the lifecycle management for Application Definition files. It enables searching over databases and web service-based repositories, as well as the connection for BDC web parts in MOSS.
Highlights:
- Tool supports Databases (SQL, Oracle, OLEDB, and ODBC) and Web Services
- Drag and drop design surface for selecting DB tables or web methods
- Metadata is automatically extracted from Databases by dragging and dropping tables
- Web Services require a few additional steps to completely configure the connection
- Users can import and export Application Definition XML files
- Users are able to test method instances incrementally from within the tool
- The tool is not required to run on a Web Front-End
- Associations are created automatically when foreign keys are selected; they can also be created easily for web services by adding an Association method instance
What else is included in the SDK?
New Tools Included with the MOSS SDK:
We rounded out our tool set in this release to include developer tools and samples for the following areas of MOSS development. New tools and samples are in bold.
Business Data Catalog Samples and Utilities:
Microsoft Business Data Catalog Definition Editor
Sample Pluggable SSO Provider
WSHelloWorld Web Service
WSOrders Web Service
Excel Services User Defined Function Sample
WSOrders Custom Proxy Sample
Amazon Web Service Sample
AdventureWorks Metadata Samples
SAP Sample
Document Management and Content Processing Samples:
Comment Scrub Document Converter
Term Replacement Document Inspector
Search Samples:
Sample Protocol Handler
Custom Content Source
Records Management and Policy Samples:
De-Duplication Router
Document Integrity Verifier
Records Center Web Service Console Application
Search, Collect, and Hold Tool
Sample Custom Barcode Generator
IRM Document Protector
Workflow Samples:
Custom Workflow Report Query Generator
Custom Workflow Report XLSX Injector
Visual Studio Workflow Templates
Enterprise Content Management Workflow Activities
List Item Activities
Hello World Sequential Workflow
State Based Approval Workflow
Modification Workflow
Replication and Contact Selector Workflow
Intersystem Purchase Order
Confidential Approval Workflow
Group Approval Workflow
Approval Workflow Sample
Multi-Stage Workflow
Server-side Collect Signatures Workflow
The white paper and code sample for Creating a Custom User Site Provisioning Solution with Microsoft Office SharePoint Server 2007, written by Sonya Zidek, is now live and up for grabs!
Here's a short summary:
This white paper examines some of the new capabilities that are built-in to the Microsoft Office SharePoint Server 2007 Enterprise Edition, which enable business process automation and simplify the business process. This white paper demonstrates the tight integration between the MOSS portal, including InfoPath Forms Services and the Business Data Catalog, as well as the various other Office System 2007 applications and features, including InfoPath 2007 and SharePoint Designer 2007. In addition, this solution illustrates how programmatically accessing and manipulating various MOSS components, such as the Member group and user permissions, as well as the BDC and legacy LOB data sources with the SharePoint APIs that are provided out-of-the-box, enables this type of business process automation. The automation was developed primarily using Visual Studio Tools for Office 2007 Second Edition and the Visual Studio 2005 Extensions for Windows Workflow Foundation. Additionally, traditional .NET console and Windows Forms applications can be developed to make use of the SharePoint and Office System 2007 APIs that will also facilitate the automation and simplification of business processes within the enterprise.
Please note that while the download of the code sample and white paper is now available, the article is still on its way and won't be available for another week or so.
I know I'm a little late announcing this, but I still wanted to get this out there. There are some great new Virtual Hands-On Labs available on MSDN, especially some really cool new VSTO ones:
Speaking of VSTO, I recently did a podcast for the VSTO blog with Steve Fox, a Program Manager on the VSTO team and fellow Canadian. Take a listen while we talk about the What and Why of OBAs, Office as a development platform, and an example of a solution that we worked on together.
Over the past few weeks while enjoying the summer weather here in Seattle (finally!), I've been busy fly fishing and preparing for a new puppy. I'm not really good at either and so I've been spending a lot of time looking through resources trying to get better. This has got me thinking about the next blog post I should write (ironically enoughJ). There's a lot to learn with all of the new products, servers and services in Office 2007. In fact, this is our biggest release of Office ever. So to help you all out, I thought I'd share my favorites:
Microsoft Office Interactive Developer Map:
Built on WPF, the great part about this is that it is completely interactive. If you want to dive deeper into a product or technology, you just click on the area and you get links to the dev features on MSDN, key namespaces, list of reference documentation, community resources and more. It's really an awesome resource. Here's a screenshot:
And here's an example of what it looks like when you click on Excel Services, for example:
You can download the Interactive Developer Map from here.
OBA Sites:
Right out of the oven, we have www.obaCentral.com and also www.msdn.microsoft.com/oba.
Other Office 2007 Sites:
Office Developer How-To Center – My favorite site to learn how to extend Office 2007 with a variety of tools and technologies. Provides short videos, articles and code snippets.
VSTO 2005 Developer Portal
VSTO 2005 SE Page on the VSTO Developer Portal
VSTO 2005 SE Download Page
Office 2007 SDK
Office SharePoint Server 2007 SDK
Office Developer Webcasts
How Do I? Screencasts
XML File Formats
Ribbon User Interface
Office Learning Portal: Free training, e-learning, books and skills assessments
Blogs:
Building Office Business Applications:
http://blogs.msdn.com/oba
http://blogs.msdn.com/rbarker/
http://blogs.msdn.com/javeds/
VS extensions for SharePoint and SharePoint Designer:
http://blogs.msdn.com/alexma/
Visual Studio Tools for Office: http://blogs.msdn.com/vsto2/
Open XML File Formats:
http://blogs.msdn.com/brian_jones/
http://blogs.msdn.com/dmahugh/
http://blogs.infosupport.com/wouterv/
SharePoint Products and Technologies: http://blogs.msdn.com/sharepoint/
http://blogs.msdn.com/mikefitz/
http://www.u2u.info/Blogs/Patrick/default.aspx
Outlook Programmability: http://blogs.msdn.com/rgregg/
Access: http://blogs.msdn.com/access/
InfoPath: http://blogs.msdn.com/InfoPath/
Excel: http://blogs.msdn.com/excel/
New User Interface: http://blogs.msdn.com/jensenh/
Office Developer: http://blogs.msdn.com/erikaehrli/
Books:
Inside Microsoft Office SharePoint Server 2007 by Patrick Tisseghem
Inside Microsoft Windows SharePoint Services 3.0 by Ted Pattison
Microsoft SharePoint: Building Office 2007 Solutions in C# 2005 by Scot Hillier
VSTO for Mere Mortals by Kathleen McGrath and Paul Stubbs
Pro SharePoint Solution Development: Combining .NET, SharePoint and Office 2007 by Ed Hild
Good luck researching and let me know how it goes.
Do you love the Information Bridge Framework (IBF) but feel at a loss now that it is not supported by Office 2007? I've been working with Scot Hillier and Bhushan Nene to provide you with a white paper complete with sample code on how you can use the Business Data Catalog and Visual Studio Tools for Office SE to build custom applications, surfacing it all up in the client through smart tags and custom UI. Scot and Bhushan have created a great white paper and solution to show you how you can build solutions on Office 2007 and still surface your LOB data to allow users to work within context with the look of feel of IBF.
Using the Business Data Catalog and Smart Tags with the 2007 Office System
This code sample and white paper walks through a custom solution that integrates LOB data with the 2007 Microsoft Office system to support the preparation of documents within Microsoft Office Word 2007. The solution presented uses product information contained within a Microsoft SQL Server 2005 database to support the creation of a quote sheet.
In this scenario, a manufacturing sales representative uses a solution based on Office Word 2007, Microsoft Visual Studio 2005 Tools for Office Second Edition, and the business data catalog feature of Microsoft Office SharePoint Server 2007 Enterprise Edition to access product information and create a new quote sheet. The solution allows the sales representative to easily browse product information from within Office Word and insert the information into a quote document template. Furthermore, the solution uses smart tags to identify product numbers within the quote and display detailed information contained in the business data catalog through Office SharePoint Server 2007.
Enjoy!
You've probably been hearing about new Office 2007 file formats and that the fact that they are based on Open XML is a great benefit. But why? Why should you as developers care? Are they nothing more than just a compressed file format? Actually, they're much, much, more than that. They're an incredibly powerful building block that you can base your Office Business Applications on. Let me explain in more detail…
First of all, Word, Excel and PowerPoint 2007 files now act as containers since they are actually compressed zip containers (just try changing the extension to a .zip and you'll know what I mean). To an end user, the file still looks like a single item but to the developer, the file is a package of parts, segmented in a logical tree structure, tied together by relationships which you can navigate through. No longer do you have the black box of a binary file from previous file formats. So knowing this, what are some of the benefits?
Interoperability – Because of the open standard of the ECMA Open XML file formats, you can do things like generate files from Office 2007 documents on a non-Microsoft platform like Linux. Take for example a partner called Sonata, who created an awesome solution for the Linux platform where they took an Office 2007 Word document and then using XSLT, converted it to HTML and published it.
Mitigation of File
Corruption – Since we now have a segmented architecture this means that if a part gets corrupted, the other parts of the package should still be safe. For example, if your style part is corrupted, you will still be able to open your document, it just won't look as nice. Also, corruption tends to occur as a result of truncation. Because we are no longer working with a black box, you can mitigate data loss by putting the most important information at the top of the XML files in the package parts.
Security – Security is greatly improved as a result of the segmented architecture. Macros now have their own parts within the package and it is easy to separate this portion from the content to better manager security issues. This is why we have the new file formats such as .xlsm, .pptm, .docm, as well as the template macro-enabled versions. All these have macros contained within them.
Digital Signatures –Now you have more power over what you sign within a document. You can digitally sign the packages using x.509 certificates, and you can sign all parts of the package, including even the digital signatures themselves. Imagine that as part of a workflow you were to sign that a certain portion of content has changed or has not changed, or perhaps you would want to digitally sign just the macro part. Some very interesting scenarios here.
Developer Scenarios
Talking about scenarios, let's get into some developer scenarios. First of all, I would suggest that you couple these scenarios with a workflow in MOSS so that the solution drives itself and stays streamlined within the business framework.
Styling Content:
Because styles are in their separate parts, you can just go into the Style package part and manipulate it. Imagine you are an enterprise which has decided to change your logo and you have hundreds of thousands or documents in your repository. Since the files are no longer binary black boxes, you can now go in and easily change out the logo of all the documents since all of the Office documents have a common structure.
Content Inspection:
- Remove confidential information, tracked changes or metadata from outbound documents.
- Remove macros, inappropriate language, or other content from inbound documents.
Consuming Documents:
- One example is where a user creates an expense report where the data is loaded into a back-end system on the server.
Document Assembly:
This is the most common Open XML development scenario. Imagine the following:
- Creating sales reports from financial and forecast data stored in a CRM system through the Business Data Catalog in MOSS
- Sales Forecasting scenario where an executive report in Word format is generated server-side on the fly
- The user goes to a web application and indicates a number of fragments, parts that he or she wants to see assembled in a document
- Create documents from the data pulled from Forms Server (online InfoPath form in MOSS)
- Create pitchbooks from Slide Server (PowerPoint slide libraries in MOSS)
Note : In our business layer, we typically I suggest using some kind of template to give us a headstart.
Custom XML Markup:
- Tagging document content with custom semantics for processing by a back-end system.
- This allows the meaning of the data to be defined separately from its presentation, allowing for more robust solutions and simpler programming.
- Note that custom tagging can be done with or without an associated schema, and with or without a custom XML data store.
- It's very valuable to be able to go and mark up a document with XML that's in your namespace so you can really identify what the meaningful regions of that document are. This way you can act on that data so that you can actually go and get the information in or out.
Document Merge:
- Aggregate data from several documents into one
- Merge two independent copies into one
Document Validation and Debugging:
- Validate structure of parts
- Validate XML against schema and for well-formedness
- Validate document by type or class
VSTO and Open XML
VSTO abstracts working with Open XML so that you never have to go through the XML and navigate through the tree structure. It makes debugging and deployment of your solution on the server much easier. With "Orcas", the next version of Visual Studio Tools for Office, there is support for the Office 2007 file formats with the ServerDocument class which allows for manipulation of the cached data in the file in an abstracted way. Rather than writing the dealing with straight XML you can work with the business objects to manipulate the data within Office 2007 files for both your client side and server side applications, where debugging and deployment is made simpler and more integrated.
Resources
This past week at TechEd Orlando, Microsoft announced the Open XML SDK CTP. This provides classes which offer higher levels of abstraction for working with Open XML, code samples, How-To articles. For a great overview of this, check out the Channel9 video with Chris Bryant.
There are some awesome How-To's on the Office Developer How-To Center with videos, articles and code samples. More to come as well.
Of course there is the non-Microsoft www.OpenXMLDeveloper.org which is a wealth of information from a vast Open XML community.
For you Java developers, check out the article on Using Java to crack Office 2007.
And here are some others:
MSDN XML in Office Developer Portal
How To Manipulate Office Open XML File Formats
Word 2007 Content Control Toolkit
Blogs:
http://blogs.msdn.com/brian_jones/
http://blogs.msdn.com/dmahugh/
http://blogs.infosupport.com/wouterv/
http://blogs.msdn.com/kevinboske
http://blogs.msdn.com/xps/
Yes! There is such a thing and it's an actual, server-grade, supported product! It's part of Microsoft Office SharePoint Server 2007 and it's the published version of an Excel spreadsheet which gives you the ability to share business intelligence around the enterprise in a safe, compliant way.
Why Did We Create Excel Services?
Multiple Versions of the Truth
- E-mailing your spreadsheets around quickly gets out of control: one person changes some data and forwards it on to someone else, this becomes a different copy, another person does the same
- individuals see everything that is in the spreadsheet - external data connections, all sheets, tables, pivot charts
Incorporating Excel Models into Applications often requires re-coding
- Algorithms get prototyped in Excel (the "programming language of the business world"), then re-coded in real code
- Duplication of work, the developer needs to understand the business model
- When the analyst changes the model, the developer needs to go back to the code and maintain it
Excel is a good standalone tool but does not play in the BI arena
Architecture of Excel Services
Excel Services is all built on Office SharePoint Server 2007. In fact, the spreadsheet just resides in a document library. There are two components to its Front End, the Excel Web Access which is the UI and the Excel Web Services which is the Programmatic Access to the Excel Calculation Services, or the engine, of Excel Services. The Excel Web Access is just a Web Part in SharePoint which displays your spreadsheet (only the parts you decide to publish, attacking problem #1 stated above) and since it's strictly HTML and JavaScript, end users do not need to install any activeX component. There is complete fidelity in the calculation and you can get lost switching between the client and the server, that's how exact the conditional formats and pivotTables look. Nevertheless, end users do not need to install the client and that's why it's zero footprint.
Excel Web Services are offered so that we can solve problem #2 stated above. In that scenario, the developer no longer needs to know anything about the business model in the spreadsheet. All they need to know is the named range so that they can access it using the web service method. If the model changes, no update to the code is needed. This helps companies immensely in terms of protecting their data.
On the back end we have Excel Calculation Services which is the main engine of Excel Services, the part which opens and calculates the spreadsheets. We've also created User Defined Functions or UDFs which are used to extend the calculation and data import capabilities of Excel. You can create UDFs to:
- Call custom mathematical functions
- Call Web services
- Get data from unsupported sources
Excel Services still doesn't support certain scenarios such as bringing in data from SAP or Access. You need to create UDFs to bring that data up into Excel Services. The UDF code is deployed on the application server.
What kinds of Office Business Applications can you build with Excel Services?
Rob Barker did a cool demo once which took him only 7 minutes but was an Office Business Application involving Excel Services, ribbon customization, and a custom task pane. Just like I described in some of my previous posts, you can create a custom ribbon from which you can have a button which launches a custom task pane, also created in VSTO SE. In another post I wrote about how you can hook these up so that clicking on a button in the ribbon launches a custom task pane. The custom task pane is nothing more than a user control so you can throw anything you want on there. Imagine that you have some LOB data, such as some CRM data, connected to a spreadsheet which you've published up to Excel Services. The whole power of OBAs is to bring complex LOB applications right to the fingertips of end users who are not comfortable using LOB applications such as SAP or Siebel. So in the scenario we are describing here, imagine that you are always filling out documents with vendor information, PO numbers, budget amounts, etc. Your task pane could have a drop down which is populated with all the named ranges in the spreadsheet. Below that, you would have a list box so that selecting an item in the drop down would populate the list box. For example, let's say you selected "Q4_vendors" in the drop down. The Excel services web service methods would first open the workbook, return a session ID, calculates and refreshes the workbook, retrieves all the values in the named range and places it in the list box. The end user can then just choose to insert into the CRM results into their Word document seamlessly; they never have to leave the application that they are comfortable.
Since I've last blogged, we've released 7 more How-To's on the Office Developer How-To Center giving you a total of 9 code samples with accompanying videos, articles, and resources. The topics range from the new Open XML file formats presented by MVP Ken Getz, customizing the Office 2007 fluent user interface, as well as several topics on Word such as working with Content Controls. In the next few months, we have many more coming in the areas of Microsoft Office SharePoint Server 2007 (including Enterprise Search, the Business Data Catalog and Excel Services), Excel, Access, InfoPath, Windows SharePoint Services, some more Word as well as Visual Studio Tools for Office in areas such as Deployment, configuration and coding.
I've just released a logical architecture diagram of Office 2007 which shows how the pieces all fit together. It's available for download here. (sorry it's cut off a bit, you'll have to download it to see the extensibility options for each of the products below such as SOAP and OM...couldn't quite fit).
In my previous posts, I showed you how to create a custom ribbon and how to create a custom Task Pane using VSTO, but how do you tie these two together? I've created a solution to show you how to do that. It's based on the scenario of a travel agency that books cruises. A custom ribbon called Contoso Travel Agency shows the Top Cruises and from each picture of the cruise in the gallery drop down, the travel agent can bring up a custom task pane populated with all the information about the cruise. The custom task pane is grabbing information from an Access database using scalar queries. Check out the source code here.
TIP: I found that it's difficult to make two projects talk together when you use VSTO. Instead, it's easier when you use one project and instead just add the ribbon files to say, the custom task pane project. Here, I'm doing just that because your custom task pane is usually more involved with perhaps external data connections, scalar queries, web service calls to the Office SharePoint Server Business Data Catalog, and therefore much more involved. It tends to be much easier to add the ribbon component to the custom task pane project than vice versa.
My ribbon.xml file contains a tag for a group called "Top Cruise Ships" which contains a callback called CTPContext. This tag defines that I have a gallery here with a bunch of images and when you click on them, the CTPContext callback is called.
<group
id="groupTravelAgency"
label="Top Cruise Ships">
<gallery
id="Top_Cruises"
showItemLabel="true"
label="Top Cruises"
image="CruiseShip"
columns="3"
rows="2"
size="large"
itemHeight="100"
itemWidth="100"
onAction="CTPContext">
<item
id="Northwind"
image ="Northwind"
label ="Northwind"/>
<item
id="Great_Waves"
image ="Great_Waves"
label ="Great Waves"/>
<item
id="Seashell"
image ="Seashell"
label ="Seashell"/>
<item
id="Clear_Waters"
image ="Clear_Waters"
label ="Clear Waters"/>
<item
id="Open_Oceans"
image ="Open_Oceans"
label ="Open Oceans"/>
<item
id="Angel_Fish"
image ="Angel_Fish"
label ="Angel Fish"/>
</gallery>
</group>
Here's the code for the callback:
public
void CTPContext(Office.IRibbonControl control, string id, int index)
{
Microsoft.Office.Tools.CustomTaskPane ctrlTaskPane =
Test1.Globals.ThisAddIn.CustomTaskPanes[0];
ctrl_CruiseInfo ctrl = (ctrl_CruiseInfo)ctrlTaskPane.Control;
if (id == "Northwind") ctrl.cruise_ship = "Northwind";
else
if (id == "Great_Waves") ctrl.cruise_ship = "Great Waves";
else
if (id == "Seashell") ctrl.cruise_ship = "Seashell";
else
if (id == "Open_Oceans") ctrl.cruise_ship = "Open Oceans";
else
if (id == "Angel_Fish") ctrl.cruise_ship = "Angel Fish";
else
if (id == "Clear_Waters") ctrl.cruise_ship = "Clear Waters";
else ctrl.cruise_ship = "Northwind";
ctrl.LoadCTP(ctrl.cruise_ship.ToString());
ctrlTaskPane.Visible = true;
}
What I'm doing here is I'm setting the taskpane variable in the ribbon class file to the custom task pane instantiated in ThisAddin.cs. This is the critical part of tying together the ribbon and the custom task pane. The rest is just doing hook ups and passing the gallery item ID into the task pane code so that the we can then go back and query the Access database.
I did a webcast on this which involved an Outlook Form Region as well so if you want to see it in action, take a look here.
