How to add sparklines and charts to MS Excel programmatically

The charting engine for Excel took a leap forward with the release of Office 2007. However, the engine was slower than Excel 2003’s version and also introduced a few bugs. Luckily Excel 2010 fixed those bugs and is back to being fast. Sparklines, a word-sized chart has also been introduced in Excel 2010. Edward Tufte invented sparklines and in 2008 Microsoft filed a patent for the implementation of sparklines in Microsoft Excel 2010.

For us as Microsoft Office developers, Excel charts and now sparklines enables us to provide our users with even more ways to visualize their data. In this post, I’ll show you how you can programmatically add sparklines and charts to Excel. I’ll be using Add-in Express 2010 for Office and .net.

Start by creating a new COM Add-in project in Visual Studio.

In the first prompt of the New Microsoft Office COM Add-in project wizard, select your programming language and the Minimum Office version you would like to support. In this example it will be Visual C# and since sparklines are only available in Excel 2010, select Microsoft Office 2010 as the minimum supported Office version.

Select Microsoft Excel in the Supported Applications list.

Complete the wizard and switch to the AddinModule designer. Add an ADXRibbonTab control, and add three ribbon button controls to the ribbon tab. The design should look something like the following image:

Select the Insert Data button and add the following code to its OnClick event handler:

private void insertDataRibbonButton_OnClick(
	object sender, IRibbonControl control, bool pressed)
{
    Excel.Worksheet activeSheet = null;
    Excel.Range range = null;
    Excel.Range headerRange = null;
    Excel.Font headerFont = null;
 
    try
    {
        activeSheet = ExcelApp.ActiveSheet as Excel.Worksheet;
        string[] header = new string[] { "1960", "1970", "1980", "1990", "2000", "2010" };
        headerRange = activeSheet.get_Range("B1", "G1");
        headerRange.set_Value(null, header);
        headerFont = headerRange.Font;
        headerFont.Bold = true;
 
        DataSet dsData = LoadData();
        int rowCount = 2;
 
        foreach (DataRow row in dsData.Tables[0].Rows)
        {
            range = activeSheet.get_Range("A" + rowCount, "G" + rowCount);
            range.set_Value(null, row.ItemArray);
            Marshal.ReleaseComObject(range);
            range = null;
            rowCount++;
        }
    }
    finally
    {
        if (activeSheet != null)
            Marshal.ReleaseComObject(activeSheet);
        if (range != null)
            Marshal.ReleaseComObject(range);
        if (headerRange != null)
            Marshal.ReleaseComObject(headerRange);
        if (headerFont != null)
            Marshal.ReleaseComObject(headerFont);
    }
}

The LoadData method returns a dataset with the historic population data per state of the United States. The code for the method is listed below.

private DataSet LoadData()
{   
    DataSet dsStats = new DataSet();
    DataTable dtPopulation = new DataTable("Population");
    dtPopulation.Columns.Add("State");
    dtPopulation.Columns.Add("1960");
    dtPopulation.Columns.Add("1970");
    dtPopulation.Columns.Add("1980");
    dtPopulation.Columns.Add("1990");
    dtPopulation.Columns.Add("2000");
    dtPopulation.Columns.Add("2010");
 
    DataRow drCalifornia = dtPopulation.NewRow();
    drCalifornia["State"] = "California";
    drCalifornia["1960"] = "15717204";
    drCalifornia["1970"] = "19953134";
    drCalifornia["1980"] = "23667902";
    drCalifornia["1990"] = "29760021";
    drCalifornia["2000"] = "33871648";
    drCalifornia["2010"] = "37253956";
    dtPopulation.Rows.Add(drCalifornia);
 
    DataRow drTexas = dtPopulation.NewRow();
    drTexas["State"] = "Texas";
    drTexas["1960"] = "9579677";
    drTexas["1970"] = "11196730";
    drTexas["1980"] = "14229191";
    drTexas["1990"] = "16986510";
    drTexas["2000"] = "20851820";
    drTexas["2010"] = "25145561";
    dtPopulation.Rows.Add(drTexas);
 
    DataRow drNewYork = dtPopulation.NewRow();
    drNewYork["State"] = "New York";
    drNewYork["1960"] = "16782304";
    drNewYork["1970"] = "18236967";
    drNewYork["1980"] = "17558072";
    drNewYork["1990"] = "17990455";
    drNewYork["2000"] = "18976457";
    drNewYork["2010"] = "19378102";
    dtPopulation.Rows.Add(drNewYork);
 
    DataRow drFlorida = dtPopulation.NewRow();
    drFlorida["State"] = "Florida";
    drFlorida["1960"] = "4951560";
    drFlorida["1970"] = "6789443";
    drFlorida["1980"] = "9746324";
    drFlorida["1990"] = "12937926";
    drFlorida["2000"] = "15982378";
    drFlorida["2010"] = "18801310";
    dtPopulation.Rows.Add(drFlorida);
 
    DataRow drIllinois = dtPopulation.NewRow();
    drIllinois["State"] = "Illinois";
    drIllinois["1960"] = "10081158";
    drIllinois["1970"] = "11113976";
    drIllinois["1980"] = "11426518";
    drIllinois["1990"] = "11430602";
    drIllinois["2000"] = "12419293";
    drIllinois["2010"] = "12830632";
    dtPopulation.Rows.Add(drIllinois);
 
    dsStats.Tables.Add(dtPopulation);
    return dsStats;
}

