Building Excel 2007 and Excel Services Dashboards by Consuming Two-Dimensional Data from Web Services
Summary: Learn how to consume Web services in Microsoft Office Excel 2007 and in Excel Services in Microsoft Office SharePoint Server 2007. In Excel 2007, the Web services are consumed through an add-in created with Microsoft Visual Studio Tools for the Microsoft Office system (version 3.0). In Excel Services, they are consumed through a user-defined function. (22 printed pages)
Bhushan Nene, Microsoft Corporation
Scot Hillier, Microsoft MVP
April 2008
Applies to: Microsoft Office Excel 2007, Microsoft Visual Studio Tools for the Microsoft Office System (Version 3.0), Microsoft Office SharePoint Server 2007
Contents
Overview of Dashboards in Excel 2007 and Excel Services
Solution Overview
Solution Approach and Architecture
Creating a Test Database with SQL Server Compact Edition 3.5
Creating a Single Sign-On Web Service
Creating a Data-Retrieval Web Service
Creating an Add-in for Excel 2007
Creating a User-Defined Function for Excel Services
Conclusion
Additional Resources
Overview of Dashboards in Excel 2007 and Excel Services
You can use Microsoft Office Excel 2007 to import data from transactional systems, databases, and data warehouses for analysis and reporting. After importing the data, you can display it in sheets, pivot tables, or charts. This type of data often includes extensive cell formatting and formulas to enhance presentation. Because Excel 2007 offers more control over creating sheets, pivot tables, and charts, information workers show a preference for using Excel over other business intelligence tools for data analysis and reporting.
After importing and manipulating the data, you can deliver the worksheet as a reporting dashboard. Typically, this involves attaching the worksheet to an e-mail message and sending it out to a group of recipients. The recipients in turn use the worksheet as a basis for business decisions.
Figure 1 shows a dashboard created in an Office Excel 2007 worksheet using the solution in this article.
Figure 1. An Excel 2007 dashboard
.gif "An Excel 2007 dashboard")
Microsoft Office SharePoint Server 2007 introduced a new technology for working with worksheet data called Excel Services. With Excel Services, you can publish sheets, pivot tables, and charts to SharePoint Server 2007 and view them in a browser. This technology enables a new and more efficient method for distributing worksheet information: instead of sending dashboards in e-mail, information workers can view them as part of a larger business intelligence Web site.
Figure 2 shows a typical dashboard created in SharePoint Server 2007 Excel Services using the solution in this article.
Figure 2. Excel Services dashboard
.gif "An Excel Services dashboard")
This article describes how to compose Excel 2007 and Excel Services dashboards by consuming data from external systems through Web services. You learn how to update dashboards dynamically by calling Web services on demand to obtain the most current data from the external system.
Solution Overview
Excel 2007 and Excel Services offer significant functionality for creating business intelligence dashboards from a variety of data sources. After you create these dashboards, you can update them on demand with real-time information from the data sources. Both Excel 2007 and Excel Services support retrieving external data from data sources, such as Microsoft SQL Server Analysis Services, SQL Server relational databases, and other database platforms that use mechanisms like ODBC and OLE DB. Workbooks can connect to live data sources through business-friendly and user-friendly mechanisms that enable information workers to take advantage of the technology without assistance, subject to rules put in place by database administrators and other resource owners. However, Excel 2007 and Excel Services do not have similar "no code" integration with Web services.
This article shows how to build a solution in Microsoft Visual Studio Tools for the Microsoft Office system (version 3.0) for Excel 2007, and how to create user-defined functions for Excel Services that enable information workers to build dynamic dashboards by consuming data from Web services. The solution offers an experience similar to connecting to a database and importing tabular data as an Excel table. In this case, information workers can bring two-dimensional data from Web services into Excel 2007 as an Excel range. After data is imported into the Excel range, a dashboard can be created by using Excel formatting features, pivot tables, and charts. Then the workbook can be published to Excel Services to render a dashboard in a Web browser. The solution also enables on-demand updating of Excel 2007 and Excel Services dashboards from live data by maintaining the association between the Web service and the Excel range.
To explain the solution, this article uses a common business scenario involving salespeople and a customer relationship management (CRM) system. The scenario involves salespeople creating personal dashboards in both Office Excel 2007 and Excel Services in Office SharePoint Server 2007. These personal dashboards contain sales opportunity information (commonly known as a "pipeline"), which shows the status of each opportunity.
The salespeople in the scenario can only create dashboards using their own opportunity data. One salesperson does not have access to another salesperson's data. Using the solution, they can easily create dashboards for both Office Excel 2007 and Excel Services. Within their dashboards, they can create worksheets, pivot tables, and charts. They can also freely format their dashboards with colors and fonts. Most important, the solution provides real-time data analysis in both Excel 2007 and Excel Services.
Solution Approach and Architecture
The solution consists of two components:
A Visual Studio Tools for Office add-in for Excel 2007 to bring data from Web services into an Excel workbook.
A user-defined function (UDF) to bring data from Web services into an Excel workbook that is rendered by Excel Services.
We implement the CRM Web service by using Windows Communications Foundation (WCF). The Web service retrieves the data from Microsoft SQL Server 2005 Compact Edition using Language Integrated Query (LINQ). Additionally, the solution uses the SharePoint Server 2007 Single Sign-On system to facilitate retrieving sales data for the individual creating the dashboard by mapping the user's Windows credentials to CRM logon information. Figure 3 shows the overall solution architecture.
Figure 3. Solution architecture
.gif "Solution architecture")
When salespeople want to create a personal dashboard, they begin by opening Excel 2007, which loads the Visual Studio Tools for Office add-in. The add-in implements a custom tab that allows for configuring the data import and initiating the import. Configuration information is stored as an XML document part within the Open XML format of the worksheet. This approach means that configuration information is retained when the worksheet is closed. Consequently, the end user only has to configure the worksheet one time and refresh it to get the latest data. Figure 4 shows the add-in.
Figure 4. Excel add-in
.gif "Excel add-in")
If salespeople want to publish their dashboards to Excel Services, they use the Excel Services Compatibility Mode option in the add-in. This option invokes a user-defined function that is designed to provide an equivalent experience in Excel Services. Configuration information under Excel Services is maintained by using named ranges because Excel Services cannot access the document part in OpenXML. These named ranges are exposed as parameters that the user can change. This configuration information is then retained so that the latest data always appears. Figure 5 shows the configuration named ranges in Excel Services.
Figure 5. Named ranges in Excel Services.
.gif "Named ranges in Excel Services")
The Excel add-in and UDF retrieve data by calling a data service that wraps the CRM database. In our scenario, the CRM system has its own security system that requires a separate logon. The data service acquires credentials for the CRM system by calling a single sign-on service that wraps the SharePoint Server 2007 Single Sign-On system. The data service then uses the supplied credentials to log on to the CRM system and return the opportunities only for the current user.
The following sections detail each component in the solution, beginning with the CRM database, and working up to the add-in and the UDF. Overall, the sections are arranged in the order in which they were created in the solution.
The Creating a Test Database with SQL Server Compact Edition 3.5 and Creating a User-Defined Function for Excel Services sections represent the primary components of the solution.
The Creating a Test Database with SQL Server Compact Edition 3.5, Creating a Single Sign-On Web Service, and Creating a Data-Retrieval Web Service sections represent secondary components of the solution.
Creating a Test Database with SQL Server Compact Edition 3.5
For this solution, we created a simple database to simulate a customer relationship management (CRM) system. This database was created in Microsoft Visual Studio 2008 using Microsoft SQL Server Compact Edition 3.5. SQL Server Compact Edition 3.5 is an embedded database engine that lets you create simple, portable databases that run from the file system.
The CRM database (VSTOExcelCRM.sdf) contains four tables with data for security, customers, and sales opportunities. This database represents a system that requires a separate logon and contains sales information by customer and sales representatives. The following table contains the relevant details for the database.
Table 1. VSTOExcelCRM.sdf information
Table Name |
Description |
Primary Key |
Foreign Keys |
|---|---|---|---|
Accounts |
Contains basic customer account information, such as company name. |
AccountID |
|
Logins |
Contains the user name and password for each user of the CRM system. |
Username |
|
Opportunities |
Contains detailed information about each sales opportunity. |
OpportunityID |
CustomerID EmployeeID |
Salespeople |
Contains information about sales representatives. |
EmployeeID |
After the CRM database was created, a data project was created to provide access to the database. The design of the solution uses LINQ to SQL technology to manage the database entities as objects. This approach requires you to create a data layer that supports LINQ to SQL. This data layer is created as a separate project in Visual Studio 2008 that has a single class. The class is generated by using the command prompt utility SQLMETAL.EXE with the following syntax:
SQLMETAL.EXE VSTOExcelCRM.sdf /code:VSTOExcelCRM.cs
Creating a Single Sign-On Web Service
The simulated CRM system does not use Windows authentication for access. Instead, it uses custom authentication based on credentials stored in its own database. Therefore, the caller of the CRM Web service must supply valid credentials. This solution uses the single sign-on (SSO) service in SharePoint Server 2007 to map a user's Windows identity to CRM system credentials.
The SSO service maintains a database that holds credentials for multiple systems. Each system is labeled with an application name and the definition of credentials fields to maintain. In this solution, an application named VSTOExcelCRM with support for the fields Username and Password was created. After the SSO application definition is created, an administrator typically sets up account information for the end users.
Figure 6 shows the SSO application definition inside the Central Administration site for SharePoint Server 2007.
Figure 6. The VSTOExcelCRM SSO definition
.gif "The VSTOExcelCRM SSO definition")
This solution uses a custom Web service to retrieve credentials from the SharePoint Server 2007 SSO service. The custom Web service was created as a new WCF Service Application project in Visual Studio 2008. Within this project, a new XML schema was created to represent the credential information returned by the Web service. Although the solution only uses two fields, the SharePoint Server 2007 SSO service supports up to five fields. The following schema was used to represent the return data.
<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="CredentialSet"
targetNamespace="http://example.com/CredentialSet.xsd"
elementFormDefault="qualified"
xmlns="http:// example.com/CredentialSet.xsd"
xmlns:mstns="http:// example.com/CredentialSet.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
>
<xs:element name="CredentialSet">
<xs:complexType>
<xs:sequence>
<xs:element name="Field1" type="xs:string" />
<xs:element name="Field2" type="xs:string" />
<xs:element name="Field3" type="xs:string" />
<xs:element name="Field4" type="xs:string" />
<xs:element name="Field5" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
Within the custom Web service project, a class was created to encapsulate the credentials and return them to the calling client. The return class was generated by using the command prompt utility SVCUTIL.EXE. This utility can create return classes and client proxies for use with Web services. The following command creates the return class named CredentialSet.
SVCUTIL.EXE
/dconly
/n:http:// example.com/CredentialSet.xsd,VSTOExcelSvcs.SSOService
/out:CredentialSet.cs Schemas\CredentialSet.xsd
Table 2. SVCUTIL Parameters
Parameter |
Description |
|---|---|
/dconly |
Data Contract Only. |
/n |
Maps the schema namespace to the CLR namespace. |
/out |
The name of the CS file to create and a reference to the source schema. |
After the return class is created, the Web service is developed to access the SharePoint Server 2007 SSO service and return credentials. The following code shows the contracts and operations for the Web service. The GetMyCredentials method is the primary method that the solution uses to retrieve credentials that can be used to access the CRM system.
[DataContract]
public class SSOError
{
public SSOError(string Message)
{ this.ErrorMessage = Message;}
[DataMember]
public string ErrorMessage;
}
[ServiceContract]
public interface ISSOService
{
[OperationContract]
CredentialSet GetMyCredentials(string ApplicationName);
[OperationContract]
string GetCredentialEntryUrl(string ApplicationName);
}
public class SSOService : ISSOService
{
public CredentialSet GetMyCredentials(string ApplicationName)
{
try
{
// Get the SSO credentials.
string[] credentials = null;
Credentials.GetCredentials(
1, ApplicationName, ref credentials);
CredentialSet credentialSet = new CredentialSet();
int i = 0;
foreach (string credential in credentials)
{
i++;
switch (i)
{
case 1:
credentialSet.Field1 = credential;
break;
case 2:
credentialSet.Field2 = credential;
break;
case 3:
credentialSet.Field3 = credential;
break;
case 4:
credentialSet.Field4 = credential;
break;
case 5:
credentialSet.Field5 = credential;
break;
}
}
return credentialSet;
}
catch(Exception x)
{
throw new FaultException<SSOError>(
new SSOError(x.Message));
}
}
Creating a Data-Retrieval Web Service
The data-retrieval Web service is used to return opportunity data from the CRM system for the current user. The scenario envisioned for the solution involves individual salespeople accessing their opportunity data and importing it into worksheets. Therefore, the data-retrieval Web service must first call the SSO Web service to retrieve the CRM system credentials for the current user. These credentials are validated and then used to access the CRM system.
The solution defines a return class named OpportunitySummary, which contains the data for an individual opportunity in the CRM system. The data-retrieval Web service returns a set of OpportunitySummary objects that represents the requested data for the current user. The following code shows the contracts and operations for the Web service. The GetMyOpportunities method is the key operation that returns data to the worksheet.
[DataContract]
public class OpportunityError
{
public OpportunityError(string Message)
{this.ErrorMessage = Message;}
[DataMember]
public string ErrorMessage;
}
[ServiceContract]
public interface IOpportunityService
{
[FaultContract(typeof(OpportunityError))]
[OperationContract]
OpportunitySummary [] GetMyOpportunities(
string CRMusername, string CRMpassword,
string Status, string Priority,
string Quarter);
}
public class OpportunityService : IOpportunityService
{
public OpportunitySummary[] GetMyOpportunities(
string CRMusername, string CRMpassword,
string Status, string Priority,
string Quarter)
{
// SQL Server CE is currently not optimized to serve as
// a database for Web sites. By default, connections from
// ASP.NET-connected applications are blocked in SQL Server CE.
// Following line of code changes that default behavior
// of SQL Server CE to work within ASP.NET.
AppDomain.CurrentDomain.SetData(
"SQLServerCompactEditionUnderWebHosting", true);
// The collection of opportunities to return.
List<OpportunitySummary> opportunities =
new List<OpportunitySummary>();
try
{
// Validate the credentials before returning data.
if (CredentialsValid(CRMusername, CRMpassword))
{
// Retrieve Orders for the current user.
string connString =
ConfigurationManager.ConnectionStrings
["VSTOExcelCRM"].ConnectionString;
VSTOExcelCRM db = new VSTOExcelCRM(connString);
var q = from l in db.Logins
join o in db.Opportunities
on l.EmployeeID equals o.EmployeeID
join a in db.Accounts
on o.CustomerID equals a.AccountID
where l.Username == CRMusername &&
Status.Split(';').Contains(o.Status)
&&
Priority.Split(';').Contains(o.Priority)
&&
o.EstimatedCloseDate.Value.CompareTo(
GetQuarterStartDate(Quarter)) >= 0
&&
o.EstimatedCloseDate.Value.CompareTo(
GetQuarterEndDate(Quarter)) <= 0
select new { o.OpportunityID,
o.OpportunityName,
a.CompanyName, o.EstimatedCloseDate,
o.ExpectedRevenue, o.Priority,
o.SalesStage, o.OpportunityType,
o.Probability, o.Status };
// Add opportunities to return array.
foreach (var opp in q)
{
OpportunitySummary opportunity =
new OpportunitySummary();
opportunity.OpportunityID = opp.OpportunityID;
opportunity.OpportunityName = opp.OpportunityName;
opportunity.CompanyName = opp.CompanyName;
opportunity.EstimatedCloseDate =
(DateTime)opp.EstimatedCloseDate;
opportunity.ExpectedRevenue =
(Double)opp.ExpectedRevenue;
opportunity.Priority = opp.Priority;
opportunity.SalesStage = opp.SalesStage;
opportunity.OpportunityType = opp.OpportunityType;
opportunity.Probability = (int)opp.Probability;
opportunity.Status = opp.Status;
opportunities.Add(opportunity);
}
return opportunities.ToArray();
}
else
throw new Exception("Invalid Credentials");
}
catch (Exception x)
{
throw new FaultException<OpportunityError>
(new OpportunityError(x.Message));
}
}
// This function checks user name and password against
// the user credentials stored in Logins table.
// Implementation is ommitted for brevity.
private bool CredentialsValid(string Username, string Password)
{...}
Creating an Add-in for Excel 2007
The data-retrieval Web service is called from an Excel 2007 add-in created in Visual Studio Tools for Office. This add-in uses a custom tab on the Microsoft Office Fluent Ribbon in Excel to invoke the Web service based on configuration values set in a custom dialog box. These configuration values are saved in the current workbook by using a custom XML part.
Creating the Custom Interface
The custom interface to the add-in consists of a tab, a button, and a configuration dialog box. You can add tabs, controls, and dialog boxes to 2007 Office system programs by using the Office Fluent Ribbon extension components of Visual Studio 2008. You can add a new Office Fluent Ribbon component by using an XML file to define the user interface elements and a class module to define the functionality of the elements. The following code shows how to define the new tab and controls.
<?xml version="1.0" encoding="UTF-8"?>
<customUI onLoad="Ribbon_Load" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="OpportunitiesTab" label="Opportunities">
<group id="OpportunitiesGroup" label="Opportunities" >
<button id="OppsRefresh" size="large"
label="Get My Opportunities"
screentip="Gets or refreshes opportunity data"
onAction="OnRefresh" imageMso="RefreshAll"/>
<dialogBoxLauncher>
<button id="OppsConfigure"
label="Configure Opportunity Data Retrieval"
screentip="Configure Opportunity Data Retrieval"
onAction="OnDialogLaunch" />
</dialogBoxLauncher>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
Configuring the Add-In
You can configure the add-in through a custom dialog box. Configuration information is used to call the CRM Web service with appropriate parameters. This information is persisted as a custom XML document part within Excel Open XML document format. The following code shows how the configuration dialog box loads when requested from a custom tab and saves configuration values.
private void DialogForm_Load(object sender, EventArgs e)
{
// Load configuration.
try
{
// Create the Configuration object.
Configuration configObject = new Configuration();
RangeName.Text = configObject.NamedRange; ;
string[] status = configObject.Status.Split(';');
...
string[] priority = configObject.Priority.Split(';');
...
string quarter = configObject.Quarter;
...
string year = configObject.Year;
...
string compatibilityMode = configObject.CompatibilityMode; ;
...
}
catch (Exception x)
{
MessageBox.Show(x.Message, "VSTO and Excel Services",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Close_Click(object sender, EventArgs e)
{
// Save Configuration.
try
{
// Create the Configuration object.
Configuration configObject = new Configuration();
configObject.NamedRange = RangeName.Text;
...
configObject.Status = status;
...
configObject.Priority = priority;
...
configObject.Quarter = QuarterPicker.Value.ToString();
...
configObject.Year = YearPicker.Value.ToString();
...
configObject.CompatibilityMode =
CompatibilityMode.Checked.ToString();
}
catch (Exception x)
{
MessageBox.Show(x.Message, "VSTO and Excel Services",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
// Close the dialog box.
this.Close();
}
The configuration dialog box uses a custom class to read and write configuration values into a custom XML part. A custom XML part enables you to save data directly in the Excel workbook. In this way, the configuration values travel with the worksheet. So if the worksheet is copied or sent in e-mail, it still keeps the current configuration settings. The following code shows the custom configuration class.
class Configuration
{
private Office.CustomXMLPart configPart = null;
private Excel.Workbook wb = null;
private const string configNamespace =
"http://www. example.com/VSTOExcelSvcs.Configuration";
public Configuration()
{
// Current workbook.
wb = Globals.ThisAddIn.Application.ActiveWorkbook;
// Try to get the configuration data.
if (wb.CustomXMLParts.SelectByNamespace
(configNamespace).Count > 0)
configPart =
wb.CustomXMLParts.SelectByNamespace(configNamespace)[1];
// If it does not exist, create it.
else
{
// Create the XML document.
XmlDocument configXml = new XmlDocument();
// Create the configuration element.
XmlElement configElement =
configXml.CreateElement("configuration");
configXml.AppendChild(configElement);
XmlAttribute nsAttribute =
configXml.CreateAttribute("xmlns");
XmlAttribute namedRangeAttribute =
configXml.CreateAttribute("namedRange");
XmlAttribute statusAttribute =
configXml.CreateAttribute("status");
XmlAttribute priorityAttribute =
configXml.CreateAttribute("priority");
XmlAttribute quarterAttribute =
configXml.CreateAttribute("quarter");
XmlAttribute yearAttribute =
configXml.CreateAttribute("year");
XmlAttribute compatibilityModeAttribute =
configXml.CreateAttribute("compatibilityMode");
configElement.Attributes.Append(nsAttribute);
configElement.Attributes.Append(namedRangeAttribute);
configElement.Attributes.Append(statusAttribute);
configElement.Attributes.Append(priorityAttribute);
configElement.Attributes.Append(quarterAttribute);
configElement.Attributes.Append(yearAttribute);
configElement.Attributes.Append(
compatibilityModeAttribute);
configXml.AppendChild(configElement);
// Set the default values.
configXml.ChildNodes[0].Attributes[0].Value =
configNamespace;
configXml.ChildNodes[0].Attributes[1].Value =
ConfigurationManager.AppSettings["NamedRange"];
configXml.ChildNodes[0].Attributes[2].Value =
ConfigurationManager.AppSettings["Status"];
configXml.ChildNodes[0].Attributes[3].Value =
ConfigurationManager.AppSettings["Priority"];
configXml.ChildNodes[0].Attributes[4].Value =
ConfigurationManager.AppSettings["Quarter"];
configXml.ChildNodes[0].Attributes[5].Value =
ConfigurationManager.AppSettings["Year"];
configXml.ChildNodes[0].Attributes[6].Value =
ConfigurationManager.AppSettings["CompatibilityMode"];
// Create the XML part in the document.
configPart = wb.CustomXMLParts.Add(configBuilder.ToString()
, System.Type.Missing);
}
}
public string NamedRange
{
get
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(configPart.XML);
return doc.ChildNodes[0].Attributes[1].Value;
}
set
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(configPart.XML);
doc.ChildNodes[0].Attributes[1].Value = value;
configPart.Delete();
configPart = wb.CustomXMLParts.Add(
doc.OuterXml, System.Type.Missing);
}
}
public string Status
{...}
public string Priority
{...}
public string Quarter
{...}
public string Year
{...}
public string CompatibilityMode
{...}
}
Retrieving the Data
The data is returned from the Web service whenever the end user clicks the custom Get My Opportunities button. The first time the user clicks this button, the add-in retrieves the CRM data, populates the worksheet, and names the column headers using ranges. Subsequent button clicks use the ranges in the worksheet to update the existing data. Using this approach, the solution can maintain column formatting, conditional formatting, formulas, pivot tables, and charts across multiple calls. This means that end users can format the sheet the way they want and can update the data in the future.
The following code shows how to initially import the opportunities into the worksheet.
private void InsertOpportunityData(Excel.Range oppRange)
{
try
{
// Call Web services.
OpportunitySummary[] opportunities = GetOpportunities();
// Insert Column Headers into visible sheet.
oppRange.get_Offset(0, 0).Value2 = "Opportunity Name";
oppRange.get_Offset(0, 1).Value2 = "Company Name";
oppRange.get_Offset(0, 2).Value2 = "Estimated Close Date";
oppRange.get_Offset(0, 3).Value2 = "Expected Revenue";
oppRange.get_Offset(0, 4).Value2 = "Priority";
oppRange.get_Offset(0, 5).Value2 = "Sales Stage";
oppRange.get_Offset(0, 6).Value2 = "Opportunity Type";
oppRange.get_Offset(0, 7).Value2 = "Probability";
oppRange.get_Offset(0, 8).Value2 = "Status";
// Insert Named Ranges into the column headers.
oppRange.get_Offset(0, 0).Name = "OpportunityName";
oppRange.get_Offset(0, 1).Name = "CompanyName";
oppRange.get_Offset(0, 2).Name = "EstimatedCloseDate";
oppRange.get_Offset(0, 3).Name = "ExpectedRevenue";
oppRange.get_Offset(0, 4).Name = "Priority";
oppRange.get_Offset(0, 5).Name = "SalesStage";
oppRange.get_Offset(0, 6).Name = "OpportunityType";
oppRange.get_Offset(0, 7).Name = "Probability";
oppRange.get_Offset(0, 8).Name = "Status";
// Insert Data into sheet.
Excel.Range nextRow = oppRange.get_Offset(1, 0);
foreach (OpportunitySummary opportunity in opportunities)
{
nextRow.Value2 = opportunity.OpportunityName;
nextRow.get_Offset(0, 1).Value2 = opportunity.CompanyName;
nextRow.get_Offset(0, 2).Value2 =
opportunity.EstimatedCloseDate.ToShortDateString();
nextRow.get_Offset(0, 3).Value2 =
opportunity.ExpectedRevenue;
nextRow.get_Offset(0, 4).Value2 = opportunity.Priority;
nextRow.get_Offset(0, 5).Value2 = opportunity.SalesStage;
nextRow.get_Offset(0, 6).Value2 =
opportunity.OpportunityType;
nextRow.get_Offset(0, 7).Value2 = opportunity.Probability;
nextRow.get_Offset(0, 8).Value2 = opportunity.Status;
// Next row.
nextRow = nextRow.get_Offset(1, 0);
}
}
catch (FaultException<OpportunityError> err)
{
MessageBox.Show(err.Message);
}
catch (Exception x)
{
MessageBox.Show(x.Message);
}
}
private OpportunitySummary[] GetOpportunities()
{
try
{
// Create the Configuration object.
Configuration configObject = new Configuration();
// Get configuration values.
string status = configObject.Status;
string priority = configObject.Priority;
string quarter = configObject.Quarter +
"Q" + configObject.Year;
// Get SSO credentials from the Web service.
SSOServiceClient ssoProxy = new SSOServiceClient();
CredentialSet credentialSet =
ssoProxy.GetMyCredentials(ConfigurationManager.AppSettings[
"SSOApplicationName"]);
ssoProxy.Close();
if (credentialSet != null)
{
// Call the opportunity Web service.
OpportunityServiceClient oppProxy =
new OpportunityServiceClient();
OpportunitySummary[] opportunities =
oppProxy.GetMyOpportunities(credentialSet.Field1,
credentialSet.Field2, status, priority, quarter);
oppProxy.Close();
return opportunities;
}
else
throw new Exception("Cannot find SSO credentials");
}
catch (FaultException<OpportunityError> err)
{
MessageBox.Show(err.Detail.ErrorMessage);
}
catch (Exception x)
{
MessageBox.Show(x.Message);
return null;
}
}
After the initial data is loaded into the worksheet, the add-in can easily update the data with subsequent calls to the Web service. The subsequent calls use the configuration data saved in the document XML as the parameters for the call. This way, users can modify the parameters to return different sets. When new data is returned, it is placed in the worksheet so that all existing formatting is respected. If the new data contains fewer rows than the existing data, the additional rows are overwritten with blank cells.
The following code shows how to update the worksheet with new data.
private void RefreshOpportunityData(Excel.Range oppRange)
{
try
{
// Call Web services.
OpportunitySummary[] opportunities = GetOpportunities();
// Insert data.
Excel.Range nextRow = oppRange.get_Offset(1, 0);
foreach (OpportunitySummary opportunity in opportunities)
{
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"OpportunityName"), GetColumnOffsetByName(oppRange,
"OpportunityName")).Value2 = opportunity.OpportunityName;
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"CompanyName"), GetColumnOffsetByName(oppRange,
"CompanyName")).Value2 = opportunity.CompanyName;
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"EstimatedCloseDate"), GetColumnOffsetByName(oppRange,
"EstimatedCloseDate")).Value2 =
opportunity.EstimatedCloseDate.ToShortDateString();
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"ExpectedRevenue"), GetColumnOffsetByName(oppRange,
"ExpectedRevenue")).Value2 = opportunity.ExpectedRevenue;
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"Priority"), GetColumnOffsetByName(oppRange,
"Priority")).Value2 = opportunity.Priority;
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"SalesStage"), GetColumnOffsetByName(oppRange,
"SalesStage")).Value2 = opportunity.SalesStage;
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"OpportunityType"), GetColumnOffsetByName(oppRange,
"OpportunityType")).Value2 = opportunity.OpportunityType;
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"Probability"), GetColumnOffsetByName(oppRange,
"Probability")).Value2 = opportunity.Probability;
nextRow.get_Offset(GetRowOffsetByName(oppRange, "Status"),
GetColumnOffsetByName(oppRange, "Status")).Value2 =
opportunity.Status;
// Next row.
nextRow = nextRow.get_Offset(1, 0);
}
// Clear any extra remaining rows.
while (nextRow != null && nextRow.Value2 != null
&& nextRow.Value2.ToString().Length > 0)
{
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"OpportunityName"), GetColumnOffsetByName(oppRange,
"OpportunityName")).Value2 = string.Empty;
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"CompanyName"), GetColumnOffsetByName(oppRange,
"CompanyName")).Value2 = string.Empty;
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"EstimatedCloseDate"), GetColumnOffsetByName(oppRange,
"EstimatedCloseDate")).Value2 = string.Empty;
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"ExpectedRevenue"), GetColumnOffsetByName(oppRange,
"ExpectedRevenue")).Value2 = string.Empty;
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"Priority"), GetColumnOffsetByName(oppRange,
"Priority")).Value2 = string.Empty;
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"SalesStage"), GetColumnOffsetByName(oppRange,
"SalesStage")).Value2 = string.Empty;
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"OpportunityType"), GetColumnOffsetByName(oppRange,
"OpportunityType")).Value2 = string.Empty;
nextRow.get_Offset(GetRowOffsetByName(oppRange,
"Probability"), GetColumnOffsetByName(oppRange,
"Probability")).Value2 = string.Empty;
nextRow.get_Offset(GetRowOffsetByName(oppRange, "Status"),
GetColumnOffsetByName(oppRange, "Status")).Value2 =
string.Empty;
// Next row.
nextRow = nextRow.get_Offset(1, 0);
}
}
catch (FaultException<OpportunityError> err)
{
MessageBox.Show(err.Detail.ErrorMessage);
}
catch (Exception x)
{
MessageBox.Show(x.Message);
}
}
Creating a User-Defined Function for Excel Services
Although the Excel add-in described here works well for bringing Web service data into Office Excel 2007, such a solution is not supported in Excel Services. Instead, the solution uses a user-defined function to provide an experience in Excel Services that is similar to the Excel add-in. User-defined functions are assemblies that you can invoke in a worksheet through a function call in a cell. In this solution, opportunities are returned to the worksheet through the following function call.
=GetMyOpportunities(rows, status, priority, year, quarter)
The GetMyOpportunities method returns a two-dimensional array, which the user can map to an Excel range. This populates the range with the returned data when Excel Services renders the workbook. Excel 2007 does not, however, natively support managed UDFs written for Excel Services. As a result, when a user is composing the workbook in Excel 2007 (before publishing it to Excel Services) and enters the formula shown earlier, Excel 2007 displays a #NAME error in the cell. The solution fixes this problem by making the GetMyOpportunities method available to Excel 2007 by making it COM visible.
This approach of mapping a two-dimensional array to an Excel range has a limitation: The dimensions of the array and the range must match. The solution avoids this problem by padding the data if the CRM Web service returns fewer rows than are available in the Excel range, or by displaying a message if the Web service returns more rows than are available in the Excel range. The solution recommends that the user select an Excel range larger than the rows expected by the CRM Web service.
The user-defined function is created in a class that is marked with the UdfClass attribute, and it implements a method that is marked with the UdfMethod attribute. The IsVolatile attribute is added to UdfMethod to prevent Excel Services from caching the data so that users see only their own data. As mentioned earlier, the ComVisible attribute and other COM-related attributes are added to make the UDF available to Excel 2007. Additionally, the assembly must be explicitly listed as trusted in the Excel Service configuration section of SharePoint Server 2007. When the user-defined function is created correctly, you can invoke it in a worksheet and publish the worksheet to a document library in SharePoint Server 2007.
The following code shows the class that implements the user-defined function for the solution.
[ProgId("VSTOExcelSvcs.UDF.OpportunityFunctions")]
[ClassInterface(ClassInterfaceType.AutoDual)]
[Guid(OpportunityFunctions.Guid)]
[ComVisible(true)]
[UdfClass]
public class OpportunityFunctions
{
public const string Guid = "76E9C116-0428-4bcd-AD63-DC618299E9E0";
[ComVisible(true)]
[UdfMethod(IsVolatile=true)]
public object[,] GetMyOpportunities(int Rows, string Status,
string Priority, string Year, string Quarter)
{
try
{
// Get SSO credentials from the Web service.
EndpointAddress ssoAddress = new
EndpointAddress("http://localhost:2012/SSOService.svc");
WSHttpBinding ssoBinding = new WSHttpBinding();
ChannelFactory<SSOService.ISSOService> ssoFactory = new
ChannelFactory<SSOService.ISSOService>(
ssoBinding, ssoAddress);
SSOService.ISSOService ssoProxy =
ssoFactory.CreateChannel();
SSOService.CredentialSet credentialSet =
ssoProxy.GetMyCredentials("VSTOExcelCRM");
ssoFactory.Close();
if (credentialSet != null)
{
// Call the opportunity Web service.
EndpointAddress oppAddress = new
EndpointAddress(
"http://localhost:2574/OpportunityService.svc");
WSHttpBinding oppBinding = new WSHttpBinding();
ChannelFactory<OpportunityService.IOpportunityService> oppFactory =
new ChannelFactory<OpportunityService.IOpportunityService>(
oppBinding, oppAddress);
OpportunityService.IOpportunityService oppProxy =
oppFactory.CreateChannel();
OpportunityService.OpportunitySummary[] opportunities =
oppProxy.GetMyOpportunities(credentialSet.Field1,
credentialSet.Field2, Status, Priority, Quarter +
"Q" + Year);
oppFactory.Close();
// Size the array.
object[,] resultArray = new object[Rows, 9];
// Write out the column headers.
resultArray[0, 0] = "Opportunity Name";
resultArray[0, 1] = "Company Name";
resultArray[0, 2] = "Estimated Close Date";
resultArray[0, 3] = "Expected Revenue";
resultArray[0, 4] = "Priority";
resultArray[0, 5] = "Sales Stage";
resultArray[0, 6] = "Opportunity Type";
resultArray[0, 7] = "Probability";
resultArray[0, 8] = "Status";
// Write out the values.
for (int row = 1; row < Rows; row++)
{
if (opportunities.GetUpperBound(0) >= (row - 1))
{
resultArray[row, 0] =
opportunities[row - 1].OpportunityName;
resultArray[row, 1] =
opportunities[row - 1].CompanyName;
resultArray[row, 2] =
opportunities[row - 1]
.EstimatedCloseDate.ToShortDateString();
resultArray[row, 3] =
opportunities[row - 1].ExpectedRevenue;
resultArray[row, 4] =
opportunities[row - 1].Priority;
resultArray[row, 5] =
opportunities[row - 1].SalesStage;
resultArray[row, 6] =
opportunities[row - 1].OpportunityType;
resultArray[row, 7] =
opportunities[row - 1].Probability;
resultArray[row, 8] =
opportunities[row - 1].Status;
}
else
{
resultArray[row, 0] = "";
resultArray[row, 1] = "";
resultArray[row, 2] = "";
resultArray[row, 3] = "";
resultArray[row, 4] = "";
resultArray[row, 5] = "";
resultArray[row, 6] = "";
resultArray[row, 7] = "";
resultArray[row, 8] = "";
}
}
if (Rows - 1 < opportunities.Length)
{
resultArray[Rows - 1, 0] = "There are " +
(opportunities.Length - Rows + 2).ToString() +
" additional rows not shown.";
resultArray[Rows - 1, 1] = "";
resultArray[Rows - 1, 2] = "";
resultArray[Rows - 1, 3] = "";
resultArray[Rows - 1, 4] = "";
resultArray[Rows - 1, 5] = "";
resultArray[Rows - 1, 6] = "";
resultArray[Rows - 1, 7] = "";
resultArray[Rows - 1, 8] = "";
}
return resultArray;
}
else
throw new Exception("Cannot find SSO credentials");
}
catch (FaultException x)
{
object[,] resultArray = new object[1, 1];
resultArray[0, 0] = x.Message;
return resultArray;
}
catch (Exception x)
{
object[,] resultArray = new object[1, 1];
resultArray[0, 0] = x.Message;
return resultArray;
}
}
The solution provides a user interface to add an array formula that contains the UDF to the workbook. The user selects a range of cells in the Excel 2007 workbook, opens the configuration dialog box from the custom Office Fluent Ribbon, and selects the Use Excel Services Compatibility Mode check box. This not only associates the array formula with the selected range but also creates named ranges to surface configuration information such as opportunity status, priority, quarter, and year. This is necessary because the configuration information is stored as a custom XML document part inside Excel, but Excel Services does not have access to this OpenXML. The added advantage of this approach is that you can publish these named ranges as workbook parameters when the workbook is published to Excel Services. The following code shows how the array formula is added to the worksheet.
// Get the named range for the data.
Excel.Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
string rangeName = configObject.NamedRange + "UDF";
Excel.Range oppRange = GetNamedRange(wb, rangeName);
// If range does not exist, create it at the current active cell.
if (oppRange == null)
{
int selectedRowCount =
((Excel.Range)Globals.ThisAddIn.Application.Selection).Rows.Count;
if (selectedRowCount <= 1)
selectedRowCount =
int.Parse(ConfigurationManager.AppSettings["DefaultRowCount"]);
Excel.Range startRange =
(Excel.Range)Globals.ThisAddIn.Application.ActiveCell;
Excel.Range endRange = startRange.get_Offset(selectedRowCount-1,8);
oppRange =
(Excel.Range)((Excel.Worksheet)
Globals.ThisAddIn.Application.ActiveSheet).get_Range(
startRange, endRange);
oppRange.Name = configObject.NamedRange + "UDF";
// Create named ranges for the parameters with default values.
startRange.get_Offset(0, 11).Name = "These_Statuses";
startRange.get_Offset(0, 11).Value2 = configObject.Status;
startRange.get_Offset(1, 11).Name = "These_Priorities";
startRange.get_Offset(1, 11).Value2 = configObject.Priority;
startRange.get_Offset(2, 11).Name = "This_Year";
startRange.get_Offset(2, 11).Value2 = configObject.Year;
startRange.get_Offset(3, 11).Name = "This_Quarter";
startRange.get_Offset(3, 11).Value2 = configObject.Quarter;
// Label the named ranges.
startRange.get_Offset(0, 10).Value2 = "Get These Statuses";
startRange.get_Offset(1, 10).Value2 = "Get These Priorities";
startRange.get_Offset(2, 10).Value2 = "Get This Year";
startRange.get_Offset(3, 10).Value2 = "Get This Quarter";
// Attach the formula to the range.
oppRange.FormulaArray = "=GetMyOpportunities(" +
selectedRowCount.ToString() +
",L1,L2,TEXT(L3,\"####\"),TEXT(L4,\"#\"))";
}
// Refresh the workbook.
wb.RefreshAll();
Conclusion
This solution shows how to use a Visual Studio Tools for Office add-in and an Excel user-defined function to consume two-dimensional array data in both Excel 2007 and Excel Services. It shows that you can achieve similar end-user experiences when you work with two-dimensional array data in both Excel 2007 and Excel Services. It also shows that you can consume Web service data in ways that are similar to how Excel 2007 currently consumes databases. By using the approach outlined in this article, you can provide end users with more flexibility in the consumption and use of two-dimensional array data returned from Web services.
Additional Resources
For more information, see the following resources: