Understanding versioning with Excel Web App

CoreyRoth — Tue, 13 Mar 2012 22:19:26 GMT

If you have used Excel Web App (part of Office Web Apps), you are familiar with the fact that there is no “Save” button. The changes you make are immediately saved back to SharePoint and you even have multiple people editing the spreadsheet at the same time with Co-authoring. I had always wondered what happened when it comes to versioning so today I decided to dig a little deeper to find out what happens. We’ll start with editing an Excel document in the browser with versioning enabled. Whenever you change the value of a cell, the results are immediately saved back to SharePoint. However, opening a second browser to look at the versioning shows that we are still on the same version.

The changes have been saved to SharePoint, but no version has been created. Effectively, you can think of this as the same thing as calling .SystemUpdate() for you programmers out there. However, what I have found is if you wait long enough (a couple of minutes), eventually it will go and commit a new version. Otherwise, it will commit a new version when you

A new version will also be created if you close the document (either close out of the file menu or using the breadcrumbs). Where it gets really interesting is when you have multiple editors. You’ll see the same behavior as before, where a new version will be saved a few minutes after the first change. However, if multiple changes have occurred, it will write the last author’s name into the version.

However, ultimately whomever closes their browser session last (or times out last apparently) is the final author of the version. In this case, Anna closes the document first, then I closed it. It then goes back and updates the author and modified date on version 4.

In summary, Excel Web App will create a new version automatically for you if one hasn’t been created whenever you close the editor or after a period of time has elapsed after the first change. Whomever makes changes last will end being the ultimate author of that version. Btw, I have never seen it create a second version of a document when you leave it open for a long period of time. It appears that it will only create one version for a given Co-authoring editing session. Hopefully, this help you understand how versioning with Excel Web App works better. These examples were demonstrated using my Office 365 E3 account.

How to: Use the Chart Web Part with Excel Services

CoreyRoth — Thu, 02 Sep 2010 20:04:35 GMT

Hopefully, you were able to impress the boss with my last post on the Chart Web Part. I explained how to use the BCS with the Chart Web Part to display great looking charts using external data. Another option is to use Excel Services. Excel Services is pretty easy to setup now and you usually don’t have to do much configuration out of the box. Let’s see what we can do. This assumes you have Excel Services installed and configured to trust spreadsheets from your SharePoint server.

First, let’s start with my super awesome spreadsheet. It's a simple example, but hopefully you get the idea. If you have Excel Services working, you should have the View in Browser option when you are looking at a file in a document library. Viewing Office documents in the browser might even be your default if you have installed Office Web Apps.

Clicking on that link, shows us the Excel document we are working with.

SharePoint 2010 has much better support for a wide variety of Excel documents. For the most part it will render most documents even if they have unsupported features in them. This post has some details on what is supported and what is not.

To use Excel Services with the Chart Web Part, we need the URL to the Excel document. You can get this in a number of ways. Just be aware if you try to copy a link from the document library it might give you a link to the xlviewer.aspx which will not work in the Chart Web Part. Make sure you get a link that references the .xslx file directly from your SharePoint server.

For example, this link will not work in the Chart Web Part:

http://sp2010/ECM/_layouts/xlviewer.aspx?id=/ECM/Company%20Documents/2010%20Budget.xlsx&Source=http%3A%2F%2Fsp2010%2FECM%2FCompany%2520Documents%2FForms%2FAllItems%2Easpx&DefaultItemOpen=1

This link will work in the Chart Web Part:

http://sp2010/ECM/Company%20Documents/2010%20Budget.xlsx

Once you have the link to your spreadsheet, edit a page and add a Chart Web Part to it. If you don’t remember how, you can find the details on it on my previous Chart Web Part post. Now, click the Data & Appearance link and then Connect Chart to Data. Now choose Excel Services. You will then be presented with a screen prompting you for the Excel Web Service URL, Excel Workbook Path, and Range Name. The Excel Web Service URL should already be filled out for you. It will look something like this.

http://sp2010/_vti_bin/excelservice.asmx

Now you need to provide a path to the workbook. Use a complete URL like the one I used above. The last thing you need to provide is the range of the spreadsheet you want to use. Here you need to know some Excel basics. You start by specifying the sheet name followed by the first cell using the !, $, and : delimiters. In my case I want A1 through B5 on Sheet1, it looks like this:

Sheet1!$A$1:$B$5

If the first row of your spreadsheet has column names, check the box. Here is what it looks like completed.

Click Next and if Excel Services like your spreadsheet, it should give you a preview of the data. If it doesn’t like it, it might give you a user friendly error or it might give you a Exception has been thrown by the target of an invocation error. If that is the case, there is likely an unsupported feature such as a chart that you need to remove for the spreadsheet to work. Here is what the preview looks like.

Click next, and you will choose the Axis and other settings for the chart. Change any settings you want and then you are done.

Once you click finish you should be able to see your data using the default chart.

Of course if you want another type of chart, you can click Data & Appearance and pick another type from the Customize your Chart link.

One thing to note is that Excel Services does cache data for a while so if you change data in the spreadsheet, it might take a few minutes for the graph to reflect your changes. This was a simple example, but as you can see it’s not very complicated to get a nice looking chart up and running quickly. If you do run into issues, I recommend tearing your spreadsheet apart and look for unsupported features.

Corey Roth [MVP] : Excel Services, SharePoint 2010, SharePoint

Understanding versioning with Excel Web App

How to: Use the Chart Web Part with Excel Services