Header Formulas

Adding formulas and sparklineEx(a group of special formulas supported in SpreadJS) to the header area can make sheets header more functional. User can use bound datasource to make some data aggregation and data visualiztion in header area easily.

Similar to frozen rows or columns, this would allow developers to display those within headers, which will always be shown no matter where the user scrolls to.

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; }