Office 2010 Service Applications Lab

Hands-On Lab

Office 2010 Service Applications

Lab version:1.0.0

Last updated:1/14/2019

Contents

Overview

Exercise 1: Create Reports using Excel Services

Task 1 – Create the Report Generation web part

Task 2 – Use Excel Services REST interface to retrieve the data

Task 3 – Configure the report site web parts

Exercise 1 Verification

Exercise 2: Generating Documents using Word Services

Task 1 – Create the Printable Document Library event receiver

Task 2 – Build the PrintingStatus web part

Task 3 – Place the Printing Status web part on the page

Exercise 2 Verification

Summary

Overview

In this lab exercise,use the Office 2010 services applications to build a report generation application. This application uses Excel Services to access an Excel workbook via REST services and generate a Word document based on the data. Word Services converts the Word document into a PDF and XPS file and stores it in a report repository. Finally, Visio Services presents a graphical representation of the process to the user showing which reports have been generated.

Objectives

In this lab you will:

Use Word Services to generate PDF and XPS files based on a Word document
Use Excel Service’s REST interface to access information in an Excel workbook

System Requirements

This lab assumes that you have SharePoint Server installed in a test environment. For guidance on how to setup SharePoint Server see Note that any URL referred to in this lab must be adjusted for use with your local setup. You must have the following items to complete this lab:

Microsoft® Windows® Vista SP1 or Microsoft® Windows Server 2008 (64-bit)
Microsoft® SharePointServer 2010 (64-bit)
Microsoft® Office Professional Plus 2010 (32-bit or 64-bit)
Microsoft® Visual Studio 2010

Setup

You must perform the following steps to prepare your computer for this lab. This consists primarily of creating a SharePoint site collection at and installing the code snippets for this lab.

Run the command file Setup.batlocated at %Office2010DeveloperTrainingKitPath%\Labs\OfficeServiceApplications\Source\.

Exercises

This Hands-On Lab is comprised of two exercises:

Create reports using Excel Services
Generating documents using Word Services

Estimated time to complete this lab: 60minutes.

Starting Materials

This Hands-On Lab includes the following starting materials.

Visual Studio solutions. The lab provides the following Visual Studio solutions that you can use as starting point for the exercises.Lab instructions will reference the Training Kit location after installation as %Office2010DeveloperTrainingKitPath%.

◦%Office2010DeveloperTrainingKitPath%\Labs\OfficeServiceApplications\Source\[language]\Starter\StateReportApplication\StateReportApplication.sln: Uses Word Services and Excel Services to generate XPS and PDF documents based on data stored in an Excel workbook.

Note:Inside the lab’sSourcefolder, you will find aSolution folder containing an endsolution with the completed lab exercise.

Exercise 1: Create Reports using Excel Services

In this exercise you will access an Excel Workbook using the Excel Services REST interface. The results of these REST requests will be integrated into a Word template document using the Open XML SDK and archived in a document repository.

Task 1 – Create the Report Generation web part

In this task, you will create the web part that initiates report generation. You will define the web part’s UI and put the code in place to start the process of building the report.

Open the starter StateReportApplication project in Visual Studio 2010
Open the StateReportApplicaiton.sln file in the %Office2010DeveloperTrainingKitPath%\Labs\OfficeServiceApplications\Source\[language]\Starter\StarteReportApplication folder
Add a new Visual Web Part to the project
Right click StateReportApplication in the Solution Explorer and click Add -> New Item
In the Add New Item dialog, select the Visual C#\Visual Basic -> SharePoint -> 2010 template category
Select the Visual Web Part template
Set the Name to GenerateReport and click Add

Figure 1(a)

Figure 1(b)

Add New Visual Web Part

Design the user interface for the web part
Open the designer by right clicking GenerateReportUserControl.ascx in the Solution Explorer and clicking View Designer
Switch to the Source view using the button at the bottom of the window
At the bottom of the ascx file, add the following markup

ASPX

<table>

<tr

<td>Generate Report for </td>

<td<asp:DropDownList ID="States" runat="server" /</td>

</tr

<tr<td colspan="2"</td</tr

<tr

<asp:Button ID="GenerateReport" Text="Generate Report"

runat="server" />

</td>

</tr

</table>

Switch to the Design view using the button at the bottom of the window and verify it looks like the image below

Figure 2

Completed Web Part Design

Load the web part with a list of all states
Switch to the code for the web part by right clicking the designer window and clicking View Code
Add the following using statements

