Excel Services Getting Started – Pt4 Web Service APIs

  • Article

This continues my previous checklists here, here and here on getting started with Excel Services. In this post, I’ll append a walkthrough for using the Excel Web Services APIs.

6. Using Excel Web Services APIs

6.1 Create an publish a workbook (RegionalSales.xlsx)

a. We could use any of the workbooks we’ve already published, but for simplicity, we’ll create a new one. Using Excel client, create a simple spreadsheet to represent regional sales. In one column put labels for the sales regions (North, South, East, West), and in the next column put some arbitrary $ values. Define names for each of the value cells, using the default names offered by Excel (based on the labels in the adjoining cell).

 

A

B

1

Sales

 

2

North

$1,234.00

3

South

$5,678.00

4

East

$9,876.00

5

West

$5,432.00

 

b. Publish the sheet to Excel Services, using an appropriate path, eg: https://MyServer/sites/Contoso/Spreadsheets/RegionalSales.xlsx

6.2 Manipulate the workbook via a web service proxy (ConsoleTestExcelServices.dll)

a. In Visual Studio, create a simple console application.

b. Add a traditional web reference. To do this, right-click on References in Solution Explorer, and select Add Service Reference. In the Add Service Reference dialog, click the Advanced button, and then the Add Web Reference button.

c. Type in the URL for your Excel Services server, eg: https://MyServer/sites/Contoso/_vti_bin/ExcelService.asmx

d. When the wizard finds the Excel Services service, specify a suitable web reference name, eg: “ES”, and click Add Reference.

e. Declare some variables for the service URL, the workbook URL, and the worksheet name.

private const String serviceUrl =
"https://MyServer/sites/Contoso/_vti_bin/ExcelService.asmx";

private const String workbookUrl =
"https://MyServer/sites/Contoso/Spreadsheets/RegionalSales.xlsx";

private const String sheetName = "Sheet1";

f. Create the proxy to Excel Services web service, and specify the SOAP 1.2 protocol so we get richer error information. Also make sure that our user credentials are used.

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

    s.SoapVersion =
System.Web.Services.Protocols.SoapProtocolVersion.Soap12;

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

    s.Url = serviceUrl;

    String sessionId = null;

g. Open the workbook from Excel Services, read a cell value, and write a cell value. Be sure to close the workbook when you’re done.

    try

    {

        Console.WriteLine(
"Opening workbook {0} at {1}", workbookUrl, s.Url);

        ES.Status[] status;

        // Open the workbook from Excel Services.

        sessionId = s.OpenWorkbook(
workbookUrl, String.Empty, String.Empty,
out status);

        Console.WriteLine("Session ID: {0}", sessionId);

        // Read a cell value.

        object north = s.GetCellA1(
sessionId, sheetName, "North", true, out status);

        Console.WriteLine("North = {0}", north);

        // Write a cell value.

        status = s.SetCellA1(sessionId, sheetName, "East", 999);

        if (status == null)

        {

            object east = s.GetCellA1(
sessionId, sheetName, "East", true, out status);

            Console.WriteLine("East = {0}", east);

        }

    }

    catch (SoapException e)

    {

        Console.WriteLine(
"SoapException: {0}", e.SubCode.Code.Name);

    }

    catch (Exception e)

    {

        Console.WriteLine(e.ToString());

    }

    finally

    {

        if (!String.IsNullOrEmpty(sessionId))

        {

            try

            {

                s.CloseWorkbook(sessionId);

            }

            catch { }

        }

    }

 

h. The runtime output should look something like this:

Opening workbook https://MyServer/sites/Contoso/Spreadsheets/RegionalSales.xlsx at https://MyServer/sites/Contoso/_vti_bin/ExcelService.asmx
Session ID: 64.7d0d5456-4b85-448d-a88e-ce9242deb7f9HtTBm7DThVZQoXsRZba94EiD6V8=117.21.4/Uqc302XUOqoMSBpn5z190.5.en-US5.en-US73.+0480#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-0060
North = $1,234.00
East = $999.00

i. Note that the changes made to the sheet cell values are not persisted in the original file on the server. Note also that the app.config is not required in this client.

6.3 Manipulate the workbook via a WCF proxy (ConsoleTestEwsWcf.dll)

a. Previously, we used a traditional web service proxy, but we can use a WCF proxy instead.

b. To do this, create a console application as before.

c. In Solution Explorer, right-click on References and select Add Service Reference.

