Using SetParameters with the Excel Web Services APIs

One of the new methods I listed in the post about new features is the SetParameters() SOAP call. This allows callers to set multiple parameters at the same time in Excel Services. In this example I have a simple list containing items and sales of those items. I then use a PivotTable to aggregate the sales and get an average.

Here’s the workbook snippet:

On the right there’s the raw table and on the right the PivotTable, showing only the average of sales with a page field acting as a parameter of the workbook. The parameter was named ItemFilter and the average of Sales cell was named AverageSales. The following piece of code shows how to set the parameter and get the value back:

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

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

ES.Status[] status;

string sessionId = client.OpenWorkbook("https://tsdlt2/docs/ParamPivot.xlsx", "", "", out status);

ES.ParameterInfo[] infos = new ES.ParameterInfo[1];

ES.ParameterInfo param = new ES.ParameterInfo();

infos[0] = param;

param.Name = "ItemFilter";

param.Values = new String[1];

param.Values[0] = "TV";

client.SetParameters(sessionId, infos);

string val = (string)client.GetCellA1(sessionId, "", "AverageSales", true, out status);

Console.WriteLine("Average Sales are: " + val);

 

As you can see, first we go and set up the parameters objects – we create a parameter array (you can pass in multiple parameters with one call). In our instance, it has a single item in it. We then create the parameter we want to set up (called ItemFilter" ) and the value we want to put in (via the Value property).

Finally, we call SetParameters() with the array – when that call comes back, it will have filtered the PivotTable. Here’s the result:

Note that if you are using the more recent versions of C#, the code can look a little less verbose:

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

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

ES.Status[] status;

string sessionId = client.OpenWorkbook("https://tsdlt2/docs/ParamPivot.xlsx", "", "", out status);

ES.ParameterInfo[] infos = new ES.ParameterInfo[]

       { new ES.ParameterInfo() { Name = "ItemFilter", Values = new string[] { "TV" } }};

client.SetParameters(sessionId, infos);

string val = (string)client.GetCellA1(sessionId, "", "AverageSales", true, out status);

Console.WriteLine("Average Sales are: " + val);

Instead of instantiating each element in its own line, we do the initialization inline – makes the code look less complicated (though it does essentially the same thing as we did in the first sample).