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.
<template>
<div class="sample-tutorial">
<gc-spread-sheets class="sample-spreadsheets" @workbookInitialized="initSpread">
</gc-spread-sheets>
<div id="optionContainer" class="optionContainer">
<div>
<input type="button" value="Group" id="groupButton" @click="groupCallback" />
<input type="button" value="RemoveGroup" id="removeGroupButton" @click="removeGroupCallback" />
</div>
<div>
<label>
<input type="checkbox" id="detailColumnsVisibleCheckbox"
@change="detailColumnsVisibleCallback($event)" />Detail
Columns Visible
</label>
</div>
<div>
<label>
Group Outline Position
<select id="groupOutlinePositionSelect" @change="groupOutlinePositionCallback($event)">
<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"
@change="expandGroupByCarrierNameCallback($event)" />Expand/Collapse "Carrier Name"
</label>
</div>
<div>
<label>
<input type="checkbox" checked class="expandGroupCheckbox" group-field="year"
@change="expandGroupByYearCallback($event)" />Expand/Collapse "Year"
</label>
</div>
<div>
<label>
<input type="checkbox" class="expandGroupCheckbox" group-field="month"
@change="expandGroupByMonthCallback($event)" />Expand/Collapse "Month"
</label>
</div>
</div>
</div>
</template>
<script setup>
import GC from "@mescius/spread-sheets";
import { ref, toRaw } from "vue";
import "@mescius/spread-sheets-tablesheet";
import "@mescius/spread-sheets-vue";
let spreadRef;
function initSpread(spread) {
spreadRef = spread;
spread.suspendPaint();
spread.clearSheets();
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();
}
function groupCallback() {
let sheet = spreadRef.getActiveSheetTab();
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: 80,
style: { foreColor: "green", formatter: "#,##0" }
},
{
caption: "Delayed",
formula: "=SUM([arr_del15])",
width: 80,
style: { foreColor: "orange", formatter: "#,##0" }
},
{
caption: "Cancelled",
formula: "=SUM([arr_cancelled])",
width: 90,
style: { foreColor: "red", formatter: "#,##0" }
},
{
caption: "Diverted",
formula: "=SUM([arr_diverted])",
width: 80,
style: { foreColor: "blue", formatter: "#,##0" }
}
],
},
]);
}
function removeGroupCallback() {
let sheet = spreadRef.getActiveSheetTab();
sheet.removeGroupBy();
}
function detailColumnsVisibleCallback($event) {
let sheet = spreadRef.getActiveSheetTab();
sheet.detailColumnsVisible($event.currentTarget.checked);
}
function groupOutlinePositionCallback($event) {
let sheet = spreadRef.getActiveSheetTab();
sheet.groupOutlinePosition(+$event.currentTarget.value);
}
function expandGroupByCarrierNameCallback($event) {
let sheet = spreadRef.getActiveSheetTab();
sheet.expandGroup("carrier_name", $event.currentTarget.checked);
}
function expandGroupByYearCallback($event) {
let sheet = spreadRef.getActiveSheetTab();
sheet.expandGroup("year", $event.currentTarget.checked);
}
function expandGroupByMonthCallback($event) {
let sheet = spreadRef.getActiveSheetTab();
sheet.expandGroup("month", $event.currentTarget.checked);
}
function getBaseApiUrl() {
return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api';
}
</script>
<style scoped>
#app {
height: 100%;
}
.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;
}
</style>
<!DOCTYPE html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<title>SpreadJS VUE</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/en/vue3/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/spread/source/data/airline_delay_causes.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/vue3/node_modules/systemjs/dist/system.src.js"></script>
<script src="./systemjs.config.js"></script>
<script src="./compiler.js" type="module"></script>
<script>
var System = SystemJS;
System.import("./src/app.js");
System.import('$DEMOROOT$/en/lib/vue3/license.js');
</script>
</head>
<body>
<div id="app"></div>
</body>
</html>
(function (global) {
SystemJS.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
packageConfigPaths: [
'./node_modules/*/package.json',
"./node_modules/@mescius/*/package.json",
"./node_modules/@babel/*/package.json",
"./node_modules/@vue/*/package.json"
],
map: {
'vue': "npm:vue/dist/vue.esm-browser.js",
'tiny-emitter': 'npm:tiny-emitter/index.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
"systemjs-babel-build": "npm:systemjs-plugin-babel/systemjs-babel-browser.js",
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-vue': 'npm:@mescius/spread-sheets-vue/index.js',
'@mescius/spread-sheets-tablesheet': 'npm:@mescius/spread-sheets-tablesheet/index.js'
},
meta: {
'*.css': { loader: 'systemjs-plugin-css' },
'*.vue': { loader: "../plugin-vue/index.js" }
}
});
})(this);
