数式にSpreadSparkline関数を使用することで、スプレッドスパークラインを作成できます。構文は次のとおりです。
=SPREADSPARKLINE(points, showAverage?, scaleStart?, scaleEnd?, style?, colorScheme?, vertical?)
有効なパラメータは次のとおりです。
points:
表示するすべての値を保持するセル範囲を表す参照。「A1:A10」など。
showAverage:（オプション）
平均値を表示するかどうかを表すブール値。デフォルト値はfalseです。
scaleStart:（オプション）
スパークラインの下限を表す数値または参照。デフォルト値は、すべての値の最小値です。
scaleEnd:（オプション）
パークラインの上限を表す数値または参照。デフォルト値は、すべての値の最大値です。
style:（オプション）
スプレッドスパークラインのスタイルを表す数値参照。次のいずれかです。
1: Stacked - 中央から2方向に向かって直線を描画
2: Spread - 中央から2方向へドットを描画
3: Jitter - ランダムな位置にドットを描画
4: Poles（既定値）- 一方向から他方向に向かって直線を描画
5: StackedDots - 一方向から他方向に向かってドットを描画
6: Stripe - 同じ長さの直線を描画
colorScheme:（オプション）
スパークラインのボックスの色を表す文字列。デフォルト値は「#646464」です。
vertical:（オプション）
スパークラインを垂直に表示するかどうかを表すブール値。デフォルト値はfalseです。
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) {
var c, r;
sheet.suspendPaint();
sheet.name(name);
sheet.getCell(0, 0).value("USA State-Wide Weather Averages").font("20px Arial").vAlign(spreadNS.VerticalAlign.center);
sheet.setArray(1, 0, [["State", "Temperature","Precipitation", "Snow (days)", "Sun (clear days)", "Morning Humidity", "Afternoon Humidity"]]);
for (c = 0; c <= 6; c++) {
sheet.setColumnWidth(c, 130);
}
sheet.setRowHeight(0, 40);
sheet.setRowHeight(3, 40);
sheet.setArray(2, 1, [["Style: Stacked (1)", "Style: Spread (2)", "Style: Jitter (3)",
"Style: Poles (4)", "Style: StackedDots (5)", "Style: Stripe (6)"]]);
sheet.setArray(4, 0, [
["Alabama",62.8,58.3,0.8,99,0.84,0.52],["Alaska",26.6,22.5,48.0,61,0.77,0.64],
["Arizona",60.3,13.6,0.3,193,0.53,0.25],["Arkansas",60.5,50.6,3.8,123,0.85,0.49],
["California",59.4,22.2,0.0,146,0.76,0.62],["Colorado",45.2,15.9,19.1,136,0.60,0.35],
["Connecticut",49.1,50.3,20.1,82,0.79,0.52],["Delaware",55.3,45.7,11.8,97,0.79,0.54],
["Florida",70.8,54.5,0.0,101,0.87,0.57],["Georgia",63.6,50.7,0.7,112,0.86,0.50],
["Hawaii",70.0,63.7,0.0,90,0.71,0.56],["Idaho",44.4,18.9,19.4,120,0.68,0.41],
["Illinois",51.8,39.2,19.9,95,0.83,0.58],["Indiana",51.7,41.7,23.5,88,0.83,0.58],
["Iowa",47.8,34.0,26.2,105,0.78,0.56],["Kansas",54.3,28.9,10.0,128,0.80,0.50],
["Kentucky",55.6,48.9,11.8,93,0.79,0.55],["Louisiana",66.4,60.1,0.1,101,0.87,0.61],
["Maine",41.0,42.2,27.8,101,0.82,0.61],["Maryland",54.2,44.5,9.6,105,0.77,0.52],
["Massachusetts",47.9,47.7,22.4,98,0.75,0.59],["Michigan",44.5,32.8,44.7,71,0.84,0.61],
["Minnesota",41.2,27.3,37.3,95,0.78,0.55],["Mississippi",63.4,59.0,0.6,111,0.91,0.54],
["Missouri",54.5,42.2,11.5,115,0.82,0.53],["Montana",42.7,15.3,32.9,82,0.71,0.45],
["Nebraska",48.8,23.6,18.3,117,0.82,0.53],["Nevada",49.9,9.5,16.3,158,0.71,0.32],
["New Hampshire",43.8,43.4,29.8,90,0.84,0.53],["New Jersey",52.7,47.1,8.7,94,0.83,0.59],
["New Mexico",53.4,14.6,9.5,167,0.60,0.29],["New York",45.4,41.8,66.4,63,0.82,0.61],
["North Carolina",59.1,50.3,3.9,109,0.83,0.52],["North Dakota",40.5,17.8,45.2,93,0.80,0.51],
["Ohio",50.8,39.1,30.4,72,0.80,0.57],["Oklahoma",59.6,36.5,5.6,139,0.79,0.48],
["Oregon",48.4,27.4,2.2,68,0.85,0.59],["Pennsylvania",48.8,42.9,19.4,87,0.77,0.54],
["Rhode Island",50.1,47.9,18.7,98,0.78,0.57],["South Carolina",62.5,49.8,0.3,115,0.86,0.49],
["South Dakota",45.2,20.1,30.1,104,0.83,0.53],["Tennessee",57.7,54.2,6.2,102,0.84,0.53],
["Texas",64.9,28.9,1.3,135,0.82,0.49],["Utah",48.6,12.2,34.8,125,0.67,0.43],
["Vermont",42.9,42.7,54.4,58,0.77,0.58],["Virginia",55.2,44.3,6.1,100,0.84,0.52],
["Washington",48.3,38.4,3.0,58,0.83,0.62],["West Virginia",51.8,45.2,37.7,60,0.83,0.59],
["Wisconsin",43.2,32.6,38.7,89,0.84,0.58],["Wyoming",42.0,12.9,36.7,114,0.63,0.43]
]);
for (c = 1; c <= 6; c++) {
var columnChar = String.fromCharCode(65 + c);
var styleType = c;
sheet.setFormula(3, c, "=SPREADSPARKLINE(" + columnChar + "5:" + columnChar + "54,TRUE,,,"+ styleType + ",\"#82BC00\")");
}
sheet.getRange(4,1,50,1).formatter("0 °F");
sheet.getRange(4,2,50,1).formatter("0 in");
sheet.getRange(4,5,50,1).formatter("0 %");
sheet.getRange(4,6,50,1).formatter("0 %");
sheet.addColumns(6, 1);
sheet.addColumns(5, 1);
sheet.addColumns(4, 1);
sheet.addColumns(3, 1);
sheet.addColumns(2, 1);
sheet.addColumns(1, 1);
sheet.addSpan(0, 0, 1, 13);
sheet.getRange(1, 0, 1, 13).foreColor("white").backColor("#999999").hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center);
for (c = 1; c < 13; c += 2) {
sheet.setColumnWidth(c, 15);
}
sheet.resumePaint();
}
function initVerticalSparkline(sheet, name) {
var c, r;
sheet.suspendPaint();
sheet.name(name);
sheet.getCell(0, 0).value("USA State-Wide Weather Averages").font("20px Arial").vAlign(spreadNS.VerticalAlign.center);
for (c = 0; c <= 5; c++) {
sheet.setColumnWidth(c, 130);
}
sheet.setRowHeight(0, 40);
sheet.setRowHeight(3, 100);
sheet.setArray(1, 0, [["Temperature","Precipitation", "Snow (days)", "Sun (clear days)", "Morning Humidity", "Afternoon Humidity"],
["Style: Stacked (1)", "Style: Spread (2)", "Style: Jitter (3)",
"Style: Poles (4)", "Style: StackedDots (5)", "Style: Stripe (6)"]]);
sheet.setFormula(3, 0, '=SPREADSPARKLINE(Horizontal!C5:C54,TRUE,,,1,"#82BC00",TRUE)');
sheet.setFormula(3, 1, '=SPREADSPARKLINE(Horizontal!E5:E54,TRUE,,,2,"#82BC00",TRUE)');
sheet.setFormula(3, 2, '=SPREADSPARKLINE(Horizontal!G5:G54,TRUE,,,3,"#82BC00",TRUE)');
sheet.setFormula(3, 3, '=SPREADSPARKLINE(Horizontal!I5:I54,TRUE,,,4,"#82BC00",TRUE)');
sheet.setFormula(3, 4, '=SPREADSPARKLINE(Horizontal!K5:K54,TRUE,,,5,"#82BC00",TRUE)');
sheet.setFormula(3, 5, '=SPREADSPARKLINE(Horizontal!M5:M54,TRUE,,,6,"#82BC00",TRUE)');
sheet.addColumns(5, 1);
sheet.addColumns(4, 1);
sheet.addColumns(3, 1);
sheet.addColumns(2, 1);
sheet.addColumns(1, 1);
for (c = 1; c < 11; c += 2) {
sheet.setColumnWidth(c, 15);
}
sheet.addSpan(0, 0, 1, 11);
sheet.getRange(1, 0, 1, 11).foreColor("white").backColor("#999999").hAlign(spreadNS.HorizontalAlign.center).vAlign(spreadNS.VerticalAlign.center);
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/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/ja/purejs/node_modules/@mescius/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;
}
