集算表可按多个组字段进行分组。
在对集算表进行分组后，可以隐藏细节列。
对集算表进行分组后，可以隐藏行头的分组轮廓。
集算表分组后，按 "月 "字段划分的组可以被折叠起来。
在对集算表进行分组后，可以对级别为2、起始索引为0的组进行折叠。
/*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="Group" id="groupButton" />
<input type="button" value="RemoveGroup" id="removeGroupButton" />
</div>
<div>
<label>
<input type="checkbox" id="detailColumnsVisibleCheckbox" />Detail Columns Visible
</label>
</div>
<div>
<label>
Group Outline Position
<select id="groupOutlinePositionSelect">
<option value="0">none</option>
<option value="1" selected>groupCell</option>
<option value="2">rowHeader</option>
<option value="3">groupCellAll</option>
</select>
</label>
</div>
<div>
<label>
<input type="checkbox" checked class="expandGroupCheckbox"
group-field="carrier_name" />Expand/Collapse "Carrier Name"
</label>
</div>
<div>
<label>
<input type="checkbox" checked class="expandGroupCheckbox" group-field="year" />Expand/Collapse
"Year"
</label>
</div>
<div>
<label>
<input type="checkbox" class="expandGroupCheckbox" group-field="month" />Expand/Collapse "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;
}
