Bullet

A bullet sparkline can show a lot of data in a small amount of space.

<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 &gt; 0 &amp;&amp; value &lt;= 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; }