数式にHBarSparklineまたはVBarSparkline関数を使用することで、水平バー/垂直バースパークラインを作成できます。構文は次のとおりです。=HBARSPARKLINE(value, colorScheme?) もしくは =VBARSPARKLINE(value, colorScheme?)
有効なパラメータは次のとおりです。
value: バーの長さを表す数値または参照。「0.3」、「A1」など。値が100%を超える場合、または-100%を下回る場合は、矢印が表示されます。
colorScheme: （オプション）バーの色を表す文字列。デフォルト値は「gray」です。
axisVisible: （オプション）軸を表示するかどうかを表すブール値。デフォルト値はtrueです。
barHeight: （オプション）セルの高さに応じてバーの高さをパーセンテージで表す数値。0から1までの範囲内である必要があります。
barWidth: （オプション）セルの幅に応じてバーの幅をパーセンテージで表す数値。0から1までの範囲内である必要があります。
HBarSparkline: 正の値を表すバーはセルの左端から、負の値を表すバーはセルの右端から開始します。
VBarSparkline: 正の値を表すバーはセルの下端から、負の値を表すバーはセルの上端から開始します。
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;
}
