Document Solutions for Excel, .NET Edition | Document Solutions
Features / Sparkline
In This Topic
    Sparkline
    In This Topic

    Sparklines can be understood as small, lightweight charts that are drawn inside cells to quickly visualize data for improved analysis. These tiny charts fit inside a cell and use data from a range of cells which is specified at the time of creating it. Typically, they are placed next to the selected cell range in the spreadsheet in order to enhance readability of data. These are particularly useful for analytical dashboards, presentations, business reports etc.

    A sparkline displays the most recent value as the rightmost data point and compares it with earlier values on a scale, allowing you to view general changes in data over time.

    DsExcel.NET allows you to highlight specific information and see how it varies over time using line, column, columnstacked100, and cascade sparklines. You can use Add method of the SparklineGroups class to add line, column, or columnstacked100 sparklines using SparkType enumeration. However, cascade sparkline is added using CASCADESPARKLINE formula. For more information about cascade sparkline, see SpreadJS Sparklines.

    Using sparklines includes the following tasks:

    Add a group of new sparklines

    You can add a group of new sparklines for each row or column of data in your worksheet by first specifying the data range and then using the Add method of the ISparklineGroups interface.

    Refer to the following example code to add a group of new sparklines.

    C#
    Copy Code 
    //Create workbook and access its first worksheet
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
// Defining data in the range
worksheet.Range["A1:C4"].Value = new object[,]
    {
        {1, 2, 3},
        {4, 5, 6},
        {7, 8, 9},
        {10, 11, 12}
    };
// Add a group of new sparklines
worksheet.Range["D1:D4"].SparklineGroups.Add(SparkType.Line, "A1:C4");

    Clear sparkline

    You can remove a sparkline from your worksheet by first specifying the data range and then using the Clear method of the ISparklineGroups interface. 

    Refer to the following example code to clear sparkline.

    C#
    Copy Code 
    // Defining data in the range
worksheet.Range["A1:C4"].Value = new object[,]
    {
        {1, 2, 3},
        {4, 5, 6},
        {7, 8, 9},
        {10, 11, 12}
    };

worksheet.Range["D1:D4"].SparklineGroups.Add(SparkType.Line, "A1:C4");
// Defining data in the range
worksheet.Range["F1:H4"].Value = new object[,]
{
    {1, 2, 3},
    {4, 5, 6},
    {7, 8, 9},
    {10, 11, 12}
};
// Add a group of new sparklines
worksheet.Range["J1:J4"].SparklineGroups.Add(SparkType.Line, "F1:H4");

//Clear D2 and J1 cell's sparkline.
worksheet.Range["D2, J1"].SparklineGroups.Clear();

    Clear sparkline groups

    You can remove a group of sparklines (added for a row or column) from your worksheet by specifying the data range and then using the ClearGroups method of the ISparklineGroups interface. 

    Refer to the following example code to clear sparkline groups.

    C#
    Copy Code 
    // Defining data in the range
worksheet.Range["A1:C4"].Value = new object[,]
{
    {1, 2, 3},
    {4, 5, 6},
    {7, 8, 9},
    {10, 11, 12}
};
// Add a group of new sparklines
worksheet.Range["D1:D4"].SparklineGroups.Add(SparkType.Line, "A1:C4");
// Defining data in the range
worksheet.Range["F1:H4"].Value = new object[,]
 {
    {1, 2, 3},
    {4, 5, 6},
    {7, 8, 9},
    {10, 11, 12}
 };
// Add a group of new sparklines
worksheet.Range["J1:J4"].SparklineGroups.Add(SparkType.Line, "F1:H4");

//Clear sparkline groups.
worksheet.Range["D2, J1"].SparklineGroups.ClearGroups();

    Create a group of existing sparklines

    You can create a group of existing sparklines by specifying the data range and then using the Group method of the ISparklineGroups interface. 

    Refer to the following example code to create a group of existing sparklines.

    C#
    Copy Code 
    // Defining data in the range
worksheet.Range["A1:C4"].Value = new object[,]
    {
        {1, 2, 3},
        {4, 5, 6},
        {7, 8, 9},
        {10, 11, 12}
    };
// Add a group of new sparklines
worksheet.Range["D1:D4"].SparklineGroups.Add(SparkType.Line, "A1:C4");
// Defining data in the range
worksheet.Range["F1:H4"].Value = new object[,]
        {
            {1, 2, 3},
            {4, 5, 6},
            {7, 8, 9},
            {10, 11, 12}
        };
// Add a group of new sparklines
worksheet.Range["J1:J4"].SparklineGroups.Add(SparkType.Column, "F1:H4");

//Create a new group, according to Range["J2"]'s sparkline group setting.            
worksheet.Range["A1:J4"].SparklineGroups.Group(worksheet.Range["J2"]);

    Add group of new sparklines with Date Axis

    You can add a group of new sparklines with date axis by first specifying the data range and then using the DateRange property of the ISparklineGroup interface.

    Refer to the following example code to add group of new sparkline with date axis.

    C#
    Copy Code 
    // Defining data in the range
worksheet.Range["A1:C4"].Value = new object[,]
 {
    {1, 2, 3},
    {4, 5, 6},
    {7, 8, 9},
    {10, 11, 12}
 };
// Add a group of new sparklines
worksheet.Range["D1:D4"].SparklineGroups.Add(SparkType.Line, "A1:C4");
worksheet.Range["A7:C7"].Value = new object[] { new DateTime(2011, 12, 16), new DateTime(2011, 12, 17), new DateTime(2011, 12, 18) };

//Set horizontal axis's Date range.
worksheet.Range["D1"].SparklineGroups[0].DateRange = "A7:C7";

worksheet.Range["D1"].SparklineGroups[0].Axes.Horizontal.Axis.Visible = true;
worksheet.Range["D1"].SparklineGroups[0].Axes.Horizontal.Axis.Color.Color = Color.Green;
worksheet.Range["D1"].SparklineGroups[0].Axes.Vertical.MinScaleType = SparkScale.SparkScaleCustom;
worksheet.Range["D1"].SparklineGroups[0].Axes.Vertical.MaxScaleType = SparkScale.SparkScaleCustom;
worksheet.Range["D1"].SparklineGroups[0].Axes.Vertical.CustomMinScaleValue = -2;
worksheet.Range["D1"].SparklineGroups[0].Axes.Vertical.CustomMaxScaleValue = 8;

    Configure layout of sparkline

    You can configure the layout of the sparkline by using the properties of the ISparklineGroup interface.

    Refer to the following example code to configure the layout of the sparkline.

    C#
    Copy Code 
    // Defining data in the range
worksheet.Range["A1:C4"].Value = new object[,]
{
    {1, 2, 3},
    {4, 5, 6},
    {7, 8, 9},
    {10, 11, 12}
};
// Adding sparkline
worksheet.Range["D1:D4"].SparklineGroups.Add(SparkType.Line, "A1:C4");           
// Configuring the layout
var sparklinegroup = worksheet.Range["D1"].SparklineGroups[0];
sparklinegroup.LineWeight = 2.5;
sparklinegroup.Points.Markers.Color.Color = Color.Red;
sparklinegroup.Points.Markers.Visible = true;
sparklinegroup.SeriesColor.Color = Color.Purple;