<p>You can create a bullet sparkline using the BulletSparkline function in a formula: <strong>=BULLETSPARKLINE(measure, target, maxi, good?, bad?, forecast?, tickunit?, colorScheme?, vertical?, measureColor?, targetColor?, maxiColor?, goodColor?, badColor?, forecastColor?, allowMeasureOverMaxi?, barSize?)</strong>.</p>
<p>The function has the following parameters:</p>
<p><strong>measure</strong>: Number or reference that represents the length of the measure bar, such as 5 or "A1".</p>
<p><strong>target</strong>: Number or reference that represents the location of the target line, such as 7 or "A2".</p>
<p><strong>maxi</strong>: Number or reference that represents the maximum value of the sparkline, such as 10 or "A3".</p>
<p><strong>good</strong>: (optional) Number or reference that represents the length of the good bar, such as 3 or "A4"; default value is 0.</p>
<p><strong>bad</strong>: (optional) Number or reference that represents the length of the bad bar, such as 1 or "A5"; default value is 0.</p>
<p><strong>forecast</strong>: (optional) Number or reference that represents the length of the forecast line, such as 8 or "A6"; default value is 0.</p>
<p><strong>tickunit</strong>: (optional) Number or reference that represents the tick unit, such as 1 or "A7"; default value is 0.</p>
<p><strong>colorScheme</strong>: (optional) String that represents the color scheme for generating a group of colors to display the sparkline; default value is "#A0A0A0".</p>
<p><strong>vertical</strong>: (optional) Boolean that represents whether to display the sparkline vertically; default value is false.</p>
<p><strong>measureColor</strong>: (optional) String that represents the color of measure bar.</p>
<p><strong>targetColor</strong>: (optional) String that represents the color of target line.</p>
<p><strong>maxiColor</strong>: (optional) String that represents the maxi area color.</p>
<p><strong>goodColor</strong>: (optional) String that represents the good area color.</p>
<p><strong>badColor</strong>: (optional) String that represents the bad area color.</p>
<p><strong>forecastColor</strong>: (optional) String that represents the forecast line color.</p>
<p><strong>allowMeasureOverMaxi</strong>: (optional) Boolean that represents if the measure could exceed maxi area; default value is false.</p>
<p><strong>barSize</strong>: (optional) Number that represents that the percentage of bar width/height according to the cell height/ width. (value > 0 && value <= 1)</p>
var spreadNS = GC.Spread.Sheets;
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 });
initSpread(spread);
};
function initSpread(spread) {
spread.options.newTabVisible = false;
initHorizontalSparkline(spread.sheets[0], "Horizontal");
initVerticalSparkline(spread.sheets[1], "Vertical");
};
function initHorizontalSparkline(sheet, name) {
sheet.suspendPaint();
sheet.name(name);
sheet.addSpan(0, 0, 1, 4);
sheet.getCell(0, 0).value("Employee KPI").font("20px Arial").hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center);
sheet.setValue(1, 0, "Name");
sheet.setValue(1, 1, "Forecast");
sheet.setValue(1, 2, "Actuality");
sheet.setValue(1, 3, "Diagram");
sheet.getRange(1, 0, 1, 4).foreColor("white").backColor("Accent 4").hAlign(1).vAlign(1);
sheet.setValue(2, 0, "Employee 1");
sheet.setValue(2, 1, 6);
sheet.setValue(2, 2, 6);
sheet.setValue(3, 0, "Employee 2");
sheet.setValue(3, 1, 8);
sheet.setValue(3, 2, 7);
sheet.setValue(4, 0, "Employee 3");
sheet.setValue(4, 1, 6);
sheet.setValue(4, 2, 4);
sheet.setValue(5, 0, "Employee 4");
sheet.setValue(5, 1, 7);
sheet.setValue(5, 2, 9);
sheet.setValue(6, 0, "Employee 5");
sheet.setValue(6, 1, 6);
sheet.setValue(6, 2, 8);
sheet.setValue(7, 0, "Employee 6");
sheet.setValue(7, 1, 8);
sheet.setValue(7, 2, 7);
sheet.setValue(8, 0, "Settings:");
sheet.setValue(9, 0, "target");
sheet.setValue(9, 1, 7);
sheet.setValue(10, 0, "maxi");
sheet.setValue(10, 1, 10);
sheet.setValue(11, 0, "good");
sheet.setValue(11, 1, 8);
sheet.setValue(12, 0, "bad");
sheet.setValue(12, 1, 5);
sheet.setValue(13, 0, "color scheme");
sheet.setValue(13, 1, "gray");
sheet.setValue(14, 0, "measure color");
sheet.setValue(14, 1, "#FFC0CB");
sheet.setValue(15, 0, "target color");
sheet.setValue(15, 1, "#FF4500");
sheet.setValue(16, 0, "maxi color");
sheet.setValue(16, 1, "#00FFFF");
sheet.setValue(17, 0, "good color");
sheet.setValue(17, 1, "#7FFF00");
sheet.setValue(18, 0, "bad color");
sheet.setValue(18, 1, " #FF0000");
sheet.setValue(19, 0, "forecast color");
sheet.setValue(19, 1, "#000080");
sheet.setValue(20, 0, "allow measure over maxi");
sheet.setValue(20, 1, true);
sheet.setValue(21, 0, "bar size");
sheet.setValue(21, 1, 0.8);
for (var index = 2; index < 8; index++) {
sheet.setFormula(index, 3, '=BULLETSPARKLINE($C' + (index + 1) + ',$B$10,$B$11,$B$12,$B$13,$B' + (index + 1)
+ ',1,$B$14,false,$B$15,$B$16,$B$17,$B$18,$B$19,$B$20,$B$21,$B$22)');
}
sheet.setRowHeight(0, 30);
for (var i = 2; i < 8; i++) {
sheet.setRowHeight(i, 40);
}
sheet.setColumnWidth(0, 100);
sheet.setColumnWidth(1, 100);
sheet.setColumnWidth(2, 100);
sheet.setColumnWidth(3, 200);
sheet.resumePaint();
}
function initVerticalSparkline(sheet, name) {
sheet.suspendPaint();
sheet.name(name);
sheet.addSpan(0, 0, 1, 7);
sheet.getCell(0, 0).value("Employee KPI").font("20px Arial").hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center);
sheet.setArray(1, 0, [
[ "Name", "Employee 1", "Employee 2", "Employee 3", "Employee 4", "Employee 5", "Employee 6" ],
[ "Forecast", 6, 8, 8, 7, 6, 8 ],
["Actuality", 6, 7, 4, 9, 8, 7],
["Diagram"]
]);
sheet.getRange(1, 0, 4, 1).foreColor("white").backColor("Accent 4").hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center);
sheet.setValue(8, 0, "Settings:");
sheet.setValue(9, 0, "target");
sheet.setValue(9, 1, 7);
sheet.setValue(10, 0, "maxi");
sheet.setValue(10, 1, 10);
sheet.setValue(11, 0, "good");
sheet.setValue(11, 1, 8);
sheet.setValue(12, 0, "bad");
sheet.setValue(12, 1, 5);
sheet.setValue(13, 0, "color scheme");
sheet.setValue(13, 1, "red");
sheet.setValue(14, 0, "measure color");
sheet.setValue(14, 1, "#FFC0CB");
sheet.setValue(15, 0, "target color");
sheet.setValue(15, 1, "#FF4500");
sheet.setValue(16, 0, "maxi color");
sheet.setValue(16, 1, "#00FFFF");
sheet.setValue(17, 0, "good color");
sheet.setValue(17, 1, "#7FFF00");
sheet.setValue(18, 0, "bad color");
sheet.setValue(18, 1, " #FF0000");
sheet.setValue(19, 0, "forecast color");
sheet.setValue(19, 1, "#000080");
sheet.setValue(20, 0, "allow measure over maxi");
sheet.setValue(20, 1, true);
sheet.setValue(21, 0, "bar size");
sheet.setValue(21, 1, 0.8);
for (var index = 1; index < 7; index++) {
var letter = String.fromCharCode(65 + index);
sheet.setFormula(4, index, '=BULLETSPARKLINE(' + letter + '$4,$B$10,$B$11,$B$12,$B$13,' + letter
+ '$3,1,$B$14,true,$B$15,$B$16,$B$17,$B$18,$B$19,$B$20,$B$21,$B$22)');
}
sheet.setRowHeight(0, 30);
sheet.setColumnWidth(0, 100);
for (var i = 1; i < 7; i++) {
sheet.setColumnWidth(i, 80);
}
sheet.setRowHeight(4, 120);
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>
<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;
}