SpreadJS Sparklines

SpreadJS supports cascade sparkline in addition to the standard sparklines supported by MS Excel. GcExcel .Net supports export of SpreadJS files containing cascade sparklines to JSON I/O, HTML, image, and PDF formats. This topic discusses about these extended sparklines and how to create them in GcExcel .NET.

Cascade Sparkline

A cascade sparkline is generally used to analyze a value over time like yearly sales, total profit, net tax etc. It is used widely in finance, sales, legal and construction sectors, to name a few. For example, you can use cascade sparkline to compare expenses and earnings of a salesman.

GcExcel .NET provides CASCADESPARKLINE formula for creating cascade sparkline.

Syntax

= CASCADESPARKLINE(pointsRange, pointIndex, labelsRange, minimum, maximum, colorPositive, colorNegative, vertical)

Parameters

Parameter Name	Description
pointsRange(Required)	A reference that represents the range of cells that contains values, such as "B2:B8".
pointIndex (Required)	A number or reference that represents the points index. The pointIndex is >= 1 such as 1 or "D2".
LabelsRange (Optional)	A reference that represents the range of cells that contains the labels, such as "A2:A8". The default value is no label.
Minimum (Optional)	A number or reference that represents the minimum values of the display area. The default value is the minimum of the sum (the sum of the points' value), such as -2000. The minimum you set must be less than the default minimum; otherwise, the default minimum is used.
maximum (Optional)	A number or reference that represents the maximum values of the display area. The default value is the maximum of the sum (the sum of the points' value), such as 6000. The maximum you set must be greater than the default maximum; otherwise, the default maximum is used.
colorPositive(Optional)	A string that represents the color of the first or last positive sparkline's box (this point's value is positive). The default value is "#8CBF64". If the first or last box represents a positive value, the box's color is set to colorPositive. The middle positive box is set to a lighter color than colorPositive.
colorNegative (Optional)	A string that represents the color of the first or last negative sparkline's box (this point's value is negative). The default value is "#D6604D". If the first or last box represents the negative value, the box's color is set to colorNegative. The middle negative box is set to a lighter color than colorNegative.
vertical (Optional)	A boolean that represents whether the box's direction is vertical or horizontal. The default value is FALSE. You must set vertical to true or false for a group of formulas, because all the formulas represent the entire sparkline.
itemTypeRange (Optional)	An array or reference that represents all the item types of the data range. The values should be {"-", "+", "="} or "A1:A7" that reference the value of {"+", "-", "="}, where "+" indicates positive change, "-" indicates negative change and "=" indicates total columns.
colorTotal (Optional)	A string that either represents the color of the last sparkline's box when itemTypeRange does not exist or represents the color of the resulting sparkline's box when itemTypeRange exists.

Refer to the following example code to add cascade sparkline using formula.

// Add cascade sparklines with horizontal bars. 

for (int i = 1; i < 8; i++) 
{ 
    worksheet.Range[i, 2].Formula = "=CASCADESPARKLINE(B2:B8, ROW() - 1, A2:A8, , , \"#8CBF64\", \"#D6604D\", FALSE)"; 
}

Line Sparkline

A line sparkline shows the trend of any expenses or data, emphasizing the changes and fluctuations.

Syntax: = LINESPARKLINE(data,dataOrientation,[dateAxisData],[dateAxisOrientation],[setting])

The formula has the following parameters:

Parameter Name	Description
data	A range reference that represents sparkline data, such as "A1:C3".
dataOrientation	A number that represents the sparkline data orientation. One of the following: vertical: 0 (default). horizontal: 1.
dateAxisData	A range reference that represents the sparkline date axis data, such as "D1:F3". If there is no dateAxisOrientation, then dateAxisData is invalid.
dateAxisOrientation	A number that represents the sparkline date axis orientation. One of the following: vertical - 0 horizontal - 1
setting	A string in JSON format. format: FullName (abbreviation) [default value]: Description

The setting parameter provides the following case-sensitive properties to set:

Parameter Name	Description
showFirst(sf)[False]	To check whether the first data point is formatted differently for Sparkline.
showHigh(sh)[False]	To check whether the data points with the highest value are formatted differently for Sparkline.
showLast(slast)[False]	To check whether the last data point is formatted differently for Sparkline.
showLow(slow)[False]	To check whether the data points with the lowest value are formatted differently for Sparkline.
showNegative(sn)[False]	To check whether the negative data points are formatted differently for Sparkline.
showMarkers(sm)[False]	To check whether data markers are displayed for Sparkline.
axisColor(ac)[#000000]	The color of the axis.
firstMarkerColor(fmc)[#95B3D7]	The color of the first data point for Sparkline.
highMarkerColor(hmc)[#0000FF]	The color of the highest data point for Sparkline.
lastMarkerColor(lastmc)[#95B3D7]	The color of the last data point for Sparkline.
lowMarkerColor(lowmc)[#0000FF]	The color of the lowest data point for Sparkline.
markersColor(mc)[#244062]	The color of the data markers for Sparkline.
negativeColor(nc)[#A52A2A]	The color of the negative data points for Sparkline.
seriesColor(sc)[#244062]	The color for Sparkline.
lineWeight(lw)[1.0]	Indicates the line weight for sparkline, where the line weight is measured in points. The weight must be greater than or equal to zero.
displayXAxis(dxa)[False]	Indicates whether the horizontal axis is displayed for Sparkline.
displayEmptyCellsAs(deca)[0]	Indicates how to display the empty cells. gaps: 0Leaves gaps for empty values in a data series, which results in a segmented line. zero: 1Handles empty values in a data series as zero values, so that the line drops to zero for zero-value data points. connect: 2Fills gaps with a connecting element instead of leaving gaps for empty values in a data series.
displayHidden(dh)[False]	Indicates whether data in hidden cells is plotted for the Sparklines.
manualMax(mmax)[0]	Indicates the maximum value for the vertical axis of the sparklines. The axis is set to zero if maxAxisType is not equal to 'custom(2)'.
manualMin(mmin)[0]	Indicates the minimum value for the vertical axis of the sparklines. The axis is set to zero if minAxisType is not equal to 'custom(2)'.
maxAxisType(maxat)[0]	Indicates how the vertical axis maximum is calculated. individual: 0Specifies that the vertical axis minimum or maximum for sparkline is calculated automatically such that the data point with the minimum or maximum value can be displayed in the plot area. custom: 2Specifies that the vertical axis minimum or maximum for sparkline is specified by the manualMin attribute or the manualMax attribute.
minAxisType(minat)[0]	Indicates how the vertical axis minimum is calculated, similar to the maxAxisType type.
rightToLeft(rtl)[False]	Indicates whether each sparkline in the sparkline group is displayed in a right-to-left manner.

Refer to the following example code to add line sparkline using formula:

// Create a new workbook.
var workbook = new Workbook();

// Get active sheet.
var worksheet = workbook.ActiveSheet;

// Add values to the table.
worksheet.Range["A1"].Value = "Sales by Country";
worksheet.Range["A2:F5"].Value = new object[,]
{
{"Countries",2016,2017,2018,2019,2020},
{"China",243000,291000,465000,282000,213000},
{"India",448000,358000,332000,489000,302000},
{"UnitedStates",439000,276000,413000,396000,392000}
};
worksheet.Range["G2"].Value = "SparkLine";

// Add line sparkline formula.
worksheet.Range["G3:G5"].Formula = "=LINESPARKLINE(B3:F3,1,,,\"{showMarkers:TRUE,lineWeight:1.5,markersColor:#7030a0}\")";

worksheet.Range["A1:G1"].Merge();
worksheet.Range["A1"].Interior.Color = System.Drawing.Color.Purple;
worksheet.Range["A1"].Font.Color = System.Drawing.Color.White;
worksheet.Range["A1"].HorizontalAlignment = HorizontalAlignment.Center;
worksheet.Range["A1:G5"].Borders.LineStyle = BorderLineStyle.Thin;

worksheet.Range["B3:F5"].NumberFormat = "$#,##0";
worksheet.Range["A1:G2"].Font.Bold = true;
worksheet.Range["A1"].Font.Size = 15;
worksheet.Range["A2:G5"].Font.Size = 13;

worksheet.Range["A:F"].AutoFit();
worksheet.Range["1:5"].RowHeight = 25;
worksheet.Range["G1"].ColumnWidth = 30;


// Save as a PDF document.
workbook.Save("LineSparkline.pdf");

// Save as a .sjs file.
workbook.Save("LineSparkline.sjs");

Column Sparkline

A column sparkline utilizes bar charts for visualizing the distribution and changes in the data. The positive data points will lie above the x-axis, and the negative data points will be below the x-axis.

Syntax: = COLUMNSPARKLINE(data,dataOrientation,[dateAxisData],[dateAxisOrientation],[setting])

The formula has the following parameters:

Parameter Name	Description
data	A range reference that represents sparkline data, such as "A1:C3".
dataOrientation	A number that represents the sparkline data orientation. One of the following: vertical: 0 (default). horizontal: 1.
dateAxisData	A range reference that represents the sparkline date axis data, such as "D1:F3". If there is no dateAxisOrientation, then dateAxisData is invalid.
dateAxisOrientation	A number that represents the sparkline date axis orientation. One of the following: vertical - 0 horizontal - 1
setting	A string in JSON format. format: FullName (abbreviation) [default value]: Description

The setting parameter provides the following case-sensitive properties to set:

Parameter Name	Description
showFirst(sf)[False]	To check whether the first data point is formatted differently for Sparkline.
showHigh(sh)[False]	To check whether the data points with the highest value are formatted differently for Sparkline.
showLast(slast)[False]	To check whether the last data point is formatted differently for Sparkline.
showLow(slow)[False]	To check whether the data points with the lowest value are formatted differently for Sparkline.
showNegative(sn)[False]	To check whether the negative data points are formatted differently for Sparkline.
showMarkers(sm)[False]	To check whether data markers are displayed for Sparkline.
axisColor(ac)[#000000]	The color of the axis.
firstMarkerColor(fmc)[#95B3D7]	The color of the first data point for Sparkline.
highMarkerColor(hmc)[#0000FF]	The color of the highest data point for Sparkline.
lastMarkerColor(lastmc)[#95B3D7]	The color of the last data point for Sparkline.
lowMarkerColor(lowmc)[#0000FF]	The color of the lowest data point for Sparkline.
markersColor(mc)[#244062]	The color of the data markers for Sparkline.
negativeColor(nc)[#A52A2A]	The color of the negative data points for Sparkline.
seriesColor(sc)[#244062]	The color for Sparkline.
lineWeight(lw)[1.0]	Indicates the line weight for sparkline, where the line weight is measured in points. The weight must be greater than or equal to zero.
displayXAxis(dxa)[False]	Indicates whether the horizontal axis is displayed for Sparkline.
displayEmptyCellsAs(deca)[0]	Indicates how to display the empty cells. gaps: 0Leaves gaps for empty values in a data series, which results in a segmented line. zero: 1Handles empty values in a data series as zero values, so that the line drops to zero for zero-value data points. connect: 2Fills gaps with a connecting element instead of leaving gaps for empty values in a data series.
displayHidden(dh)[False]	Indicates whether data in hidden cells is plotted for the Sparklines.
manualMax(mmax)[0]	Indicates the maximum value for the vertical axis of the sparklines. The axis is set to zero if maxAxisType is not equal to 'custom(2)'.
manualMin(mmin)[0]	Indicates the minimum value for the vertical axis of the sparklines. The axis is set to zero if minAxisType is not equal to 'custom(2)'.
maxAxisType(maxat)[0]	Indicates how the vertical axis maximum is calculated. individual: 0Specifies that the vertical axis minimum or maximum for sparkline is calculated automatically such that the data point with the minimum or maximum value can be displayed in the plot area. custom: 2Specifies that the vertical axis minimum or maximum for sparkline is specified by the manualMin attribute or the manualMax attribute.
minAxisType(minat)[0]	Indicates how the vertical axis minimum is calculated, similar to the maxAxisType type.
rightToLeft(rtl)[False]	Indicates whether each sparkline in the sparkline group is displayed in a right-to-left manner.

Refer to the following example code to add column sparkline using formula:

// Create a new workbook.
var workbook = new Workbook();

// Get active sheet.
var worksheet = workbook.ActiveSheet;

// Add values to the table.
worksheet.Range["A1"].Value = "Sales Data";
worksheet.Range["A2:F6"].Value = new object[,]
{
{"Salesperson","Jan","Feb","Mar","Apr","May"},
{"Courtney Graves",76000,68250,72000,99750,45500},
{"Crystal Cross",118750,89250,121250,92500,60750},
{"Jodi Hall",75500,56500,65500,62750,77500},
{"Andrew James",76750,108500,88000,55750,106000}
};
worksheet.Range["G2"].Value = "SparkLine";

// Add column sparkline formula.
worksheet.Range["G3:G6"].Formula = "=COLUMNSPARKLINE(B3:F3,1,,,\"{showMarkers:TRUE}\")";

worksheet.Range["A1:G1"].Merge();
worksheet.Range["A1"].Interior.Color = System.Drawing.Color.Purple;
worksheet.Range["A1"].Font.Color = System.Drawing.Color.White;
worksheet.Range["A1"].HorizontalAlignment = HorizontalAlignment.Center;
worksheet.Range["A1:G6"].Borders.LineStyle = BorderLineStyle.Thin;

worksheet.Range["B3:F6"].NumberFormat = "$#,##0";
worksheet.Range["A1:G2"].Font.Bold = true;
worksheet.Range["A1"].Font.Size = 15;
worksheet.Range["A2:G6"].Font.Size = 13;

worksheet.Range["A:F"].AutoFit();
worksheet.Range["1:6"].RowHeight = 25;
worksheet.Range["G1"].ColumnWidth = 30;

// Save as a PDF document.
workbook.Save("ColumnSparkline.pdf");

// Save as a .sjs file.
workbook.Save("ColumnSparkline.sjs");

Win-Loss Sparkline

A win-loss sparkline utilizes bar charts to provide a visual representation of positive and negative values. The positive values (wins) lie above the x-axis, and the negative values (losses) lie below the x-axis. This sparkline supports values with two states, such as true or false or 1 or -1. For example, game results where 1's represent wins and -1's represent defeats.

Syntax: = WINLOSSSPARKLINE(data,dataOrientation,[dateAxisData],[dateAxisOrientation],[setting])

The formula has the following parameters:

Parameter Name	Description
data	A range reference that represents sparkline data, such as "A1:C3".
dataOrientation	A number that represents the sparkline data orientation. One of the following: vertical: 0 (default). horizontal: 1.
dateAxisData	A range reference that represents the sparkline date axis data, such as "D1:F3". If there is no dateAxisOrientation, then dateAxisData is invalid.
dateAxisOrientation	A number that represents the sparkline date axis orientation. One of the following: vertical - 0 horizontal - 1
setting	A string in JSON format. format: FullName (abbreviation) [default value]: Description

The setting parameter provides the following case-sensitive properties to set:

Parameter Name	Description
showFirst(sf)[False]	To check whether the first data point is formatted differently for Sparkline.
showHigh(sh)[False]	To check whether the data points with the highest value are formatted differently for Sparkline.
showLast(slast)[False]	To check whether the last data point is formatted differently for Sparkline.
showLow(slow)[False]	To check whether the data points with the lowest value are formatted differently for Sparkline.
showNegative(sn)[False]	To check whether the negative data points are formatted differently for Sparkline.
showMarkers(sm)[False]	To check whether data markers are displayed for Sparkline.
axisColor(ac)[#000000]	The color of the axis.
firstMarkerColor(fmc)[#95B3D7]	The color of the first data point for Sparkline.
highMarkerColor(hmc)[#0000FF]	The color of the highest data point for Sparkline.
lastMarkerColor(lastmc)[#95B3D7]	The color of the last data point for Sparkline.
lowMarkerColor(lowmc)[#0000FF]	The color of the lowest data point for Sparkline.
markersColor(mc)[#244062]	The color of the data markers for Sparkline.
negativeColor(nc)[#A52A2A]	The color of the negative data points for Sparkline.
seriesColor(sc)[#244062]	The color for Sparkline.
lineWeight(lw)[1.0]	Indicates the line weight for sparkline, where the line weight is measured in points. The weight must be greater than or equal to zero.
displayXAxis(dxa)[False]	Indicates whether the horizontal axis is displayed for Sparkline.
displayEmptyCellsAs(deca)[0]	Indicates how to display the empty cells. gaps: 0Leaves gaps for empty values in a data series, which results in a segmented line. zero: 1Handles empty values in a data series as zero values, so that the line drops to zero for zero-value data points. connect: 2Fills gaps with a connecting element instead of leaving gaps for empty values in a data series.
displayHidden(dh)[False]	Indicates whether data in hidden cells is plotted for the Sparklines.
manualMax(mmax)[0]	Indicates the maximum value for the vertical axis of the sparklines. The axis is set to zero if maxAxisType is not equal to 'custom(2)'.
manualMin(mmin)[0]	Indicates the minimum value for the vertical axis of the sparklines. The axis is set to zero if minAxisType is not equal to 'custom(2)'.
maxAxisType(maxat)[0]	Indicates how the vertical axis maximum is calculated. individual: 0Specifies that the vertical axis minimum or maximum for sparkline is calculated automatically such that the data point with the minimum or maximum value can be displayed in the plot area. custom: 2Specifies that the vertical axis minimum or maximum for sparkline is specified by the manualMin attribute or the manualMax attribute.
minAxisType(minat)[0]	Indicates how the vertical axis minimum is calculated, similar to the maxAxisType type.
rightToLeft(rtl)[False]	Indicates whether each sparkline in the sparkline group is displayed in a right-to-left manner.

Refer to the following example code to add win-loss sparkline using formula:

// Create a new workbook.
var workbook = new Workbook();

// Get active sheet.
var worksheet = workbook.ActiveSheet;

// Add values to the table.
worksheet.Range["A1"].Value = "Employee Performance";
worksheet.Range["A2:F5"].Value = new object[,]
{
{"Team Member","Wk1","Wk2","Wk3","Wk4","Wk5"},
{"Pamela Nelson",5,1,7,5,8},
{"Gabriel Reed",1,2,4,2,9},
{"Christina Robbins",4,10,8,5,10}
};
worksheet.Range["H2"].Value = "Target (tasks per week):";
worksheet.Range["I2"].Value = 5;
worksheet.Range["G2"].Value = "SparkLine";


// Add win-loss sparkline formula. Use Formula2 as the formula contains a dynamic array formula (B3:F3-$I$2).
worksheet.Range["G3:G5"].Formula2 = "=WINLOSSSPARKLINE(B3:F3-$I$2,1,,,\"{showNegative:TRUE}\")";

worksheet.Range["A1:G1"].Merge();
worksheet.Range["A1"].Interior.Color = System.Drawing.Color.Purple;
worksheet.Range["A1"].Font.Color = System.Drawing.Color.White;
worksheet.Range["A1"].HorizontalAlignment = HorizontalAlignment.Center;
worksheet.Range["A1:G5"].Borders.LineStyle = BorderLineStyle.Thin;

worksheet.Range["A1:G2"].Font.Bold = true;
worksheet.Range["A1"].Font.Size = 15;
worksheet.Range["A2:G5"].Font.Size = 13;
worksheet.Range["H2:I2"].Font.Bold = true;
worksheet.Range["H2:I2"].Font.Size = 13;

worksheet.Range["A:F"].AutoFit();
worksheet.Range["1:5"].RowHeight = 25;
worksheet.Range["G1:H1"].ColumnWidth = 30;

// Save as a PDF document.
workbook.Save("WinLossSparkline.pdf");

// Save as a .sjs file.
workbook.Save("WinLossSparkline.sjs");

Note:
Using Value property of IRange interface to get the formula result will return the ISparkLineObject representing the Sparkline object. ToJson method of ISparkLineObject interface can return the JSON string corresponding to the object.
Using Text property of IRange interface to get the formula result will return an empty string.
MS Excel does not support the SPARKLINE formula, so the result of this formula will be "#NAME?".
A user can import and export JSON or .sjs files containing sparkline.
GcExcel supports exporting the sparkline formula to PDF, HTML, or Image.
If the formula is incorrect, the result will be CalcError.Value.