Welcome to the new world of Excel Services in the Microsoft Office Sharepoint Server 2007 product line! During the next few months, I will be exploring the various programmability capabilities of Excel Services.
On this first post of many, we will make our first “Hello World” application that uses Excel Services to execute Excel 2007 Workbooks on the server. I will be using Visual Studio 2005 throughout my samples, but any language or tool that supports consuming web services will be able to achieve the same results.
Note that for the most part, my examples will be in C# - I will try to include VB.NET samples for most of these posts though. I will post the VB.NET equivalent of the sample in this post next monday.
Preparing the workbook for the sample
To begin, we will need a workbook. This will be our Hello World workbook and will contain a single cell (A1 of course) that contains the following:
A1: Hello
A2: Excel 2003
A3: =Concatenate(A1, " ", A2)
After creating this workbook, save it to your favorite Sharepoint Library or UNC and make sure Excel Services is aware of that location in its Trusted Locations list.
Creating the form and importing Excel Services API
Next, we will create a sample Win-Forms applications that will contain a single text-box control and two buttons. We will set the text on one button as “Get Default” and call it getDefaultButton and set the text on the other to be “Get Correct” and set the name to be getCorrectButton.
Our sample application will get the value in A3 when the getDefaultButton is pressed. However, when the getCorrectButton is pressed, it will change the value in A2 to the correct version of Excel and get the recalculated value in A3 (which will hopefully be “Hello Excel 2007”).
The last part of the needed preparation is to import the Excel Services API into our Visual Studio 2005 project. To do that, we will go to the Project/Add Web Services menu option. In the dialog that opens up, in the address text box we will enter:
http://<sharepoint server name>/_vti_bin/ExcelService.asmx?wsdl
Also, in the Web Service Name we will enter “ES” – this will be our namespace for all things Excel Services API from now on.
Finally, writing code to get information from Excel Services
Next, we are going to implement the code behind the Get Default button. The first step is to actually create the proxy object. We will place that object inside a using() statement since it implements the IDisposable pattern (note that neither the generated proxy class nor any of the classes it derives from actually do any real work in their dispose, but since the base class Component has a Finalizer, we will incur some unneeded perf degredation if we don’t dispose):
using (ES.ExcelService excelService = new ES.ExcelService())
{
excelService.Credentials = System.Net.CredentialCache.DefaultCredentials;
}
So far so good – we created the proxy instance. This instance will allow us to actually make calls into Excel Services. Note that we set the credentials to be those of the user that is currently logged on to the machine – if we don’t do that, there’s a good chance the web server will deny us access to the web-service.
Next we will call into the server, requesting that it opens the workbook we saved. We will do that by calling into the OpenWorkbook() method. This method will try to open the workbook on the server – if it succeeds, it will return a session id that we will use to continue the communication with the server. The other parameters this method takes are the locales to use (I am leaving these empty for now – more on those at a later blog) and an out parameter that will contain status information on the operation (more on that in a later post as well):
using (ES.ExcelService excelService = new ES.ExcelService())
{
excelService.Credentials = System.Net.CredentialCache.DefaultCredentials;
ES.Status[] stati;
string sessionId = excelService.OpenWorkbook(
"http://MyServerName/Shared Documents/HelloWorld.xlsx",
String.Empty, String.Empty, out stati);
}
If our call did not fail, it means we have a workbook waiting for us on the server. Next, we will get the value from cell A3 (the one that contains the formula). For that, we will add the following code below the OpenWorkbook() call:
object o = excelService.GetCellA1(sessionId,
"Sheet1",
"A3",
true,
out stati);
As you can see, we pass the session id we got from the OpenWorkbook() call into the GetCellA1() call (the A1 in the name of the method denotes the way the method works – it can be used to get any cell from the workbook) – almost all the methods on the Excel web service require this first parameter. The second and third parameters are used to tell Excel Services what part of the workbook we want. In this case, we want cell A3 in Sheet1. The Boolean we pass as the fourth parameter tells Excel Services if we want the data returning to be formatted or not . The last parameter is the status array. More on those parameters and on the various ways to get data from Excel Services in a future post.
The local variable o should now hold the string “Hello Excel 2003”. There are two more things we need to do before we finish with this phase. The first is to place the value we got from the cell into the text box and the other is to close the workbook we opened. Note that Excel Services will timeout workbooks that are left opened on the server. To minimize wasted resources, it is advised that you close a workbook you are done with. So the final code will look like this:
using (ES.ExcelService excelService = new ES.ExcelService())
{
excelService.Credentials = System.Net.CredentialCache.DefaultCredentials;
ES.Status[] stati;
string sessionId = excelService.OpenWorkbook(
"http://MyServerName/Shared Documents/HelloWorld.xlsx",
String.Empty, String.Empty, out stati);
object o = excelService.GetCellA1(sessionId,
"Sheet1",
"A3",
true,
out stati);
textBox1.Text = (string)o;
excelService.CloseWorkbook(sessionId);
}
If you run your program now and click the Get Default button, you should get the “Hello Excel 2003” appear in the textbox.
Writing data back to Excel Services
We will now add code to the other button. Feel free to copy and paste the code we wrote for the first one into the Click event of the second one – we will just be adding code.
For this second example, we will write the value “Excel 2007” into the A2 cell – if everything works right, and you get the value from A3 (after setting the value in A2 of course), you should see the text “Hello Excel 2007” in the cell, instead of the default one in the saved workbook. For that, we will add a call to SetCellA1() into the code we copied from the Get Default button, right before we call GetCellA1():
excelService.SetCellA1(sessionId, "Sheet1",
"A2", "Excel 2007");
That’s it! If you now run your sample and click the “Get Correct” button, you should see “Hello Excel 2007” in the textbox!
So what did we learn?
- We now know how to add a web reference to Excel Services so that we can use it in our program.
- We used the OpenWorkbook() method to open a workbook on the server.
- Calling the SetCellA1() method allows us to manipulate the workbook that’s opened inside the session OpenWorkbook() gave us.
- To get data from the workbook, we can call the GetCellA1() method.
- When we are done with our session, we can call the CloseWorkbook() method.
Note about the attached sample: You will need to change the name of the workbook constant at the top of the Form1 class to work with the name of your workbook. You will also need to change the ES web service properties to point to the server that hosts your Excel Services.
