You can create a cascade sparkline using the CascadeSparkline function in a formula: =CASCADESPARKLINE(pointsRange, pointIndex, labelsRange?, minimum?, maximum?, colorPositive?, colorNegative?, vertical?, itemTypeRange?).
The function has the following parameters:
pointsRange: Reference that represents the range of cells that contain values, such as "B2:B8".
pointIndex: Number or reference that represents the index of the points. The pointIndex is >= 1 such as 1 or "D2".
labelsRange: (optional) Reference that represents the range of cells that contains the labels, such as "A2:A8"; default value is no label.
minimum: (optional) Number or reference that represents the minimum values of the display area, such as -2000; the default value is the minimum of the sum (the sum of the points' value).
maximum: (optional) Number or reference that represents the maximum values of the display area, such as 6000; the default value is the maximum of the sum (the sum of the points' value).
colorPositive: (optional) String that represents the color of the first or last positive sparkline's box (this point's value is positive); default value is "#8CBF64".
colorNegative: (optional) String that represents the color of the first or last negative sparkline's box (this point's value is negative); default value is "#D6604D".
vertical: (optional) Boolean that represents whether the box's direction is vertical or horizontal; default value is false.
itemTypeRange: (optional) Array or reference that represents the all item types of data range. The value should be {"-", "+", "="} or "A1:A7" that reference the value of {"+", "-", "="}. And "+" as positive change, "-" as negative change and "=" for total columns.
colorTotal: (optional) String that represents the color of the last sparkline's box when itemTypeRange did not exist. Or represents the color of the result sparkline's box when itemTypeRange exists..
var spreadNS = GC.Spread.Sheets;
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
spread.fromJSON(incomeJson);
initSpread(spread);
};
function initSpread(spread) {
spread.options.newTabVisible = false;
spread.options.allowDynamicArray = true;
initCascadeSparklineWithType(spread.sheets[0], "ItemType");
initHorizontalSparkline(spread.sheets[1], "Horizontal");
initVerticalSparkline(spread.sheets[2], "Vertical");
}
function initCascadeSparklineWithType (sheet, name) {
sheet.suspendPaint();
sheet.name(name);
sheet.getCell(3, 0).font('bold normal 11px normal');
sheet.getCell(6, 0).font('bold normal 11px normal');
sheet.getCell(11, 0).font('bold normal 11px normal');
sheet.getCell(13, 0).font('bold normal 11px normal');
sheet.getCell(15, 0).font('bold normal 11px normal');
sheet.options.gridline = { showVerticalGridline: false, showHorizontalGridline: false };
sheet.getRange(3, 0, 1, 3).setBorder(
new GC.Spread.Sheets.LineBorder("#404040", GC.Spread.Sheets.LineStyle.thin),
{ top: true }, GC.Spread.Sheets.SheetArea.viewport);
sheet.getRange(6, 0, 1, 3).setBorder(
new GC.Spread.Sheets.LineBorder("#404040", GC.Spread.Sheets.LineStyle.thin),
{ top: true }, GC.Spread.Sheets.SheetArea.viewport);
sheet.getRange(8, 0, 1, 3).setBorder(
new GC.Spread.Sheets.LineBorder("#939393", GC.Spread.Sheets.LineStyle.thin),
{ top: true }, GC.Spread.Sheets.SheetArea.viewport);
sheet.getRange(11, 0, 1, 3).setBorder(
new GC.Spread.Sheets.LineBorder("#404040", GC.Spread.Sheets.LineStyle.thin),
{ top: true }, GC.Spread.Sheets.SheetArea.viewport);
sheet.getRange(13, 0, 1, 3).setBorder(
new GC.Spread.Sheets.LineBorder("#404040", GC.Spread.Sheets.LineStyle.thin),
{ top: true }, GC.Spread.Sheets.SheetArea.viewport);
sheet.getRange(15, 0, 1, 3).setBorder(
new GC.Spread.Sheets.LineBorder("#404040", GC.Spread.Sheets.LineStyle.thin),
{ top: true }, GC.Spread.Sheets.SheetArea.viewport);
sheet.setFormula(1, 1, '=CASCADESPARKLINE(D2:D16, SEQUENCE(COUNTA(D2:D16),1,1,1),D2:D16,,,"#939393","#939393",false,F2:F16,"#2E8B57")');
sheet.setFormula(1, 2, '=CASCADESPARKLINE(E2:E16, SEQUENCE(COUNTA(E2:E16),1,1,1),E2:E16,,,"#404040","#404040",false,F2:F16,"#008080")');
sheet.resumePaint();
}
function initHorizontalSparkline(sheet, name) {
sheet.suspendPaint();
sheet.name(name);
sheet.addSpan(0, 0, 1, 5);
sheet.getCell(0, 0).value("Checkbook Register")
.font("20px Arial")
.hAlign(spreadNS.HorizontalAlign.center)
.vAlign(spreadNS.VerticalAlign.center)
.backColor("purple")
.foreColor("white");
sheet.setArray(1, 0, [
["","Opening Balance",815.25, "\u03A3"],
["12/11/2020", "CVS", -20],
["12/12/2020", "Bank", 100.12],
["12/12/2020", "Starbucks", -5.43],
["12/12/2020", "Taco Bell", -7.03],
["12/13/2020", "From Work", 534.98],
["12/13/2020","From Savings", 1000],
["12/15/2020", "Mexicali Restaurant",-22.35],
["12/17/2020", "Staples",-55.50],
["12/18/2020", "Cash", 165],
["12/18/2020", "W&P", -58.60],
["12/18/2020", "Gas", -15.80],
["12/22/2020", "Bookstore", -65.00],
["12/23/2020", "Target", -120.65],
["1/1/2021", "Rent", -550.00],
["","Closing Balance"]
]);
sheet.setColumnWidth(0, 80);
sheet.setColumnWidth(1, 120);
sheet.setColumnWidth(2, 80);
sheet.setColumnWidth(3, 80);
sheet.setColumnWidth(4, 340);
sheet.setRowHeight(0, 40);
sheet.getRange(2, 0, 14, 1).formatter("MM/dd/yyyy");
sheet.getRange(1, 2, 16, 1).formatter("#,###.00");
sheet.getRange(2, 3, 14, 1).formatter("#,###.00");
sheet.setFormula(2, 3, "=C3 + C2");
var r;
for (r = 4; r <= 16; r++) {
sheet.setFormula(r - 1, 3, "=C" + r + " + D" + (r - 1));
}
for(r = 1; r <=17; r++) {
sheet.setRowHeight(r, 24);
}
sheet.setFormula(16, 2, "=D16");
sheet.getCell(1, 3).hAlign(1);
sheet.getRange(1, 1, 1, 2).font("bold 14px Georgia");
sheet.getRange(16, 1, 1, 2).font("bold 14px Georgia");
sheet.getRange(1, 0, 1, 5).setBorder(new spreadNS.LineBorder("black", spreadNS.LineStyle.thin), { bottom: true });
sheet.getRange(16, 0, 1, 5).setBorder(new spreadNS.LineBorder("black", spreadNS.LineStyle.medium), { top: true });
for (var i = 1; i <= 16; i++) {
sheet.setFormula(i, 4, '=CASCADESPARKLINE($C$2:$C$17,ROW()-1,$B$2:$B$17,,,"#8CBF64","#D6604D",false)');
}
sheet.resumePaint();
}
function initVerticalSparkline(sheet, name) {
sheet.suspendPaint();
sheet.name(name);
sheet.setRowHeight(0, 50);
sheet.addSpan(0, 0, 1, 6);
sheet.getCell(0, 0).value("Checkbook Register")
.font("20px Arial")
.hAlign(spreadNS.HorizontalAlign.center)
.vAlign(spreadNS.VerticalAlign.center)
.backColor("purple")
.foreColor("white");
sheet.setRowHeight(3, 200);
sheet.setArray(1, 0, [
["Initial", "Tips", "Car", "Foods", "Saving", "Final"],
[345, 400, -115.2, -87.34, -100]
]);
sheet.setFormula(2, 5, "=Sum(A3:E3)");
sheet.getRange(2, 0, 1, 6).formatter(".00");
for (var c = 0; c < 6; c++) {
sheet.setColumnWidth(c, 100);
sheet.setFormula(3, c, '=CASCADESPARKLINE($A$3:$F$3,COLUMN(),$A$2:$F$2,,,"#8CBF64","#D6604D",true)');
}
sheet.getRange(1, 0, 2, 1).font("bold 14px Georgia");
sheet.getRange(1, 5, 2, 1).font("bold 14px Georgia");
sheet.getRange(1, 0, 3, 1).setBorder(new spreadNS.LineBorder("black", spreadNS.LineStyle.thin), { right: true });
sheet.getRange(1, 5, 3, 1).setBorder(new spreadNS.LineBorder("black", spreadNS.LineStyle.medium), { left: true });
sheet.resumePaint();
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/data/income-statement-for-cascade.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" style="width:100%;height:100%"></div>
</div></body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}