1 / 18

DEV12 Excel Services, Server APIs and Managed UDFs

DEV12 Excel Services, Server APIs and Managed UDFs. Aaron Saikovski Senior Consultant - Readify Australia Email: Aaron.Saikovski@readify.net Blog: http://ruskydotnet.blogspot.com. John Hodgson Solution Architect - HP Services Australia Email: John.Hodgson@hp.com. Overview Architecture

miya
Download Presentation

DEV12 Excel Services, Server APIs and Managed UDFs

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DEV12Excel Services, Server APIs and Managed UDFs Aaron SaikovskiSenior Consultant - Readify AustraliaEmail: Aaron.Saikovski@readify.netBlog: http://ruskydotnet.blogspot.com • John Hodgson • Solution Architect - HP Services Australia • Email: John.Hodgson@hp.com

  2. Overview • Architecture • Demo – Browser based rendering • Limitations • User Defined Functions (UDFs) • Demo – UDFs • Web Services • Demo – Web Services • Summary Agenda

  3. 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

  4. 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

  5. High quality web rending Zero-footprint Interactive: Set parameters, sort, filter, explore Browser Excel 2007 View and Interact Save Spreadsheets Design and author Save to SharePoint Export/Snapshot into Excel Excel 2007 Programmatic Access Open in Excel for rich exploration and analysis Open snapshots Microsoft Office SharePoint Server 2007 Spreadsheet calculation and rendering External data retrieval and caching 100% calculation fidelity SharePoint platform Customapplications Set values, perform calculations, get updated values via web services Retrieve full workbook file Excel ServicesOverview

  6. 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

  7. Browser-Based Spreadsheet Rendering

  8. Limitations? • Limited built-in functions? • No VBA available? • Want external data sources? • What about Webservices? • How about .NET Framework functionality? • Realtime data support?

  9. 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

  10. 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

  11. Excel Services & UDFs

  12. 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

  13. Excel Web ServicesScenarios • Using server-side Excel logic in applications • Automating spreadsheet updates on servers • Custom UI to server-side Excel calculation

  14. Excel Web ServicesFunctionality • Open a spreadsheet file • Set values to cells and ranges • Process the session spreadsheet • Get results

  15. Using Excel Functionality in WinForms Applications thru WebServices

  16. 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

  17. 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 • ShaharPrish’s (Software Design Engineer) blog: • http://blogs.msdn.com/cumgranosalis/ • Microsoft SharePoint Team Blog: • http://blogs.msdn.com/sharepoint

More Related