Click or drag to resize

Adding Sparklines to Excel Spreadsheets with ExpertXls

Sparklines are small charts drawn in cells, one for each row or column of a data range: lines, columns or win/loss blocks. The SparklineGroups property of a worksheet returns an ExcelSparklineGroupsCollection; its Add(ExcelSparklineType, ExcelRange, ExcelRange) method adds an ExcelSparklineGroup: the sparklines of a group share the type, the colors and the highlighted points.

The location range is a single row or a single column of cells. When it is a column, each row of the data range is drawn in the cell of the same row; when it is a row, each column of the data range is drawn in the cell of the same column. Sparklines are available only in the Excel 2007 or later (.xlsx) format and are displayed by Excel 2010 and later; the Excel 97-2003 format cannot store them.

Adding Sparklines
// a line in each cell of I7:I18, drawn from the four quarters C:F of the same row
ExcelSparklineGroup trend = worksheet.SparklineGroups.Add(ExcelSparklineType.Line, worksheet["C7:F18"], worksheet["I7:I18"]);
trend.ShowMarkers = true;
trend.ShowHighPoint = true;
trend.HighPointColor = Color.FromArgb(0, 176, 80);
trend.LineWeight = 1.25;

// a column chart under each quarter, drawn from the 12 rows of the column
worksheet.SparklineGroups.Add(ExcelSparklineType.Column, worksheet["C7:F18"], worksheet["C19:F19"]);

// a win/loss chart in a single cell, from a column of positive and negative values
ExcelSparklineGroup winLoss = worksheet.SparklineGroups.Add(ExcelSparklineType.WinLoss, worksheet["K7:K18"], worksheet["K19"]);
winLoss.ShowAxis = true;

Setting the color of a point (for example HighPointColor) also shows that point. The markers can be set only for line sparklines.

See Also