Creating Basic Column Charts in Excel 2007

Summary: This topic illustrates using the Microsoft Excel 12.0 Object Library and the Chart.ChartWizard method to create a basic embedded column chart programmatically.

Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007

Joel Krist, Akona Systems

March 2007

The 2007 Microsoft Office system introduces multiple changes to the way charting is integrated across the Office programs. Specifically, Microsoft Office Excel 2007 is now the charting engine for all Microsoft Office system applications and helps provide a consistent charting experience for both developers and end-users. For developers, one of the key components of the Office Excel 2007 object model is the Chart.ChartWizard method, which simplifies the programmatic creation of charts. For more information about other changes to the 2007 Office system charting functionality, see Changes in the 2007 Office system.

Watch the Video

Length: 07:42 | Size: 6.22 MB | Type: WMV file

Code It | Read It | Explore It

Code It

Download the Sample Code

This section illustrates nine key steps to create a basic column chart programmatically in Excel 2007. They are:

Adding a reference to the Excel 12.0 Object Library.
Importing the Excel interop assembly namespace.
Creating an instance of the ApplicationClass object.
Declaring the appropriate variables.
Creating a data insertion helper method.
Creating a workbook and adding a worksheet to it.
Inserting chart data.
Getting a Range object for the chart data.
Implementing the chart creation code.

1. Adding a Reference to the Excel 12.0 Object Library

Begin by adding a reference to the Microsoft Excel 12.0 Object Library in the Visual Studio project.

To add a reference to the Excel 12.0 object library

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.
Click OK to add the reference.

Figure 1. Adding a Reference

2. Importing the Excel Interop Namespace

The next step is to import the Microsoft.Office.Interop.Excel namespace to the project.

To import the Microsoft.Office.Interop.Excel namespace

For Visual Basic projects, right-click the project in the Visual Studio Solution Explorer and select the Properties menu item.
On the Properties pages for the project, select the References tab and then select the check box next to the Microsoft.Office.Interop.Excel entry in the list of imported namespaces.
Close the Properties pages.

Figure 2. Importing Namespace

For Microsoft Visual C# projects add the following line to the top of the source file:

using Microsoft.Office.Interop.Excel;

3. Creating an Instance of the Excel ApplicationClass Object

To work with Excel's object model, create an instance of Excel's top-level ApplicationClass object and declare variables to hold references to the other Excel objects used in the chart creation code.

Visual Basic

Dim excelApplication As ApplicationClass = New ApplicationClass()
Dim newWorkbook As Workbook = Nothing
Dim targetSheet As Worksheet = Nothing
Dim dataRange As Range = Nothing
Dim chartObjects As ChartObjects = Nothing
Dim newChartObject As ChartObject = Nothing

ApplicationClass excelApplication = new ApplicationClass();
Workbook newWorkbook = null;
Worksheet targetSheet = null;
Range dataRange = null;
ChartObjects chartObjects = null;
ChartObject newChartObject = null;

4. Declaring Appropriate Variables

You must declare variables for parameters that are passed to methods used in chart creation. This modification makes the chart creation code easier to read.

The paramWorkbookPath variable specifies the path and file name of the Excel workbook to create.

The paramMissing variable is used when calling methods that accept optional parameters. Optional parameters are only optional when using Microsoft Visual Basic. You must specify a value for optional parameters when using C#. Using Type.Missing as the value for an optional parameter signals to the method being called that the parameter is not specified and that the method should use the parameter's default value.

Visual Basic

Dim paramWorkbookPath As String = "C:\Temp\Test.xlsx"

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

The following variables are used with the Chart.ChartWizard method. For more information about the ChartWizard method and the parameters it accepts, see Chart.ChartWizard Method.

Visual Basic

Dim paramChartFormat As Object = 1
Dim paramCategoryLabels As Object = 0
Dim paramSeriesLabels As Object = 0
Dim paramHasLegend As Boolean = True
Dim paramTitle As String = "Sales by Quarter"
Dim paramCategoryTitle As String = "Fiscal Quarter"
Dim paramValueTitle As String = "Billions"

object paramChartFormat = 1;
object paramCategoryLabels = 0;
object paramSeriesLabels = 0;
bool paramHasLegend = true;
object paramTitle = "Sales by Quarter";
object paramCategoryTitle = "Fiscal Quarter";
object paramValueTitle = "Billions";

5. Creating a Data Insertion Helper Method

The following code declares a helper method, making it easier to add data for the chart to the worksheet.

Visual Basic

Sub SetCellValue(ByRef targetSheet as WorkSheet, _
    ByRef Cell as String, ByRef Value as Object)
    targetSheet.Range(Cell).Value = Value
End Sub

static void SetCellValue(Worksheet targetSheet, string Cell,
    object Value)
{
    targetSheet.get_Range(
        Cell, Cell).set_Value(XlRangeValueDataType.xlRangeValueDefault, 
        Value);
}

6. Creating a New Workbook

This sample shows code to create a workbook that contains one worksheet named Quarterly Sales.

Visual Basic

newWorkbook = excelApplication.Workbooks.Add(XlWBATemplate.xlWBATWorksheet)
targetSheet = newWorkbook.Worksheets(1)
targetSheet.Name = "Quarterly Sales"

