So why do you care about Excel Services? Well, since you bought this book, there is a chance your organization uses Excel... more

So why do you care about Excel Services? Well, since you bought this book, there is a chance your organization uses Excel in its day-to-day operation. Who can blame them? Excel is the most popular modeling tool and the most popular database tool. It is so versatile that the same person can use it for both a complex financial model and a simple task list. In the Excel organization, for example, it is not uncommon to use Excel for managing tasks, project milestones, and bug reports. Over the years, Excel has gathered a very large set of features ranging from advanced formatting to advanced data acquisition mechanisms. With Excel 2007, this set of features has been bolstered even more to allow Excel to be a first-class BI (business intelligence) tool.

... less

This chapter gives an overview of the differences between Excel 2007 and Excel Services, what Excel Server can do right out... more

This chapter gives an overview of the differences between Excel 2007 and Excel Services, what Excel Server can do right out of the box, the various topologies available when deploying Excel Services, and a cheat sheet for how to do basic administration of Excel Services.

... less

There are various technical details one needs to understand about Excel Services for the rest of the book to make sense.... more

There are various technical details one needs to understand about Excel Services for the rest of the book to make sense. Even though some of this information may not have an immediate impact on how you use, or even program against, Excel Services, it will help you understand what happens under the hood.

... less

There are three programmability interfaces you can use when you want to extend or work against Excel Services. Each one stands... more

There are three programmability interfaces you can use when you want to extend or work against Excel Services. Each one stands alone, although some can be used together to develop more complex solutions. Because each programmability option is aimed at solving a completely different problem, they require different skill sets. This chapter enumerates these different interfaces and gives a short explanation about what they can be used for. The next chapters in the book will go into more detail about these subjects.

... less

In this chapter, you write your first “Hello World” program using Excel Web Services. You learn how to achieve the following... more

In this chapter, you write your first “Hello World” program using Excel Web Services. You learn how to achieve the following:

• Add a reference to Excel Web Services by using Visual Studio 2005 to gain access to the Excel Services APIs.

• Use the APIs to open a workbook on the server and get back a Session ID.

• Extract values from cells in the workbook and set values into them.

• Use a model inside the workbook by setting values into some cells and getting results from another.

... less

As described in the introduction to this book, programming against Excel Services is generally done through the Excel Services... more

As described in the introduction to this book, programming against Excel Services is generally done through the Excel Services Web Service. This chapter will dive into more details about how to use Excel Services via the API. This will also set the stage for the next project, Excel Web Services Wrapper, creating a library that will help you make better use of Excel Web Services.

... less

In the “Hello World” example from Chapter 5, the Excel Web Services API was imported directly into the project. While there... more

In the “Hello World” example from Chapter 5, the Excel Web Services API was imported directly into the project. While there is nothing wrong with that, it is not the most maintainable way of using it. Here are some of the reasons why using a wrapper around the APIs is a good idea:

• Direct linking — When using direct linking to communicate with Excel Services, the methods that are used will look somewhat different from the generated Web Service proxy. For the same piece of code to work against a directly linked Excel Services library, recompilation will always be needed. More than that, in some cases, even recompilation will not help as the signatures of the methods will be different. It is better to have a mechanism that will allow developers to write the same code whether they are working against the actual SOAP server or if they are using the server via direct linking.

• Tool import differences — VS 2005 imports Web services one way. WSDL.EXE can import it in different ways. Other environments may have still other ways. Keeping the calls consistent is important so that future projects can be written the same way, reducing the time needed to get used to a new way of doing them.

• Multiple projects — As developers start writing more projects for Excel Services, they may need multiple projects to interact with Excel Services. Because Excel Web Services uses a string for the Session ID, this is probably not going to be a problem from the technical sense, but it does mean developers will end up having multiple namespaces each containing the same functionality.

• Snags and “gotchas” — While the Excel Web Services is a relatively simple API, there are a few snags to watch for. For example, one of the most common problems is remembering to call the CloseWorkbook method when you are done with a workbook. Writing a wrapper that will remember to do that for you goes a long way toward writing better and more scalable code.