using System.Collections.Generic;

using System.IO;

using System.Linq;

using Microsoft.Office.Word.Server.Conversions;

using Microsoft.SharePoint;

Visual Basic

Imports System.Collections.Generic

Imports System.IO

Imports System.Linq

Imports Microsoft.Office.Word.Server.Conversions

Imports Microsoft.SharePoint

Add code to the Page_Load method to make sure the drop down list is only initialized when the page is first loaded

if (!IsPostBack || States.Items.Count == 0)

{

}

Visual Basic

If (Not IsPostBack) OrElseStates.Items.Count = 0 Then

End If

Create a list of states that exclude the ones that already have reports and add them to the drop down list

if (!IsPostBack || States.Items.Count == 0)

{

States.Items.AddRange(

Constants.States.Select(n => new ListItem(n)).ToArray());

}

Visual Basic

If (Not IsPostBack) OrElseStates.Items.Count = 0 Then

States.Items.AddRange(Constants.States.Select(Function(n) New ListItem(n)).ToArray())

End If

Add code to the button click event handler to create a new folder for the state and generate the report in the new folder
Open the designer for the visual web part by right clicking GenerateReportUserControl.ascx in the Solution Explorer and selecting View Designer
Generate the click event handler by double clicking the button in the designer
In the new GenerateReport_Click event handler, add the following code to lookup the State Reports document library

SPWeb web = SPContext.Current.Web;

SPDocumentLibrarystateReports =

SPContext.Current.Web.Lists["State Reports"]

as SPDocumentLibrary;

Visual Basic

Dim web As SPWeb = SPContext.Current.Web

Dim stateReports As SPDocumentLibrary = TryCast(SPContext.Current.Web.Lists("State Reports"), SPDocumentLibrary)

Using the States control’s SelectedValue, lookup the folder in the States Report document library

string state = States.SelectedValue;

SPListItemlistItem =

stateReports.Folders.CastSPListItem>().

FirstOrDefault(n => (n["Name"] as string) == state);

Visual Basic

Dim state As String = States.SelectedValue

Dim listItem As SPListItem = stateReports.Folders.Cast(Of SPListItem)().FirstOrDefault(Function(n) (TryCast(n("Name"), String)) = state)

If no folder for the selected state was found, add the new folder named after the selected state

if (listItem == null)

{

listItem = stateReports.AddItem(string.Empty, SPFileSystemObjectType.Folder);

listItem["ContentTypeId"] = SPContext.Current.Web.ContentTypes["Folder"].Id;

listItem["Name"] = state;

listItem.Update();

}

Visual Basic

If listItem Is Nothing Then

listItem = stateReports.AddItem(String.Empty, SPFileSystemObjectType.Folder)

listItem("ContentTypeId") = SPContext.Current.Web.ContentTypes("Folder").Id

listItem("Name") = state

listItem.Update()

End If

Using the StateReport class, generate a new report and save it in the new folder

using (MemoryStream stream = new MemoryStream())

{

StateReport report =

new StateReport(web, "Data/StateRankings.xlsx", state);

report.GenerateReport(stream);

listItem.Folder.Files.Add("Report.docx", stream.ToArray(), true);

}

Visual Basic

Using stream As New MemoryStream()

Dim report As New StateReport(web, "Data/StateRankings.xlsx", state)

report.GenerateReport(stream)

listItem.Folder.Files.Add("Report.docx", stream.ToArray(), True)

End Using

Task 2 – Use Excel Services REST interface to retrieve the data

In this task, you will add the Excel Services REST requests to the web part to provide the appropriate data to the report generation code.

Setup the WebClient object used to make web request to Excel Services
Open the StateReport.cs(StateReport.vb in case of VB) class by double clicking it in the Solution Explorer
In the GenerateReport method, create a new WebClient object and set it to use the default user credentials

WebClient client = new WebClient();

client.UseDefaultCredentials = true;

Visual Basic

Dim client As New WebClient()

client.UseDefaultCredentials = True

Load the StateReportCard template docx file into a new WordprocessingDocument
Write the template docx file to the stream

stream.Write(Properties.Resources.StateReportCard,

0, Properties.Resources.StateReportCard.Length);

Visual Basic

stream.Write(My.Resources.StateReportCard, 0, My.Resources.StateReportCard.Length)

Create a new writable WordprocessingDocument object using the stream