newWorkbook = excelApplication.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
targetSheet = (Worksheet)(newWorkbook.Worksheets[1]);
targetSheet.Name = "Quarterly Sales";

7. Inserting Chart Data into the Worksheet

Call the helper method created in Step 5. Creating a Data Insertion Helper Method, to insert data for the chart into the target worksheet. The inserted data used in this example are fictional quarterly sales numbers for four regions and are located in cells A1:E5:

Table 1. Example worksheet

	A	B	C	D	E
1		Q1	Q2	Q3	Q4
2	N. America	1.5	2	1.5	2.5
3	S. America	2	1.75	2	2
4	Europe	2.25	2	2.5	2
5	Asia	2.5	2.5	2	2.75

Visual Basic

SetCellValue(targetSheet, "A2", "N. America")
SetCellValue(targetSheet, "A3", "S. America")
SetCellValue(targetSheet, "A4", "Europe")
SetCellValue(targetSheet, "A5", "Asia")
SetCellValue(targetSheet, "B1", "Q1")
SetCellValue(targetSheet, "B2", 1.5)
SetCellValue(targetSheet, "B3", 2)
SetCellValue(targetSheet, "B4", 2.25)
SetCellValue(targetSheet, "B5", 2.5)
SetCellValue(targetSheet, "C1", "Q2")
SetCellValue(targetSheet, "C2", 2)
SetCellValue(targetSheet, "C3", 1.75)
SetCellValue(targetSheet, "C4", 2)
SetCellValue(targetSheet, "C5", 2.5)
SetCellValue(targetSheet, "D1", "Q3")
SetCellValue(targetSheet, "D2", 1.5)
SetCellValue(targetSheet, "D3", 2)
SetCellValue(targetSheet, "D4", 2.5)
SetCellValue(targetSheet, "D5", 2)
SetCellValue(targetSheet, "E1", "Q4")
SetCellValue(targetSheet, "E2", 2.5)
SetCellValue(targetSheet, "E3", 2)
SetCellValue(targetSheet, "E4", 2)
SetCellValue(targetSheet, "E5", 2.75)

SetCellValue(targetSheet, "A2", "N. America");
SetCellValue(targetSheet, "A3", "S. America");
SetCellValue(targetSheet, "A4", "Europe");
SetCellValue(targetSheet, "A5", "Asia");
SetCellValue(targetSheet, "B1", "Q1");
SetCellValue(targetSheet, "B2", 1.5);
SetCellValue(targetSheet, "B3", 2);
SetCellValue(targetSheet, "B4", 2.25);
SetCellValue(targetSheet, "B5", 2.5);
SetCellValue(targetSheet, "C1", "Q2");
SetCellValue(targetSheet, "C2", 2);
SetCellValue(targetSheet, "C3", 1.75);
SetCellValue(targetSheet, "C4", 2);
SetCellValue(targetSheet, "C5", 2.5);
SetCellValue(targetSheet, "D1", "Q3");
SetCellValue(targetSheet, "D2", 1.5);
SetCellValue(targetSheet, "D3", 2);
SetCellValue(targetSheet, "D4", 2.5);
SetCellValue(targetSheet, "D5", 2);
SetCellValue(targetSheet, "E1", "Q4");
SetCellValue(targetSheet, "E2", 2.5);
SetCellValue(targetSheet, "E3", 2);
SetCellValue(targetSheet, "E4", 2);
SetCellValue(targetSheet, "E5", 2.75);

8. Getting a Range Object for the Chart Data

This code gets a Range object for the worksheet cells holding the chart data. This Range object is used later in the call to the ChartWizard method.

Visual Basic

dataRange = targetSheet.Range("A1", "E5")

dataRange = targetSheet.get_Range("A1", "E5");

9. Adding a Chart to the ChartObjects Collection

In the ChartObjects collection of the target worksheet, add a Chart object to the collection, by placing the chart below the data inserted above.

Visual Basic

chartObjects = targetSheet.ChartObjects()
newChartObject = chartObjects.Add(0, 100, 300, 300)

chartObjects = (ChartObjects)(targetSheet.ChartObjects(paramMissing));
newChartObject = chartObjects.Add(0, 100, 300, 300);

Create the Chart

Use the ChartObject.Chart.ChartWizard method to create a 3-D column chart using the data inserted previously. The ChartWizard method accepts the following parameters:

Source. The range that contains the source data for the new chart. The code below uses the dataRange variable to specify the cells that contain the chart data.
Gallery. Choose the chart type to create. XlChartType.xl3DColumn specifies a 3-D column chart.
Format. The option number for the built-in auto formatting. This is a number ranging from 1 through 10, depending on the gallery type.
PlotBy. Specifies whether the data for each series is in rows or columns. The series data for the chart inserted in the previous example is row-based.
CategoryLabels. An integer that specifies the number of rows or columns within the source range that contain category labels. The code below uses the paramCategoryLabels variable, which was set in the previous example to a value of 0 to specify that the category labels are located in Row 0 of the source range.
SeriesLabels. An integer that specifies the number of rows or columns within the source range that contain series labels. The code below uses the paramSeriesLabels variable, which was previously set to a value of 0 to specify that the series labels are located in Column 0 of the source range.
HasLegend. Specifies whether to include a legend.
Title. Indicates the title text of the chart.
CategoryTitle. Shows the title text of the category axis.
ValueTitle. Shows the title text of the value axis.

