2. Accessing Excel Services Over SOAP
Just as you're able to access
Excel sheets over REST based APIs, they are also exposed over SOAP
based APIs. The previous sheet that I was working with involved a pivot
table and pivot chart. Pivot tables and pivot charts are extremely
powerful, but since their data is being driven by an external data
source, they do not lend themselves to be changed easily from the front
end.
For this section, let's craft up a
slightly different and perhaps simpler Excel sheet. Let's say the sheet
that I want to create reflects my daily activities for the day. This
sheet can be seen in Figure 10.
If you note closely, C7 is
filled with a formula, whereas the other rows in the C column are static
values. Also, there is a pie chart being driven from the same data.
Anytime I change the number of hours spent in any activity, the pie
chart automatically redraws itself to show the updated data.
Publish this to Excel
services and choose to also display this in the Excel web access
WebPart. When choosing to display the sheet in the Excel web access
WebPart, look for a check box called "All Workbook interactivity" under
"General Interactivity Settings" when the webpart is in edit mode. Check
that checkbox. The Excel sheet running in the browser looks like Figure 11.
Let's say that people are
complaining that I was spending too much time drinking others. So, I
need to reduce the time spent in biting and perhaps increase the time
spent in eating. Go ahead and edit the Excel sheet, and change cell C6's
value to eight. At the same time, click on cell C3 and change its value
to four. You would note that the chart immediately reflects your
changed values and also the formula at C7 immediately reflects your
changed values in the browser. I wish my parents that easily reflected
my changed values.
There is one important thing
to note here though. All the changes that you're doing from the Excel
web access WebPart are being changed only for your current web session.
In other words, if you were to close your browser and come back to the
Excel web access WebPart, all the changes you had done previously would
be lost. To make the changes permanent, you need to edit the source
Excel sheet using thick client Excel or using Excel web applications.
Interestingly, all these
embedded formulas and intelligence are a sheet that is exposed over a
soap based API. Let's see this in action. Start Visual Studio 2010 and
create a console application. In this console application, you will add a
reference to /_vti_bin/excelservice.asmx, so this can be any version of
.NET that can consume a web service. Since WCF can consume web services
using basicHttpBinding, you're going to go with the .net 3.5
application. Call your console application ExcelSvcClient. In this
console application, choose to add a service reference to http://sp2010/_vti_bin/ExcelService.asmx
in the namespace ExcelService. After you have added the service
reference, quickly examine the app.config for your client application.
You should see a section that specifies the security settings for the
client WCF application and you should change it to the following code:
<security mode="TransportCredentialOnly">
<transport clientCredentialType="Ntlm" proxyCredentialType="Ntlm" realm="" />
<message clientCredentialType="UserName" algorithmSuite="Default" />
</security>
This section now ensures
that the client sends the NTLM identity to the server with all its
requests. This is necessary since your SharePoint installation is
protected behind active directory authentication.
Now modify the source code for your console application, as shown in Listing 1.
Example 1. Source Code for Your ExcelService As WCF client
private static string workbookURL = "http://sp2010/sheets/Book1.xlsx";
static void Main(string[] args)
{
ExcelService.ExcelServiceSoapClient client =
new ExcelService.ExcelServiceSoapClient();
client.ClientCredentials.Windows.AllowedImpersonationLevel =
TokenImpersonationLevel.Impersonation;
ExcelService.Status[] outStatus;
string sessionID =
client.OpenWorkbook(workbookURL, "en-US", "en-US", out outStatus);
ExcelService.RangeCoordinates rc = new ExcelService.RangeCoordinates()
{
Column = 3,
Row = 6,
Height = 1,
Width = 1
};
client.SetCell(sessionID, "Sheet1", 4, 2, 10);
outStatus = client.Calculate(sessionID, "Sheet1", rc);
Console.WriteLine(
client.GetCell(
sessionID, "Sheet1", 6,2, false, out outStatus).ToString());
}
|
At this time, if you
compile and run your console application, you would note that the total
which is calculated out of a formula is written out to your console.
Again, it is important to note that you interact with Excel services
using the sessionID and these changes are specific only to your
sessionID. The actual sheet itself is unchanged. You should further
explore the various other methods available to you in ExcelService.asmx.
Some of the interesting methods allow you to export binary data out of
the sheet, or to export a snapshot of an edited sheet as a byte array.
