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
• Adding a chart to the active worksheet
• Changing the chart style
• Changing the chart color
• Changing the chart type
• Filtering the chart’s 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.

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.

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

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.

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.

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:

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:

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.

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.

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:

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:

• Part 1: Application and base objects
• Part 2: Customizing the Excel User Interface: What is and isn't customizable
• Part 3: Customizing Excel ribbons and toolbars
• Part 4: Customizing Excel main menu, context menus, and Backstage view
• Part 5: Creating custom task panes for Excel 2013 – 2003
• Part 6: Working with Excel workbooks and worksheets: VB.NET examples
• Part 7: Working with Excel cell values, formulas and formatting: C# samples
• Part 9: Working with Excel tables and ranges
• Part 10: Importing data from SQL databases and other sources to Excel
• Part 11: Working with Excel pivot tables: VB.NET code examples