programming4us
programming4us
WEBSITE

Sharepoint 2013 : Developing Applications Using Office Services - What’s New in Excel Services

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
7/2/2014 9:29:13 PM

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.


TRY IT OUT: Excel Interactive View in an Autohosted App for SharePoint (C14ExcelInteractive.zip)

In this exercise, within an Autohosted app for SharePoint, you dynamically create an HTML table in your Web page from an OData source and wire up the Excel Interactive View for end-user analysis.

1. Run Visual Studio 2012 as Administrator. Select New Project.

2. In the New Project dialog, expand the Templates ⇒ Visual C# ⇒ Office/SharePoint ⇒ Apps nodes. Select App for SharePoint 2013 and provide the name C14ExcelInteractive. Click OK.

3. In the Specify the app for SharePoint dialog, set the SharePoint site URL you will deploy the app to (this example used Office 365) and choose Autohosted as the host for your app for SharePoint. Click Finish.

4. In the Solution Explorer, expand the Pages node and double-click the Default.aspx file to open it. Delete the <html>...</html> contents and replace them with the following: (Notice the highlighted lines below are the only lines needed to invoke the Excel Interactive View.)
<html >
<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>
5. In the Solution Explorer, under the C14ExcelInteractiveWeb project, right-click the Scripts folder and select Add ⇒ New Item.

6. In the Add New Item dialog, select JavaScript File, name it App.js, and click Add.

7. In the App.js file enter the following code lines:
$(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(''));
}
}
8. In the Solution Explorer, under the C14ExcelInteractiveWeb project, right-click the Pages folder and select Add ⇒ New Item.

9. In the Add New Item dialog, select Web Form File, name it ProxyHTTPRequest.aspx, and click Add. This Web page is used to proxy the call to the Northwind OData service which is an HTTP endpoint and your app for SharePoint is HTTPS. It also keeps this code sample simple to implement. You would most likely use your own HTTPS service to make this call in a production setup.

10. Right-click the ProxyHTTPRequest.aspx file, select View Code, and enter the following code in the Page_Load method:
// 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();
11. Excel Interactive View cannot interact with your local host so rather than pressing F5, you need to deploy your Autohosted app. In the Solution Explorer, right-click the C14ExcelInteractive node and select Deploy.
12. As the app deploys, respond appropriately to the prompts. When your Autohosted app Web page loads, click the Excel Interactive View icon just above your table with its dynamically loaded data. When the Excel Interactive View loads, it should look something like Figure 1.

FIGURE 1

image

13. Notice the various sheet formatting and chart options on the right under View. Excel Services automatically assesses (based on the data) and presents the most likely sheet and chart views you would use with this data. On the left side are the data slicers. By single or multi-selecting one or more slicers, you can quickly filter the data, look for trends and so on. Click your way around the UI to experience some of the capabilities this interactive tool brings to what would normally have been a static, flat HTML table on the Web page. And if you want to work with the raw table data directly, click the Excel icon and select whether to open the file in the Excel Web App or download it.

14. Close the Excel Interactive View and close the browser.

How It Works

In this Try It Out you brought together the new cloud app model by retrieving data from an OData source and dynamically building an HTML table in your app for SharePoint. You then coupled the HTML table with the new Excel Interactive View to bring it to life in a quick analysis and data visualization experience for the end user.

Notice in this Try It Out that the <script> element src= attribute for the Excel Interactive View uses HTTPS because that is the requirement for Apps for SharePoint in Office 365. You can toggle this to HTTP in situations where you do not have an SSL requirement. The src= URL can take one additional query string parameter for language localization. For example, add the &locale=fr-fr to the end of the URL to specify French or any other language of choice.

Review the <a> element for the Excel Interactive View to see the data-xl- attributes used. You can also include other data-xl- attributes to further customize what the end user will see in the UI. These are data-xl-fileName="MyBookFileName" and data-xl-attribution="Brought to you by CompanyName", where data-xl-fileName is any arbitrary name you want to set as the default name for the file when downloaded, and data-xl-attribution is any arbitrary phrase you want to use to identify the provider of the data. The data-xl-tableTitle used in the <a> element in the code and the data-xl-attribution attributes can be up to 255 characters in length. The data-xl-attribution value will be included in the downloaded spreadsheet.

The data-xl-buttonstyle attribute used in the <a> element in the code can have either the value of Standard or Small. Lastly, you can use a data-xl-dataTableID attribute to uniquely identify your table. However, it’s important to note that none of the <a> element attributes are required for the Excel Interactive View to render; it will provide an appropriate default value for any data-xl- attribute you do not specifically include.

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.

  • Service root URI: This includes the host and service endpoint; for example, https://YourSharePointHost/SiteIfOne/_vti_bin/ExcelRest.aspx/.
  • Resource path: This includes the name of the document library, name of the Excel file, and the literal value OData to define the protocol request; for example, LibraryName/FileName.xlsx/OData/.
  • System query options: You can use a number of optional parameters individually or in concert with others to describe specifically what you want to retrieve from a document. For instance, you might just want to know what tables are available in a given document, so you would use the system query option, $metadata.

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.


TRY IT OUT Accessing Excel Table Data with OData for Excel Services (C14ODataAccessSample.zip)
In this Try It Out you exercise a number of system query options using the new OData protocol for Excel Services.

1. Extract the ODataAccessSample.xlsx file to a location on your machine from the ODataAccessSample.zip file.