using (WordprocessingDocument document =

WordprocessingDocument.Open(stream, true))

{

}

Visual Basic

Using document As WordprocessingDocument = WordprocessingDocument.Open(stream, True)

End Using

Perform the REST requests for the graphical report
Inside the using statement wrapping the WordprocessingDocument, generate the REST url to request the Report chart from the Excel Workbook

string reportRestUrl =

string.Format("{0}/model/Charts('Report')?Ranges('State')={1}",

m_baseUrl, m_state);

Visual Basic

Dim reportRestUrl As String = String.Format("{0}/model/Charts('Report')?Ranges('State')={1}", m_baseUrl, m_state)

Note: The Ranges(‘State’) parameter in the url sets a cell in the workbook to a specific state causing the report’s content to be updated

Using the rest url, open the URL as a stream and write it to the first image part in the WordprocessingDocument

using (Stream imageStream = client.OpenRead(reportRestUrl))

document.MainDocumentPart.ImageParts.First().FeedData(imageStream);

Visual Basic

Using imageStream As Stream = client.OpenRead(reportRestUrl)

document.MainDocumentPart.ImageParts.First().FeedData(imageStream)

End Using

Perform the REST request for the atom feed representing the report’s title
Immediately following the image write, generate the REST url to retrieve the title atom feed

string titleRestUrl =

string.Format("{0}/model/Ranges('Title')" +

"?Ranges('State')={1}&$format=atom",

m_baseUrl, m_state);

Visual Basic

Dim titleRestUrl As String = String.Format("{0}/model/Ranges('Title')" & "?Ranges('State')={1}&$format=atom", m_baseUrl, m_state)

Use the rest url to retrieve the atom xml data for the range and write it to the custom XML part in the document

using (Stream titleStream = client.OpenRead(titleRestUrl))

WriteCustomXmlPart(document.MainDocumentPart,

"{3D15FA4A-EB75-46F1-93D1-8DA4AAFE30A5}", titleStream);

Visual Basic

Using titleStream As Stream = client.OpenRead(titleRestUrl)

WriteCustomXmlPart(document.MainDocumentPart, "{3D15FA4A-EB75-46F1-93D1-8DA4AAFE30A5}", titleStream)

End Using

Note: The WriteCustomXmlPart method is a helper method that finds a custom xml part based on its ID and then writs the stream into the part

Perform the REST request for the atom feed representing the report card data
Immediately after the title custom xml part write, write the report card custom xml

string reportCardRestUrl =

string.Format("{0}/model/Ranges('ReportCard')" +

"?Ranges('State')={1}&$format=atom",

m_baseUrl, m_state);

using (Stream reportCardStream = client.OpenRead(reportCardRestUrl))

WriteCustomXmlPart(document.MainDocumentPart,

"{5182EF05-94F6-41B6-B0F4-6BE836E29F5F}", reportCardStream);

Visual Basic

Dim reportCardRestUrl As String = String.Format("{0}/model/Ranges('ReportCard')" & "?Ranges('State')={1}&$format=atom", m_baseUrl, m_state)

Using reportCardStream As Stream = client.OpenRead(reportCardRestUrl)

WriteCustomXmlPart(document.MainDocumentPart, "{5182EF05-94F6-41B6-B0F4-6BE836E29F5F}", reportCardStream)

End Using

Task 3 – Configure the report site web parts

In this task, you will place the new web part on the site’s main page and configure the site for use.

Build and deploy the State Report application to SharePoint
Right click StateReportApplication in the Solution Explorer and click Deploy
Enable the Office Service Application features on the site
In Internet Explorer navigate to
Click Site Actions -> Site Settings
On the Site Settings page, click the Site collection features link
Activate the SharePoint Server Enterprise Site Collection featuresfeature

Figure 3

Activated Site Collection Feature

Navigate back to the Site Settings page using Site Actions -> Site Settings
Click the Manage Site Features link

Figure 4

Activated Site Feature

Activate the SharePoint Server Enterprise Site features feature

Add the GenerateReport Web Part to the main page
In Internet Explorer navigate to
Click Edit Page on the Page ribbon tab
Click Add a Web Part in the left web part zone
In the Web Part pane, choose the Lists and Libraries category and the State Reports web part
Click Add to add the web part to the page

Figure 5

Add State Reports List Web Part

Click Add a Web Part in the right web part zone
In the Web Part pane, choose the Custom category and the GenerateReport web part

Figure 6

Add Generate Report Web Part