• Excel Web Services is not object oriented — Due to the nature of SOAP services, they are exposed as a set of functions rather than an object hierarchy. In the case of Excel Web Services, this is not too bad, but abstracting the calls into an object hierarchy can simplify some cases and make the code more maintainable and easier to read.

• Some parameters are rarely used — In the Excel Web Services interface, a few of the parameters that are used to call methods are often ignored. This falls into two categories — parameters that are returned (out parameters) but are usually not inspected and parameters where the caller passes in a value that tells Excel Services to behave in some default manner. An example for the first category is the Status[] parameter that almost all methods contain. It is not really needed in most cases and will actually be empty in quite a few of the methods. The second example is the sheetName parameter in the methods that have the A1 postfix (GetCellA1, SetRangeA1, etc.). In those methods, the sheetName is usually not really useful and in most cases will be passed in as an empty string. Because Web Services do not support overriding function names, easier versions of the calls could not be offered to the developers. Having the library allows us to have overloads that are easier to use.

The Excel Services Library (ESL) was designed to handle these issues and more.

Note

This chapter will walk through the process of creating the ESL, giving you a greater understanding (for those who need it) about how Web services work and how to debug applications using the ESL. While it’s possible to skip this chapter and use the library as it is on the companion CD, it is worthwhile to take a look through the design so that you at the very least know how it works.

... less

Excel Services allows developers to extend the library of cell functions with their own code. This is very similar to XLLs... more

Excel Services allows developers to extend the library of cell functions with their own code. This is very similar to XLLs, automation addins, and VBA work. When a workbook has a formula in it that contains unrecognized names that look like function calls, Excel Services will search for that name in the UDF assemblies that the administrator registered and if the same name is found, Excel Services will try to call into it. As mentioned before, Excel Services does not have an object model — this means that when UDF code is executing, there is very little information it can know about the environment in which it executes. The next chapter will delve more into the limitations of UDFs.

... less

Excel Services gives developers the ability to augment the native library of functions that can be used inside Excel formulas... more

Excel Services gives developers the ability to augment the native library of functions that can be used inside Excel formulas. Such augmenting functions are called UDFs (user defined functions) and can be written using any tool that can create .NET 2.0 assemblies.

... less

Chapter 8 introduced UDFs on the server. At the beginning of the chapter it was shown how the... more

Chapter 8 introduced UDFs on the server. At the beginning of the chapter it was shown how the #NAME error is displayed inside Excel Workbooks. This happens because Excel does not natively support managed UDFs written for the server, which can have a negative impact on people authoring workbooks for the server. Because of this, it is sometimes useful to make server UDFs work on the client.

This chapter discusses two possible ways to add client support to UDFs written for the server. The first way involves adding functionality to the UDF code so that it will be usable on the server. After that, you learn how to develop a generic solution that can be used to utilize server UDFs on the client without any modification to their code. Finally, this chapter will suggest ways to allow UDFs to distinguish when they are executed on the client or on the server.

... less

Excel Web Access (EWA) allows users to view workbooks in a Web browser using plain HTML and ECMA script... more

Excel Web Access (EWA) allows users to view workbooks in a Web browser using plain HTML and ECMA script (JavaScript). The EWA is a Web Part that can be hosted inside a SharePoint Web Part page or inside any ASPX page hosted inside of SharePoint. For the first version of the server, the programmability options of the EWA are very limited. This chapter discusses ways to use the workbook rather than program it. The examples show how to develop a Web Part that supports part to part communication to allow Web Part page designers to write more elaborate pages.

... less

As long as they’ve been around, Web Services have functioned like Distributed Component Object Model... more

As long as they’ve been around, Web Services have functioned like Distributed Component Object Model (DCOM) and Common Object Request Broker Architecture (CORBA), but without their advanced features and without inspiring the fear usually associated with those technologies. Also, Web Services actually work most of the time.

To this point, this book has discussed Web Services in two ways. The first was when it introduced Excel Web Services—the interface that allows callers to use Excel Services programmatically. The second was when it introduced UDFs—one of the examples showed how to consume Web Services inside Excel Services.

