水平バー/垂直バースパークライン

水平バーおよび垂直バースパークラインを使用すると、データの変動や範囲を示すことができます。

<p>数式にHBarSparklineまたはVBarSparkline関数を使用することで、水平バー/垂直バースパークラインを作成できます。構文は次のとおりです。<strong>=HBARSPARKLINE(value, colorScheme?)</strong> もしくは <strong>=VBARSPARKLINE(value, colorScheme?)</strong></p> <p>有効なパラメータは次のとおりです。</p> <p><strong>value</strong>: バーの長さを表す数値または参照。「0.3」、「A1」など。値が100%を超える場合、または-100%を下回る場合は、矢印が表示されます。</p> <p><strong>colorScheme</strong>: (オプション)バーの色を表す文字列。デフォルト値は「gray」です。</p> <p><strong>axisVisible</strong>: (オプション)軸を表示するかどうかを表すブール値。デフォルト値はtrueです。</p> <p><strong>barHeight</strong>: (オプション)セルの高さに応じてバーの高さをパーセンテージで表す数値。0から1までの範囲内である必要があります。</p> <p><strong>barWidth</strong>: (オプション)セルの幅に応じてバーの幅をパーセンテージで表す数値。0から1までの範囲内である必要があります。</p> <p>HBarSparkline: 正の値を表すバーはセルの左端から、負の値を表すバーはセルの右端から開始します。</p> <p>VBarSparkline: 正の値を表すバーはセルの下端から、負の値を表すバーはセルの上端から開始します。</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, 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 name="spreadjs culture" content="ja-jp" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/ja/purejs/node_modules/@grapecity/spread-sheets-resources-ja/dist/gc.spread.sheets.resources.ja.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; }