自定义分组配置

概述本 Demo 展示了 TableSheet 分组功能的高级配置，包括多级分组、自定义汇总字段、控制明细列显示、调整分组大纲位置以及动态展开折叠分组。Demo 使用航空公司航班延误数据，按航空公司名称、年份和月份进行三级分组，并在月份级别添加了到达、延误、取消和改航航班的汇总统计。实现思路初始化数据管理器和数据表，加载航空公司航班延误数据创建 TableSheet 并配置视图，绑定数据列使用 groupBy 方法设置三级分组，并为月分组添加四个汇总字段通过 detailColumnsVisible(false) 隐藏明细列，只显示分组和汇总信息通过 expandGroup("month", false) 折叠月份分组，优化初始显示效果添加交互控件，允许用户动态调整分组配置和显示选项代码解析设置多级分组和汇总字段这段代码设置了三级分组。groupBy 方法接收一个分组选项数组，每个分组通过 field 指定分组字段，caption 设置显示标题。最内层的月份分组通过 summaryFields 定义了四个汇总列，每个汇总列使用 formula 属性设置计算公式，通过 style 属性设置显示样式（前景色和数字格式）。隐藏明细列 detailColumnsVisible 方法控制分组后是否显示明细列。设置为 false 时，只显示分组列和汇总列，不显示原始数据的明细列，使界面更简洁。折叠指定分组 expandGroup 方法用于展开或折叠指定字段的分组。第一个参数是分组字段名称，第二个参数是布尔值，true 表示展开，false 表示折叠。这里将月份分组折叠，用户可以通过界面控件手动展开。控制分组大纲位置 groupOutlinePosition 方法控制分组大纲的显示位置。GroupOutlinePosition 枚举提供四个选项：none（不显示）、groupCell（显示在分组单元格）、rowHeader（显示在行标题）、allGroupCell（显示在所有分组单元格）。运行效果点击"分组"按钮，表格会按航空公司名称、年份和月份进行三级分组，月份分组下显示四个彩色汇总列默认情况下，明细列被隐藏，只显示分组和汇总信息勾选"显示明细列"复选框可以切换明细列的显示状态通过"分组大纲位置"下拉框可以调整分组大纲的显示位置或完全隐藏勾选"展开/折叠"复选框可以控制各级分组的展开折叠状态点击"取消分组"按钮可以移除所有分组设置 API 参考 groupBy 方法 options: 分组选项数组每个分组选项包含：field（分组字段）、caption（标题）、width（宽度）、style（样式）、summaryFields（汇总字段数组）汇总字段选项包含：caption、formula、width、style detailColumnsVisible 方法 value: 布尔值，表示明细列的可见性返回值：当前明细列的可见性 expandGroup 方法 field: 分组字段名称 expand: 布尔值，true 表示展开，false 表示折叠 groupOutlinePosition 方法 value: GroupOutlinePosition 枚举值返回值：当前分组大纲位置

