SharePoint 2013, Office Web Apps 2013 and Excel Service application issue

What is the Issue?

If you Installed office web app server and associated it with your SharePoint farm, now you have you SharePoint 2013 farm connected to a WAC 2013 farm and then you use all your Excel skills and starts retrieving data from other data sources.

You would be able to see the preview of Office documents in Search result, Preview of office documents in SP Document library and you will be able to edit the office documents word, excel, PowerPoint in web Browser.

But you won’t be able to see the excel Pivot table or Excel sheet’s data that is coming from outside [external data source] unfortunately your data will not refresh and you will get error.

There is conflicts between office web app Server and Excel Service application in SharePoint Issue is using Both Excel Service application and office web app server in SharePoint

For instance this is what you will get if you try to display a Power View worksheet:

When this issue occurs?

When we install office Web app server and associated it with SharePoint farm.

And our SharePoint Farm is configured with Excel Service application to utilize excel features in SharePoint.

So here if we have already configured the Excel Service application on SharePoint farm to use excel calculation service or render Excel Pivot table, you will be in trouble

We can use one of them either we can use office web app server or Excel Service Application.

 Excel Service Application in SharePoint, Why we need it?

Excel Service application is a feature of SharePoint that enable us to view and interact with Excel workbook in web browser window similar to how we use Excel Sheet on our local computer.

Excel Service application in SharePoint uses a built in calculation engine to connect to external data and render the data in Excel Sheet, Pivot table, queries and Formulas.

Excel Service uses server technology to provide Excel Calculation and similar functionality in browser window.

Excel Service was introduce in SharePoint 2007 Version and still we are using with latest version even in SharePoint online.

Office Web App Server in SharePoint, Why we need It?

Office Web App Server and Excel Service in SharePoint have a lot of common but they are not the same.

Both office web app Server and Excel Service application enable us to view Excel workbook in a browser window and both enables us to interact with Excel features and explore data in Excel on the web browser.

What is difference between two?

Excel Service application support external data connection, data models and ability to intract with items that use data model such as (Excel PivotChart report, Excel Pivot table, reports and timeline controls)

But office web app Server does not support external data connection or the ability to interact with reports that fetching the data out of the SharePoint such as external data source using BCS or other RDBMS. Office web app server provides feature to view and interact with excel workbook in browser as Excel Service application does.

Excel Service application provides more business intelligent functionality than office web app Server.

But Excel Service application does not support to create and Edit Excel workbook in Browser window, simply you can’t edit the Excel data in Browser.

Is there any service Limitation or what is Culprit?

“Office Web Apps 2013 cannot read data from external data sources” if your any Excel web part that fetching data outside from SharePoint like using BDC or BCS any other data source.

And Excel Service application does not support to create and Edit Excel workbook in Browser window, in other way if we say like user can’t edit the Excel workbook data in web Browser.

It means both have their own limitation and own specific features, and both have conflicts we can’t use both of them to achieve the similar functionality.

Let’s come to Solution now?

A common solution but that described most of the blogs is to remove only the Excel Web Access functionality from office web app Server to leverage the Excel Service application’s functionality so that we can show our excel data that is coming from external data source.

But in this case we will not be able to leverage the office web app server‘s functionality like excel preview and editing excel data in browser, not a good solution

What we need to do? To apply above solution Go to SharePoint Server machine [where you Central Admin hosted]

Go to SharePoint Management Shell by go to Start – All Programs – SharePoint 2013 Products – Click on SharePoint Management Shell

And type paste the following command.

Remove-SPWOPIBinding –Application Excel

Now you are able to use Excel Service feature and able to render all your excel Sheets that getting data from External data source as earlier you used to do. The Problem is

You are not using the office web app server’s cool features that provides to

View the preview of excel file, in SharePoint Search result or by Go to SP document library Click on three small dot … to see the document property and you will see the Previews of office document but you wont see the preview of Excel File, bad right.

 

See the snap shot here.

Office Document Priew Image

 

Editing in Browser no, you cant, see this.

EditinginBrowser

Then what is better solution : using this you can use all good features of Excel Service application and all good feature of office web app server, cool hmm ?

 

Better Solution to stop rendering Excel files in Office Web Apps 2013

SharePoint is very cool and SharePoint 2013 allows us to do suppressions for the different WOPI Bindings. What we would like to do is to suppress the WOPI Binding that is responsible for viewing of Excel files this means only for viewing the Excel sheet we will use Excel Service application.

What you need to do ?

Go to SharePoint Management Shell by go to Start – All Programs – SharePoint 2013 Products – Click on SharePoint Management Shell

And type paste the following command.

New-SPWOPISuppressionSetting -Extension XLSX -Action View

This command will suppress the WOPI Binding for the extension XLSX (Excel files) and for the WOPI Action View. If we now have Excel Services running in our SharePoint farm then Excel Services will be responsible for viewing Excel (XLSX) files. But Office Web Apps 2013 will be responsible for previews (in search and document libraries) and editing of the files.

You don’t have to do anything else, no IISRESET (this must be the only time you don’t need this for a SharePoint config change!) or anything else.

How to Verify if Excel files are rendered by Office Web Apps

If we want to check like Excel files are rendered using Office Web app server or Excel Calculation Service application we can easily take a look at the URL.

When you click on any Excel File and open it in browser check the URL

When Office Web Apps is responsible for the rendering the URL will look something like this:
https://server/_layouts/15/WopiFrame.aspx?sourcedoc=/Documents/excel.xlsx&….
And when Excel Calculation Service application is rendering the document it should look like the following:
https://server/_layouts/15/xlviewer.aspx?id=/Documents/excel.xlsx&…

 

You can also check the WOPI suppression settings using the Get-

SPWOPISuppressionSetting cmdlet if you have shell access to the SharePoint farm.

If you want you’re Office Web Apps viewing back…

No doubt in this anytime we can revert back to using the Office Web Apps for rendering of the Excel files. This is done using the

Remove-SPWOPISuppressionSetting cmdlet.

 

Conclusion Sir Ji:

Basically for every farm where you are using BI features such as Excel Services and/or PowerPivot you need to do this WOPI suppression setting. But if you don’t have these requirements you should stick to using Office Web Apps 2013, to avoid unnecessary Service Applications and take advantage of the features in WAC that are not available in Excel Services.

 

 

 

 

About Krishana Kumar

Krishana Kumar is SharePoint Architect/Trainer having Architecture experience with high volumes at Enterprise level and global scale - creation of highly scalable solutions with global user base and geographically distributed architectural components. Good knowledge of SharePoint best practices and governance models. I hold Two Master degree in Computer Science with over 11 years of experience working on Microsoft Technologies specially SharePoint, Project, .NET and other Information Worker Technologies. Having good exposer in Client side scripting Angular.js, backbone and Node. I am currently responsible for SharePoint Infrastructure set up and leading teams in various medium and large scale projects, architecting, designing & installing SharePoint farms, developing custom components,, and providing advanced SharePoint administration and development training to teams and customers. I regularly speaks in various SharePoint User Groups and other Events. I have MCSA Windows Azure, MCSA Office 365, MCSE & MCSD SharePoint 2013, Microsoft Certified Developer (MCD) and holds MCPD, MCTIP and MCTS for SharePoint 2010, MCTS MOSS 2007 & WSS 3.0, MCPD, MCITP (EPM 2010 & 2007) and MCSD .NET.
This entry was posted in General Interest. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *