Rendering Excel Sheets with Excel Web Services and Excel Web Access

Went through this in work, just thought I would share it :-)

Let's start by creating our Excel sheet.

image

Nothing fancy, couple lf KPIs pull me back on track. Looks like I'm doing well with the games part! hehehe Smile

Now after creating the Excel sheet, we need to upload it.

Let's create a Document Library and place it there.

image

To continue with this, let's make sure we understand how Excel Services works:

image

(Source: https://msdn2.microsoft.com/en-us/library/ms582023.aspx)

There are three core Excel Services components:

  • Excel Web Access
  • Excel Web Services
  • Excel Calculation Services

At the moment we will be working with 2 of them: Excel Web Access and Excel Web Services. We use the Excel Web Access to render the Excel sheets on the browser and the Excel Web Services to facilitate access to Excel Services Web Services.

The logic goes something like this, the Excel Web Access web part will try to render the Excel sheet that we stored in the Document Library, and then Excel Web Services will facilitate the access to that Excel Sheet. However, it can't do that unless we define our Document Library as a Trusted File Location. To do that we must pay the SSP site a visit. Now under the Excel Services Settings section on the SSP sites homepage you will find a link for trusted file locations.

image

The form will display all the defined Trusted File Locations. To add a new one, click on Add trusted file location.

Fill the address field with you document library URL and select Windows SharePoint Services for the Location Type and click ok. At the moment we need to define these 2 properties only, keep the others as default.

Finally, let's go to our page and add the Excel Web Access web part. Under the Business Data web parts category in the Add Web Part dialogue

image

After adding the web part, click on open the tool pane link inside it.

image   image

Inside the tool pane we need to define the workbook. We can do that by browsing to the document library, selecting our excel sheet, and clicking OK. No need to define anything else here.

image

And VOILA Smile you have your Excel sheet rendered in your browser!