Create an Excel's User Defined Function (UDF) to display web service data
Scenario
You have built a Project Server Interface (PSI) extension to query the Reporting database and you would like to display the data in your favorite reporting tool: Excel.
The solution is to create an Excel Services User Defined Function that will call the custom PSI Extension created and return an array of data.
Code
Before you start check out this code sample on how to do it: https://msdn2.microsoft.com/en-us/library/bb428649.aspx, as well as this blog from Shahar Prish: https://blogs.msdn.com/cumgranosalis/ .
In Visual Studio 2008 create a C# class for .NET 2.0, add a web reference to the web service (PSI Extension created) and start coding!
Note the UDF specific class attributes:
[UdfClass]
public class RetrievePSData
{
wsReporting.Reporting reporting = new wsReporting.Reporting();
[UdfMethod]
public string[,] Reporting(string method)
{
// Initialize variables
DataSet ds = null;
string msg = string.Empty;
reporting.Credentials = System.Net.CredentialCache.DefaultCredentials;
// Retrieve web service data based on method
try
{
switch (method.ToUpper())
{
case "GETACTUALWORK":
ds = reporting.GetActualWork();
break;
and now construct the array by looping over the web service datatable:
// Populate result object
string[,] result = new string[dt.Rows.Count+1, dt.Columns.Count];
// Add header column name first
int i = 0;
foreach (DataColumn column in dt.Columns)
{
result[0, i] = column.ColumnName;
i++;
}
// Build array of data
int r = 1;
foreach(DataRow row in dt.Rows)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
result[r, c] = row[c].ToString();
}
r++;
}
Full code sample is attached at the bottom of this blog post.
To debug the code attach w3wp.exe process after it's deployed.
Deployment
In your Office Server farm, go to the Shared Service Provider and select: Excel Services -> User-Defined Functions ->
- Add User-Defined Function Assembly (file path of C:\ODC\ExcelPSUDF\bin\Debug\Microsoft.EPM.ExcelPSUDF.dll for instance)
- Add a Trusted File Location (https://w2k3:82/Excel%20Services/ for instance), and ensure you check Allow User-Defined Functions
Testing
- Create an Excel spreadsheet in the trusted file location defined earlier
- Enter the UDF as an array function:
- Choose the range in the spreadsheet you think it will return and type the formula (without clicking the mouse - the range needs to be selected)
- Ctrl-Shift-Enter the formula
- Spreadsheet should look like this before rending it via Excel Services (note {=Reporting("GetTimePeriods")} ):
- After rendering it using Excel Services, voila!