Header Formulas

Add formulas and sparklineEx (a group of special formulas supported in SpreadJS) to the header area to quickly highlight important data. Users can also bind these to a datasource to easily provide data aggregation and data visualization features to their users.

Similar to frozen rows or columns, these headers will always be visible to the user when scrolling through the data.

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/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/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; }