One of the most interesting outcomes of decoupling the Office Web Apps from the SharePoint server is that now Office documents are being served up in consumer-facing scenarios by both Microsoft, through Hotmail, Outlook and SkyDrive, and by other non-Microsoft websites that have implemented their own Office Web Apps servers. From the Office client and server perspective, the leader in this notion of Office documents being available everywhere is Excel. Excel Services has certainly been one of the innovation leaders over the past two releases of SharePoint and continues to do so with this release. Because of the ability to have public-facing Office Web App servers rendering Excel content, Excel is pressing its end-user, business intelligence (BI) dominance forward to Web endpoints greater than ever before. In doing this, developers can join in by taking advantage of new integration capabilities and the extensibility points that have been provided.
One intriguing new integration capability is Excel Interactive View. This innovative, Excel Services rendered viewer lets you render any HTML table on a Web page within the Excel Interactive View by adding a script element and an HTML anchor tag to the page. Users who do not even have Excel on their device can click the Excel Interactive View icon above the table, and the HTML will be retrieved from the Web page and rendered in the Excel Interactive View. Excel Services automatically assesses the best charts to represent the data and determines which Excel slicers to present for filtering the data. The option to download the HTML table content into Excel for deeper analysis is available as well.
The best way to understand Excel Interactive View is to experience it. In this Try It Out you create an Autohosted app for SharePoint that accesses a public-facing OData endpoint and dynamically builds a table that can be analyzed with Excel Interactive View.
<html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>Excel Interactive Demo</title> <script type="text/javascript" src="../Scripts/jquery-1.7.1.min.js"></script> <script type="text/javascript" src="../Scripts/App.js"></script> <script type="text/javascript" src="https://r.office.microsoft.com/r/rlidExcelButton?v=1&kip=1"></script> </head> <body> <form id="form1" runat="server"> <div> <h2>App for SharePoint using Excel Interactive View</h2> <div id="Content"></div> <div> <a href="#" name="MicrosoftExcelButton" data-xl-buttonstyle="Small" data-xl-tabletitle="Product Sales"></a> <table id="showProductCategoryTable"> </table> </div> </div> </form> </body> </html>
$(document).ready(function () { getProducts(); }); function getProducts() { //Due to the Web page for the app being HTTPS and the Northwind OData //service being HTTP, a proxy page is used to broker the call to //keep this code sample simple. You would most likey have your own //HTTPS service deployed to broker this call. $.ajax({ url: '/Pages/ProxyHTTPRequest.aspx', type: 'GET', headers: { "accept": "application/json", }, success: showProducts, error: function () { alert('Failed!'), } }); function showProducts(data) { var items = []; // Build table header row for Products items.push("<tr><th>Category Name</th>" + "<th>Product Name</th>" + "<th>Product Sales</th></tr>"); // Load each row with data $.each(data.d.results, function (key, val) { items.push('<tr>' + '<td>' + val.CategoryName + '</td>' + '<td>' + val.ProductName + '</td>' + '<td>' + parseFloat(val.ProductSales) + '</td></tr>'), }); $("#showProductCategoryTable").append(items.join('')); } }
// This is Web page is simply to proxy the HTTP request for the HTTPS page var url = "http://services.odata.org/Northwind/Northwind.svc/Sales_by_Categories? $select=CategoryName,ProductName,ProductSales&$format=json"; string output = new WebClient().DownloadString(url); Response.Clear(); Response.ContentType = "application/json"; Response.Write(output); Response.End();
Although the Excel Interactive View is a great tool you can easily integrate into your Web pages, you’re going to want to switch gears and leverage the power of Excel Services when it comes time to build out broad and deep solutions. Excel Services made a significant investment to provide Open Data Protocol (OData) access to Excel documents in SharePoint libraries. SharePoint 2010 Excel Services provided REST-based access to these documents, which opened up a host of opportunities for rich clients and Web applications to essentially have any named range or chart in an Excel document as an accessible endpoint for data retrieval. But now the RESTful capability has been extended to include OData access to any Excel table in a document hosted on SharePoint. Therefore your favorite OData client can read from Excel tables like other OData sources.
Because the intent for OData is to be a standards-based protocol, visit OData.org to read the details: The protocol specification is available publicly for anyone to implement and provide OData service endpoints. Microsoft therefore built its OData service for Excel Services using this specification, which also outlines the URL syntax that a calling application must implement to retrieve data from the service. For you to access Excel table data on SharePoint you use a prescribed syntax to construct your URL. You should be aware of the three discrete parts of the URL; these follow the standard OData protocol URI conventions.
Using the preceding URL snippets the composed URL would be https://YourSharePointHost/SiteIfOne/_vti_bin/ExcelRest.aspx/ LibraryName/FileName.xlsx/OData/$metadata. Because a number of system query options are available, working with them and getting a feel for the results they each return is best. In the following Try It Out you exercise several of the system query options available from Excel Services.
https://YourSharePointHost/SiteIfOne/_vti_bin/ExcelRest.aspx/
https://YourSharePointHost/SiteIfOne/_vti_bin/ExcelRest.aspx/LibraryName/ ODataAccessSample.xlsx/OData/
As you can see, Excel Services has continued to innovate and provide additional capabilities for developers to build on. In this section you worked with some of the new capabilities, but don’t forget that all the great new Excel Services capabilities that came with SharePoint 2010 are still available in this new release. Also, some really exceptional resources came online post-SharePoint 2010 that can help get you bootstrapped so you can tap into the whole of Excel Services when building your solutions. One amazingly such resource is http://www.excelmashup.com — it almost writes the code for you.
3.141.30.162