TableSheet can be grouped by multiple group fields.
After TableSheet is grouped, the detail columns can be hidden.
After TableSheet is grouped, the grouping outline in the row header can be hidden.
After TableSheet is grouped, the groups that are separated by the "month field can be collapsed.
After TableSheet is grouped, the group which level is 2 and start index is 0 could be collapsed.
/*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 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/@mescius/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$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.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;
}
