.jpg "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.
.jpg "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
Start Microsoft Visual Studio 2010. On the File menu, click New, and then click Project. In the New Project dialog box, select the Visual C# Windows type in the Project types pane. In the Templates pane, select Console Application. Name the project and solution Excel2010Sparklines.
Figure 1. Creating the solution
.jpg "Creating the solution")
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
On the Visual Studio Project menu, click Add Reference. 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
.jpg "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
.jpg "Worksheet with Sparklines")
.jpg "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"));
|
.jpg "See It")
Watch the video
> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/4c620c14-dcc6-42c4-a12c-ef7452da61a9]
Length: 03:16
.jpg "Click to grab code")
Grab the Code
.jpg "Explore It")
|
.jpg "Office Visual How To")