Overview of Excel Calculation Services on Compute Cluster Server API

Article
10/16/2013

This topic provides an overview of the API for the Excel Calculation Services on Windows Compute Cluster Server (CCS) 2003. Using this API, you can submit Microsoft Office Excel 2007 workbooks to a compute cluster for calculations by Excel Calculation Services. The calculation results are then gathered and returned to you.

Two primary classes within this API orchestrate the execution of Excel Calculation Services on a compute cluster: ExcelServiceJob and ExcelServiceWork in the Microsoft.Office.Excel.Server.Addins.ComputeCluster****namespace.

The ExcelServiceJob class contains the information to execute the job against the entire cluster.
The ExcelServicesWork class contains the information to execute an individual compute cluster task against a single node of the cluster. Also, ExcelServicesWork provides a series of generic collection classes that contain the input rows, inputs, and calculation results for the work encapsulated within a single instance of the ExcelServicesWork object.

API Execution Overview

The basic execution of the API for Excel Calculation Services on Windows CCS 2003 is as follows:

Prepare the information for the ExcelServicesJob constructor.
Create an instance of the ExcelServicesWork object that contains all inputs to run against the compute cluster.
Call the ExcelServicesJob constructor with the ExcelServiceWork instance containing the total work to execute against the cluster, and other prepared values for the constructor.
Set up event handlers for the notifications you want.
Execute the API as an asynchronous or immediate job, a synchronous scheduled execution, or a synchronous complete execution.

Preparing the ExcelServicesJob Constructor

As described earlier, you must prepare values before you can pass them to the selected ExcelServicesJob constructor. The constructor we are using is described in the following code comments.

/// <summary>
/// Distributed Divisor Type constructor for executing against the cluster. The inputs are distributed evenly among the currently available nodes.
/// </summary>
/// <param name="Input">The ExcelServicesWork object containing all of the inputs to run against the cluster.</param>
/// <param name="Priority">The cluster job priority of the job being submitted against the cluster.</param>
/// <param name="ClusterName">The cluster name where the job is to run.</param>
/// <param name="UserName">The user name being used to execute the job.</param>
/// <param name="Password">The SecureString password of the user name.</param>
/// <param name="Template">The template location to use for synchronous executions.</param>
/// <param name="TypeOfLocation">The output location type.</param>
/// <param name="OutputLocation">The output location.</param>
/// <param name="Coordinates">The coordinates to place the results within the template workbook.</param>
/// <param name="IncludeHeader">Boolean value indicating whether the header should be placed within the result workbook.</param>
public ExcelServicesJob(ExcelServicesWork Input, Microsoft.ComputeCluster.JobPriority Priority, string ClusterName, string UserName, SecureString Password, string Template, LocationType TypeOfLocation, string OutputLocation, string Coordinates, bool IncludeHeader)

Following are the steps to prepare the values the constructor needs, as described in the previous code comments.

// Prepare required values for submitting jobs against the API.
Microsoft.ComputeCluster.JobPriority oJobPriority = (Microsoft.ComputeCluster.JobPriority)Enum.Parse(typeof(Microsoft.ComputeCluster.JobPriority), ConfigurationManager.AppSettings["ClusterPriority"]);
string sClusterName = ConfigurationManager.AppSettings["ClusterName"];
string sUserName = textBoxUserName.Text;
// Convert the clear string PWD into a .NET Framework 2.0 secure string.
SecureString oPwd = new SecureString();
string sPwd = textBoxUserName.Text;
for (int iCounter = 0; iCounter < sPwd.Length; iCounter++)
    oPwd.AppendChar(sPwd[iCounter]);
sPwd = string.Empty;
oPwd.MakeReadOnly();
string sTemplateLocation = textBoxTemplateLocation.Text;
LocationType oLocationType = (LocationType)Enum.Parse(typeof(LocationType), comboBoxLocationType.Text);
string sOutputLocation = textBoxOutputLocation.Text;
string sOutputCoords = textBoxOutputCoords.Text;
bool bIncludeHeaders = false;
bool.TryParse(Convert.ToString(ConfigurationManager.AppSettings["IncludeHeaders"]), out bIncludeHeaders);

Following is the call to the constructor to create an instance of the ExcelServicesJob object, which orchestrates the execution of the workbooks against the cluster.

// Create an instance of the ExcelServicesJob object.
oJob = new ExcelServicesJob(ExcelServicesWork.DeserializeMe(textBoxXml.Text), oJobPriority, sClusterName, sUserName, oPwd, sTemplateLocation, oLocationType, sOutputLocation, sOutputCoords, bIncludeHeaders);

ExcelServicesJob Event Handlers

The following event handler section registers a number of event handlers to enable notification within the calling application about the internal execution against the cluster as it proceeds.

