dev12 excel services, server apis and managed udfs aaron saikovski senior consultant - readify...
TRANSCRIPT
DEV12Excel Services, Server APIs and Managed UDFs
Aaron SaikovskiSenior Consultant - Readify AustraliaEmail: [email protected]: http://ruskydotnet.blogspot.com
John HodgsonSolution Architect - HP Services AustraliaEmail: [email protected]
Agenda• Overview• Architecture• Demo – Browser based rendering• Limitations• User Defined Functions (UDFs) • Demo – UDFs• Web Services• Demo – Web Services• Summary
Microsoft Office Excel ServicesCustomer needs and challenges
• “Maintain a single version of your Excel workbook on the server
• Excel often doesn’t ‘play’ in the BI dashboard and reporting world because there is no live thin solution for sharing spreadsheets
• Information in Spreadsheets is difficult to protect• Incorporating Excel logic into applications often
requires re-coding• Excel was designed as a client program
Excel ServicesWhat is it?• A new server product built on the SharePoint platform• Available in MOSS 2007 Enterprise SKU• Server-side Excel calculation engine• Excel for the Web• Web service access to server-side Excel calculation
services• Developers can write custom calculation code
Excel 2007
Design and authorSave to SharePoint
Browser
High quality web rendingZero-footprintInteractive: Set parameters, sort, filter, explore
View and Interact
Customapplications
Set values, perform calculations, get updated values via web servicesRetrieve full workbook file
Programmatic Access
Open in Excel for rich exploration and analysisOpen snapshots
Excel 2007
Export/Snapshot into Excel
Spreadsheet calculation and rendering External data retrieval and caching100% calculation fidelitySharePoint platform
Microsoft Office SharePoint Server 2007
Save Spreadsheets
Excel ServicesOverview
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
• Office SharePoint Server 2007 provides– Solution platform– Store: Spreadsheets, connection files– Management: Settings, UI, scripting– Security: Authentication, Authorization
• Single box or multi-tier• Independent scale-out
Application ServerUser-Defined
FunctionsExcel Calculation
Services
Excel Web Access
Excel Web Services
Web Front End
External Data Sources
SharePoint ContentDatabase
Excel Workbooks
Excel Web Access
Excel Web Services
Web Front EndExcel Web
AccessExcel Web Services
Web Front End
Excel Web Access
Excel Web Services
Web Front EndExcel Web
AccessExcel Web Services
Web Front End
Excel Web Access
Excel Web Services
Web Front EndExcel Web
AccessExcel Web Services
Web Front End
Application ServerUser-Defined
FunctionsExcel Calculation
Services
Application ServerUser-Defined
FunctionsExcel Calculation
Services
Application ServerUser-Defined
FunctionsExcel Calculation
Services
Application ServerUser-Defined
FunctionsExcel Calculation
Services
Application ServerUser-Defined
FunctionsExcel Calculation
Services
Application ServerUser-Defined
FunctionsExcel Calculation
Services
Browser-Based Spreadsheet Rendering
Limitations?
• Limited built-in functions?• No VBA available?• Want external data sources?• What about Webservices?• How about .NET Framework functionality?• Realtime data support?
User Defined Functions
• Extend calculation and data-import capabilities–Functions that are not built into Excel.–Call UDFs from a cell – e.g.
"=MySampleUDF(A1*3.42)“ –Custom implementations to built-in functions.–Call WebServices, data feeds, legacy data sources
• Compiled Code based on .NETFx 2.0–Reference to Excel Services UDF Framework
User Defined Functions – cont..
• To build a UDF:–Reference to
Microsoft.Office.Excel.Server.Udf.dll–UDF class marked with the [UdfClass] attribute–Public, parameterless, public constructor–Methods decorated with [UdfMethod] attribute–Assembly placed in “trusted” folder location–Strong name & sign your assembly
Excel Services & UDFs
User Defined Functions - cont..
• Visual Studio 2005 Class Library • Assembly Reference
–Microsoft.Office.Excel.Server.Udf.dll
• Classes–Microsoft.Office.Excel.Server.Udf.UdfClass
attribute
• Functions–Microsoft.Office.Excel.Server.Udf.UdfMethod
attribute– IsVolatile property –ReturnsPersonalInformation property
Excel Web ServicesScenarios
• Using server-side Excel logic in applications• Automating spreadsheet updates on servers• Custom UI to server-side Excel calculation
Excel Web ServicesFunctionality
• Open a spreadsheet file • Set values to cells and ranges• Process the session spreadsheet• Get results
Using Excel Functionality in WinForms Applications thru WebServices
Summary
• Excel Services–Excel spreadsheets processed on the server–Web Service API for code access to server
Excel calculation–Extendable spreadsheets thru UDFs–Based on Microsoft Office SharePoint Server 2007
Resources
• The Australian MOSS online community mailing list:–http://www.OzMOSS.com
• Creating Custom Solutions with Excel Services:–http://msdn2.microsoft.com/en-us/library/
ms517343.aspx
• Excel 2007 blog:–http://blogs.msdn.com/Excel
• Shahar Prish’s (Software Design Engineer) blog:–http://blogs.msdn.com/cumgranosalis/
• Microsoft SharePoint Team Blog:–http://blogs.msdn.com/sharepoint