Next, add the following code to the Insert Chart button’s OnClick event handler in order to create a chart based on the recently added data on the sheet:

private void insertChartRibbonButton_OnClick(
	object sender, IRibbonControl control, bool pressed)
{
    Excel.Worksheet activeSheet = null;
    Excel.Range dataSourceCells = null;
    Excel.ChartObjects chartObjects = null;
    Excel.ChartObject chartObject = null;
    Excel.Chart newChart = null;
 
    try
    {
        activeSheet = ExcelApp.ActiveSheet as Excel.Worksheet;
        dataSourceCells = activeSheet.Range["A1", "G6"];
        chartObjects = (Excel.ChartObjects)activeSheet.ChartObjects(Type.Missing);
        chartObject = (Excel.ChartObject)chartObjects.Add(100, 100, 350, 300);
        newChart = chartObject.Chart;
        newChart.SetSourceData(dataSourceCells);
        newChart.ChartType = Excel.XlChartType.xlLineMarkers;
    }
    finally
    {
        if (activeSheet != null)
            Marshal.ReleaseComObject(activeSheet);
        if (dataSourceCells != null)
            Marshal.ReleaseComObject(dataSourceCells);
        if (chartObjects != null)
            Marshal.ReleaseComObject(chartObjects);
        if (chartObject != null)
            Marshal.ReleaseComObject(chartObject);
        if (newChart != null)
            Marshal.ReleaseComObject(newChart);
    }
}

Build, register and run your project. Click the Insert Data button and then click the Insert Chart button. The result should look similar to the screenshot below.

Finally, we’ll add the necessary code to the Insert Sparklines button’s OnClick event handler. As you can see it is surprisingly easy.

private void insertSparkLinesRibbonButton_OnClick(
	object sender, IRibbonControl control, bool pressed)
{
    Excel.Worksheet activeSheet = null;
    Excel.Range insertRange = null;
    Excel.Range sparkLinesRange = null;
    Excel.SparklineGroup sparkLineGroup = null;
    Excel.SparklineGroups sparkLineGroups = null;
    Excel.SparkPoints sparkPoints = null;
    Excel.SparkColor sparkMarkers = null;
 
    try
    {
        activeSheet = ExcelApp.ActiveSheet as Excel.Worksheet;
        insertRange = activeSheet.Range["B:B"];
        insertRange.Insert(Excel.XlDirection.xlToLeft,
			Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
        sparkLinesRange = activeSheet.Range["B2", "B6"];
        sparkLineGroups = sparkLinesRange.SparklineGroups;
        sparkLineGroup = sparkLineGroups.Add(
			Excel.XlSparkType.xlSparkLine, "C2:H6");
 
        sparkPoints = sparkLineGroup.Points;
        sparkMarkers = sparkPoints.Markers;
        sparkMarkers.Visible = true;
    }
    finally
    {
        if (activeSheet != null)
            Marshal.ReleaseComObject(activeSheet);
        if (insertRange != null)
            Marshal.ReleaseComObject(insertRange);
        if (sparkLinesRange != null)
            Marshal.ReleaseComObject(sparkLinesRange);
        if (sparkLineGroup != null)
            Marshal.ReleaseComObject(sparkLineGroup);
        if (sparkLineGroups != null)
            Marshal.ReleaseComObject(sparkLineGroups);
        if (sparkPoints != null)
            Marshal.ReleaseComObject(sparkPoints);
        if (sparkMarkers != null)
            Marshal.ReleaseComObject(sparkMarkers);
    }
}

When you run the add-in and click the Insert Sparklines button the result should look similar to the following image.

And there you have it, enhancing your customer’s reports has never been so quick and easy. Thank you for reading. Until next time, keep coding!

Available downloads:

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