Stacked Sparkline | Spread Windows Forms 18

Stacked Sparkline

In This Topic

A stacked sparkline is used to show a sliced breakdown of a value in different categories. It is useful for comparing values across categories as shown in the image below.

The stacked sparkline formula has the following syntax:

=STACKEDSPARKLINE(points, [colorRange, labelRange, maximum, targetRed, targetGreen, targetBlue, tragetYellow, color, highlightPosition, vertical, textOrientation, textSize, minimum])

The formula options are described below:

Option

Description

points

A reference that represents a range of cells that contains the values, such as "A1:A4".

colorRange

Optional

A reference that represents a range of cells that contains all colors, such as "B1:B4".

The default value is generated by color.

labelRange

Optional

A reference that represents a cell range that contains all labels, such as "C1:C4".

The default value is empty.

maximum

Optional

A number that represents the maximum value of the sparkline.

The default value is the summary of all positive values.

targetRed

Optional

A number that represents the location of the red line. This setting is optional.

The default value is empty.

targetGreen

Optional

A number that represents the location of the green line.

The default value is empty.

targetBlue

Optional

A number that represents the location of the blue line.

The default value is empty.

targetYellow

Optional

A number that represents the location of the yellow line.

The default value is empty.

color

Optional

A string that represents the color if colorRange is omitted.

The default value is "#646464".

highlightPosition

Optional

A number that represents the index of the highlight area.

The default value is empty.

vertical

Optional

A boolean that represents whether to display the sparkline vertically.

The default value is false.

textOrientation

Optional

A number that represents the label text orientation.

The default value is 0 (horizontal). The vertical setting is 1.

textSize

Optional

A number that represents the size of the label text in pixels.

The default value is 10.

minimum

Optional

A number that represents the minimum axis value.

The default value is 0.

Usage Scenario

Consider a scenario where a company wants to display the sales of products in different US states. Stacked sparklines can show each US state divided into the shares of the company products.

C#	Copy Code
// Get sheet var worksheet = fpSpread1_Sheet1.AsWorksheet(); // Add data for sparkline worksheet.SetValue(1, 0, "State"); worksheet.SetValue(1, 1, "Gloves"); worksheet.SetValue(1, 2, "Sanitizer"); worksheet.SetValue(1, 3, "Masks"); worksheet.SetValue(1, 4, "Diagram"); worksheet.SetValue(2, 0, "Idaho"); worksheet.SetValue(2, 1, 10000); worksheet.SetValue(2, 2, 12000); worksheet.SetValue(2, 3, 15000); worksheet.SetValue(3, 0, "Montana"); worksheet.SetValue(3, 1, 11000); worksheet.SetValue(3, 2, 10000); worksheet.SetValue(3, 3, 15000); worksheet.SetValue(4, 0, "Oregon"); worksheet.SetValue(4, 1, 10000); worksheet.SetValue(4, 2, 17000); worksheet.SetValue(4, 3, 12000); worksheet.SetValue(5, 0, "Washington"); worksheet.SetValue(5, 1, 15000); worksheet.SetValue(5, 2, 10000); worksheet.SetValue(5, 3, 15000); worksheet.SetValue(6, 0, "Utah"); worksheet.SetValue(6, 1, 10000); worksheet.SetValue(6, 2, 15000); worksheet.SetValue(6, 3, 12000); worksheet.SetValue(7, 1, "#B39A9A"); worksheet.SetValue(7, 2, "#E3C3C3"); worksheet.SetValue(7, 3, "#F5E4E4"); // Set number format for columns worksheet.Columns["B:D"].NumberFormat = "$#,##0"; // Set StackedSparkline formula worksheet.Cells[2, 4].Formula = "STACKEDSPARKLINE(B3:D3,B8:D8,B2:D2,40000)"; worksheet.Cells[3, 4].Formula = "STACKEDSPARKLINE(B4:D4,B8:D8,B2:D2,40000)"; worksheet.Cells[4, 4].Formula = "STACKEDSPARKLINE(B5:D5,B8:D8,B2:D2,40000)"; worksheet.Cells[5, 4].Formula = "STACKEDSPARKLINE(B6:D6,B8:D8,B2:D2,40000)"; worksheet.Cells[6, 4].Formula = "STACKEDSPARKLINE(B7:D7,B8:D8,B2:D2,40000)"; // Set backcolor for cells worksheet.Cells["A1"].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFDEACA7)); worksheet.Cells["A2:E2"].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFF5E4E4)); worksheet.Cells["A3:E7"].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFfdfafa));

Copy Code

// Get sheet
var worksheet = fpSpread1_Sheet1.AsWorksheet();

// Add data for sparkline
worksheet.SetValue(1, 0, "State");
worksheet.SetValue(1, 1, "Gloves");
worksheet.SetValue(1, 2, "Sanitizer");
worksheet.SetValue(1, 3, "Masks");
worksheet.SetValue(1, 4, "Diagram");
worksheet.SetValue(2, 0, "Idaho");
worksheet.SetValue(2, 1, 10000);
worksheet.SetValue(2, 2, 12000);
worksheet.SetValue(2, 3, 15000);
worksheet.SetValue(3, 0, "Montana");
worksheet.SetValue(3, 1, 11000);
worksheet.SetValue(3, 2, 10000);
worksheet.SetValue(3, 3, 15000);
worksheet.SetValue(4, 0, "Oregon");
worksheet.SetValue(4, 1, 10000);
worksheet.SetValue(4, 2, 17000);
worksheet.SetValue(4, 3, 12000);
worksheet.SetValue(5, 0, "Washington");
worksheet.SetValue(5, 1, 15000);
worksheet.SetValue(5, 2, 10000);
worksheet.SetValue(5, 3, 15000);
worksheet.SetValue(6, 0, "Utah");
worksheet.SetValue(6, 1, 10000);
worksheet.SetValue(6, 2, 15000);
worksheet.SetValue(6, 3, 12000);
worksheet.SetValue(7, 1, "#B39A9A");
worksheet.SetValue(7, 2, "#E3C3C3");
worksheet.SetValue(7, 3, "#F5E4E4");

// Set number format for columns
worksheet.Columns["B:D"].NumberFormat = "$#,##0";

// Set StackedSparkline formula
worksheet.Cells[2, 4].Formula = "STACKEDSPARKLINE(B3:D3,B8:D8,B2:D2,40000)";
worksheet.Cells[3, 4].Formula = "STACKEDSPARKLINE(B4:D4,B8:D8,B2:D2,40000)";
worksheet.Cells[4, 4].Formula = "STACKEDSPARKLINE(B5:D5,B8:D8,B2:D2,40000)";
worksheet.Cells[5, 4].Formula = "STACKEDSPARKLINE(B6:D6,B8:D8,B2:D2,40000)";
worksheet.Cells[6, 4].Formula = "STACKEDSPARKLINE(B7:D7,B8:D8,B2:D2,40000)";

// Set backcolor for cells
worksheet.Cells["A1"].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFDEACA7));
worksheet.Cells["A2:E2"].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFF5E4E4));
worksheet.Cells["A3:E7"].Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(unchecked((int)0xFFfdfafa));

Visual Basic	Copy Code
'Get sheet Dim worksheet = FpSpread1_Sheet1.AsWorksheet() 'Add data for sparkline worksheet.SetValue(1, 0, "State") worksheet.SetValue(1, 1, "Gloves") worksheet.SetValue(1, 2, "Sanitizer") worksheet.SetValue(1, 3, "Masks") worksheet.SetValue(1, 4, "Diagram") worksheet.SetValue(2, 0, "Idaho") worksheet.SetValue(2, 1, 10000) worksheet.SetValue(2, 2, 12000) worksheet.SetValue(2, 3, 15000) worksheet.SetValue(3, 0, "Montana") worksheet.SetValue(3, 1, 11000) worksheet.SetValue(3, 2, 10000) worksheet.SetValue(3, 3, 15000) worksheet.SetValue(4, 0, "Oregon") worksheet.SetValue(4, 1, 10000) worksheet.SetValue(4, 2, 17000) worksheet.SetValue(4, 3, 12000) worksheet.SetValue(5, 0, "Washington") worksheet.SetValue(5, 1, 15000) worksheet.SetValue(5, 2, 10000) worksheet.SetValue(5, 3, 15000) worksheet.SetValue(6, 0, "Utah") worksheet.SetValue(6, 1, 10000) worksheet.SetValue(6, 2, 15000) worksheet.SetValue(6, 3, 12000) worksheet.SetValue(7, 1, "#B39A9A") worksheet.SetValue(7, 2, "#E3C3C3") worksheet.SetValue(7, 3, "#F5E4E4") 'Set number format for columns worksheet.Columns("B:D").NumberFormat = "$#,##0" 'Set StackedSparkline formula worksheet.Cells(2, 4).Formula = "STACKEDSPARKLINE(B3:D3,B8:D8,B2:D2,40000)" worksheet.Cells(3, 4).Formula = "STACKEDSPARKLINE(B4:D4,B8:D8,B2:D2,40000)" worksheet.Cells(4, 4).Formula = "STACKEDSPARKLINE(B5:D5,B8:D8,B2:D2,40000)" worksheet.Cells(5, 4).Formula = "STACKEDSPARKLINE(B6:D6,B8:D8,B2:D2,40000)" worksheet.Cells(6, 4).Formula = "STACKEDSPARKLINE(B7:D7,B8:D8,B2:D2,40000)" 'Set back color for cells worksheet.Cells("A1").Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFDEACA7) worksheet.Cells("A2:E2").Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFF5E4E4) worksheet.Cells("A3:E7").Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFFDFAFA)