2. Log in to SharePoint, whether on-premises or Office 365, and access the site document library that you can use for this exercise. Upload the ODataAccessSample.xlsx document.

3. When the document has uploaded, right-click the link and select Copy shortcut, or click the ellipsis (...) and copy the URL from the fly-out menu.

4. You will be doing a number of copy/paste actions in the browser so first construct the service root and resource path part of the URL you need. For the service root, copy the portion of the URL up through the ending slash (/) just prior to the name of your document library, and then add _vti_bin/ExcelRest.aspx/ after it. Your URL should match the following pattern (you should use HTTP or HTTPS as appropriate for your service host):
https://YourSharePointHost/SiteIfOne/_vti_bin/ExcelRest.aspx/
5. For the resource path, from your original URL, copy from the beginning of the document library name through the full filename for the Excel document and paste it on the end of your service root. Add /OData/ following it.
https://YourSharePointHost/SiteIfOne/_vti_bin/ExcelRest.aspx/LibraryName/
ODataAccessSample.xlsx/OData/
6. With the service root and resource path fully constructed, now you can exercise OData access using the system query options. But before you do, open the ODataAccessSample.xlsx file in Excel. You can see three worksheets, each with a table on it. These tables could all be on one sheet, so that is not important, but what is noteworthy is that the document contains three named ranges, each associated with a table. On the left side of Excel, just under the ribbon, click the drop-down arrow for the Name box. Here you will see three names that begin with Table_.

7. Click each of the Table_ names to navigate to each of the tables and get a sense for the data they contain. Excel Services will expose these three tables to you through its OData service. You can either close Excel at this point or leave it open for reference.

8. Return to your browser that is logged on to your SharePoint site. Copy your fully constructed URL and paste it into the address bar, add $metadata to the end, and press Enter. Excel Services returns the entity data model for all the named tables within the document. This includes the property name for each column and its data type.

9. Replace $metadata with Table_Sales_by_Categories and press Enter. By your just entering the name for the table, Excel Services will return the first 500 rows (0-based count, so from 0–499), which it considers a page. The maximum number of rows to be delivered at a time is 500. Because you do not have enough rows you cannot exercise the $skiptoken system query option. However, with this option you can skip to get rows in pages; for example, $skiptoken=499 starts you at the second page of data and returns the next 500 rows. The $skiptoken=N is where Excel Services calculates N+1 for the starting row for the next page. This way you can iteratively retrieve data from the table in your application if needed. However, you can use $skip on this example. Add ?$skip=5 after Table_Sales_by_Categories and press Enter to skip the first 5 rows.

10. In the browser address bar, remove ?$skip=5, replace it with ?$orderby=CategoryName, and press Enter. Now the data is returned sorted by category name.

11. In the address bar, remove Table_Sales_by_Categories?$orderby=CategoryName and replace it with Table_Sales_Totals_by_Amounts?$orderby=SaleAmount desc&$top=5. Here the sales totals table is sorted in descending order and the top 5 rows are returned.

12. In the address bar, remove Table_Sales_Totals_by_Amounts?$orderby=SaleAmount desc&$top=5 and replace it with Table_Alphabetical_list_of_products?$filter=UnitPrice gt 200. Here you apply a filter to the data to only return rows where the unit price for the product is greater than 200.

13. In the address bar, remove Table_Alphabetical_list_of_products?$filter=UnitPrice gt 200 and replace it with Table_Alphabetical_list_of_products?$select=ProductName. Here you requested to only return the product name column. So in cases where you do not want every column in the table, you can request the ones you specifically want with the $select system query option. You can string multiple property names together for the selection using a comma as the delimiter.
14. In the address bar, remove Table_Alphabetical_list_of_products?$select=ProductName and replace it with Table_Sales_by_Categories?$inlinecount=allpages. Here you request a count of the number of rows in the table. This helps when you need to programmatically assess how many iterations you might need in conjunction with $skiptoken. You’ll see this value returned on the eighth line of XML.

How It Works
In this Try It Out you learned how to construct an appropriate OData URL for accessing SharePoint Excel documents according to the service root, resource path, and system query options pattern. You also exercised all the available system query options currently available for Excel Services except one: $format. Although this system query option is available, the Atom XML format is the only supported format that the Excel Services OData service will provide.

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.

Other  
  •  Sharepoint 2013 : Developing Applications Using Office Services - WOPI and the New Office Web Apps Server
  •  Sharepoint 2013 : Building a BCS-enabled Business Solution : Building an Integrated BCS Solution with an App for SharePoint Containing an App for Office
  •  Business Connectivity Services in Apps for SharePoint 2013 : Building an App-level BCS Solution for Office 365 SharePoint Online
  •  Business Connectivity Services in SharePoint 2013 : Adding a Business Data Connectivity Model to Office 365 SharePoint Online
  •  Remote Event Receivers in Sharepoint 2013 : Introducing Remote Event Receivers
  •  InfoPath with SharePoint 2010 : Apply Rich Text to the Entry
  •  InfoPath with SharePoint 2010 : Add Changed Event Code for Tracking Changes
  •  InfoPath with SharePoint 2010 : Set Up the Form for Tracking Changes
  •  Sharepoint 2013 : Farm Management - Rename a Server on the Farm, Display the Configured Managed Paths
  •  Sharepoint 2013 : Farm Management - Retrieve the System Accounts, Retrieve Managed Accounts
  •  
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us
    programming4us
     
     
    programming4us