How to create a VSTO add-in for PowerPoint to insert charts with External Data using the Business Connectivity Services API.
Summary
It is a common scenario to use charts in the PowerPoint presentation as a way of summarizing the data in and make it more readable and actionable. Frequently, the data used to generate the charts will often reside in an external system such as a database, CRM or an ERP system. This involves switching between the applications and is often time consuming in the getting the data. Also it may be desirable to get the up-to-date information on such charts.
For example, many sales departments need to report sales figures in a regular weekly or monthly meeting. Typically this would be presented as a chart in a PowerPoint presentation. This data is often stored in an external database. The sales manager can use the add-in to create the chart and the add-in will in turn use the BCS API to get the data. We can develop the PowerPoint add-in using Visual Studio 2010. This add-in will connect to the back-end data using Business Connectivity Services (BCS), a set of capabilities that ship as part of Office and SharePoint 2010.Following sections explain how to develop this add in.
Important Note:
I co-wrote this article with Eric White .Many thanks to him for providing the insights. Many thanks to Brad Stevenson, Juan Balmori Labra , Russell Palmer and Thomas Mechelkefor reviewing this article. They are the program managers behind the article.
This article will be published on MSDN some time.
About the sample
The sample in the article is a PowerPoint add-in. If you follow the steps mentioned in this document you will be creating an add-in that would insert the new slide and the chart. This chart will use the data that is fetched using the BCS API to get the data. This data is stored in the SQL Server. To make the example simple we will use the data from the “SalesTerritory” table. This table contains the sales data across various territories in the United States.
This add-in contains a ribbon tab “BCS” which has a button “Add External Data to Chart”.
When the user clicks on this button, the add-in will open the custom task pane.
This custom task pane is populated with the external content types available in the business connectivity services cache. It uses the BCS client object model to get the data from the external system.
When you click on the button “Insert Chart” it will insert the new slide and the chart by using the values selected in the custom task pane and inserts it in the new slide like this.
Finally you will use the BCS Solution Package Tool to package and deploy the solution to ensure that the metadata model is available to the client before invoking the BCS runtime APIs.
The following section describes how to generate such a chart.
Generating Chart in PowerPoint Add-in
In Office 2010, there is a chart object. The chart object mainly surfaces a chart on the slide and renders a graph with the supplied values. For more information about the chart object, see MSDN blog post, Chart Object.
In our sample each chart draws the trend of “Last Year Sales” to “Year to Date Sales” using the following lines of code.
public void InsertChart(ChartDataObject chartDataObject)
{
Chart chart = null;
Shape shape = null;
Microsoft.Office.Interop.Excel.Workbook Workbook = null;
Microsoft.Office.Interop.Excel.Application Application = null;
try
{
//Add the new slide
Slide slide = this.Application.ActivePresentation.Slides.Add(this.Application.ActivePresentation.Slides.Count + 1, PpSlideLayout.ppLayoutChart);
//Insert the chart
shape = slide.Shapes.AddChart(Office.XlChartType.xlLine);
chart = shape.Chart;
//Clear the chart contents
chart.ChartArea.ClearContents();
object[] Values = new object[chartDataObject.XValues.Count()];
for (int i = 0; i < chartDataObject.Data.Rows.Count; i++)
{
for (int j = 0; j < chartDataObject.XValues.Length; j++)
{
Values[j] = chartDataObject.Data.Rows[i][chartDataObject.XValues[j].ToString()];
}
SeriesCollection SeriesCollection = chart.SeriesCollection() as SeriesCollection;
//Create new series based on the data
Series Series = SeriesCollection.NewSeries();
Series.Name = chartDataObject.Data.Rows[i][chartDataObject.SeriesName].ToString();
Series.XValues = chartDataObject.XValues;
Series.Values = Values;
}
//Activate the chart to refresh with the fresh data.
chart.ChartData.Activate();
}
finally
{
//Close the Excel Workbook and clean up resources.
if (chart != null && chart.ChartData != null)
{
Workbook = chart.ChartData.Workbook as Microsoft.Office.Interop.Excel.Workbook;
if (Workbook != null)
Application = Workbook.Application;
if (Application != null && Workbook!=null)
{
//Quit Excel Application
Application.Quit();
Application = null;
Workbook = null;
//Force the GC, this is recommended for releasing the resources.
//Quitting Excel doesnt ensure resources are cleaned immediatly
GC.Collect();
GC.WaitForPendingFinalizers();
//Repeated calls is necessary is because the memory for the Excel
//would have survived for the first pass.
//Hence the second try to recalim completely.
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
}
The add in will use BCS to get the data for the chart. Following section provides an overview of BCS.
Overview of BCS
Business Connectivity Services (BCS) in SharePoint 2010 is the successor of Business Data Catalog that is available in MOSS 2007, with Enterprise Client Access License. It has rich designer capabilities.
For more information about the overview of BCS, see MSDN documentation Overview of Business Connectivity Services. Integration with Office add-ins applications is much simpler.
The following figure shows the high level architecture of BCS.
BCS is all about connecting to external data. This external data can be viewed in the SharePoint list, Word, Outlook and SharePoint Workspace out of the box without any additional work. If you want to build a custom Word or Outlook solution, or surface the external data in applications that are not supported out of the box such as Excel or PowerPoint application, you can code against the BCS client runtime.
Following are the important components in BCS that you need to understand.
· External Content Type
· BCS Metadata Model (BDC Service).
· BCS Client Runtime.
In simple terms External content type enables us to define re-usable business entities. For more information about the External Content Types, see “External Content Types”. The BDC service models the metadata objects like the external content type and stores it in the metadata store. This essentially contains the metadata about the programmatic interfaces of the external systems. For more information about the BDC Service, see “Mechanics of Using Business Connectivity Services”.
The sample metadata model in xml format is presented in the appendix.
The BCS client runtime which is part of the Microsoft Office 2010 professional plus edition will use the metadata model to connect to the external data. This metadata will contain enough information on how to access the external system.
We can use the SharePoint Designer 2010 to create an External Content type. Following sections describe how to create this.
Creating "SalesBCS" External Content Type for the sample
In order to provide the required external data to our chart in the PowerPoint presentation, you need to create at least one external content type.
The name and the name space will be used to identify an external content type at runtime. In
Once an external content type it is created you can configure the type of “External Data Source”. It can be one of the following.
1. .NET Type.
2. SQL Server.
3. WCF Service.
In this example, you will use the “SQL Server” and the “SalesTerritory” table. This table contains sales details across various territories in the United States. This is the data that you will display in your PowerPoint presentation.
You can set the various operations on this table like Create, Read, Update and Delete and specify filters, if any and these will be used while accessing the data.
Save this information in SharePoint Designer 2010 for further processing.
Once the “External Content Type is created you can use SharePoint Designer 2010 to create a list and view in the browser.
You will use the SharePoint designer 2010 to create the external content type “SalesBCS”. The example presented in this article connects to the Adventure Works database. Download the AdventureWorks 2008R2 November CTP and install it. In reality this can be any database. Once the database is installed, we can use SharePoint Designer 2010 to create an “External Content Type”. This contains metadata about the data access capabilities of the external system.
Before creating the external content type you will need to set the permissions at the metadata store level. This will ensure that users will have appropriate permissions to get the external data.
To set the permissions
1. Start SharePoint 2010 Central Administration.
2. Click the Manage Service Applications link on the home page of SharePoint 2010 Central Administration.
3. Click the Business Connectivity Service link.
4. In the ribbon click on the Edit tab and then click on the button Set Object Permissions.
5. Add the account information and select all operations in the Permissions list box.
This will set the appropriate permissions at the service level.
To define a new External Content Type
1. Launch SharePoint Designer 2010.
2. Click on the button Open Site and specify the site address.
3. On the left Navigation pane, point to Site Objects, and then click on the button External Content Types.
4. Click on the ribbon button External Content Type.
5. In the External Content Type information, specify the Name as “SalesBCS”.
6. Specify the Namespace.
7. Select the Office Item Type asGeneric List-this is the default option.
8. Click on the link Click here to discover external data sources and define operations.
9. Click on the button Add Connection.
10. Select the Data Source Type as SQL Server.
11. Specify the Database Server and the Database name as Adventure Works.
12. Expand the Adventure Works database and select the Sales Territory table.
13. Right-click on the Sales Territory table and choose Create All Operations.
14. In the All Operations user interface click Finish.
15. Click on the Save button in the SharePoint designer.
This will save the External Content Type information in the BDC Metadata store.
Following figure shows the External Content Type that is created using SharePoint designer 2010.
To create the External List
1. Launch SharePoint Designer 2010.
2. Click on the button Open Site to connect to the site
3. On the left pane navigate to the SalesBCS external content type.
4. Click on ribbon button Create Lists & Forms.
5. Choose Create New List and specify the List Name.
6. Click Ok to create the list. - This will create the external list.
7. Click on the ribbon button Preview in Browser to view the list in the browser.
Following section describes how to connect to the back-end using the BCS client runtime.
PowerPoint add-in will use BCS client runtime object model, which internally uses the Metadata model to connect to the back-end data. The custom task pane in our sample will use the following lines of code to populate the user control using the external data.
Connecting to back-end data using BCS client runtime:
public void PopulateControls()
{
if (!CheckDependentDataSolution())
return;
RemoteSharedFileBackedMetadataCatalog Catalog = new RemoteSharedFileBackedMetadataCatalog();
IEntityInstanceEnumerator InstanceEnumerator = null;
INamespacedEntityDictionaryDictionary entDictDict = Catalog.GetEntities("*");
foreach (INamedEntityDictionary entDict in entDictDict.Values)
{
foreach (IEntity entity in entDict.Values)
{
EntityLookup[entity.Name] = entity;
ectList.Items.Add(entity.Name);
}
}
// Populate the controls
}
It first begins by creating an instance of RemoteSharedFileBackedMetadataCatalog. This is the base object that uses metadata model to connect to the back-end database. The GetEntities method will return an INamespacedEntityDictionaryDictionary interface which represents the external content type’s collection from the back-end. The GetEntity method will return an IEntityInstanceEnumerator interface which represents collection or stream as “entity” instances from the back-end. The term “entity” here is the external content type. The term “entity” is used in the programmatic interfaces. The documentation and the SharePoint Designer 2010 will call this as the “External Content Type”. We obtain an instance of InstanceEnumerator based on set of filters against the LobSystemInstance, which in this case is the Adventure Works database instance. The GetMethodInstances method returns the instances of the entity. Then you can get a DataTable that contains the external data using the IRuntimeHelper interface.
The following section describe how to create the data solution to get the metadata model.
In order to get the external data using the BCS client object model in the PowerPoint add-in you need to get the metadata model to the client computer. The BCS Solution Packaging Tool helps in getting the external data to the add-in.
For more information on how to create the data solution for office add-in, download the “BCS Solution Packaging Tool”. You need to ensure that the Data Solution is functioning before invoking the BCS runtime APIs. To do this
1. Launch the BCS Solution Packaging Tool.
2. Navigate to the section “Package BCS Data Only Solution for Office Add-in”.
3. Copy the “Code Example”.
You can now use it in the add-in. The “CheckDependentDataSolution” from the BCS Solution Packaging Tool will ensure that the metadata model is available before invoking the BCS runtime APIs.
In the PowerPoint add-in before invoking the external data you need to execute the following code.
public void PopulateControls()
{
if (!CheckDependentDataSolution())
return;
//Get the external data
//Populate the controls
}
Packaging and Deploying the Solution
Before installing the PowerPoint add-in you need to package and deploy the solution. To do this
1. Launch the BCS Solution Packaging Tool
2. Specify the Solution Name.
3. Specify the Solution Version.
4. Select the solution type to “Data Solution for Office Add-in” from the combo box.
5. Browse and select the Source Folder Path
This folder will contain the PowerPoint add-in solution. Place the metadata model and the subscriptions xml file in this folder.
6. Browse and select Output Folder Path
7. Click on the button Package
Click on the button Deploy
This will deploy the solution package to the BCS runtime.
The subscription file is an xml file that contains the information about what gets populated in the cache. For more information about the subscriptions, see “Cache Subscriptions”. The appendix provides the information about how to export the metadata model.
Following are the pre-requisites that are required to run the solution
1. Install Visual Studio 2010
2. Install Office 2010 Professional Plus edition
3. Install SharePoint 2010
4. Download and Install the AdventureWorks 2008R2 November CTP
Download the code and build it using Visual Studio 2010.
The following procedure explains how to run the sample.
1. Press-F5
2. This will launch the PowerPoint presentation
3. Click on the ribbon tab “BCS”
4. Click on the ribbon button “Add External Data to Chart”
5. This will open the custom task pane
6. Select an item from the “Select External Content Type” combo box.
7. Select an item from the “Select Series Name” combo box.
8. Check an item from the “Select Chart Categories” check box list control.
9. Click on the button “Insert Chart”
This will insert the chart on the active slide like this.
To update an existing chart you need to select the existing chart. Then you need to click on the button “Update Existing Chart”. This will use the selected values in the custom task pane to update an existing chart.
When you insert a chart, it automatically adds an Excel sheet. This sheet will contain the data that chart will use to insert the chart. You need to force a Garbage Collection to ensure memory consumed by the excel application is reclaimed. The excel process will remain in memory for some indeterminate time after we quit the excel application. A repeated call to GC.Collect is necessary because the memory for the Excel reference might have survived the first pass, although it will then have been marked for collection on the next pass.
You have the following options to deploy the PowerPoint add-in.
1. The Visual Studio publish wizard
2. The Windows installer.
Deploying the add-in using the publish wizard is simple. For advanced deployment features like installing the add-in for all users, executing some custom actions the windows installer will be the preferable option.
To deploy the add-in using the publish wizard
1. Launch the Visual studio 2010
2. Open the PowerPoint add-in project.
3. Right click on the solution explorer to open the Publish property page
4. Specify the Publish location
5. Click finish
6. Double click on the VSTO deployment file.
This will install the PowerPoint add-in to the computer.
For more information on advanced deployment using the windows installer, see “Deploying Office Solutions by Using Windows Installer”.
Appendix A: SalesBCS Metadata Model
This section provides the Metadata model for our SalesBCS external content type.
To view and export the Metadata model
1. Launch SharePoint Designer 2010.
2. Click on the button Open Site to connect to the site
3. On the left Navigation pane, point to Site Objects, and then on the button External Content Types.
4. Right-click on the SalesBCS External Content Type and select Export Application Data Model.
BCS client runtime will use this to connect to back-end. Metadata model for our SalesBCS in Xml format looks like this.
Conclusion
BCS 2010 offers a new way of integrating external data to Office applications. It handles the complicated problem of fetching and synchronizing the data between Office and one or more external systems. Without out BCS it would be very difficult to implement. This sort of rich integration to Office applications with BCS enables us to build classic Office Business Applications.
Additional Resources
What is Microsoft Business Connectivity Services?
BCS Blogs: