Adding Chart Elements to Charts in Excel 2007

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:

1. An Excel 2007 workbook exists in the C:\Temp folder. It is named SalesChart.xlsx, and it contains a single worksheet named Quarterly Sales.

2. The Quarterly Sales worksheet contains an embedded column chart named Sales Chart.

Download the Code Sample
To illustrate how to programmatically set the elements of a chart in Excel 2007, this section walks through seven key steps:

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:

using Microsoft.Office.Interop.Excel;

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.

ApplicationClass excelApplication = new ApplicationClass();
Workbook excelWorkBook = null;
Worksheet targetSheet = null;
ChartObjects chartObjects = null;
ChartObject existingChartObject = null;

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.

string paramWorkbookPath = @"C:\Temp\SalesChart.xlsx";
object paramMissing = Type.Missing;

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,
    paramMissing, paramMissing, paramMissing, paramMissing, 
    paramMissing, paramMissing, paramMissing, paramMissing, 
    paramMissing, paramMissing, paramMissing, paramMissing, 
    paramMissing, paramMissing);

// Get the worksheet that contains the chart.
targetSheet = (Worksheet)(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 = (ChartObjects)(targetSheet.ChartObjects(paramMissing));

// Get the chart to modify.
existingChartObject = (ChartObject)(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, paramMissing,
    paramMissing, paramMissing, paramMissing, paramMissing, 
    paramMissing, paramMissing, paramMissing, paramMissing);

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.

// Release the references to the Excel objects.
existingChartObject = null;
chartObjects = null;
targetSheet = null;

// Close the Workbook object.
if (excelWorkBook != null)
{
    excelWorkBook.Close(false, paramMissing, paramMissing);
    excelWorkBook = null;
}

// Close the ApplicationClass object.
if (excelApplication != null)
{
    excelApplication.Quit();
    excelApplication = null;
}

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

Watch the Video

Video Length: 00:04:49

File Size: 4.60 MB WMV

• Excel Developer Portal

• Excel 2007 Resource Center

• Office Developer Center

• Understanding Optional Parameters in COM Interop

• Blog: Microsoft Excel 2007