excel services

Excel Services – One version of the truth

Paul Cross

Architect, Microsoft

Office System Timeline

October October 20032003

OfficeOfficeSystemSystem

May 2004May 2004Office 12Office 12

PillarsPillars

Q4 CY Q4 CY 20052005TechTechBetaBeta

~$700M annual R&D investment across Office programs, servers, ~$700M annual R&D investment across Office programs, servers, and servicesand services

Driven by customer, partner and analyst feedbackDriven by customer, partner and analyst feedback

Enterprise focus with emphasis on empowering the individualEnterprise focus with emphasis on empowering the individual

Long-term commitment to investment areasLong-term commitment to investment areas

Requirements: Requirements:

Client programs: Windows XP SP1 or laterClient programs: Windows XP SP1 or later

Servers: Windows Server 2003, SQL Server 2000 or laterServers: Windows Server 2003, SQL Server 2000 or later

Q4 CY 2006Q4 CY 2006Public Public

AvailabilityAvailability

Enterprise Content ManagementMake it simple to author and manage

content and documents

CollaborationKeep co-workers, partners

and customers in sync

Knowledge Discovery and Insight

Make the right informationavailable to more people

Personal ProductivityIncrease employee self-sufficiency and

effectiveness

Fundamentals Fundamentals Make it more secure, Make it more secure,

manageable and reliablemanageable and reliable

InformationInformationWorker SolutionsWorker Solutions

Build client and web-basedBuild client and web-basedapplications with workflow and applications with workflow and line-of-business interoperability line-of-business interoperability

Office System Investments

Office SharePoint Server

Docs/tasks/calendars, blogs, wikis, e-mail integration, project management

“lite”, Outlook integration, offline docs/lists

CollaborationCollaborationBusinessBusiness

IntelligenceIntelligence

PortalPortal

Enterprise Portal template, Site Directory, My Sites,

social networking, privacy control

Enterprise scalability,contextual relevance, rich

people and business data search

Rich and Web forms based front-ends, LOB actions, pluggable SSO

Server-based Excel spreadsheets and data visualization, Report Center, BI Web Parts, KPIs/Dashboards

Integrated document management, records management, and Web content management with policies and workflow

BusinessBusinessProcesseProcesse

ss

SearchSearchContentContentManagementManagement

PlatformPlatformServicesServices

Workspaces, Mgmt,Workspaces, Mgmt,

Security, Storage,Security, Storage,

Topology, Site ModelTopology, Site Model

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 “12”Excel “12”

BrowserBrowser100% thin100% thin

View and Interact

CustomCustomapplicationsapplications

Web Services Access

Excel “12”Excel “12”clientclient

Open Spreadsheet/Snaps

hot

Author & Publish Spreadsheets

Excel Services

Architecture

• Web front-end• SharePoint UI• HTML rendering + web services

• Application server• Loads spreadsheets, refreshes data,

calculates• Maintains state for interactivity• File + query caches for performance

• The Office servers provide• Solution platform• Store: spreadsheets, connection files• Management: settings, UI, scripting• Security: Authentication, Authorization

• Single box or multi-tier • Independent scale-out

Web front endWeb front end

Excel web Excel web accessaccess

Excel web Excel web servicesservices

ECS ProxyECS Proxy

Excel calculation serviceExcel calculation service

ECS interfaceECS interface

Application ServerApplication Server

External data sourcesExternal data sources


Excel wExcel wb accessb access

Excel web seExcel web sevicesvices

ECS ProxyECS Proxy




ECS ProxyECS Proxy




ECS ProxyECS Proxy




ECS ProxyECS Proxy




ECS ProxyECS Proxy




ECS ProxyECS Proxy



















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.


• Refresh• 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.

• Calculate• 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:

• Recalculate - Calculate only formulas that have dependencies that changed (aka “dirty” formulas).• CalculateFull - Calculate all formulas, regardless of dependency changes.

• GetCell• 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.


• GetWorkbook• 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.

• CancelRequest• 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 serviceXlMortgageCalcWebPart.Es.ExcelService es = new XlMortgageCalcWebPart.Es.ExcelService();// Step 2: Set web service linkes.Url = this.ExcelWebServiceUrl;// Step 3: Set credentialses.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Step 4: Start the sessiontry {

sessionId = es.OpenWorkbook(this.MortgageCalculatorWorkbookUrl, String.Empty, String.Empty, out status);}catch{

sessionId = null;}


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 parameterses.SetCellA1(sessionId, "SimpleCalculator", "MortgageAmount", _txtMortgageAmount.Text.Trim());

es.SetCellA1(sessionId, "SimpleCalculator", "MortgageLength", _txtMortgageLength.Text.Trim()); es.SetCellA1(sessionId, "SimpleCalculator", "InterestRate", _txtInterestRate.Text.Trim());


// Step 6: Get resultobject 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 sessionstatus = 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

• Enabled/Disabled. Default = Enabled.• 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)• 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

Server-based SpreadsheetsPublish Spreadsheets to the Server to Share Securely

Confidential – Microsoft CorporationConfidential – Microsoft Corporation

Define parameter Define parameter cells that can be cells that can be changed on the changed on the

serverserver

Control what is Control what is visible on the visible on the

serverserver

Saves regular Saves regular Excel “12” filesExcel “12” files


100% HTML and 100% HTML and script no client script no client

componentscomponents

Server side Server side chartingcharting

Excellent visual Excellent visual fidelity including all fidelity including all

new conditional new conditional formattingformatting

Server-based SpreadsheetsView and interact with spreadsheets in the browser

Server-based SpreadsheetsView and interact with spreadsheets in the browser


10+ out-of-the-10+ out-of-the-box filter partsbox filter parts

Display specific Display specific spreadsheet spreadsheet

ranges or chartsranges or charts

Unified filtering Unified filtering across all parts on across all parts on

the dashboardthe dashboard

Server-based SpreadsheetsIncorporate spreadsheet components in BI

dashboards

Server-based SpreadsheetsIncorporate spreadsheet components in BI

dashboards


Out-of-the-box Out-of-the-box templatetemplate

Personalized and Personalized and targeted content targeted content

deliverydelivery

Server-based SpreadsheetsBuild on SharePoint content management features

Server-based SpreadsheetsBuild on SharePoint content management features

Search for Search for reports, reports,

spreadsheets spreadsheets and dashboardsand dashboards

excel services – one version of the truth paul cross architect, microsoft

Documents

excel logic

excel dashboards

complex excel

update of server spreadsheets

windows server

sql server

microsoft slide