/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
window.onload = function() {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 });
initSpread(spread);
};
function initSpread(spread) {
spread.suspendPaint();
spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;
//init a data manager
var dataManager = spread.dataManager();
var myTable = dataManager.addTable("myTable", {
data: airline_delay_causes,
schema: {
type: "csv",
columns: {
year: { dataType: "number" },
month: { dataType: "number" },
arr_flights: { dataType: "number" },
arr_del15: { dataType: "number" },
carrier_ct: { dataType: "number" },
weather_ct: { dataType: "number" },
nas_ct: { dataType: "number" },
security_ct: { dataType: "number" },
late_aircraft_ct: { dataType: "number" },
arr_cancelled: { dataType: "number" },
arr_diverted: { dataType: "number" }
}
}
});
//init a table sheet
var sheet = spread.addSheetTab(0, "TableSheet1", GC.Spread.Sheets.SheetType.tableSheet);    
sheet.setDefaultRowHeight(100, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = false; //hide new row
//bind a view to the table sheet
myTable.fetch().then(function () {
var myView = myTable.addView("myView",
[
{ value: "year", caption: "Year", width: 80 },
{ value: "month", caption: "Month", width: 100 },
{ value: "carrier_name", caption: "Carrier name", width: 250 },
{ value: "airport_name", caption: "Airport Name", width: "*" },
{ value: "arr_flights", caption: "Number of flights which arrived at the airport", width: "*" },
{ value: "arr_del15", caption: "Number of flights delayed (>= 15minutes late)", width: "*" },
{ value: "carrier_ct", caption: "Number of flights delayed due to air carrier", width: "*" },
{ value: "weather_ct", caption: "Number of flights delayed due to weather", width: "*" },
{ value: "nas_ct", caption: "Number of flights delayed due to National Aviation System", width: "*" },
{ value: "security_ct", caption: "Number of flights delayed due to security", width: "*" },
{ value: "late_aircraft_ct", caption: "Number of flights delayed due to a previous flight using the same aircraft being late", width: "*" },
{ value: "arr_cancelled", caption: "Number of cancelled flights", width: "*" },
{ value: "arr_diverted", caption: "Number of diverted flights", width: "*" }
]
);
spread.suspendPaint();
sheet.setDataView(myView);
groupCallback();
sheet.detailColumnsVisible(false);
sheet.expandGroup("month", false);
spread.resumePaint();
});
spread.resumePaint();
var groupCallback = function () {
sheet.groupBy([
{
field: "carrier_name", caption: "Carrier Name"
},
{
field: "year", caption: "Year"
},
{
field: "month", caption: "Month", width:100, summaryFields: [
{
caption: "Arrived",
formula: "=SUM([arr_flights])",
width: 90,
style: { foreColor: "green", formatter: "#,##0"}
},
{
caption: "Delayed",
formula: "=SUM([arr_del15])",
width: 90,
style: { foreColor: "orange", formatter: "#,##0"}
},
{
caption: "Cancelled",
formula: "=SUM([arr_cancelled])",
width: 100,
style: { foreColor: "red", formatter: "#,##0"}
},
{
caption: "Diverted",
formula: "=SUM([arr_diverted])",
width: 90,
style: { foreColor: "blue", formatter: "#,##0"}
}
]
}
]);
};
var groupButton = document.getElementById("groupButton");
groupButton.addEventListener("click", groupCallback);
var removeGroupButton = document.getElementById("removeGroupButton");
removeGroupButton.addEventListener("click", function () {
sheet.removeGroupBy();
});
var detailColumnsVisibleCheckbox = document.getElementById("detailColumnsVisibleCheckbox");
detailColumnsVisibleCheckbox.addEventListener("change", function () {
sheet.detailColumnsVisible(detailColumnsVisibleCheckbox.checked);
});
var groupOutlinePositionSelect = document.getElementById("groupOutlinePositionSelect");
groupOutlinePositionSelect.addEventListener("change", function () {
sheet.groupOutlinePosition(+groupOutlinePositionSelect.value);
});
var expandGroupCheckboxes = document.getElementsByClassName("expandGroupCheckbox");
for (var i = 0; i < expandGroupCheckboxes.length; i++) {
var element = expandGroupCheckboxes[i];
element.addEventListener("change", function () {
sheet.expandGroup(this.getAttribute("group-field"), this.checked);
});
}
}

<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta name="spreadjs culture" content="zh-cn" />
<meta charset="utf-8" />
<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">
<!-- Promise Polyfill for IE, https://www.npmjs.com/package/promise-polyfill -->
<script src="https://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script>
<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-tablesheet/dist/gc.spread.sheets.tablesheet.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/data/airline_delay_causes.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 id="optionContainer" class="optionContainer">
<div>
<input type="button" value="分组" id="groupButton" />
<input type="button" value="取消分组" id="removeGroupButton" />
</div>
<div>
<label>
<input type="checkbox" id="detailColumnsVisibleCheckbox" />显示明细列
</label>
</div>
<div>
<label>
分组大纲位置
<select id="groupOutlinePositionSelect">
<option value="0">无</option>
<option value="1" selected>分组单元格</option>
<option value="2">行标题</option>
<option value="3">全部分组单元格</option>
</select>
</label>
</div>
<div>
<label>
<input type="checkbox" checked class="expandGroupCheckbox"
group-field="carrier_name" />展开/折叠"航空公司名称"
</label>
</div>
<div>
<label>
<input type="checkbox" checked class="expandGroupCheckbox" group-field="year" />展开/折叠
"年份"
</label>
</div>
<div>
<label>
<input type="checkbox" class="expandGroupCheckbox" group-field="month" />展开/折叠"月份"
</label>
</div>
</div>
</div>
</html>

.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.optionContainer {
float: right;
width: 280px;
}
.optionContainer div {
margin: 10px;
}
label {
font-size: 13.3px;
}

SpreadJS 在线示例