Pieter van der Westhuizen

Working with Excel charts: how to change a chart style, color or type programmatically

When it comes to visualizing data nothing beats charts and there are only few applications that tie data and charts so well together as Microsoft Excel.

Adding charts in your Excel add-ins using the Excel object model is easier than you might think. In this article we’ll take a look at how to insert charts programmatically, format their style and colors as well as how to change the chart’s display by filtering its data.

Creating an Excel COM Add-in project

Let’s start by creating a new ADX COM Add-in project using Add-in Express for Office and .net. You will find the project template under Other Project Types > Extensibility.

Creating a new Excel COM Add-in project in Visual Studio

When prompted select your programming language (C#, VB.NET or C++.NET) and the Minimum Office version you would like to support. We’ll choose Office 2013 for this example, as there has been a few changes to the object model I’d like to show you.

Select your programming language (C#, VB.NET or C++.NET) and the minimum Office version you would like to support

Lastly, select Microsoft Excel from the list of supported applications and finish the New Microsoft Office COM Add-in wizard.

Select Microsoft Excel from the list of supported application

Adding a chart to the active worksheet

Firstly, we need to have some data in order to create a chart. I’ve added some sales data from our old friend Northwind Traders to the sheet, which will act as the data source for our chart.

Source data for creating a chart

Next, add a custom ribbon tab with a ribbon button, which we’ll use as the trigger to create a chart, using the currently selected range in Excel as the data source.

Creating a custom ribbon tab with a button, clicking on which will create a chart

Add the following to the Ribbon buttons’ OnClick event:

private void insertChartRibbonButton_OnClick(object sender,
    IRibbonControl control, bool pressed)
{
    Excel.Worksheet activeSheet = null;
    Excel.Range selectedRange = null;
    Excel.Shapes shapes = null;
    Excel.Chart chart = null;
    Excel.ChartTitle chartTitle = null;
 
    try
    {
        activeSheet = (Excel.Worksheet)ExcelApp.ActiveSheet;
        selectedRange = (Excel.Range)ExcelApp.Selection;
        shapes = activeSheet.Shapes;
        shapes.AddChart2(Style: 201, XlChartType: Excel.XlChartType.xlColumnClustered,
            Left: Type.Missing, Top: Type.Missing, Width: Type.Missing,
            Height: Type.Missing, NewLayout: true).Select();
 
        chart = ExcelApp.ActiveChart;
        chart.SetSourceData(selectedRange);
        chartTitle = chart.ChartTitle;
        chartTitle.Text = "Product Sales by Month";
    }
    finally
    {
        if (chartTitle != null) Marshal.ReleaseComObject(chartTitle);
        if (chart != null) Marshal.ReleaseComObject(chart);
        if (shapes != null) Marshal.ReleaseComObject(shapes);
        if (selectedRange != null) Marshal.ReleaseComObject(selectedRange);
    }
}

The code above uses the AddChart2 method of the Shapes collection. This method is new to Excel 2013 and makes adding charts to Excel a little bit easier. Take note that you can still create charts using the Add method of the ChartObjects collection.

Clicking the button will add a chart to the active worksheet:

Clicking the button adds a chart to the active worksheet

Changing Excel charts’ style, color and type

Next, let’s add some functionality to change the style, color and type of the selected chart. I’ve added three dropdowns and buttons to the Ribbon tab we’ve added earlier:

Three dropdowns and buttons added to the custom Excel ribbon tab

Changing the chart style

First, add the following to the “Change Style” buttons’ OnClick event:

private void changeStyleRibbonButton_OnClick(object sender, 
    IRibbonControl control, bool pressed)
{
    Excel.Chart chart = null;
    ADXRibbonItem selectedItem = null;
 
    try
    {
        chart = ExcelApp.ActiveChart;
        selectedItem = (ADXRibbonItem)styleNumberRibbonDropDown1.Items[
            styleNumberRibbonDropDown1.SelectedItemIndex];
        chart.ChartStyle = selectedItem.Caption;
    }
    finally
    {
        if (chart != null) Marshal.ReleaseComObject(chart);
    }
}

The Style Number dropdown lists a number of available styles. It can be an integer between 1 and 48. The values correspond to the options you’ll see in the Chart Styles group on the Excel Design ribbon tab.

Changing the chart color

To change the chart’s color is as simple as setting its ChartColor property. Add the following to the “Change Color” ribbon button:

private void changeChartColorRibbonButton_OnClick(object sender, 
    IRibbonControl control, bool pressed)
{
    Excel.Chart chart = null;
    ADXRibbonItem selectedItem = null;
 
    try
    {
        chart = ExcelApp.ActiveChart;
        selectedItem = (ADXRibbonItem)colorNumberRibbonDropDown.Items[
            colorNumberRibbonDropDown.SelectedItemIndex];
        chart.ChartColor = selectedItem.Caption;
    }
    finally
    {
        if (chart != null) Marshal.ReleaseComObject(chart);
    }
}

Changing the chart type

To change the type of chart, we need to set the Chart objects’ ChartType property to one of the values available in the XlChartType enumeration. In the following code snippet we cast the selected item in the “Chart Type” dropdown tag property to XLChartType and set the chart’s ChartType property to the selected value.

private void changeChartTypeRibbonButton_OnClick(object sender, 
    IRibbonControl control, bool pressed)
{
    Excel.Chart chart = null;
    ADXRibbonItem selectedItem = null;
 
    try
    {
        chart = ExcelApp.ActiveChart;
        selectedItem = (ADXRibbonItem)chartTypeRibbonDropDown.Items[
            chartTypeRibbonDropDown.SelectedItemIndex];
        chart.ChartType = (Excel.XlChartType)selectedItem.Tag;
    }
    finally
    {
        if (chart != null) Marshal.ReleaseComObject(chart);
    }
}

Filtering the chart’s data

When selecting a chart in Excel you are able to filter the chart’s data by either the series or categories.

When selecting a chart in Excel you are able to filter the chart's data by either the series or categories

You are, however, also able to do the same using the Excel object model. I’ve added a Ribbon split button with a few product names.

A split button with a few product names is added to the custom ribbon tab

When the user clicks on one of the product names, the FilterChart method is called, that filters the charts’ data:

private void FilterChart(string productName)
{
    Excel.Chart chart = null;
    Excel.ChartGroups chartGroups = null;
    Excel.ChartGroup chartGroup = null;
    Excel.ChartCategory chartCategory = null;
    Excel.CategoryCollection categoryCollection = null;
 
    try
    {
        chart = ExcelApp.ActiveChart;
        chartGroups = chart.ChartGroups() as Excel.ChartGroups;
        chartGroup = chartGroups.Item(1);
        categoryCollection = chartGroup.FullCategoryCollection() as
            Excel.CategoryCollection;
        for (int i = 1; i <= categoryCollection.Count; i++)
        {
            chartCategory = categoryCollection.Item(i);
            if (chartCategory.Name != productName)
            {
                chartCategory.IsFiltered = true;                        
            }
            if (chartCategory != null) Marshal.ReleaseComObject(chartCategory);
        }
    }
    finally
    {
        if (categoryCollection != null) Marshal.ReleaseComObject(categoryCollection);
        if (chartGroup != null) Marshal.ReleaseComObject(chartGroup);
        if (chartGroups != null) Marshal.ReleaseComObject(chartGroups);
        if (chart != null) Marshal.ReleaseComObject(chart);
    }
}

The result of the code above will change the chart to only show information for the selected product:

A chart in Excel 2013 that shows information for the selected product only

Thank you for reading. Until next time, keep coding!

Available downloads:

This sample Excel add-in was developed using Add-in Express for Office and .net:

Excel Charts add-in (C#)

Excel add-in development in Visual Studio for beginners:

2 Comments

  • http://1.gravatar.com/avatar/59ba2129a10941a75f0f894104dba30c?s=32&d=http%3A%2F%2F1.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Gaurav says:

    Hi,

    I am able to create pivot table and charts but using Excel.introp but in that I am unable to use AutoShow option. Can any one provide me help with example(in vb.net)

  • http://0.gravatar.com/avatar/ab4ec2858cfdf1e44dadf8c50fae314d?s=32&d=http%3A%2F%2F0.gravatar.com%2Favatar%2Fad516503a11cd5ca435acc9bb6523536%3Fs%3D32&r=G Dmitry Kostochko (Add-in Express Team) says:

    Hi Gaurav,

    Sorry but I do not quite understand the root of the problem. Do you mean the AutoShow method of the PivotField object?

    Doesn’t this method work? Do you get any exception? Please clarify.

Post a comment

Have any questions? Ask us right now!