// Register all events so we're notified of what's happening as it's happening within the execution of the job against the cluster.
oJob.TaskCompleted += new ExcelServicesJob.TaskCompletedEventHandler(oJob_TaskCompleted);
oJob.JobCompleted += new ExcelServicesJob.JobCompletedEventHandler(oJob_JobCompleted);
oJob.JobCredentialsCached += new ExcelServicesJob.CredentialCacheEventHandler(oJob_JobCredentialsCached);
oJob.JobErrored += new ExcelServicesJob.JobErroredEventHandler(oJob_JobErrored);
oJob.JobInputDivided += new ExcelServicesJob.InputDivisionEventHandler(oJob_JobInputDivided);
oJob.JobResourcesValidated += new ExcelServicesJob.ResourceValidationEventHandler(oJob_JobResourcesValidated);
oJob.JobScheduledTaskCredentialsCached += new ExcelServicesJob.ScheduledTaskCredentialsEventHandler(oJob_JobScheduledTaskCredentialsCached);
oJob.JobScheduledTaskSave += new ExcelServicesJob.ScheduledTaskSaveEventHandler(oJob_JobScheduledTaskSave);
oJob.JobTaskScheduled += new ExcelServicesJob.ScheduledTaskCreatedEventHandler(oJob_JobTaskScheduled);
oJob.JobTimeout += new ExcelServicesJob.JobTimeoutEventHandler(oJob_JobTimeout);
oJob.JobUserImpersonated += new ExcelServicesJob.UserImpersonationEventHandler(oJob_JobUserImpersonated);
oJob.JobXmlGenerated += new ExcelServicesJob.XmlGenerationEventHandler(oJob_JobXmlGenerated);

For asynchronous (immediate) executions, use the RunNow method and provide a timeout value for the total execution against the cluster. The second parameter, ResultsPauseInterval, is deprecated from an earlier version of this Job Submission sample.

oJob.RunNow(6000, 500);

For synchronous executions, use the following three methods:

Schedule: Schedules the work to be performed at a later date with no regard for retrieving the results immediately.
ScheduleRunNow: Schedules the work to be performed at a later date and executes the scheduled execution immediately to have a current copy of the execution available.
ScheduleRunNowWait: Schedules the work to be performed at a later date, executes the scheduled execution immediately, and does not return until the completion of the execution. This permits the developer to examine the results of the execution immediately upon the method call's return.

Preparing the ExcelServicesWork Object

A fully populated instance of the****ExcelServicesWork class is required to execute the ExcelServicesJob object. The steps to populate an ExcelServicesWork object are described in comments within the following code example.

// Create an instance with the default constructor for the ExcelServicesWork class.
ExcelServicesWork oWork = new ExcelServicesWork();
// An input is a value that must be placed within the workbook for the calculation within it to be performed.
// Create an instance of an Input object for each input specified within the workbook.
Input oInput1 = new Input("Sheet1", "NamedRange1", CellType.NamedRange, 4);
Input oInput2 = new Input("Sheet1", "NamedRange2", CellType.NamedRange, 45.22);
Input oInput3 = new Input("Sheet1", "NamedRange3", CellType.NamedRange, "Blue");
// Create an instance of the InputRow object where the Inputs will be placed.
InputRow oInputRow = new InputRow();
oInputRow.Add(oInput1);
oInputRow.Add(oInput2);
oInputRow.Add(oInput3);
// Place the InputRow into the InputCollection.
oWork.InputCollection.Add(oInputRow);
// The Result is the location where the calculated values will be placed.
// After the calculation is performed, the results will be retrieved
// from this location and stored within the result value (not supplied as part of the Result constructor).
Result oResult = new Result("Sheet1", "ResultCollection", CellType.NamedRange);
// Add the Result object to the Results collection. 
oWork.InputCollection[oWork.InputCollection.Count - 1].Results.Add(oResult);
// One or more input values might change between each InputRow prior to populating the InputCollection with 
// this new row of modified Input objects.
oInput1.Value = 4.5;
// Create an instance of the InputRow object to populate with the existing or modified inputs.
oInputRow = new InputRow();
oInputRow.Add(oInput1);
oInputRow.Add(oInput2);
oInputRow.Add(oInput3);
// Each InputRow object must have a Result within its Results collection. This adds the previous result into the 
// new InputRow object's Results collection.
oInputRow.Results.Add(oResult);
// Add the new InputRow into the InputCollection object.
oWork.InputCollection.Add(oInputRow);
// Continue for as many InputRow objects and Results collections as necessary.

Overview of Excel Calculation Services on Compute Cluster Server API

API Execution Overview

Preparing the ExcelServicesJob Constructor

ExcelServicesJob Event Handlers

Preparing the ExcelServicesWork Object

See Also

Concepts

Additional resources