This chapter will show how to build a component that can automatically generate Excel Services UDFs such as those described in Chapter 8. For the sake of completion, the code on the book’s web site will also contain an implementation for generating such UDFs so that they are usable in Excel 2007.

The main part of the project will be a library that takes care of the assembly generation. The second part will show a simple WinForms application written using the library (the UI application will not be shown in detail, just the idea behind it).

... less

Excel Services provides a good set of APIs to modify and manage workbooks. When using these APIs, though, the developer ends... more

Excel Services provides a good set of APIs to modify and manage workbooks. When using these APIs, though, the developer ends up working with Excel primitives. Namely, workbooks, sheets, cells, and named ranges. While this is not overly complex, it can be made even easier and more abstract.

This chapter will demonstrate how to develop a solution that will allow users (who are not software developers) with access to an Excel workbook and Excel Services to create a custom Web Service that does not require any Excel Web Services knowledge to use. On top of that, the project developed in the previous chapter will be utilized to bring everything back into Excel client, showing one way of doing Excel/Excel Services integration.

Since there is quite a bit of code in this solution, this chapter will only touch on some of the more important pieces.

... less

The previous chapter showed how any user can create custom Web Services by employing Excel Services as the “programming engine”... more

The previous chapter showed how any user can create custom Web Services by employing Excel Services as the “programming engine” behind the scenes. That’s a very compelling scenario that can give nondevelopers the ability to create models that are easily consumed by developers who may not have any knowledge about Excel Services.

This chapter takes the same idea and pushes it even further. Instead of creating Web Services that require the consumer to be a developer, it provides an RSS interface to workbooks published in Excel Services, allowing any user who has an RSS reader to consume information from Excel Services in a form which is not necessarily the original workbook form.

Note

And in this day and age, what user doesn’t have an RSS reader? Some high-end refrigerators even come with RSS readers built in.

... less

RTD (Real Time Data) was first introduced with Excel 2002, allowing organizations to get fast-updating information into workbooks... more

RTD (Real Time Data) was first introduced with Excel 2002, allowing organizations to get fast-updating information into workbooks without Excel users having to manually update the data. Since the mechanism uses a simple in-process COM object with very little overhead to get the data, it was possible for Excel to get updates hundreds of times a second.

Note

RTD can also be used with DCOM to execute objects in a remote location. The overhead, when compared to database queries for example, is still relatively small in that case.

According to Microsoft documentation, a “Pentium III 500 MHz processor with 128mb of RAM” can have 200 updates a second on a single value. One can just imagine how many updates tomorrow’s multicore machines will be able to achieve in a second!

Instead of the pull model employed by other external data sources such as databases and UDFs, RTD servers were based on a push model. (“push model” was the “Web 2.0” of the late 1990s.) While with databases, the client needed to call out and ask for an update, push models told the client when an update was available, allowing it to ask for the value then and there.

Note

Server is used here in the sense of a server component — not a server machine. Through this chapter, these servers will be referred to as “COM servers” or “RTD servers” to reduce confusion.

There are many scenarios for RTD in Excel. For the most part, organizations have been using them when there’s need to keep an eye on changing data and act according to how worksheet models change because of that.

This chapter discusses an Excel client addin that will let users bring Real Time Data from Excel Services to the client. The scenarios for using Excel Services as an RTD server are very similar to the classic RTD scenarios. This solution allows the callers to have a model calculate on Excel Services and then get the values back from it in real time.

... less

The previous chapter discussed RTD (Real Time Data) in depth and showed a way of creating an RTD server that uses Excel Services... more

The previous chapter discussed RTD (Real Time Data) in depth and showed a way of creating an RTD server that uses Excel Services as the source of its data. This chapter is also related to RTD, but from a different angle.

Since Excel Services does not natively support RTD, and since there are solutions out there that use RTD to bring data into workbooks, it seemed like a good idea to add pseudo-support of RTD to the server.

It is important to understand what this solution is not before going into what it is.

