Excel Services – One version of the truth
Paul Cross
Architect, Microsoft
Office System Timeline
October 2003
Office
System
May 2004
Office 12
Pillars
Q4 CY 2005
Tech
Beta
Q4 CY 2006
Public
Availability
~$700M annual R&D investment across Office programs, servers,
and services
Driven by customer, partner and analyst feedback
Enterprise focus with emphasis on empowering the individual
Long-term commitment to investment areas
Requirements:
Client programs: Windows XP SP1 or later
Servers: Windows Server 2003, SQL Server 2000 or later
Office System Investments
Enterprise Content Management
Make it simple to author and manage
content and documents
Collaboration
Keep co-workers, partners
and customers in sync
Personal Productivity
Increase employee
self-sufficiency and
effectiveness
Information
Worker Solutions
Build client and web-based
applications with workflow and
line-of-business interoperability
Knowledge Discovery
and Insight
Make the right information
available to more people
Fundamentals
Make it more secure,
manageable and reliable
Office SharePoint Server
Docs/tasks/calendars, blogs, wikis, email integration, project management
“lite”, Outlook integration,
offline docs/lists
Server-based Excel
spreadsheets and data
visualization, Report Center, BI
Web Parts, KPIs/Dashboards
Business
Intelligence
Rich and Web forms based
front-ends, LOB actions,
pluggable SSO
Business
Processes
Platform
Services
Workspaces, Mgmt,
Security, Storage,
Topology, Site Model
Content
Management
Integrated document management,
records management, and Web
content management with policies
and workflow
Collaboration
Portal
Enterprise Portal template,
Site Directory, My Sites,
social networking, privacy
control
Search
Enterprise scalability,
contextual relevance, rich
people and business data search
Excel on Servers today
• Automating spreadsheet creation, update and
calculation is complex
• Excel was designed as a client program; not robust
and scalable on servers
• Publishing spreadsheets to users leads to many
versions of the truth
• Incorporating Excel logic into applications often
requires re-coding
Why Excel on the Server?
• Provide browser-based access to spreadsheets
• Incorporate spreadsheets in portals and dashboards
• Limit access to spreadsheets for regulatory concerns and to
protect proprietary information
• Eliminate “multiple versions of the truth” caused by distributing
copies of spreadsheets
• Leverage servers to offload long-running calculations from
desktop machines
• Reuse logic & business models built in Excel in applications
written in other languages without having to re-code the
logic/business models
Excel Services
•
•
•
•
What is it?
Server-side Excel calculation engine
Browser access to live, interactive server spreadsheets
Programmatic Web service access to server-side Excel calculation
•
•
•
•
•
•
•
What are the benefits?
Server-grade: scalable, performant, robust
Easy to deploy: zero-footprint on client machines
Lockdown access to protect sensitive Excel models
“BI Portals” with Excel dashboards – built by users
Automate creation and update of server spreadsheets
Create solutions using Excel authored business logic
Excel Services
Browser
100% thin
View and
Interact
Author & Publish
Spreadsheets
Open
Spreadsheet/Snapshot
Excel “12”
Web Services
Access
Excel “12”
client
Custom
applications
Architecture
Web front end
Excel w
b access
Excel web se
vices
Web front end
Excel w
b access
ECS Proxy
ECS Proxy
Web front end
Web front-end
•
•
•
Application server
•
•
•
•
Loads spreadsheets, refreshes data, calculates
Maintains state for interactivity
File + query caches for performance
The Office servers provide
•
•
•
•
•
•
SharePoint UI
HTML rendering + web services
Solution platform
Store: spreadsheets, connection files
Management: settings, UI, scripting
Security: Authentication, Authorization
Web front end
Web front end
Excel web
Excel web
access
services
Excel w
b access
•
Excel web se
vices
Excel web se
vices
Excel w
b access
ECS Proxy
ECS Proxy
Web front end
Excel w
b access
Excel web se
vices
ECS Proxy
Excel web se
vices
Web front end
Excel w
b access
Excel web se
vices
ECS Proxy
ECS Proxy
Application Server
Application Server
Application Server
ECS interface
ECS interface
ECS interface
Excel calculation service
Excel calculation service
Application Server
Application Server
ECS interface
ECS interface
Excel calculation service
Excel calculation service
Application Server
Application Server
ECS interface
ECS interface
Excel calculation service
Excel calculation service
Excel calculation service
Single box or multi-tier
Independent scale-out
External data sources
Architecture
• Three components divided into two major groups
• Front-end (“web front end”)
• Back-end
• Configurations
• Simple - Single box
• Complex – Multiple boxes (scale up/out)
• Security provided by SharePoint
• Performance and scalability
• Optimised for multi-user access
• Caching at multiple levels (sheets, external data, etc.)
What Excel Services is not…
• A spreadsheet creation tool
• Unable to edit spreadsheets in the browser
• Multi-user spreadsheet authoring
• Workbook loaded once in memory
• Each user has there own session (UI or WS-*)
• High-end calculation server
• Optimised for multi-user access as opposed to calculations
• Some tweaks made for leveraging server-side hardware but
spreadsheets are not spread across multiple boxes
Excel Web Services - Purpose
• Build custom .NET applications around
server-side Excel spreadsheets
• Scenarios
• Using server-side Excel logic in applications
• Author part of the business logic in Excel
• Protect and maintain Excel IP on the server
• Automating spreadsheet updates on servers
• Refresh external data and parameterize
• Process generated spreadsheets
• Create, store and deliver snapshots
• Custom UI to server-side Excel calculation
Excel Web Services - Functionality
• “Open” a spreadsheet file
(start a session with Excel services)
• Set values to cells and (named) ranges
• Process the session spreadsheet:
• Refresh external data sources
• Calculate spreadsheet or specific ranges
• Get results
• Entire calculated spreadsheet – live or snapshot
• Values from cells and (named) ranges
Excel Web Services – Functionality
•
GetApiVersion
• Returns a version string of the installed web service API build.
•
sessionId = OpenWorkbook(filePath,…)
• Open a server-side calculation session, returning a sessionid.
•
GetSessionInformation
• Returns properties associated with the server session, e.g. the language
context of the session.
•
SetCell
• Set a value into a cell on one of the workbook’s sheets (Accessed using
cell address, e.g. “B52”, named range, e.g. “Interest” or integer
coordinates (may be more appropriate for programmatic access)
•
SetRange
• Same as SetCell, but for setting values into an entire contiguous range.
Same two flavours exist.
Excel Web Services - Functionality
•
Refresh
•
•
Calculate
•
•
Read data from an external data connection (or all of the workbook’s connections) and
refresh the values in the relevant cells, e.g. in PivotTable cells or in the results of cube
formulas.
Recalculate the formulas in a specific range or in the entire workbook. Useful when the
workbook author has turned off automatic calculation. Two flavours – using a string or
integer coordinates to refer to a range – much like in the Set methods.
CalculateWorkbook
•
Calculate the entire workbook, using one of two calculation methods:
•
•
•
GetCell
•
•
Recalculate - Calculate only formulas that have dependencies that changed (aka “dirty” formulas).
CalculateFull - Calculate all formulas, regardless of dependency changes.
Get a value out of a cell. Two regular addressing flavours exist. Formatted string values,
or raw binary values.
GetRange
•
Get a set of values out of a contiguous range of cells. Same addressing flavours.
Excel Web Services - Functionality
•
GetWorkbook
•
•
CancelRequest
•
•
Get the entire calculated workbook into application memory as a byte Llive result, or a
snapshot (essentially a workbook with the layout of the original workbook, with all the
original formatting and with up-to-date values – but with all the formulas and external
connections stripped, and without the portions of the workbook that were marked not for
viewing during publish.
If your application runs the Excel Web Services session in a separate thread, and
wishes to abort a long-running server request (e.g. a long calculation that the user got
tired of waiting to) – it can do so by calling this method.
CloseWorkbook
•
Tell the server to close the workbook that it opened for this session, thereby also
allowing the server to release all the resources that it maintained for the context of your
session.
Excel Services - Error handling
• Errors are exposed to developers in three ways:
• Calculation errors appear as they do in traditional Excel,
e.g. #VALUE!
• Calling GetCell or GetRange requesting formatted values, #-style error
string returned.
• Calling GetCell or getRange requested unformatted values, enumerated
error code returned.
• Web services errors exposed as SOAP exceptions
• Less critical errors returned as part of the method
arguments, e.g. those errors that do not cause abnormal
results to be returned.
• Checking for these errors is optional
Excel Services - Web Service Example
private void CalculateUsingWebService()
{
Status[] status;
string sessionId = null;
// Step 1: Instantiate the web service
XlMortgageCalcWebPart.Es.ExcelService es = new
XlMortgageCalcWebPart.Es.ExcelService();
// Step 2: Set web service link
es.Url = this.ExcelWebServiceUrl;
// Step 3: Set credentials
es.Credentials = System.Net.CredentialCache.DefaultCredentials;
// Step 4: Start the session
try
{
sessionId = es.OpenWorkbook(this.MortgageCalculatorWorkbookUrl,
String.Empty, String.Empty, out status);
}
catch
{
sessionId = null;
}
Excel Services - Web Service Example
if (sessionId == null)
{
_lblError.Text = "Error opening workbook. Please make sure that the correct
MortgageCalculatorWorkbookUrl and ExcelWebServiceUrl are specified in the Web Part Properties.";
this.Controls.Clear();
this.Controls.Add(_lblError);
return;
}
// Step 5: Set parameters
es.SetCellA1(sessionId, "SimpleCalculator", "MortgageAmount", _txtMortgageAmount.Text.Trim());
es.SetCellA1(sessionId, "SimpleCalculator", "MortgageLength", _txtMortgageLength.Text.Trim());
es.SetCellA1(sessionId, "SimpleCalculator", "InterestRate", _txtInterestRate.Text.Trim());
Excel Services - Web Service Example
// Step 6: Get result
object o = es.GetCellA1(sessionId, "SimpleCalculator", "Payment", true, out status);
if (o != null)
{
_lblTotal.Text = Convert.ToString(o);
}
Else
{
_lblError.Text = "Error getting total value from workbook.";
this.Controls.Clear();
this.Controls.Add(_lblError);
return;
}
// Step 7: End the session
status = es.CloseWorkbook(sessionId);
}
Threading
• Client
• Is multi-threaded
• Supports multi-threaded recalculation
• Only functions defined in XLLs able to participate (XLL interface modified to allow
developer to specify)
• UDFs/VBA unable to participate
• However, XLL -> .NET a possibility…
• Server
• Is multi-threaded
• Each user session or workbook session opens on a different thread
• Box with multiple CPUs or cores, we can calculate multiple spreadsheets or
instances of a spreadsheet simultaneously
• Multi-threads external data queries (native to Excel such as PivotTables not UDFs)
• Does not support multi-threaded recalculation
Multi-threaded Calculation (MTC/R)
• Utilises multi-processor or dual core-core chips
• Excel spots formulae that can be calculated concurrently
• Default is 1 thread per processor
• Monte Carlo calcs will benefit (large number of independent
functions)
• Functions defined in XLLs able to participate
• VBA and automation add-in UDFs will not be multi-threaded
• XLL interface has been updated to allow developers to advertise
their XLL functions as thread-safe
Multi-threaded Calculation (MTC/R)
• Calculation results unaffected by MTC/R
• Capability may be switched off
• Some overhead first time workbook loaded
• Information cached and subsequent calls unaffected
• User may override thread settings
Excel Services – Limitations?
• In this first release of Excel services:
• No Excel object model
• Extensibility not supported
• No VBA / Excel Macros
• No add-ins... But 
Excel Services Management - Configuration
• Security
• Authentication
• Impersonation or Process account. Default = Impersonation.
• Communication
• Connection encryption (None, All). Default = None.
• Load Balancing
• Schemes
• Workbook URL (Default)
• Round Robin
• Local
• Retry Interval (secs)
• Valid values: from 5 through 2073600 (24 days). Default = 30.
• Session Management
• Maximum sessions per user
• Valid values: -1 (no limit); any positive integer. Default = 25.
• Memory Utilization
• Maximum Private Bytes
• Valid values: -1 (the limit is set to 50% of physical memory on the machine); any positive
integer. Default = -1
Excel Services Management - Configuration
• Workbook Cache
• Location
• The local file system location of the workbook file cache. No value indicates that a
subdirectory in the system temporary directory is the location of the workbook file cache.
• Maximum Size (MB)
• Valid values: -1 (no limit); any positive integer. Default = 40960.
• Includes the maximum disk space that can be allocated for recently used files that are not
open.
• Caching of Unused Files
• Caching Enabled/Disabled. Default = Enabled.
• External Data
• Query Timeout (secs)
•
Valid values: -1 (no enforced timeout, but workbook timeouts still respected); from 0
through 2073600 (24 days). Default = 30.
• Connection Lifetime
• Valid values: -1 (never recycle); from 0 through 2073600 (24 days). Default = 1800.
• Unattended Service Account
Excel Services Management
• List of Excel 12 workbook file locations that you consider
trustworthy
• Requests to open files that are not stored in one of the trusted locations are
denied
• Location
• Address
• Location Type
• Windows SharePoint Services
• UNC
• HTTP
• Trust Children (Trust child libraries or directories)
• Children trusted (Enabled/Disabled)
• Description
• Session Management
• As per Configuration
• Workbook Properties
• Maximum size (in MB) of a workbook that can be opened by Excel
Calculation Services
• Valid values: from 1 through 2000. Default = 10.
• Maximum Chart Size (MB)
• Valid values: any positive integer. Default = 1.
Excel Services Management
•
Calculation Behaviour
•
Volatile Function Cache Lifetime
•
Maximum time (secs) that a computed value for a volatile function is cached
• Automatic calculations
• Manual calculations
• Valid values: -1 (calculate once per session); 0 (always calculated); from
1 through 2073600 (24 days). Default = 300.
•
Workbook Calculation Mode
•
•
•
All of the settings except the File setting override the workbook settings
Valid values: File/ManualAutomatic/Automatic except data tables. Default = File.
External Data
•
Allow External Data
•
None/DCL/DCL and embedded. Default = None.
•
Warn on Refresh
•
Stop When Refresh on Open Fails
•
•
•
File cannot be refreshed while it is opening and the user does not have an Open user right to the file.
Default = Enabled.
External Data Cache Lifetime (secs)
•
•
•
•
Enabled/Disabled. Default = Enabled.
Automatic refresh (periodic / on-open)
Manual refresh
Valid values: -1 (never refresh after first query); from 0 through 2073600 (24 days). Default = 300.
Maximum Concurrent Queries Per Session
•
Valid values: any positive integer. Default = 5.
Key Take-Aways
• New Excel services: scalable, performant, and robust Excel
spreadsheet calculation on the server
• Excel Web services enable you to
• Create solutions using Excel authored business logic
• Automate creation and update of server spreadsheets
• Protect the IP in spreadsheets
• Excel services also enable
• Browser access to interactive spreadsheets
• Spreadsheet lockdown and control
• “BI Portals” with Excel based dashboards
© 2005 Microsoft Corporation. All rights reserved.
This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.
Server-based Spreadsheets
Publish Spreadsheets to the Server to Share Securely
Define parameter
cells that can be
changed on the
server
Control what is
visible on the
server
Saves regular
Excel “12” files
Confidential – Microsoft Corporation
Server-based Spreadsheets
View and interact with spreadsheets in the browser
Excellent visual
fidelity including all
new conditional
formatting
100% HTML and
script no client
components
Server side
charting
Confidential – Microsoft Corporation
Server-based Spreadsheets
Incorporate spreadsheet components in BI dashboards
Unified filtering
across all parts on
the dashboard
10+ out-of-thebox filter parts
Display specific
spreadsheet
ranges or charts
Confidential – Microsoft Corporation
Server-based Spreadsheets
Build on SharePoint content management features
Personalized and
targeted content
delivery
Out-of-the-box
template
Search for
reports,
spreadsheets
and dashboards
Confidential – Microsoft Corporation