Table of Contents
Acknowledgments xi
Introduction xxiii
Introducing Excel Services 1
Introduction to Excel Services 3
Why Use Excel Services 3
The Excel Client Farms 4
Rewriting the Models 4
How Excel Services Comes to the Rescue 5
Excel Services Goals 5
First Goal - 100% Fidelity with Excel 6
Second Goal - Security 6
Third Goal - Robustness and Reliability 6
User and Administrator Cheat Sheet 7
Excel 2007 and Excel Services 7
Object Model Support 7
Addin Model Support 8
Excel Services Out of the Box 8
Excel Web Access 8
Topologies 9
Departmental Deployment 10
Large Scale Deployment 10
Unsupported Features 10
Old File Format 10
VBA 10
Query Tables 11
External Workbooks 11
Real-Time Data 11
Images and Shapes 11
Protection/Encryption 12
Other Unsupported Features 12
Roundtrip-Only Features 12
How to Administer Excel Services 12
Excel Services Settings 13
Trusted File Locations 14
User Defined Function Assemblies 15
Summary 15
Inside Excel Services 17
Session, State, and Workbooks 17
Caches 18
Workbook Caches 18
Data Caches 19
Caching Calculations 19
Summary 20
Programmability Options 21
Excel Web Services 21
Opening and Closing the Workbook 21
Getting Values from the Workbook 22
Writing Values to the Workbook 22
Workbook-level Interaction 22
Getting a Whole Workbook 23
Miscellaneous Methods 23
User Defined Function Assemblies 23
EWA Web Part 23
Summary 23
Excel Web Services 25
Hello World Sample 27
Hello World Overview 27
Preparing the Project and Opening a Workbook 28
Making Sure the File Can Be Accessed by Excel Services 28
Preparing the Project for Use with Excel Services 30
Opening a Workbook on the Server 32
Getting a Cell from the Workbook 34
Writing Values into Cells in the Workbook 35
Using the Model Part of the Workbook 36
Finishing the Sample Project 36
Updating the Model 37
Extras 38
Final Program 38
C# Hello World Program 38
VB.NET Sample Code 40
Summary 41
Excel Web Services Reference 43
Direct Linking 43
Notations Used in the API Reference 43
Session Management Methods 44
OpenWorkbook 44
CloseWorkbook 46
CancelRequest 46
GetSessionInfo 48
Cell Retrieval Methods 49
Common Characteristics for GetXXX Methods 49
GetCell 52
GetCellA1 53
GetRange 54
GetRangeA1 55
Workbook Retrieval 56
GetWorkbook 57
Writing Values into Cells 57
Type and Content Restrictions with Set Methods 57
SetCell 58
SetCellA1 59
SetRange 59
SetRangeA1 60
Workbook Updating Methods 61
Calculate 62
CalculateA1 63
CalculateWorkbook 64
Refresh 64
Support Classes and Enumerations 66
RangeCoordinates 66
Status 66
Exceptions and Errors 66
SoapException 67
Subcode List 68
Summary 70
Building the Excel Services Library (ESL) 71
Project Design 72
Abstraction Classes 72
Namespace 73
Support Classes 74
Method Call Abstraction 76
IExcelServiceWrapper 77
IWrapperCredentials 78
Modifying ExcelServiceSoap 78
Exception Classes 80
ExcelServices Class 80
ExcelServicesType 80
Implementing ExcelServices 80
ExcelServices.Open() 82
ExcelServices.FromSessionId() 83
The Session Class 83
Dispose Code 85
Refresh Functionality 87
GetRangeA1 Functionality 88
Session Class Summary 89
The RangeResult Class 89
Example 90
How RangeResult Works 91
WorkbookData Class 96
Summary 97
User Defined Functions 99
UDF Sample 101
What This Sample Contains 101
Creating a UDF Assembly 101
Required References 102
Adding the UDF Class 103
Adding the First UDF 103
Optional Parameters 108
Getting External Data 109
Using Ranges with UDFs 111
Caching Information 115
Summary 118
UDF Reference 119
History 119
Excel Services UDFs 120
Execution of UDFs 120
UDF Requirements 120
The Assembly Loading Process under the Hood 121
UDF Classes' Lifetime 122
UDF Methods 122
Cell Ranges 127
UDFs and Interop 128
Thread Safety 128
Volatility of UDFs 129
Personal Data UDFs 130
Caching Information in UDF Classes 132
Loading UDFs on the Server 134
Updating Assemblies 134
Troubleshooting UDFs 136
Debugging UDFs 138
Summary 140
Client Support for Server UDFs 141
Modifying UDFs to Run on the Client 141
Add COM Support to a UDF Assembly 142
Registering the Assembly 144
Using the UDF inside Excel Client 144
Adding Client Support for GetMultiStockQuote 145
Conclusion 147
Generic Client UDF Support 147
Configuration Application 148
UdfProxy 149
CUdfAutomation 159
Final Steps 169
Improvements 170
Other Generic Solutions 170
Summary 171
Excel Web Access 173
Using Excel Web Access 175
EWA Usage 175
Web Part Properties 177
Part to Part Communication 179
Inside EWA 181
Frame Communication 182
Session Cookie 182
Summary 182
Reusable Excel Web Services Projects 183
Utilizing Web Services in UDFs 185
Library Component 185
How It Works 186
About Code Generation 186
WebServiceMethodCollection and WebServiceMethod Classes 187
WebServiceParameter and WebServiceParameterCollection Classes 189
UdfDefinition Class 189
GeneratedResult Class 195
Helper Class 200
ExcelServicesHelper Class 203
ExcelHelper Class 206
UI Component 209
Summary 211
Custom Web Services 213
Overview 213
Usage Example 214
Using the Custom Web Service 218
Updating the Model 219
More Type Safety 219
Generating UDFs 219
Topology 219
Coding the Solution 221
Solution Structure 221
Library 222
Excel Addin 223
Web Application 230
Enumerating Web Services 233
Summary 234
RSS over Excel Services 235
Overview 235
What Is RSS 236
Usage Example 236
Coding the Solution 245
Solution Structure 245
Library 245
Excel Addin 247
RSS Server 250
Summary 257
Excel Services as an RTD Server 259
Overview 260
Use 260
Coding the Solution 262
How RTD Works on the Client 262
ClientSideRtd 263
Summary 271
Real Time Data UDF 273
Overview 273
Use 274
Dangers of Using RTD in Excel Services 275
Coding the Solution 276
RtdUdf Project 277
Summary 285
Directly ParameterizedWorkbooks 287
Overview 287
Use 287
CustomWorkbooks Project 291
Summary 295
SQL Tables and SharePoint Lists 297
Overview 297
Use 298
Coding the Solution 306
Pes.DataUdfs Project 306
OleDbUdfs Class 311
SharePointUdfs Class 311
Summary 316
External Workbook References 319
Overview 319
Use 321
Coding the Solution 328
Pes.DataUdfs Project 328
Utils Class 337
Summary 337
Excel Services Workflows 339
Overview 339
Use 340
Coding the Solution 347
Pes.ExcelWorkflows Project 347
Deploying the Solution 365
Summary 368
EWA Projects 369
Parameterized EWA Links 369
How It Works 369
How to Build 370
Custom XLViewer 376
How It Works 376
Coding the Solution 379
Manipulating the EWA Session 382
How It Works 382
Coding the Solution 384
Summary 392
Excel Data Filter 393
Overview 393
Use 394
Coding the Solution 401
The Excel Services AJAX library 401
How Part to Part Works 406
Pes.Filter Project 407
Summary 418
Excel Services Mashup 419
Overview 419
Use 419
Coding the Solution 423
Workbook Model 423
EWA Page 424
UDF Assembly 425
HTML 425
Summary 430
Index 431
Read a Sample Chapter
Professional Excel Services
By Shahar Prish John Wiley & Sons
Copyright © 2007 John Wiley & Sons, Ltd
All right reserved. ISBN: 978-0-470-10486-6
Chapter One
Introduction to Excel Services
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.
Why Use Excel Services
While Excel is a great tool, it really lacks in one specific area: it is a client application. It was designed to be a client application from the get-go, and in all probability it will stay that way. That means it is focused on one user getting whatever Excel functionalitythat user needs on a PC. There are many indicators showing that organizations will want to run Excel on the server:
For one, a lot of people go to great lengths to try to get Excel client to work in a server environment - at great cost and with great frustration.
People want one version of the truth - but when workbook files are used with Excel client, there is no real protection against people modifying them. With only the client at the users' disposal, it is much harder to keep a single version that will be the "single point of entry" to the data.
Intellectual property is expensive, and companies want to guard it. Excel models can become extremely complex and give a real edge to their owners. Those owners do not want others to be able to access the models - only the results of the models. Excel does not really supply such protection.
Running a lot of models, whether as part of a mechanized process or because a lot of users need to get at the data, is virtually impossible to do in a scalable manner with Excel.
To see any part of an Excel file, the entire file must be opened. This can put a strain on even the fastest networks and can be completely impractical when people are connecting over a WAN.
More accurately, in some cases Excel is smart enough to delay-load some types of data caches such as pivot tables.
People want to see and navigate Excel worksheets inside a browser. But they also want those worksheets to be up to date, and they want the ability to navigate them and do simple operations such as drilling down through information or filtering lists.
Administrators want more manageability of what Excel does. Some workbooks have complex data queries that, when executed by too many users at the same time, can bring databases to their knees. Conversely, some data sources are accessible only to specific users. For these reasons, workbooks that are distributed may sometimes only have copies of data rather than actual live data. This raises the "one version of the truth" problem - how can organizations know what data is current?
For these reasons and others, organizations end up producing various creative solutions to the problem. These solutions usually come in two flavors: large, custom-built farms of Excel client applications and rewriting the underlying models.
The Excel Client Farms
Excel farms are relatively large farms (or computing clusters) that run multiple instances of Excel on request. People usually build some kind of protocol that allows them to extract information from the Excel processes and transfer it back to the user in whatever form is desired.
These solutions, while creative and impressive, do not scale well and are not fun to maintain. They require a lot of hardware due to various Excel limitations (again that pesky "was not designed for the server" thing), and managing the whole thing is really hard to do.
Rewriting the Models
The other solution organizations come up with, which is no less painful (and in some ways, more painful), is to take tried-and-true models written in Excel and rewrite them in some other language so that they can be used without Excel. This is of course a huge time investment - not only can the models be excessively complex, but when a model changes, the code needs to be updated appropriately, which takes a lot of time (testing alone is a major time sink, since one needs to make sure that the model behaves the same way that it does in Excel).
Note that, in some cases at least, the reason for transferring models into code does not have to do with the inability to get server capabilities but rather is done to squeeze every ounce of performance out of a model. Excel Services may or may not help in these cases. Some organizations will retain their need to rewrite Excel models.
How Excel Services Comes to the Rescue
Excel Services has been created to solve all these problems and more. While still using portions of Excel code that have been rewritten to be serverworthy, large amounts of work has been done around that code to make it into an actual server product.
Excel Services solves some of the problems simply by virtue of being a server product - intellectual property is protected because users do not have access to the actual workbook (unless the admin allows them to see it). Since all the calculations are done on the server, it is not even an issue - there is no need to transfer the model itself to the user. This also takes care of the "one version of the truth" issue - there is a central repository for information, and only people who are allowed to update that repository will. Furthermore, because Excel Services is leveraging the SharePoint infrastructure, it can make use of such features as "view-only rights" where some users can access the complete file (by loading it in Excel or by saving it to their hard drive), while others can only view it through the server.
Because it is a server, it can also do various things that the client was never designed to do, such as sharing information across users. The file itself need not be loaded more than once. This not only reduces network traffic, but it also means that the actual process of loading the file, which can be very long and CPU-intensive for large models, is only done once. This is doubly true for models that rely on getting data from databases - Excel Services can figure out what data is sharable among which users and make sure that it does not query the database too many times. On top of that, the administrators can also instruct Excel Services to only hit the database server periodically - say, not more than once every 5 minutes - giving them much more control over how many times the database will be hit by requests.
Since Excel Services was built from the ground up to have multiple instances of the same workbook open at any given time, it is possible for multiple users to open any number of workbooks and work with them. The same goes for processes that need concurrent access to workbooks. The number of workbooks that can be interacted with at a given moment is only limited by memory and CPU.
Finally, because the server supplies the means to access parts of loaded workbooks, people who are across the WAN will not need to take the hit of loading the entire file - they can just request a small part of the workbook. This also ties into EWA (Excel Web Access), which is a Web Part that allows people to navigate Excel workbooks inside a browser (no ActiveXs, I repeat, no ActiveXs at all, just plain old HTML and JavaScript, honest).
Excel Services Goals
It is important to understand what our goals were when we started thinking and designing Excel Services. Understanding the goals explains a lot of the technical decisions we made throughout the project. I am only listing the goals that have specific bearings on our server product - obviously goals such as customer satisfaction are there by default.
First Goal - 100% Fidelity with Excel
Our first and foremost goal was to have 100% fidelity with Excel in every Excel feature we support. That is to say, if we support a given Excel feature, our goal is to support it in exactly the same manner that Excel 2007 supports it. It does not mean, though, that we support all of Excel's features.
That said, sometimes when things are transferred to a server environment, "100% fidelity" stops being a clear-cut thing. As an example, take the Data Refresh feature. In Excel, when the user refreshes the data, Excel will go and grab new data from the database back end. On the server, as described before, the administrator can place a limit on how often refreshes may occur.
Second Goal - Security
It is incredibly important that Excel Services does not expose information to unauthorized users. If a user does not have access to a workbook, that person should never be able to use Excel Services to circumvent that principle (unless the administrator specifically set up the server for that). Similarly, if a workbook is set up such that users should not be able to see the actual models and formulas of the workbook, Excel Services should never supply that information to users.
Additionally, users should never be able to see information that belong to other users. So, if a user accesses data and gets a set of results and a second user gets a different set of results, neither of them should be able to see the results of the other.
Third Goal - Robustness and Reliability
Since this is a server product, robustness and reliability are paramount - we want to be able to keep the server up and running for as long as possible. If there is a feature in Excel that is impossible to translate well to the server because it will reduce robustness or reliability, there is a good chance we will preemptively decide not to do it at all.
(Continues...)
Excerpted from Professional Excel Services by Shahar Prish Copyright © 2007 by John Wiley & Sons, Ltd. Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
loading...