Visual Basic

Copy Code

'Get sheet
Dim worksheet = FpSpread1_Sheet1.AsWorksheet()
        
'Add data for sparkline
worksheet.SetValue(1, 0, "State")
worksheet.SetValue(1, 1, "Gloves")
worksheet.SetValue(1, 2, "Sanitizer")
worksheet.SetValue(1, 3, "Masks")
worksheet.SetValue(1, 4, "Diagram")
worksheet.SetValue(2, 0, "Idaho")
worksheet.SetValue(2, 1, 10000)
worksheet.SetValue(2, 2, 12000)
worksheet.SetValue(2, 3, 15000)
worksheet.SetValue(3, 0, "Montana")
worksheet.SetValue(3, 1, 11000)
worksheet.SetValue(3, 2, 10000)
worksheet.SetValue(3, 3, 15000)
worksheet.SetValue(4, 0, "Oregon")
worksheet.SetValue(4, 1, 10000)
worksheet.SetValue(4, 2, 17000)
worksheet.SetValue(4, 3, 12000)
worksheet.SetValue(5, 0, "Washington")
worksheet.SetValue(5, 1, 15000)
worksheet.SetValue(5, 2, 10000)
worksheet.SetValue(5, 3, 15000)
worksheet.SetValue(6, 0, "Utah")
worksheet.SetValue(6, 1, 10000)
worksheet.SetValue(6, 2, 15000)
worksheet.SetValue(6, 3, 12000)
worksheet.SetValue(7, 1, "#B39A9A")
worksheet.SetValue(7, 2, "#E3C3C3")
worksheet.SetValue(7, 3, "#F5E4E4")

'Set number format for columns
worksheet.Columns("B:D").NumberFormat = "$#,##0"

'Set StackedSparkline formula
worksheet.Cells(2, 4).Formula = "STACKEDSPARKLINE(B3:D3,B8:D8,B2:D2,40000)"
worksheet.Cells(3, 4).Formula = "STACKEDSPARKLINE(B4:D4,B8:D8,B2:D2,40000)"
worksheet.Cells(4, 4).Formula = "STACKEDSPARKLINE(B5:D5,B8:D8,B2:D2,40000)"
worksheet.Cells(5, 4).Formula = "STACKEDSPARKLINE(B6:D6,B8:D8,B2:D2,40000)"
worksheet.Cells(6, 4).Formula = "STACKEDSPARKLINE(B7:D7,B8:D8,B2:D2,40000)"

'Set back color for cells
worksheet.Cells("A1").Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFDEACA7)
worksheet.Cells("A2:E2").Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFF5E4E4)
worksheet.Cells("A3:E7").Interior.Color = GrapeCity.Spreadsheet.Color.FromArgb(&HFFFDFAFA)

Using the Spread Designer

Type data in a cell or a column or row of cells in the designer.
Select a cell for the sparkline.
Select the Insert menu.
Select a sparkline type.
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.
Select OK.
Select Apply and Exit from the File menu to save your changes and close the designer.