Visual Basic

newChartObject.Chart.ChartWizard(dataRange, XlChartType.xl3DColumn, _
    paramChartFormat, XlRowCol.xlRows, paramCategoryLabels, _
    paramSeriesLabels, paramHasLegend, paramTitle, _
    paramCategoryTitle, paramValueTitle)

newChartObject.Chart.ChartWizard(dataRange, XlChartType.xl3DColumn,
    paramChartFormat, XlRowCol.xlRows, paramCategoryLabels,
    paramSeriesLabels, paramHasLegend, paramTitle,  paramCategoryTitle, 
    paramValueTitle, paramMissing);

Save the Workbook

Next, you should save the workbook and quit Excel. The following C# code passes the XlSaveAsAccessMode.xlNoChange value as the seventh parameter to the Workbook.SaveAs method. This is performed instead of using the paramMissing variable because the SaveAs method takes an XlSaveAsAccessMode enumerated type rather than an object type for this parameter. For the purposes of this article, the actual value from the XlSaveAsAccessMode enumeration that is passed to the SaveAs method is not important.

After saving the workbook, the code closes the Excel Workbook object and Application object, and releases references to the underlying Excel COM objects, allowing Excel to unload from memory. For more information about releasing COM objects when using managed code see Chapter 2: Basics of Office Interoperability, Part 2, an excerpt from the book, Microsoft .NET Development for Microsoft Office in Office Interoperability.

Visual Basic

newWorkbook.SaveAs(paramWorkbookPath)

' Release the references to the Excel objects.
newChartObject = Nothing
chartObjects = Nothing
dataRange = Nothing
targetSheet = Nothing

' Close and release the Workbook object.
If Not newWorkbook Is Nothing Then
    newWorkbook.Close(False)
    newWorkbook = Nothing
End If

' Quit Excel and release the ApplicationClass object.
If Not excelApplication Is Nothing Then
    excelApplication.Quit()
    excelApplication = Nothing
End If

GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()

newWorkbook.SaveAs(paramWorkbookPath, paramMissing, paramMissing,
    paramMissing, paramMissing, paramMissing,
    XlSaveAsAccessMode.xlNoChange, paramMissing, paramMissing, 
    paramMissing, paramMissing, paramMissing);

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

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

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

    GC.Collect();
    GC.WaitForPendingFinalizers();
    GC.Collect();
    GC.WaitForPendingFinalizers();

The sample code shown above creates a workbook that contains a single worksheet named Quarterly Sales that contains the following data and embedded chart.

Figure 3. Sales Chart

The code shown in the previous example illustrates using the ChartWizard method to create a chart, but you can use the method to modify the properties of an existing chart. The code to modify and existing chart object is as follows.

Visual Basic

' Open the source workbook.
Dim excelWorkBook As Workbook = _
    excelApplication.Workbooks.Open(paramWorkbookPath)

' Get the worksheet that contains the chart.
Dim targetSheet As Worksheet = _
    excelWorkBook.Worksheets("Quarterly Sales")

' Get the ChartObjects collection for the sheet.
Dim chartObjects As ChartObjects = targetSheet.ChartObjects()

' Get the chart to modify.
Dim existingChartObject As ChartObject = _
    chartObjects.Item("Sales Chart")

' Use the ChartWizard method to modify the chart's title and legend
' properties.
existingChartObject.Chart.ChartWizard(Type.Missing, Type.Missing, _
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, False, _
    "Quarterly Sales")

// Create an instance of the Excel ApplicationClass object.          
excelApplication = new ApplicationClass();

// Open the source workbook.
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.
Worksheet targetSheet = 
    (Worksheet)(excelWorkBook.Worksheets["Quarterly Sales"]);

// Get the ChartObjects collection for the sheet.
ChartObjects chartObjects = 
    (ChartObjects)(targetSheet.ChartObjects(paramMissing));

// Get the chart to modify.
ChartObject existingChartObject =
    (ChartObject)(chartObjects.Item("Sales Chart"));

// Use the ChartWizard method to modify the chart's title and legend
// properties.
existingChartObject.Chart.ChartWizard(paramMissing, paramMissing,
    paramMissing,paramMissing, paramMissing, paramMissing, false, 
    "Quarterly Sales", paramMissing, paramMissing, paramMissing);

Read It

The 2007 Microsoft Office system introduces multiple changes to the way it integrates charts across the 2007 Office programs. Specifically, Excel 2007 is now the charting engine for all Office applications and helps 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 charting functionality is now pervasive throughout the 2007 Office system, and is used in many more places than previously. Now, a broader set of Office business applications can use this functionality.

In addition to general changes to charting in Excel 2007, there is a specific addition that helps developers - ChartWizard method helps simplify the process of programmatically creating charts in Excel 2007.