Spread Windows Forms 17
Spread Windows Forms 17.0 Product Documentation / Developer's Guide / Sparklines / Add Sparklines using Formulas / Histogram Sparkline
In This Topic
    Histogram Sparkline
    In This Topic

    Histograms are used to represent the frequency distribution of a data set. Spread for Winforms allows you to create histogram sparklines by using the histogram sparkline function.

    histogram sparkline element label

    The above image shows a histogram sparkline and its elements:

    Types of Histogram Sparklines

    The Histogram sparklines can be displayed in a discrete or continuous manner as shown below:

    discrete histogram sparkline type continuous histogram sparkline type

    Discrete Histogram represents the data in a discrete manner (without continuous intervals) by taking existing values. For example: 20, 34, 38. The values are painted in ascending order.

    Continuous Histogram represents the data by taking a range of continuous values. The intervals for the first bin are left-closed and right-closed, whereas intervals for rest of the bins are left-open and right-closed.

    The type of histogram can be specified by using the continuous option in the HistogramSparkline formula.

    The histogram sparkline formula has the following syntax:

    =HISTOGRAMSPARKLINE(dataRange, [continuous, paintLabel, scale, barWidth, barColor, labelFontStyle, labelColor, edgeColor])

    The formula options are described below:

    Option Description

    dataRange

    Specifies the range of data sources. It supports a range.

    It supports calc array and calc reference.

    continuous

    Optional

    If set to true, the sparkline is a continuous histogram.

    If set to false, the sparkline is a discrete histogram.

    The default value is true.

    paintLabel

    Optional

    If set to true, the sparkline paints the data label.

    The default value is false.

    scale

    Optional

    Specifies the bin width. This value is useful when histogram type is continuous.

    The default value is false.

    barWidth

    Optional

    Specifies the bar width.

    Default value is 1.

    Bar width = auto calc width * barWidth

    Value range is 0 < value ≤ 1.

    barColor

    Optional

    Specifies the color of the bar.

    Default value is '5B9BD5'.

    Supports CSS color property.

    labelFontStyle

    Optional

    Specifies the font style of the data label font.

    The default value is 9pt Calibri.

    Supports CSS font property. It supports font-style, font-weight, font-size, font-family.

    labelColor

    Optional

    Specifies the font color of data label font.

    Default is black (#00000).

    Supports CSS color string.

    edgeColor

    Optional

    Specifies the color of the edge.

    Default value is silver (#C0C0C0).

    Supports CSS color property.

    Notes

    Usage Scenario

    Consider a scenario where an organization has conducted a survey of social media consumption by users on a weekly basis. The histogram sparkline can depict these statistics and analyze the trends about how much time users spent on different social media platforms.

    Histogram Sparkline Usage Scenario

    Histogram Sparkline Usage Scenario

    C#
    Copy Code
    // Get sheet
    var worksheet = fpSpread1.Sheets[0].AsWorksheet();
                
    // Set data for histogram sparkline
    worksheet.SetValue(1, 0, new object[,]
    {
        { "Name", "Facebook", "Instagram", "LinkedIn", "Twitter" }
    });
    
    worksheet.SetValue(3, 0, new object[,]
    {
            {"John Mathew", 71, 74, 97, 85},
            {"Jim Parker", 76, 58, 88, 84},
            {"Sophia Ran", 97, 80, 74, 80},
            {"Wendi Blake", 55, 97, 84, 60},
            {"Stephan Lai", 81, 83, 94, 90},
            {"Fay Van Damme", 71, 78, 72, 82},
            {"Brevin Dice", 84, 80, 83, 93},
            {"Regina Oleveria", 86, 76, 56, 92},
            {"Georgi Facello", 78, 61, 91, 68},
            {"Bezalel Simmel", 85, 58, 92, 68},
            {"Sachin Tsukuda", 76, 70, 55, 81},
            {"Patricia Breugel", 64, 77, 83, 85},
            {"Zito Baaz", 82, 97, 66, 90},
            {"Navin Argence", 59, 87, 83, 85},
            {"Siamak Salverda", 77, 85, 97, 65},
            {"Khaled Kohling", 87, 67, 77, 97},
            {"Bangqing Kleiser", 49, 97, 78, 75},
            {"Rimli Dusink", 25, 61, 91, 91},
            {"Nathan Ranta", 65, 60, 96, 92},
            {"Gila Lukaszewicz", 27, 91, 62, 93},
            {"Kaijung Rodham", 31, 81, 67, 87},
            {"Mohammed Pleszkun", 20, 98, 67, 82},
            {"Shuichi Piazza", 67, 56, 81, 82},
            {"Katsuo Leuchs", 83, 74, 51, 42},
            {"Masanao Ducloy", 67, 64, 58, 73},
            {"Mihalis Crabtree", 80, 91, 57, 84},
            {"Danny Lenart", 92, 38, 99, 86},
            {"Yongqiao Dalton", 85, 61, 73, 81},
            {"Gaetan Veldwijk", 64, 52, 76, 72},
            {"Leszek Pulkowski", 95, 75, 64, 99},
            {"Weidon Gente", 77, 88, 77, 96},
            {"Krister Stranks", 72, 38, 89, 55},
            {"Ziyad Baaz", 89, 81, 83, 48},
            {"Ymte Perelgut", 67, 70, 97, 94},
            {"Tonia Butner", 99, 71, 87, 76},
            {"Shigeaki Narlikar", 78, 80, 80, 97},
            {"Ayakannu Beerel", 86, 61, 85, 81},
            {"Moni Bale", 60, 72, 71, 86},
            {"Manohar Heemskerk", 97, 60, 75, 78},
            {"Angus Swan", 33, 97, 99, 99},
            {"Christ Murtagh", 91, 76, 73, 80},
            {"Maren Baez", 45, 77, 86, 88},
            {"Greger Jahnichen", 98, 89, 81, 63},
            {"Ymte Duclos", 95, 83, 72, 72},
            {"Chenyi Hainaut", 81, 97, 99, 87},
            {"Kasidit Picel", 87, 80, 88, 80},
            {"Elrique Walstra", 96, 54, 38, 54},
            {"Adel Reghbati", 56, 88, 81, 88}
     });
    
    // Set HistogramSparkline formulas
    worksheet.Cells[2, 1].Formula = "HISTOGRAMSPARKLINE(Sheet1!B4: B51, true, true, 20, 1,\"#DC4463\", \"bold normal 10pt Calibri\", \"black\")";
    worksheet.Cells[2, 2].Formula = "HISTOGRAMSPARKLINE(Sheet1!C4: C51, true, true, 20, 1, \"#FFAE49\", \"bold normal 10pt Calibri\", \"black\")";
    worksheet.Cells[2, 3].Formula = "HISTOGRAMSPARKLINE(Sheet1!D4: D51, true, true, 20, 1, \"#5AB4BD\", \"bold normal 10pt Calibri\", \"black\")";
    worksheet.Cells[2, 4].Formula = "HISTOGRAMSPARKLINE(Sheet1!E4: E51, true, true, 20, 1, \"#C2EDB2\", \"bold normal 10pt Calibri\", \"black\")";
    
    Visual Basic
    Copy Code
    'Get sheet
    Dim worksheet = FpSpread1.Sheets(0).AsWorksheet()        
            
    'Set data for histogram sparkline
    worksheet.SetValue(1, 0, New Object(,) {
        {"Name", "Facebook", "Instagram", "LinkedIn", "Twitter"}
    })
    
    worksheet.SetValue(3, 0, New Object(,) {
        {"John Mathew", 71, 74, 97, 85},
        {"Jim Parker", 76, 58, 88, 84},
        {"Sophia Ran", 97, 80, 74, 80},
        {"Wendi Blake", 55, 97, 84, 60},
        {"Stephan Lai", 81, 83, 94, 90},
        {"Fay Van Damme", 71, 78, 72, 82},
        {"Brevin Dice", 84, 80, 83, 93},
        {"Regina Oleveria", 86, 76, 56, 92},
        {"Georgi Facello", 78, 61, 91, 68},
        {"Bezalel Simmel", 85, 58, 92, 68},
        {"Sachin Tsukuda", 76, 70, 55, 81},
        {"Patricia Breugel", 64, 77, 83, 85},
        {"Zito Baaz", 82, 97, 66, 90},
        {"Navin Argence", 59, 87, 83, 85},
        {"Siamak Salverda", 77, 85, 97, 65},
        {"Khaled Kohling", 87, 67, 77, 97},
        {"Bangqing Kleiser", 49, 97, 78, 75},
        {"Rimli Dusink", 25, 61, 91, 91},
        {"Nathan Ranta", 65, 60, 96, 92},
        {"Gila Lukaszewicz", 27, 91, 62, 93},
        {"Kaijung Rodham", 31, 81, 67, 87},
        {"Mohammed Pleszkun", 20, 98, 67, 82},
        {"Shuichi Piazza", 67, 56, 81, 82},
        {"Katsuo Leuchs", 83, 74, 51, 42},
        {"Masanao Ducloy", 67, 64, 58, 73},
        {"Mihalis Crabtree", 80, 91, 57, 84},
        {"Danny Lenart", 92, 38, 99, 86},
        {"Yongqiao Dalton", 85, 61, 73, 81},
        {"Gaetan Veldwijk", 64, 52, 76, 72},
        {"Leszek Pulkowski", 95, 75, 64, 99},
        {"Weidon Gente", 77, 88, 77, 96},
        {"Krister Stranks", 72, 38, 89, 55},
        {"Ziyad Baaz", 89, 81, 83, 48},
        {"Ymte Perelgut", 67, 70, 97, 94},
        {"Tonia Butner", 99, 71, 87, 76},
        {"Shigeaki Narlikar", 78, 80, 80, 97},
        {"Ayakannu Beerel", 86, 61, 85, 81},
        {"Moni Bale", 60, 72, 71, 86},
        {"Manohar Heemskerk", 97, 60, 75, 78},
        {"Angus Swan", 33, 97, 99, 99},
        {"Christ Murtagh", 91, 76, 73, 80},
        {"Maren Baez", 45, 77, 86, 88},
        {"Greger Jahnichen", 98, 89, 81, 63},
        {"Ymte Duclos", 95, 83, 72, 72},
        {"Chenyi Hainaut", 81, 97, 99, 87},
        {"Kasidit Picel", 87, 80, 88, 80},
        {"Elrique Walstra", 96, 54, 38, 54},
        {"Adel Reghbati", 56, 88, 81, 88}
    })
    
    'Set HistogramSparkline formulas
    worksheet.Cells(2, 1).Formula = "HISTOGRAMSPARKLINE(Sheet1!B4: B51, true, true, 20, 1,""#DC4463"", ""bold normal 10pt Calibri"", ""black"")"
    worksheet.Cells(2, 2).Formula = "HISTOGRAMSPARKLINE(Sheet1!C4: C51, true, true, 20, 1, ""#FFAE49"", ""bold normal 10pt Calibri"", ""black"")"
    worksheet.Cells(2, 3).Formula = "HISTOGRAMSPARKLINE(Sheet1!D4: D51, true, true, 20, 1, ""#5AB4BD"", ""bold normal 10pt Calibri"", ""black"")"
    worksheet.Cells(2, 4).Formula = "HISTOGRAMSPARKLINE(Sheet1!E4: E51, true, true, 20, 1, ""#C2EDB2"", ""bold normal 10pt Calibri"", ""black"")"
    

    Using the Spread Designer

    1. Type data in a cell or a column or row of cells in the designer.
    2. Select a cell for the sparkline.
    3. Select the Insert menu.
    4. Select a sparkline type.
    5. Set the Data Range in the Create Sparklines dialog (such as =Sheet1!$E$1:$E$3).
      Alternatively, set the range by selecting the cells in the range using the pointer.

      You can also set additional sparkline settings in the dialog if available.

    6. Select OK.
    7. Select Apply and Exit from the File menu to save your changes and close the designer.