Export (0) Print
Expand All

Programming with Sparklines in Excel 2010

Office 2010

Office Visual How To

Summary:  Learn to programmatically add Sparklines to a Microsoft Excel 2010 worksheet.

Last modified: September 12, 2012

Applies to: Excel 2010 | Office 2010 | VBA

Published:   March 2010

Provided by:   Michael Case, iSoftStone

Overview

Microsoft Excel 2010 introduces Sparklines, a new feature. By using Sparklines, you can create small charts in a single cell to quickly discover patterns in your data. It is a quick way to highlight important data trends such as seasonal increases or decreases. In this article, learn how to use the Excel 2010 primary interop assembly to create an Excel workbook programmatically, add trending data for book sales, and add a Sparkline to each row to show the sales trend.

This Office Visual How To describes how to programmatically add Sparklines to an Excel workbook:

  • Create a Windows console application solution in Microsoft Visual Studio 2010.

  • Add references to the Excel 2010 primary interop assembly.

  • Add the sample code to the solution.

Code It

Creating a Windows Console Application in Visual Studio 2010

The example in this article uses a Windows console application as the framework for the sample code. The console application type was selected only for its simplicity. Other application types could use the same approach that is described here.

To create a Windows console application in Visual Studio 2010

  1. Start Microsoft Visual Studio 2010.

  2. On the File menu, click New, and then click Project.

  3. In the New Project dialog box, select the Visual C# Windows type in the Project types pane.

  4. In the Templates pane, select Console Application.

  5. Name the project and solution Excel2010Sparklines.

    Figure 1. Creating the solution

    Creating the solution
  6. To create the solution, click OK.

Adding References to the Excel 2010 primary interop assembly

The example in this article uses the Excel 2010 primary interop assembly to create and manipulate an Excel workbook. The Excel 2010 primary interop assembly exposes the Excel 2010 object model to managed code. The primary interop assembly is installed with Visual Studio 2010.

To add references to the Excel 2010 primary interop assembly

  1. On the Visual Studio Project menu, click Add Reference.

  2. In the Add Reference dialog box, select version 14.0.0.0 of the Microsoft.Office.Interop.Excel component, and then click OK to add the reference.

    Figure 2. Adding the reference

    Adding the reference

Adding the Sample Code to the Solution

Replace the contents of the Program.cs source file with the following code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Office.Interop.Excel;

namespace Excel2010Sparklines
{
 class Program
 {
  static void Main(string[] args)
  {
    //Declare variables that hold references to Excel objects.
    Application eApplication = null;
    Workbook eWorkbook = null;
    Worksheet sheet = null;

    Range sparklineLocation;
    SparklineGroup sparkline;

    //Declare helper variables.
    string workBookName = @"C:\temp\Excel2010Sparklines.xlsx";
    string workSheetName = @"Book Sales";

    try
    {
      //Create an instance of Excel.
      eApplication = new Application();

      //Create a workbook and add a worksheet.
      eWorkbook = eApplication.Workbooks.Add(
          XlWBATemplate.xlWBATWorksheet);
      sheet = (Worksheet)(eWorkbook.Worksheets[1]);
      sheet.Name = workSheetName;

      //Add trending data to the worksheet.
      //The custom SetRow helper function located at the end of this 
      //source file is used to add a row of values to the worksheet.
      SetRow(sheet, 1, "Book Category", 
        "Sales 2008", "Sales 2007", "Sales 2006", "Sales 2005");
      SetRow(sheet, 2, "Fiction", "702", "312", "1170", "1123.2");
      SetRow(sheet, 3, "Nonfiction", "789", "741", "592", "62");
      SetRow(sheet, 4, "Technical", "2607", "2261", "1104", "1776");
      SetRow(sheet, 5, "Business", "990", "1045", "935", "693");
      SetRow(sheet, 6, "Childrens", "490", "420", "352", "368");
      sheet.Columns.AutoFit();

      //Add a Sparkline to the cell at the end of the first row.
      sparklineLocation = sheet.get_Range("F2");
      sparkline = sparklineLocation.SparklineGroups.Add(
        XlSparkType.xlSparkLine, "B2:E2");

      //Format the Sparkline by defining a color theme.
      sparkline.SeriesColor.ThemeColor = 5;

      //Display a mark for the high and low data point.
      sparkline.Points.Highpoint.Visible = true;
      sparkline.Points.Lowpoint.Visible = true;

      //Copy the Sparkline to the end of the remaining rows.
      sparklineLocation.Copy(sheet.get_Range("F3:F6"));

      //Save the workbook.
      sheet.get_Range("A1").Activate();

      eWorkbook.SaveAs(workBookName,
        AccessMode: XlSaveAsAccessMode.xlNoChange);
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.Message);
    }
    finally
    {
      //Release the references to the Excel objects.
      sparklineLocation = null;
      sparkline = null;
      sheet = null;

      //Release the Workbook object.
      if (eWorkbook != null)
        eWorkbook = null;

      //Release the ApplicationClass object.
      if (eApplication != null)
      {
        eApplication.Quit();
        eApplication = null;
      }

      GC.Collect();
      GC.WaitForPendingFinalizers();
      GC.Collect();
      GC.WaitForPendingFinalizers();
    }
  }
  /// <summary>
  /// Helper method to set values for a row of cells.
  /// </summary>
  static void SetRow(Worksheet sheet, int row, params string[] values)
  {
    for (int x = 0; x < values.Length; x++)
    {
      sheet.Cells[row, x + 1] = values[x];
    }
  }
 }
}

The sample code shown here creates a workbook named Excel2010Sparklines.xlsx in the C:\Temp folder. The code then adds trending data and a Sparkline to each row to show the sales trend.

Figure 3. Worksheet with Sparklines

Worksheet with Sparklines

Read It

The sample code used with this Visual How To shows how to use the Excel 2010 primary interop assembly to create a worksheet, add sales data, and add Sparklines to show trends in the sales data.

The following code example shows how to use the Excel 2010 primary interop assembly to add a Sparkline to a cell by using the Range.SparklineGroups.Add method. The first parameter uses the XlSparkType.xlSparkLine value to create a Line Sparkline; other kinds of Sparklines include Column and Win/Loss. The second parameter defines the range of cells that contain the data for the Sparkline.

The Sparkline is then formatted. The color is set by using the SparklineGroup.SeriesColor.ThemeColor property. The SparklineGroup.Points.Highpoint.Visible property and the SparklineGroup.Points.Lowpoint.Visible property are set to true to highlight the High data points and Low data points in the Sparkline. Other data points that could be highlighted include the First, Last, and Negative data points. It is also possible to display a marker for all data points on a Line Sparkline.

After the initial Sparkline is created and formatted, use the Range.Copy method to copy the Sparkline to the end of the remaining rows.

//Add a Sparkline to the cell at the end of the first row.
sparklineLocation = sheet.get_Range("F2");
sparkline = sparklineLocation.SparklineGroups.Add(
  XlSparkType.xlSparkLine, "B2:E2");

//Format the Sparkline by defining a color theme.
sparkline.SeriesColor.ThemeColor = 5;

//Display a mark for the high and low data point.
sparkline.Points.Highpoint.Visible = true;
sparkline.Points.Lowpoint.Visible = true;

//Copy the Sparkline to the end of the remaining rows.
sparklineLocation.Copy(sheet.get_Range("F3:F6"));

See It

Watch the video

Watch video

Length: 03:16

Click to grab code

Grab the Code

Explore It
Show:
© 2014 Microsoft