Adding Chart Elements to Charts in Excel 2007
Summary: This visual how-to article walks through how to programmatically add chart elements in Excel 2007.
Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007
Joel Krist, Akona Systems
The 2007 Microsoft Office system introduces multiple changes to the way charting is integrated across the Microsoft Office system. Specifically, Microsoft Office Excel 2007 is now the charting engine for all Microsoft Office system applications. It helps to provide a consistent charting experience for both developers and end-users. For developers, one of these changes is the way that Excel 2007 charts provide new formatting objects based on the OfficeArt object model. This article illustrates how to use the Microsoft Excel 12.0 Object Library to programmatically set the elements of an embedded column chart. For more information about other changes to the 2007 Office system charting functionality, see Changes in the 2007 Office system.
The sample code that is shown later assumes the following:
Download the Code Sample
To programmatically set the elements of a chart in Excel 2007
1. Add a Reference to the Excel 12.0 Object Library
First add a reference to the Microsoft Excel 12.0 Object Library to the Microsoft Visual Studio project. To do this, right-click the project in the Visual Studio Solution Explorer and select the Add Reference… menu item. Select the COM tab in the Add Reference dialog box, then scroll down to the Microsoft Excel 12.0 Object Library component, select it, and then click OK to add the reference.
Figure 1. Adding a Reference
2. Import the Excel Interop Namespace
Next import the Microsoft.Office.Interop.Excel namespace to allow access to the objects defined in the Microsoft Excel 12.0 Object Library. To do this, add the following line to the top of the source file:
To import the namespace for Microsoft Visual Basic projects, right-click the project in the Visual Studio Solution Explorer and select the Properties menu item. On the project properties page select the References tab and then select the check box next to the Microsoft.Office.Interop.Excel entry in the list of imported namespaces.
3. Create an Instance of the Excel 2007 ApplicationClass Object
To work with the Excel 2007 object model, create an instance of the Excel 2007 top-level ApplicationClass object and declare variables to hold references to the other Excel 2007 objects used in the chart manipulation code.
Dim excelApplication As ApplicationClass = New ApplicationClass() Dim excelWorkBook As Workbook = Nothing Dim targetSheet As Worksheet = Nothing Dim chartObjects As ChartObjects = Nothing Dim existingChartObject As ChartObject = Nothing
4. Declare Variables
The following code declares variables that help to make the parameters passed to methods used in the chart element modification code easier to read.
Use the paramWorkbookPath variable to specify the path and filename of the Excel 2007 workbook that contains the chart to be modified.
Use the paramMissing variable to call methods that accept optional parameters. Optional parameters are only optional when you use Microsoft Visual Basic. You must specify a value for optional parameters when you use Microsoft Visual C#. Using Type.Missing as the value for an optional parameter tells the method being called that the parameter is not being specified and that the method should use the parameter's default value.
5. Open the Worksheet that Contains the Chart
The following code shows how to open the workbook and get the worksheet named Quarterly Sales.
' Open the source workbook. excelWorkBook = excelApplication.Workbooks.Open(paramWorkbookPath) ' Get the worksheet that contains the chart. targetSheet = excelWorkBook.Worksheets("Quarterly Sales")
6. Get the Chart Object to Modify
Next add the code that loads the Chart object for the chart named "Sales Chart".
' Get the ChartObjects collection for the sheet. chartObjects = targetSheet.ChartObjects() ' Get the chart to modify. existingChartObject = chartObjects.Item("Sales Chart")
7.Set the Chart's Elements
' Modify the chart's title existingChartObject.Chart.ChartTitle.Text = "Quarterly Sales" ' Remove the chart's legend and cause the chart data table to be ' displayed with no vertical cell borders. existingChartObject.Chart.HasLegend = False existingChartObject.Chart.HasDataTable = True existingChartObject.Chart.DataTable.HasBorderVertical = False ' Set the chart to show data point values for data labels. existingChartObject.Chart.ApplyDataLabels( _ XlDataLabelsType.xlDataLabelsShowValue)
Save the Workbook
Save the workbook and exit Excel 2007. After the code saves the Excel 2007 workbook and releases any references to the underlying Excel 2007 COM objects, it closes the workbook and the application objects, and makes calls to the .NET Framework garbage collector. For more information on how to release COM objects when using managed code, see Chapter 2: Basics of Office Interoperability (Part 2 of 3) from Microsoft .NET Development for Microsoft Office.
excelWorkbook.Save() ' Release the references to the Excel objects. existingChartObject = Nothing chartObjects = Nothing targetSheet = Nothing ' Close the Workbook object. If Not excelWorkBook Is Nothing Then excelWorkBook.Close(False) excelWorkBook = Nothing End If ' Close the ApplicationClass object. If Not excelApplication Is Nothing Then excelApplication.Quit() excelApplication = Nothing End If GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers()
The 2007 Microsoft Office system introduces multiple changes to the way that charting is integrated across the Microsoft Office system. Specifically, Excel 2007 is now the charting engine for all Microsoft Office applications. It helps to provide a consistent charting experience for both developers and end-users. As a developer, getting familiar with the charting capabilities of Excel 2007 is an important part of exploring the 2007 Office system. The Excel 2007 charting functionality is now pervasive through the 2007 Microsoft Office system, and is used in many more places than it was previously. This also means that a broader set of Microsoft Office business applications can use this functionality.
In addition to general changes to charting in Excel 2007, a specific addition that will help developers is the ability to programmatically change chart elements using the OfficeArt object model.
This article explores the key steps for how to programmatically set the elements of a chart in Excel 2007, including:
To programmatically set the elements of a chart in Excel 2007
Video Length: 00:04:49
File Size: 4.60 MB WMV