d. Type in the URL for the Excel Services service, eg: https://MyServer/sites/Contoso/_vti_bin/ExcelService.asmx, and click Go.

e. When the wizard finds the Excel Services service, select the ExcelServiceSoap node, specify a suitable namespace name, eg: “ES”, and click OK.

f. As before, declare some variables for the the workbook URL, and the worksheet name. Note that the service URL was written into the app.config by the service reference wizard.

//private const String serviceUrl =

//"https://MyServer/sites/Contoso/_vti_bin/ExcelService.asmx";

private const String workbookUrl =
"https://MyServer/sites/Contoso/Spreadsheets/RegionalSales.xlsx";

private const String sheetName = "Sheet1";

g. Create the proxy to Excel Services web service. Security information will be provided in the app.config.

    ES.ExcelServicesSoapClient s =
new ES. ExcelServicesSoapClient ();

//s.SoapVersion =
// System.Web.Services.Protocols.SoapProtocolVersion.Soap12;

//s.Credentials =
// System.Net.CredentialCache.DefaultCredentials;

//s.Url = serviceUrl;

    String sessionId = null;

h. Open the workbook from Excel Services, read a cell value, and write a cell value. Be sure to close the workbook when you’re done. This code is the same for both the traditional web service proxy and the WCF proxy, except that we allow for catching FaultExceptions not SoapExceptions.

    try

    {

        Console.WriteLine(
"Opening workbook {0} at {1}", workbookUrl, s.Url);

        ES.Status[] status;

        // Open the workbook from Excel Services.

        sessionId = s.OpenWorkbook(
workbookUrl, String.Empty, String.Empty,
out status);

        Console.WriteLine("Session ID: {0}", sessionId);

        // Read a cell value.

        object north = s.GetCellA1(
sessionId, sheetName, "North", true, out status);

        Console.WriteLine("North = {0}", north);

        // Write a cell value.

        status = s.SetCellA1(sessionId, sheetName, "East", 999);

        if (status == null)

        {

            object east = s.GetCellA1(
sessionId, sheetName, "East", true, out status);

            Console.WriteLine("East = {0}", east);

        }

    }

//catch (SoapException e)

//{

// Console.WriteLine(
// "SoapException: {0}", e.SubCode.Code.Name);

//}

catch (System.ServiceModel.FaultException e)

{

    Console.WriteLine("FaultException: {0}", e.ToString());

    if (e.Code.SubCode != null)

    {

        Console.WriteLine(
"SubCode: {0}", e.Code.SubCode.Name);

    }

}

    catch (Exception e)

    {

        Console.WriteLine(e.ToString());

    }

    finally

    {

        if (!String.IsNullOrEmpty(sessionId))

        {

            try

            {

                s.CloseWorkbook(sessionId);

            }

            catch { }

        }

    }

 

i. Note the additional changes we must make to the app.config to specify security. First, the service reference wizard gives us security mode None by default, but we want to specify NTLM credentials:

          <!--<security mode="None">

                   <transport clientCredentialType="None"
proxyCredentialType="None"

                      realm="" />

                   <message clientCredentialType="UserName"
algorithmSuite="Default" />

                </security>-->

          <security mode="TransportCredentialOnly">

           <transport clientCredentialType="Ntlm"
proxyCredentialType="None" realm="" />

            <message clientCredentialType="UserName"
algorithmSuite="Default" />

          </security>

j. We add an endpoint behavior to allow impersonation, so that the endpoint can act with the client’s credentials on the server.

<behaviors>

  <endpointBehaviors>

    <behavior name="AllowImpersonationBehavior">

      <clientCredentials>

        <windows allowedImpersonationLevel="Impersonation"/>

      </clientCredentials>

    </behavior>

  </endpointBehaviors>

</behaviors>

k. Finally, apply this behavior to the endpoint.

   <endpoint

        address="https://MyServer/_vti_bin/ExcelService.asmx"

        binding="basicHttpBinding"
bindingConfiguration="ExcelServiceSoap"

        contract="ES.ExcelServiceSoap"

behaviorConfiguration = "AllowImpersonationBehavior"

        name="ExcelServiceSoap" />

l. The runtime output will be the same as before.

OK, this series of posts has built up a very simple checklist of tasks for setting up and configuring MOSS and Excel Services, publishing workbooks, building and publishing UDFs, and using Excel Web Access and Excel Web Services APIs. There’s obviously a lot more information on MSDN on these topics, and you’re encouraged to flesh out the information in this checklist with the much richer information in the published documentation.