In order to set/get formulas on different sheet area, sheetArea argument has been added to these APIs below, the default sheetArea is GC.Spread.Sheets.SheetArea.viewport.
Get formulas
Get formulas from different sheet area.
Argument
Type
Description
row
number
(Required) The row index.
col
number
(Required) The column index.
sheetArea
GC.Spread.Sheets.SheetArea
The sheet area. Default is GC.Spread.Sheets.SheetArea.viewport.
Set formulas
Set formula to different sheet area, pay attention to the different formula reference string between "A1:A2" and "Sheet1!A1:A2".
If a formula reference string is without sheet name, it will be treated as the sheet area reference your specified on calculation. It means you must invoked a sheet name if you want to refer sheet viewport reference into header area.
Argument
Type
Description
row
number
(Required) The row index.
col
number
(Required) The column index.
formula
string
(Required) The formula to place in the specified cell.
sheetArea
GC.Spread.Sheets.SheetArea
The sheet area. Default is GC.Spread.Sheets.SheetArea.viewport.
The data or calculation result in header area can just be referred by itself. It cannot be referred by other sheet area.
We don't support these reference ways:
Cross header area to another header area. Such as it cannot refer row header area to column header or any other sheet's header area.
Referring header area to viewport area. There is no ambiguity to represent a header reference.
SparklineEx
SparklineEx is a group of special formulas, you can find details in demo Feature / Sparklines.
To set a sparklineEx to header area is same as to set a formula to header area.
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 = [
{"name":"Student 1","sex":"F","school":"School A","chinese":70,"math":90,"english":51,"physics":107},
{"name":"Student 2","sex":"M","school":"School D","chinese":99,"math":59,"english":63,"physics":100},
{"name":"Student 3","sex":"F","school":"School A","chinese":89,"math":128,"english":74,"physics":156},
{"name":"Student 4","sex":"F","school":"School D","chinese":93,"math":61,"english":53,"physics":132},
{"name":"Student 5","sex":"F","school":"School A","chinese":106,"math":82,"english":80,"physics":152},
{"name":"Student 6","sex":"M","school":"School A","chinese":108,"math":124,"english":90,"physics":174},
{"name":"Student 7","sex":"F","school":"School C","chinese":112,"math":100,"english":75,"physics":156},
{"name":"Student 8","sex":"F","school":"School C","chinese":78,"math":111,"english":84,"physics":161},
{"name":"Student 9","sex":"M","school":"School C","chinese":116,"math":116,"english":99,"physics":165},
{"name":"Student 10","sex":"M","school":"School B","chinese":119,"math":114,"english":92,"physics":130},
{"name":"Student 11","sex":"M","school":"School C","chinese":121,"math":99,"english":93,"physics":161},
{"name":"Student 12","sex":"F","school":"School B","chinese":112,"math":93,"english":95,"physics":74},
{"name":"Student 13","sex":"F","school":"School D","chinese":55,"math":66,"english":105,"physics":97},
{"name":"Student 14","sex":"F","school":"School B","chinese":104,"math":51,"english":118,"physics":56},
{"name":"Student 15","sex":"F","school":"School D","chinese":77,"math":81,"english":99,"physics":51},
{"name":"Student 16","sex":"M","school":"School B","chinese":70,"math":91,"english":120,"physics":120},
{"name":"Student 17","sex":"F","school":"School B","chinese":113,"math":56,"english":153,"physics":106},
{"name":"Student 18","sex":"M","school":"School A","chinese":77,"math":58,"english":141,"physics":88},
{"name":"Student 19","sex":"F","school":"School C","chinese":84,"math":78,"english":160,"physics":122},
{"name":"Student 20","sex":"F","school":"School D","chinese":109,"math":103,"english":106,"physics":49},
{"name":"Student 21","sex":"F","school":"School A","chinese":55,"math":118,"english":111,"physics":64},
{"name":"Student 22","sex":"M","school":"School B","chinese":75,"math":86,"english":110,"physics":92},
{"name":"Student 23","sex":"F","school":"School B","chinese":66,"math":67,"english":99,"physics":114},
{"name":"Student 24","sex":"F","school":"School D","chinese":123,"math":88,"english":124,"physics":124},
{"name":"Student 25","sex":"M","school":"School A","chinese":90,"math":84,"english":154,"physics":68},
{"name":"Student 26","sex":"M","school":"School B","chinese":124,"math":95,"english":101,"physics":68},
{"name":"Student 27","sex":"F","school":"School C","chinese":91,"math":79,"english":116,"physics":56},
{"name":"Student 28","sex":"F","school":"School D","chinese":119,"math":120,"english":134,"physics":89},
{"name":"Student 29","sex":"M","school":"School A","chinese":116,"math":123,"english":174,"physics":65},
{"name":"Student 30","sex":"F","school":"School B","chinese":61,"math":73,"english":171,"physics":90}
];
sheet1.autoGenerateColumns = true;
sheet1.setDataSource(data);
sheet1.getRange(-1, 0, -1, 7).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
sheet1.setValue(0, 0, "Student Grade Statistics", 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, 'Name', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 1, 'Sex', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 2, 'School', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 3, 'Chinese', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 4, 'Math', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 5, 'English', GC.Spread.Sheets.SheetArea.colHeader);
sheet1.setValue(1, 6, 'Physics', 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="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/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" 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;
}
