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.
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
createRoot(document.getElementById('app')).render(<AppFunc />);
// 2. Class Component sample
// createRoot(document.getElementById('app')).render(<App />);
/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-tablesheet";
import { SpreadSheets } from '@mescius/spread-sheets-react';
import './styles.css';
let spread = null;
export function AppFunc() {
const initSpread = (workbook) => {
spread = workbook;
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();
}
const groupCallback = () => {
let sheet = spread.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: 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" }
}
]
}
]);
}
const removeGroupCallback = () => {
let sheet = spread.getActiveSheetTab();
sheet.removeGroupBy();
}
const detailColumnsVisibleCallback = ($event) => {
let sheet = spread.getActiveSheetTab();
sheet.detailColumnsVisible($event.currentTarget.checked);
}
const groupOutlinePositionCallback = ($event) => {
let sheet = spread.getActiveSheetTab();
sheet.groupOutlinePosition(+$event.currentTarget.value);
}
const expandGroupByCarrierNameCallback = ($event) => {
let sheet = spread.getActiveSheetTab();
sheet.expandGroup("carrier_name", $event.currentTarget.checked);
}
const expandGroupByYearCallback = ($event) => {
let sheet = spread.getActiveSheetTab();
sheet.expandGroup("year", $event.currentTarget.checked);
}
const expandGroupByMonthCallback = ($event) => {
let sheet = spread.getActiveSheetTab();
sheet.expandGroup("month", $event.currentTarget.checked);
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
</SpreadSheets>
</div>
<div id="optionContainer" class="optionContainer">
<div>
<input type="button" value="Group" id="groupButton" onClick={() => groupCallback()} />
<input type="button" value="RemoveGroup" id="removeGroupButton" onClick={() => removeGroupCallback()} />
</div>
<div>
<label>
<input type="checkbox" id="detailColumnsVisibleCheckbox" onChange={(e) => detailColumnsVisibleCallback(e)} />Detail Columns Visible
</label>
</div>
<div>
<label>
Group Outline Position
<select id="groupOutlinePositionSelect" onChange={(e) => groupOutlinePositionCallback(e)}>
<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" defaultChecked="checked" class="expandGroupCheckbox" group-field="carrier_name" onChange={(e) => expandGroupByCarrierNameCallback(e)} />Expand/Collapse "Carrier Name"
</label>
</div>
<div>
<label>
<input type="checkbox" defaultChecked="checked" class="expandGroupCheckbox" group-field="year" onChange={(e) => expandGroupByYearCallback(e)} />Expand/Collapse "Year"
</label>
</div>
<div>
<label>
<input type="checkbox" class="expandGroupCheckbox" group-field="month" onChange={(e) => expandGroupByMonthCallback(e)} />Expand/Collapse "Month"
</label>
</div>
</div>
</div>
);
}
/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-tablesheet";
import { SpreadSheets } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
</SpreadSheets>
</div>
<div id="optionContainer" class="optionContainer">
<div>
<input type="button" value="Group" id="groupButton" onClick={() => this.groupCallback()} />
<input type="button" value="RemoveGroup" id="removeGroupButton" onClick={() => this.removeGroupCallback()} />
</div>
<div>
<label>
<input type="checkbox" id="detailColumnsVisibleCheckbox" onChange={(e) => this.detailColumnsVisibleCallback(e)} />Detail Columns Visible
</label>
</div>
<div>
<label>
Group Outline Position
<select id="groupOutlinePositionSelect" onChange={(e) => this.groupOutlinePositionCallback(e)}>
<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" defaultChecked="checked" class="expandGroupCheckbox" group-field="carrier_name" onChange={(e) => this.expandGroupByCarrierNameCallback(e)} />Expand/Collapse "Carrier Name"
</label>
</div>
<div>
<label>
<input type="checkbox" defaultChecked="checked" class="expandGroupCheckbox" group-field="year" onChange={(e) => this.expandGroupByYearCallback(e)} />Expand/Collapse "Year"
</label>
</div>
<div>
<label>
<input type="checkbox" class="expandGroupCheckbox" group-field="month" onChange={(e) => this.expandGroupByMonthCallback(e)} />Expand/Collapse "Month"
</label>
</div>
</div>
</div>
);
}
initSpread(spread) {
this.spread = 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
let _this = this;
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);
_this.groupCallback();
sheet.detailColumnsVisible(false);
sheet.expandGroup("month", false);
spread.resumePaint();
});
spread.resumePaint();
}
groupCallback() {
let sheet = this.spread.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: 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"}
}
]
}
]);
}
removeGroupCallback() {
let sheet = this.spread.getActiveSheetTab();
sheet.removeGroupBy();
}
detailColumnsVisibleCallback($event) {
let sheet = this.spread.getActiveSheetTab();
sheet.detailColumnsVisible($event.currentTarget.checked);
}
groupOutlinePositionCallback($event) {
let sheet = this.spread.getActiveSheetTab();
sheet.groupOutlinePosition(+$event.currentTarget.value);
}
expandGroupByCarrierNameCallback($event) {
let sheet = this.spread.getActiveSheetTab();
sheet.expandGroup("carrier_name", $event.currentTarget.checked);
}
expandGroupByYearCallback($event) {
let sheet = this.spread.getActiveSheetTab();
sheet.expandGroup("year", $event.currentTarget.checked);
}
expandGroupByMonthCallback($event) {
let sheet = this.spread.getActiveSheetTab();
sheet.expandGroup("month", $event.currentTarget.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/react/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>
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app" style="height: 100%;"></div>
</body>
</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 input {
margin-right: 5px;
}
.optionContainer div {
margin: 10px;
}
label {
font-size: 13.3px;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-tablesheet': 'npm:@mescius/spread-sheets-tablesheet/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/cjs/react.production.js',
'react-dom': 'npm:react-dom/cjs/react-dom.production.js',
'react-dom/client': 'npm:react-dom/cjs/react-dom-client.production.js',
'scheduler': 'npm:scheduler/cjs/scheduler.production.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