The solution does not magically give Excel Services the ability to have true Real Time Data feeds into the sheet and actively cause refreshes in workbooks that are then updated automatically on the browser. While it may be possible to come close to such a solution, it is outside of the scope of this chapter to do so.

What this chapter does offer is a way of leveraging existing RTD servers and using them to get information into server calculated workbooks without rewriting the code that gets the information.

At the end of the day, it is highly recommended that users rewrite the RTD servers as UDFs for consumption on the server. That’s the only way to guarantee smooth server operation.

... less

Before Excel Services, one scenario for running Excel client on the server was to prepare workbooks out of templates on the... more

Before Excel Services, one scenario for running Excel client on the server was to prepare workbooks out of templates on the server and make those available to users.

With some help, Excel Services can be used in a similar manner, allowing modified workbooks to be downloaded to the client (either as a file via the Save As dialog box or directly into Excel) without actually creating physical files on the server or running Excel on the server.

... less

While Excel Services does not support relational query tables out of the box, it is possible to add a UDF that will make... more

While Excel Services does not support relational query tables out of the box, it is possible to add a UDF that will make some of the functionality available.

With query tables in Excel, it is possible to bring massive amounts of data in raw form directly into the grid. This is different from having the data as part of a PivotTable, which is supported in Excel Services but requires extra steps of postprocessing.

... less

One of the features that Excel supports and Excel Services does not is the External Workbook Reference feature. This allows... more

One of the features that Excel supports and Excel Services does not is the External Workbook Reference feature. This allows an Excel workbook to reference cells in another workbook. The behavior of this feature depends on a number of things, described below in the “Overview” section.

The solution shown in this chapter brings some of the capabilities of this feature to the server. It also adds some capabilities that the Excel feature does not contain.

... less

With SharePoint 2007, Microsoft introduced workflow into SharePoint Services. The workflow is based on the Windows Workflow... more

With SharePoint 2007, Microsoft introduced workflow into SharePoint Services. The workflow is based on the Windows Workflow Foundation (WWF) included in .NET 3.0, adding some of its own capabilities to it.

Workflow allows SharePoint managers to cause various operations to run when certain events happen in the system. Events are either items changing or being added to lists and a manual invocation of a workflow. There are also many operations (or activities) defined in the system that can be used when the event occurs. This can be as simple as adding a task to the site or running custom .NET code.

The solution introduced in this chapter contains two workflows. One for calculating workbooks saved to a document library so that the author does not need to calculate them on his machine. The second workflow saves a series of generated snapshots of a document library for consumption by users.

... less

This chapter consists of multiple projects that can be done with EWA — some require coding; others do not.... more

This chapter consists of multiple projects that can be done with EWA — some require coding; others do not.

... less

Chapter 21 showed how to interact with the Excel Web Part to implement various solutions. Some of the solutions relied solely... more

Chapter 21 showed how to interact with the Excel Web Part to implement various solutions. Some of the solutions relied solely on the SharePoint Web Part page feature and showed how to create a rudimentary application using EWA and some filter Web Parts.

Other solutions showed how to interact with Excel Web Services from IE, using the same session EWA is using to both get information from a workbook (and place it in a combo box) and to set information back to the workbook and have EWA display it.

The project in this chapter shows how to create a filter Web Part that uses an Excel range in a workbook as the data from which users can choose. The filter can be integrated with an EWA placed on the page, or it can be used with any other Web Part that knows how to consume filters.

... less

A software book coming out in 2007 without at least a reference to mashups and Web 2.0 is like a software book coming out... more

A software book coming out in 2007 without at least a reference to mashups and Web 2.0 is like a software book coming out at the mid-nineties without a reference to “design patterns” or “object-oriented programming.”

This chapter uses a lot of the skills shown in the previous chapters and shows how to put them to use to create an HTML/JavaScript application that brings together an Excel model encapsulated in a workbook, a UDF module, API calls, and EWA programming to give HR specialists the ability to easily find employees and partners home addresses on a Virtual Earth map by using their titles as a filter.

... less