Click Add to add the web part to the page
Stop editing by clicking the Stop Editing button on the Page ribbon tab

Figure 7

Completed Web Part Page

Exercise 1 Verification

In order to verify that you have correctly performed all steps of exercise 1, proceed as follows:

Test the Web Part

Test the GenerateReport web part by generating reports for several states.

In Internet Explorer navigate to
Choose AL in the drop down list inside the GenerateReport web part and click Generate Report
Verify that AL shows up in the State Reports list

Figure 8

Alabama State Report

Verify the report is correctly generated in the AL folder inside the State Reports document library
Click the AL link in the State Reports web part
Click the drop down arrow to the right of Report and select Edit in Microsoft Word

Figure 9

Edit Report in Word 2010

Verify the document contains information for Alabama

Figure 10

Generated Report viewed in Word 2010

Exercise 2: Generating Documents using Word Services

In this exercise you will be adding an Event Receiver to the State Reports document library that will convert all docx files submitted into XPS and PDF files. This is done using Word Services and a scheduled job that converts all registered documents every 15 minutes. To provide feedback to the user you will also be implementing a simple web part that will provide a summary of the jobs submitted to Word Services.

Task 1 – Create the Printable Document Library event receiver

In this task, you will define a List Item Event Receiver that will fire anytime an item a new file is added or updated in the document library. This Event Receiver will start a print job that will convert the added or updated docx file into an XPS and PDF document.

Create a new event receiver item in the PrintableDocumentLibrary item
Right click PrintableDocumentLibrary in the Solution Explorer and select Add -> New Item
In the Add New Item dialog, select the Visual C#\Visual Basic -> SharePoint -> 2010 template category
Select the Event Receiver template
Set the Name to PrintableDocumentReceiver and click Add
In the dialog check the checkboxes by
An item was added
An item was updated

Figure 11

Create new Event Receiver

Click Finish to create the Event Receiver

Create a StartJob method that accepts the files to convert and the format and starts a conversion job
Add the following using statement to the file

using Microsoft.Office.Word.Server.Conversions;

Visual Basic

Imports Microsoft.Office.Word.Server.Conversions

In the PrintableDocumentReceiver class, add the following StartJob method

private void StartJob(SPWeb web, SaveFormat format,

string sourceUrl, string targetUrl)

{

}

Visual Basic

Private Sub StartJob(ByVal web As SPWeb, ByVal format As SaveFormat, ByValsourceUrl As String, ByValtargetUrl As String)

End Sub

In the new method, create a ConversionJobSettings object to control how the documents are converted

ConversionJobSettings settings = new ConversionJobSettings()

{

OutputFormat = format,

UpdateFields = true,

OutputSaveBehavior = SaveBehavior.AlwaysOverwrite

};

Visual Basic

Dim settings As New ConversionJobSettings() With {.OutputFormat = format, .UpdateFields = True, .OutputSaveBehavior = SaveBehavior.AlwaysOverwrite}

Create a new ConversionJob using the settings and your current user token

ConversionJobconversionJob =

new ConversionJob(Constants.WordServicesAppName, settings)

{

SubscriptionId = Constants.SubscriptionId,

UserToken = web.CurrentUser.UserToken

};

Visual Basic

Dim conversionJob As New ConversionJob(Constants.WordServicesAppName, settings) With {.SubscriptionId = Constants.SubscriptionId, .UserToken = web.CurrentUser.UserToken}

Note: The SubscriptionId is a shared Guid that allows the application to find its active jobs.

Add the source and target file urls to the job and then start it

conversionJob.AddFile(sourceUrl, targetUrl);

conversionJob.Start();

Visual Basic

conversionJob.AddFile(sourceUrl, targetUrl)

conversionJob.Start()

Note: It is possible to add multiple source/target pairs and even choose to convert entire folders or lists. In this case, you are starting one job for each type of conversion.

Implement a PrintDocument method that uses StartJob to convert all docx files to XPS and PDF
Add a new PrintDocument method to the PrintableDocumentReceiver class

private void PrintDocument(SPWeb web, string listItemUrl)

{

}

Visual Basic

Private Sub PrintDocument(ByVal web As SPWeb, ByVallistItemUrl As String)

End Sub

Wrap all the code in the method with an if statement that checks if the listItemUrl ends with a .docx extension

private void PrintDocument(SPWeb web, string listItemUrl)

{

if (listItemUrl.EndsWith(".docx"))

{