表头公式

将公式和迷你图(SpreadJS支持的一组特殊公式)添加到表头区域可以使工作表表头更具功能性。 用户可以使用绑定的数据源轻松地在表头区域中进行一些数据聚合和数据可视化。

与冻结的行或列类似,这将允许开发人员在表头区域内显示那些无论用户滚动到何处都始终显示的内容。

为了在不同的视图区域上设置/获取公式, sheetArea 参数已添加到下面的这些API中, 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. 表头区域中的数据或计算结果值可以被表头自己引用。 不能由其他工作表区域引用。 我们不支持以下引用方式: 将表头区域交叉到另一个表头区域。 例如它不能将行表头区域引用到列表头或任何其他工作表的表头区域。 将表头区域引用到视图区域。这表示采用的是表头引用。 迷你图 迷你图是一组特殊公式,您可以在 功能例子 / 迷你图 的演示中找到详细信息。 在表头区域设置迷你图与在表头区域设置公式相同。
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss")); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); spread.options.allowDynamicArray = true; var sheet1 = spread.sheets[0]; var data = [ { "name": "学生 1", "sex": "女", "school": "学校 A", "chinese": 70, "math": 90, "english": 51, "physics": 107 }, { "name": "学生 2", "sex": "男", "school": "学校 D", "chinese": 99, "math": 59, "english": 63, "physics": 100 }, { "name": "学生 3", "sex": "女", "school": "学校 A", "chinese": 89, "math": 128, "english": 74, "physics": 156 }, { "name": "学生 4", "sex": "女", "school": "学校 D", "chinese": 93, "math": 61, "english": 53, "physics": 132 }, { "name": "学生 5", "sex": "女", "school": "学校 A", "chinese": 106, "math": 82, "english": 80, "physics": 152 }, { "name": "学生 6", "sex": "男", "school": "学校 A", "chinese": 108, "math": 124, "english": 90, "physics": 174 }, { "name": "学生 7", "sex": "女", "school": "学校 C", "chinese": 112, "math": 100, "english": 75, "physics": 156 }, { "name": "学生 8", "sex": "女", "school": "学校 C", "chinese": 78, "math": 111, "english": 84, "physics": 161 }, { "name": "学生 9", "sex": "男", "school": "学校 C", "chinese": 116, "math": 116, "english": 99, "physics": 165 }, { "name": "学生 10", "sex": "男", "school": "学校 B", "chinese": 119, "math": 114, "english": 92, "physics": 130 }, { "name": "学生 11", "sex": "男", "school": "学校 C", "chinese": 121, "math": 99, "english": 93, "physics": 161 }, { "name": "学生 12", "sex": "女", "school": "学校 B", "chinese": 112, "math": 93, "english": 95, "physics": 74 }, { "name": "学生 13", "sex": "女", "school": "学校 D", "chinese": 55, "math": 66, "english": 105, "physics": 97 }, { "name": "学生 14", "sex": "女", "school": "学校 B", "chinese": 104, "math": 51, "english": 118, "physics": 56 }, { "name": "学生 15", "sex": "女", "school": "学校 D", "chinese": 77, "math": 81, "english": 99, "physics": 51 }, { "name": "学生 16", "sex": "男", "school": "学校 B", "chinese": 70, "math": 91, "english": 120, "physics": 120 }, { "name": "学生 17", "sex": "女", "school": "学校 B", "chinese": 113, "math": 56, "english": 153, "physics": 106 }, { "name": "学生 18", "sex": "男", "school": "学校 A", "chinese": 77, "math": 58, "english": 141, "physics": 88 }, { "name": "学生 19", "sex": "女", "school": "学校 C", "chinese": 84, "math": 78, "english": 160, "physics": 122 }, { "name": "学生 20", "sex": "女", "school": "学校 D", "chinese": 109, "math": 103, "english": 106, "physics": 49 }, { "name": "学生 21", "sex": "女", "school": "学校 A", "chinese": 55, "math": 118, "english": 111, "physics": 64 }, { "name": "学生 22", "sex": "男", "school": "学校 B", "chinese": 75, "math": 86, "english": 110, "physics": 92 }, { "name": "学生 23", "sex": "女", "school": "学校 B", "chinese": 66, "math": 67, "english": 99, "physics": 114 }, { "name": "学生 24", "sex": "女", "school": "学校 D", "chinese": 123, "math": 88, "english": 124, "physics": 124 }, { "name": "学生 25", "sex": "男", "school": "学校 A", "chinese": 90, "math": 84, "english": 154, "physics": 68 }, { "name": "学生 26", "sex": "男", "school": "学校 B", "chinese": 124, "math": 95, "english": 101, "physics": 68 }, { "name": "学生 27", "sex": "女", "school": "学校 C", "chinese": 91, "math": 79, "english": 116, "physics": 56 }, { "name": "学生 28", "sex": "女", "school": "学校 D", "chinese": 119, "math": 120, "english": 134, "physics": 89 }, { "name": "学生 29", "sex": "男", "school": "学校 A", "chinese": 116, "math": 123, "english": 174, "physics": 65 }, { "name": "学生 30", "sex": "女", "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, "学生成绩统计", 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,"女")/ROWS(Sheet1!B:B),"#33689e","#e91e63")', GC.Spread.Sheets.SheetArea.colHeader); sheet1.setFormula(2, 2, 'COLUMNSPARKLINE(MAP(UNIQUE(TRIMRANGE(Sheet1!C:C)),LAMBDA(name,SUMPRODUCT((Sheet1!C:C=name)*(Sheet1!D:G)))),0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader); sheet1.setFormula(2, 3, 'COLUMNSPARKLINE(Sheet1!D:D,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader); sheet1.setFormula(2, 4, 'COLUMNSPARKLINE(Sheet1!E:E,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader); sheet1.setFormula(2, 5, 'COLUMNSPARKLINE(Sheet1!F:F,0,,0,"{seriesColor:#33689e}")', GC.Spread.Sheets.SheetArea.colHeader); sheet1.setFormula(2, 6, 'COLUMNSPARKLINE(Sheet1!G:G,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, 'COUNTA(Sheet1!A:A)&" 名学生"', GC.Spread.Sheets.SheetArea.colHeader); sheet1.setFormula(3, 2, '"共 "& COUNTA(UNIQUE(Sheet1!C:C)) &" 所学校"', 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); spread.resumePaint(); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="spreadjs culture" content="zh-cn" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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; }