表头公式 | 功能示例 | SpreadJS 在线示例

概述本 Demo 展示了如何在列表头区域使用公式和迷你图，实现学生成绩数据的聚合统计和可视化展示。Demo 通过在表头区域设置多种公式，展示了学生性别比例饼图、各科成绩分布柱状图以及统计数据（总人数、学校数量、各科成绩的最小/平均/最大值）。实现思路加载学生成绩数据到工作表（使用 setDataSource 绑定数据源）设置列表头区域为 4 行，每行展示不同内容第 0 行设置标题并合并单元格第 1 行设置列标题（姓名、性别、学校、各科名称）第 2 行使用迷你图公式展示数据可视化（饼图显示性别比例、柱状图显示成绩分布）第 3 行使用聚合公式计算统计数据（学生总数、学校数量、各科成绩统计）代码解析设置列表头区域行数 setRowCount(4, 1) 将列表头区域设置为 4 行。参数 1 表示 SheetArea.colHeader（列标题区域）。这样可以在表头区域展示多行内容。设置标题并合并单元格在列表头区域第 0 行设置标题文本，并使用 addSpan 合并第 0 行的第 0-6 列为一个单元格。所有操作都指定了 SheetArea.colHeader 参数。使用迷你图公式可视化数据在列表头第 2 行使用迷你图公式。PIESPARKLINE 创建饼图显示男女学生比例，COLUMNSPARKLINE 创建柱状图展示各科成绩的分布情况。关键点是通过 Sheet1!D:D 引用视图区域的数据列。使用聚合公式统计数据在列表头第 3 行使用内置函数公式进行数据聚合。公式中使用 COUNTA、MIN、MAX、AVERAGE、ROUND 等函数计算统计数据，并通过字符串连接符 & 组合显示结果。运行效果表格顶部显示"学生成绩统计"标题，占据整行第二行显示性别比例饼图和各科成绩柱状图，直观展示数据分布第三行显示统计信息：学生总数（30 名学生）、学校数量（4 所学校）以及各科成绩的最小值、平均值、最大值滚动表格数据时，表头区域的公式和图表始终可见所有公式都会随着数据变化自动更新计算结果 API 参考 setFormula 方法 row：行索引 col：列索引 formula：公式字符串 sheetArea：可选，指定工作表区域，默认为 GC.Spread.Sheets.SheetArea.viewport（视图区域）。设置为 GC.Spread.Sheets.SheetArea.colHeader 时，公式将设置到列标题区域。 setRowCount 方法 count：行数 sheetArea：可选，指定工作表区域。参数值为 1 时表示列标题区域（SheetArea.colHeader）。注意事项表头区域中的公式引用视图区域数据时，必须使用工作表名称前缀（如 Sheet1!A:A），否则会引用表头区域自身的数据。表头区域的数据不能被其他工作表区域引用。

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