Use the Excel Export API to Create Sparklines

• Sep 19, 2023
• 2 minutes to read

Worksheet sparklines are organized in groups. Each group contains one or more sparklines of the same type that share identical format settings and axis scaling options. To create a new group of sparklines, use the XlSparklineGroup constructor which requires the following parameters:

• Data Range. A XlCellRange object that specifies the data source for the sparkline group. Note that a data range for a single sparkline should be a continuous one-dimensional cell range (all cells of which are located in a single row or column). The number of the ranges must be equal to the number of cells in the location range. If the specified cell range is insufficient for a sparkline group, an exception is thrown.
• Location Range. A XlCellRange object that specifies a range of cells where the sparkline group should be located. The dimension of the location range must be the same as the corresponding dimension of the data range, otherwise an exception is thrown.

You do not have to create individual sparklines in a group, they are created automatically with this constructor. All created sparklines have the default XlSparklineType.Line type.

To add a sparkline group to the worksheet, use the Add method of the collection available with the XlSparklineGroup.Sparklines property, as illustrated in the code snippet below:

View Example

• SparklineActions.cs
• SparklineActions.vb

// Create a group of line sparklines.
XlSparklineGroup group = new XlSparklineGroup(XlCellRange.FromLTRB(1, 1, 4, 6), XlCellRange.FromLTRB(5, 1, 5, 6));
// Set the sparkline weight.
group.LineWeight = 1.25;
// Display data markers on the sparklines.
group.DisplayMarkers = true;
sheet.SparklineGroups.Add(group);

' Create a group of line sparklines.
Dim group As New XlSparklineGroup(XlCellRange.FromLTRB(1, 1, 4, 6), XlCellRange.FromLTRB(5, 1, 5, 6))
' Set the sparkline weight.
group.LineWeight = 1.25
' Display data markers on the sparklines.
group.DisplayMarkers = True
sheet.SparklineGroups.Add(group)

Note

If you create a sparkline group using the default parameterless constructor, the group does not contain sparklines. You should manually create a XlSparkline object and add it to the collection accessible with the XlSparklineGroup.Sparklines property.