Excel Services Interface

Excel Services provides two interfaces , Web Interface that produces DHTML in the browser window and does not require the client to be installed.

The second interface and the one I'm interested in for the purpose of this sample is the Web Services Interface.

I'll create a simple windows forms application to see how things work and if it could actually replace a bespoke system in relation to performance.

Create a Web Reference 

In typical Model View Controller(MVC) style  I have created a windows form with a separate assembly that houses all the interaction logic with the Web Service. In Visual Studio I simply added a reference by Selecting References -> Add Web Reference and in the URL location I provided the detail of my site (hosted on a Virtual PC connected with the Loop back Adapter). 

http://{ServerName}:{PortNumber}/sites/{SiteCollection}/_vti_bin/excelservice.asmx?WSDL

Once the reference is created you need to add the using Directive into your class. This should take the form of your default name space extended by the name you assigned to the Web Reference.

Done , and ready to move on !

Create the View Interface

For communication between my windows form(View) and the assembly (Controller) I have created an Interface that allows me to retrieve and set values on the form. Very basic but it makes sense to do it in this fashion as this application can now easily be migrated to a Web Form, a bespoke Web Services or might deliver a service as part of an application.

Authentication    

Because we have one Computer , my laptop , in a a different domain from my Virtual PC I needed to find a way of providing credentials. For this I created a simple internal Class called creds that implements the interface

System.Net.ICredentials

Required when you call the Web Service. See below

/// <summary>
/// Class that represents Basic Authentication Credentials (Machines are not on the same Domain)
/// </summary>
internal class creds : System.Net.ICredentials
{
#region ICredentials Members

/// <summary>
/// Default Implementation
/// </summary>
/// <param name="uri"></param>
/// <param name="authType"></param>
/// <returns></returns>
public System.Net.NetworkCredential GetCredential(Uri uri, string authType)
{
return new System.Net.NetworkCredential(@"Administrator", "Password1", "win2003-sql2005");
}

#endregion
}

Getting the Web Service to do stuff

Next we create the Excel Service object

ExcelService es = new ExcelService();

we then set the Credentials property on the Excel Services object just created, thus passing the detail contained in the internal class for Authentication.

Setting values on the Workbook 

One option I would probably never use is to set range coordinates

RangeCoordinates rangeCoordinates = new RangeCoordinates();

you can see how this could get messy very quickly and error prone.

Named Ranges created in the workbook can be used from code as I have done. Options are to set the ranges like parameters one at a time , or pass the whole range as an array. When setting the synchronously that means a web call for every Named Range. I experienced problems when setting a number of values asynchronously where some values are not set by the time I calculate the workbook.

Option 1

//Set the Values one at a time

//Sync web call for each value

//es.SetCellA1(sessionId, "Calculator", "Loan_Amount", loanDetail.LoanAmount);

//es.SetCellA1(sessionId, "Calculator", "Deposit", loanDetail.Deposit);

//es.SetCellA1(sessionId, "Calculator", "Interest_Rate", (loanDetail.Interest/100) );

//es.SetCellA1(sessionId, "Calculator", "Age", loanDetail.Age);

//es.SetCellA1(sessionId, "Calculator", "Period_Y", loanDetail.Term);

Option 2

//Set the values at the same time

//One Sync Call for the range

object[] array = new object[] { new object[] { loanDetail.LoanAmount.ToString() },

new object[]{loanDetail.Deposit.ToString()},

new object[]{(loanDetail.Interest/100)},

new object[]{loanDetail.Age.ToString()},

new object[]{loanDetail.Term.ToString()} };

I found the latter more efficient and could even be abstracted into classes that would make a big solution less error prone.

After setting the named range 

Summary

Thats my take on Excel Web services and how it could be used in Enterprise applications. I would recommend using named ranges and would certainly use a scalable architecture using patterns such as MVC , Facade and Service Layer.  Find the solution and use it to experiment like I have. Good luck !