異なるシート領域に数式を設定・取得するために、以下のAPIに sheetArea 引数が追加されました。デフォルトの sheetArea は GC.Spread.Sheets.SheetArea.viewport です。
数式を取得する
シート上の異なる領域から数式を取得します。
引数
型
説明
row
number
(必須) 行インデックス
col
number
(必須) 列インデックス
sheetArea
GC.Spread.Sheets.SheetArea
シート領域を指定します。デフォルトは GC.Spread.Sheets.SheetArea.viewport です。
数式を設定する
異なるシート領域に数式を設定する場合は、 "A1:A2"と"Sheet1!A1:A2"は参照するセルが異なる場合があることに注意してください。
数式のセル参照にシート名を付けない場合、数式を設定したシート領域上のセルへの参照として扱われます。このため、ヘッダ領域に設定する数式でシート上のセルを参照する場合は、セルの参照文字列にシート名を付ける必要があります。
引数
型
説明
row
number
(必須) 行インデックス
col
number
(必須) 列インデックス
formula
string
(必須) 指定したセルに設定する数式
sheetArea
GC.Spread.Sheets.SheetArea
シート領域を指定します。デフォルトは GC.Spread.Sheets.SheetArea.viewport です。
ヘッダ領域のデータや計算結果は、同じヘッダ領域内だけで参照することができます。他のシート領域からは参照できません。
次のような参照方法はサポートされません。
他のヘッダ領域で参照する。例えば、行ヘッダ領域を列ヘッダや他のシートのヘッダ領域で参照することはできません。
ヘッダー領域をビューポート領域で参照する。"Sheet1!A1:A2"のようにヘッダー領域への参照を表現する方法はありません。
拡張スパークライン
拡張スパークライン拡張は特殊な数式機能です。詳細は 機能紹介 / スパークライン のデモをご確認ください。
数式をヘッダ領域に設定するのと同様の方法で、スパークラインをヘッダ領域に設定することができます。
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 });
initSpread(spread);
};
function initSpread(spread) {
spread.suspendPaint();
var sheet1 = spread.sheets[0];
var data = [
{"名前":"Student 1","性別":"F","学校":"School A","国語":70,"数学":90,"英語":51,"物理":107},
{"名前":"Student 2","性別":"M","学校":"School D","国語":99,"数学":59,"英語":63,"物理":100},
{"名前":"Student 3","性別":"F","学校":"School A","国語":89,"数学":128,"英語":74,"物理":156},
{"名前":"Student 4","性別":"F","学校":"School D","国語":93,"数学":61,"英語":53,"物理":132},
{"名前":"Student 5","性別":"F","学校":"School A","国語":106,"数学":82,"英語":80,"物理":152},
{"名前":"Student 6","性別":"M","学校":"School A","国語":108,"数学":124,"英語":90,"物理":174},
{"名前":"Student 7","性別":"F","学校":"School C","国語":112,"数学":100,"英語":75,"物理":156},
{"名前":"Student 8","性別":"F","学校":"School C","国語":78,"数学":111,"英語":84,"物理":161},
{"名前":"Student 9","性別":"M","学校":"School C","国語":116,"数学":116,"英語":99,"物理":165},
{"名前":"Student 10","性別":"M","学校":"School B","国語":119,"数学":114,"英語":92,"物理":130},
{"名前":"Student 11","性別":"M","学校":"School C","国語":121,"数学":99,"英語":93,"物理":161},
{"名前":"Student 12","性別":"F","学校":"School B","国語":112,"数学":93,"英語":95,"物理":74},
{"名前":"Student 13","性別":"F","学校":"School D","国語":55,"数学":66,"英語":105,"物理":97},
{"名前":"Student 14","性別":"F","学校":"School B","国語":104,"数学":51,"英語":118,"物理":56},
{"名前":"Student 15","性別":"F","学校":"School D","国語":77,"数学":81,"英語":99,"物理":51},
{"名前":"Student 16","性別":"M","学校":"School B","国語":70,"数学":91,"英語":120,"物理":120},
{"名前":"Student 17","性別":"F","学校":"School B","国語":113,"数学":56,"英語":153,"物理":106},
{"名前":"Student 18","性別":"M","学校":"School A","国語":77,"数学":58,"英語":141,"物理":88},
{"名前":"Student 19","性別":"F","学校":"School C","国語":84,"数学":78,"英語":160,"物理":122},
{"名前":"Student 20","性別":"F","学校":"School D","国語":109,"数学":103,"英語":106,"物理":49},
{"名前":"Student 21","性別":"F","学校":"School A","国語":55,"数学":118,"英語":111,"物理":64},
{"名前":"Student 22","性別":"M","学校":"School B","国語":75,"数学":86,"英語":110,"物理":92},
{"名前":"Student 23","性別":"F","学校":"School B","国語":66,"数学":67,"英語":99,"物理":114},
{"名前":"Student 24","性別":"F","学校":"School D","国語":123,"数学":88,"英語":124,"物理":124},
{"名前":"Student 25","性別":"M","学校":"School A","国語":90,"数学":84,"英語":154,"物理":68},
{"名前":"Student 26","性別":"M","学校":"School B","国語":124,"数学":95,"英語":101,"物理":68},
{"名前":"Student 27","性別":"F","学校":"School C","国語":91,"数学":79,"英語":116,"物理":56},
{"名前":"Student 28","性別":"F","学校":"School D","国語":119,"数学":120,"英語":134,"物理":89},
{"名前":"Student 29","性別":"M","学校":"School A","国語":116,"数学":123,"英語":174,"物理":65},
{"名前":"Student 30","性別":"F","学校":"School B","国語":61,"数学":73,"英語":171,"物理":90}
];
sheet1.autoGenerateColumns = true;
sheet1.setDataSource(data);
sheet1.getRange(-1, 0, -1, 7).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
sheet1.setValue(0, 0, "学生の学年統計", GC.Spread.Sheets.SheetArea.colHeader);
sheet1.addSpan(0, 0, 1, 7, GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setRowCount(4, 1);
sheet1.setColumnWidth(0, 80);
sheet1.setColumnWidth(2, 120);
sheet1.setColumnWidth(3, 200);
sheet1.setColumnWidth(4, 200);
sheet1.setColumnWidth(5, 200);
sheet1.setColumnWidth(6, 200);
sheet1.setValue(1, 0, '名前', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 1, '性別', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 2, '学校', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 3, '国語', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 4, '数学', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 5, '英語', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 6, '物理', GC.Spread.Sheets.SheetArea.colHeader);
// Chart by using sparklineEx formulas in header
sheet1.addSpan(2, 0, 1, 2, GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setRowHeight(2, 100, GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 0, 'PIESPARKLINE(COUNTIF(Sheet1!B:B,"F")/ROWS(Sheet1!B1:B30),"#33689e","#e91e63")', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 2, 'COLUMNSPARKLINE(Sheet2!B1:B4,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 3, 'COLUMNSPARKLINE(Sheet1!D1:D30,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 4, 'COLUMNSPARKLINE(Sheet1!E1:E30,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 5, 'COLUMNSPARKLINE(Sheet1!F1:F30,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(2, 6, 'COLUMNSPARKLINE(Sheet1!G1:G30,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader);
// Data aggregation by using built-in function formulas in header
sheet1.addSpan(3, 0, 1, 2, GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 0, 'CEILING.MATH(SUMPRODUCT(1/COUNTIF(Sheet1!A1:A30,Sheet1!A1:A30)))&" Students"', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 2, '"From "&CEILING.MATH(SUMPRODUCT(1/COUNTIF(Sheet1!C1:C30,Sheet1!C1:C30)))&" Schools"', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 3, '"Min: "&MIN(Sheet1!D:D)&" Avg: "&ROUND(AVERAGE(Sheet1!D:D),0)&" Max: "&MAX(Sheet1!D:D)', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 4, '"Min: "&MIN(Sheet1!E:E)&" Avg: "&ROUND(AVERAGE(Sheet1!E:E),0)&" Max: "&MAX(Sheet1!E:E)', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 5, '"Min: "&MIN(Sheet1!F:F)&" Avg: "&ROUND(AVERAGE(Sheet1!F:F),0)&" Max: "&MAX(Sheet1!F:F)', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setFormula(3, 6, '"Min: "&MIN(Sheet1!G:G)&" Avg: "&ROUND(AVERAGE(Sheet1!G:G),0)&" Max: "&MAX(Sheet1!G:G)', GC.Spread.Sheets.SheetArea.colHeader);
var sheet2 = spread.sheets[1];
sheet2.setValue(0, 0, 'School A');
sheet2.setValue(1, 0, 'School B');
sheet2.setValue(2, 0, 'School C');
sheet2.setValue(3, 0, 'School D');
sheet2.setFormula(0, 1, 'COUNTIF(Sheet1!C1:C30,A1)');
sheet2.setFormula(1, 1, 'COUNTIF(Sheet1!C1:C30,A2)');
sheet2.setFormula(2, 1, 'COUNTIF(Sheet1!C1:C30,A3)');
sheet2.setFormula(3, 1, 'COUNTIF(Sheet1!C1:C30,A4)');
spread.resumePaint();
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="spreadjs culture" content="ja-jp" />
<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" class="sample-spreadsheets"></div>
</div></body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: 100%;
height: 100%;
overflow: hidden;
float: left;
border: 1px solid gray;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
