HBar and VBar

The hbar and vbar sparklines can be used to show variations or ranges in data.

You can create an hbar or vbar sparkline using the HBarSparkline or VBarSparkline function in a formula: =HBARSPARKLINE(value, colorScheme?, axisVisible?, barHeight?) or =VBARSPARKLINE(value, colorScheme?, axisVisible?, barWidth?). The functions have the following parameters: value: Number or reference that represents the length of the bar, such as 0.3 or "A1". If value is greater than 100% or smaller than -100%, an arrow is displayed. colorScheme: (optional) String that represents the color of the bar; default value is "gray". axisVisible: (optional) Boolean that represents whether to show the axis, default value is true. barHeight: (optional) Number that represents the percentage of bar height according to the cell height. (value > 0 && value <= 1) barWidth: (optional) Number that represents the percentage of bar width according to the cell width. (value > 0 && value <= 1) HBarSparkline: Positive values, sparkline starts at left of cell; negative values, sparkline starts at right of cell. VBarSparkline: Positive values, sparkline starts at bottom of cell; negative values, sparkline starts at top of cell.
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, 3); sheet.getCell(0, 0).value("SPRINT 4").font("20px 'Comic Sans MS'").hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center); sheet.getRange(1, 0, 1, 3).foreColor("white").backColor("#C0504D").hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center); sheet.setValue(1, 0, "Name"); sheet.setValue(1, 1, "Progress"); sheet.setValue(1, 2, "Diagram"); sheet.setValue(2, 0, "Employee 1"); sheet.setValue(2, 1, 0.7); sheet.setValue(3, 0, "Employee 2"); sheet.setValue(3, 1, 0.1); sheet.setValue(4, 0, "Employee 3"); sheet.setValue(4, 1, 0.3); sheet.setValue(5, 0, "Employee 4"); sheet.setValue(5, 1, 1.1); sheet.setValue(6, 0, "Employee 5"); sheet.setValue(6, 1, 0.5); sheet.setFormula(2, 2, getFormula("B3")); sheet.setFormula(3, 2, getFormula("B4")); sheet.setFormula(4, 2, getFormula("B5")); sheet.setFormula(5, 2, getFormula("B6")); sheet.setFormula(6, 2, getFormula("B7")); sheet.setRowHeight(0, 40); for (var i = 1; i < 7; i++) { sheet.setRowHeight(i, 30); } sheet.setColumnWidth(0, 100); sheet.setColumnWidth(1, 100); sheet.setColumnWidth(2, 300); sheet.resumePaint(); } function getFormula(range) { return "IF(" + range + ">=0.8,HBARSPARKLINE(" + range + ",\"green\",true," + range + "), " + "IF(" + range + ">=0.6,HBARSPARKLINE(" + range + ",\"blue\",true," + range + "), " + "IF(" + range + ">=0.4,HBARSPARKLINE(" + range + ",\"yellow\",true," + range + "), " + "IF(" + range + ">=0.2,HBARSPARKLINE(" + range + ",\"orange\",true," + range + "), " + "IF(" + range + ">=0,HBARSPARKLINE(" + range + ",\"red\",true," + range + "), HBARSPARKLINE(" + range + ",\"red\") " + ")))))"; } function getVBarFormula(row) { return "=IF((C5:N5>0)=(ROW($C$29:$N$30)=ROW($C$29)),VBARSPARKLINE((C5:N5)/max(abs(C5:N5)),$C$4:$N$4,true,0.8),\"\")".replace(/(C|N)5/g, "$$$1$$" + row); } function initVerticalSparkline(sheet, name) { sheet.suspendPaint(); sheet.name(name); sheet.addSpan(0, 0, 2, 15); sheet.getCell(0, 0).value("The Temperature Variation").font("20px 'Comic Sans MS'").hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center); sheet.getRange(2, 0, 1, 15).foreColor("white").backColor("#C0504D").hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center); sheet.setArray(2, 0, [["City", "", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]]); sheet.setColumnWidth(0, 120); for (var j = 1; j < 14; j++) { sheet.setColumnWidth(j, 40); } var colors = ["#0099FF", "#33FFFF", "#9E0142", "#D53E4F", "#F46D43", "#FDAE61", "#FEE08B", "#E6F598", "#ABDDA4", "#66C2A5", "#3288BD", "#5E4FA2"]; sheet.setArray(3, 2, [colors]); sheet.setRowVisible(3, false); var datas = [ ["Austin", "", 5, 7, 11, 15, 19, 22, 24, 24, 21, 16, 10, 6], ["Buffalo", "", -8, -7, -3, 3, 9, 14, 17, 16, 12, 6, 1, -4], ["Chicago", "", -9, -7, -2, 4, 9, 15, 18, 17, 12, 6, 0, -6], ["Denver", "", -8, -7, -3, 1, 7, 12, 15, 14, 9, 2, -4, -9], ["Houston", "", 6, 8, 11, 15, 20, 23, 24, 24, 21, 16, 11, 7], ["Las Vegas", "", 4, 6, 10, 13, 19, 24, 27, 26, 22, 15, 8, 4], ["Miami", "", 16, 17, 18, 20, 23, 24, 25, 25, 25, 23, 20, 17], ["Minneapolis", "", -14, -11, -4, 3, 9, 15, 18, 17, 11, 4, -3, -11] ]; for (var i = 0; i < datas.length; i++) { var row = 4 + 3 * i; sheet.setArray(row, 0, [datas[i]]); sheet.addSpan(row, 0, 3, 2); sheet.setArrayFormula(row + 1, 2, 2, 12, getVBarFormula(row + 1)); sheet.setRowHeight(row + 1, 30); sheet.setRowHeight(row + 2, 30); } 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; }