Using the Web Services APIs to open a workbook for editing and set calculation options

This post is going to be pretty light-weight. It will show how to use the OpenWorkbookForEditing and SetCalculationOptions methods. I won’t create some over-arching example – the skills you have in interacting with sessions in Excel Services are the going to be the same. I will discuss the behavior and what one can expect.

OpenWorkbookForEditing

In SharePoint 2007, developers could open workbooks for transient operations – once a workbook was closed, the sessions went away and all changes to the workbook disapeared. with the introduction of the ability of editing workbooks on EWA, the Web Services API follows suit and allows the developer to apply editing to the workbook. The same methods used to change values in a view-session (that’s what we call the SharePoint 2007-type sessions) work in editing and they work the same way as they did before.

Here’s what an OpenWorkbookForEditing call looks lilke:

static void Main(string[] args)

{

    ES.ExcelService client = new ES.ExcelService();

    client.Credentials = System.Net.CredentialCache.DefaultCredentials;

    ES.Status[] status;

    string sessionId = client.OpenWorkbookForEditing("https://tsdlt2/docs/bugs.xlsx", "", "", out status);

    client.CalculateWorkbook(sessionId, ES.CalculateType.Recalculate);

    client.CloseWorkbook(sessionId);

}

Of course, the first thing you notice is that there’s not much difference between the call to OpenWorkbookForEditing() and OpenWorkbook() . The important part though is the fact that once this function exists, the workbook will be saved, recalculated back into the server. A few things to know about using the WSAPI to edit workbooks:

1. Calling CloseWorkbook() does not really close the workbook – it just takes the API out of the list of people using the workbook.

2. The workbook will save regardless to whether or not you call .Save(). Because Excel Services uses an AutoSave mechanism for saving workbooks, any change is actually written to the workbook.

3. When editing a workbook, you may get an error if the server recycled by the admin or crashed (this is exactly the same as with view sessions). However, when doing editing, re-opening the workbook has a very good chance of having your changes in it (again, because of auto-save).

4. When opening a workbook via the API, the presence UI in Excel Services (the thing that shows you who is editing the workbook) will show the user under which the API is running:

In this case, xladmin is the user inside EWA, viewing the file, and shaharp is the user who is running the APIs. The little swirly icon you see will be replaced by something that indicates that  it’s an API user (I believe we chose a Robot for that).

SetCalculationOptions

An API that was requested often by user is the SetCalculationOptions API which can change whether or not a workbook calculates automatically. It is especially useful if you have a workbook that is, by default, auto-recalced and you want to disable that feature while calling multiple SetRange/SetCell calls. That way, the workbook needs not calculate each time and developers can get more perf out of it. The call is straightforward:

client.SetCalculationOptions(sessionId, ES.WorkbookCalculation.Manual);

This call will change the calculation mode to manual, meaning that when you set values into the workbook, it will not recalculate. You can turn auto-recalc back on by passing in the Automatic enum value.

I also want to show you the SaveWorkbookCopy() API, but I will show it in one of the future Javascript examples I will post.

Comments

• Anonymous
  July 14, 2010
  The comment has been removed
• Anonymous
  March 14, 2011
  Same error for me -- "This workbook contains external data or functions that can return personal information."  Can't the workbook have any external data for it to be able to refresh??