Remove
You can click the Remove button (which uses tablesheet.removeRow(rowIndex)
) to remove a row.
Save
You can edit a cell by double-clicking a cell or inputting data directly from your keyboard, and then clicking the Save button (which uses tableSheet.saveRow(rowIndex)
) to save a row.
Reset
You can click the Reset button (which uses tableSheet.resetRow(rowIndex)
) to reset a row. This will restore the row data.
Add
You can edit the template row data by clicking the Edit Fields button, and then clicking the Add button (which uses tableSheet.addRow(rowData)
to add a row.
The added rowData must have the same structure as the data in the data source, such as { "first_name": "Michael", "last_name": "Lu", country: "China" }.
Save All
If you have large changes that need to be saved, you can click the Save All button or use Ctrl+Shift+S to save all of the changes at one time.
Response Error
When there is a server connection error (i.e. HTTP 404, HTTP 500) or a server database error (i.e. duplicated primary key, update failed), TableSheet will show a red alarm icon in the row header and show error tips when you hover over them.
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';
const useState = React.useState;
const tablesheetName = 'MyTableSheet';
export function AppFunc() {
const [spread, setSpread] = useState(null);
const [tablesheet, setTablesheet] = useState(null);
const [state, setState] = useState({
fields: {},
selections: null,
isEdit: false
});
const initSpread = (spread) => {
setSpread(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: employees
});
//init a table sheet
var tablesheet = spread.addSheetTab(0, tablesheetName, GC.Spread.Sheets.SheetType.tableSheet);
tablesheet.options.allowAddNew = false;
setTablesheet(tablesheet);
//bind a view to the table sheet
myTable.fetch().then((data) => {
var view = myTable.addView("myView", [
// { value: "Id", width: 50 },
{ value: "FirstName", width: 100 },
{ value: "LastName", width: 100 },
{ value: "Title", width: 150 },
{ value: "HomePhone", width: 120 },
{ value: "Address", width: 160 },
{ value: "City", width: 100 },
{ value: "State", width: 100 },
{ value: "PostalCode", width: 100 },
{ value: "Region", width: 100 }
]);
tablesheet.setDataView(view);
generateFields(clone({
"FirstName": "",
"LastName": "",
"Title": "",
"HomePhone": "",
"Address": "",
"City": "",
"State": "",
"PostalCode": "",
"Region": ""
}));
});
spread.bind(GC.Spread.Sheets.Events.SelectionChanged, (e, args) => {
setState(st => ({ ...st, selections: args.newSelections }));
});
spread.resumePaint();
}
const removeRow = () => {
traverseSelectionsRowsWithOperation((row) => {
tablesheet.removeRow(row);
});
}
const saveRow = () => {
traverseSelectionsRowsWithOperation((row) => {
tablesheet.saveRow(row);
});
}
const resetRow = () => {
traverseSelectionsRowsWithOperation((row) => {
tablesheet.resetRow(row);
});
}
const saveAllRows = () => {
spread.commandManager().SaveAll.execute(spread, { sheetName: tablesheetName });
}
const editRow = () => {
setState({ ...state, isEdit: !state.isEdit });
}
const addRow = () => {
tablesheet.addRow(state.fields);
}
const generateFields = (data) => {
setState({ ...state, fields: data });
}
const traverseSelectionsRowsWithOperation = (operation) => {
var selections = state.selections;
if (selections) {
selections.sort(function (a, b) { return b.row - a.row; });
for (var i = 0; i < selections.length; i++) {
var selection = selections[i];
var row = selection.row;
var rowCount = selection.rowCount;
for (var r = row + rowCount - 1; r >= row; r--) {
operation(r);
}
}
}
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}></SpreadSheets>
</div>
<div id="options-container" class="options-container">
<div class="option-row"><label>Use these buttons to perform row operations.</label>
</div>
<hr />
<fieldset>
<legend>Active Row Operations</legend>
<div class="field-line">
<input id="remove" type="button" value="Remove" onClick={() => { removeRow() }} />
</div>
<div class="field-line">
<input id="save" type="button" value="Save" onClick={() => { saveRow() }} />
</div>
<div class="field-line">
<input id="reset" type="button" value="Reset" onClick={() => { resetRow() }} />
</div>
</fieldset>
<fieldset>
<legend>Save All Rows</legend>
<div class="field-line">
<input id="save-all" type="button" value="Save All" onClick={() => { saveAllRows() }} />
</div>
</fieldset>
<fieldset>
<legend>Add New Row</legend>
<div class="field-line">
<input type="button" value={state.isEdit ? 'Hide Fields' : 'Edit Fields'} id="toggle" onClick={() => { editRow() }} />
</div>
<div class="field-line" id="fields" style={{ display: state.isEdit ? 'block' : 'none' }}>
<FieldItemsJSX state={state} setState={setState}></FieldItemsJSX>
</div>
<div class="field-line">
<input type="button" value="Add" id="add" onClick={() => { addRow() }} />
</div>
</fieldset>
</div>
</div>
);
}
const FieldItemsJSX = ({ state, setState }) => {
return Object.keys(state.fields).map(field => {
return <div class="field-line">
<label class="field-inline" for={'field_' + field}>{field}<span class="required">{field === "FirstName" || field === "LastName" ? "(*)" : ""}</span></label>
<input class="field-inline" id={'field_' + field} type="text" value={state.fields[field]} onChange={(e) => { let data = Object.assign({}, state.fields, { [field]: e.target.value }); setState({ ...state, fields: data }) }} />
</div>;
});
};
function clone(source) {
var result = {};
for (var key in source) {
result[key] = source[key];
}
return result;
}
/*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;
var tablesheetName = 'MyTableSheet';
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
this.tablesheet = null;
this.state = {
fields: {},
selections: null,
isEdit: false
};
}
render() {
const fieldItemsJSX = () => {
return Object.keys(this.state.fields).map(field => {
return <div class="field-line">
<label class="field-inline" for={'field_' + field}>{field}<span class="required">{field === "FirstName" || field === "LastName" ? "(*)" : ""}</span></label>
<input class="field-inline" id={'field_' + field} type="text" value={this.state.fields[field]} onChange={(e) => { let data = Object.assign({}, this.state.fields, { [field]: e.target.value }); this.setState({ fields: data }) }} />
</div>;
});
};
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}></SpreadSheets>
</div>
<div id="options-container" class="options-container">
<div class="option-row"><label>Use these buttons to perform row operations.</label>
</div>
<hr />
<fieldset>
<legend>Active Row Operations</legend>
<div class="field-line">
<input id="remove" type="button" value="Remove" onClick={() => { this.removeRow() }} />
</div>
<div class="field-line">
<input id="save" type="button" value="Save" onClick={() => { this.saveRow() }} />
</div>
<div class="field-line">
<input id="reset" type="button" value="Reset" onClick={() => { this.resetRow() }} />
</div>
</fieldset>
<fieldset>
<legend>Save All Rows</legend>
<div class="field-line">
<input id="save-all" type="button" value="Save All" onClick={() => { this.saveAllRows() }} />
</div>
</fieldset>
<fieldset>
<legend>Add New Row</legend>
<div class="field-line">
<input type="button" value={this.state.isEdit ? 'Hide Fields' : 'Edit Fields'} id="toggle" onClick={() => { this.editRow() }} />
</div>
<div class="field-line" id="fields" style={{ display: this.state.isEdit ? 'block' : 'none' }}>
{fieldItemsJSX()}
</div>
<div class="field-line">
<input type="button" value="Add" id="add" onClick={() => { this.addRow() }} />
</div>
</fieldset>
</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: employees
});
//init a table sheet
var tablesheet = spread.addSheetTab(0, tablesheetName, GC.Spread.Sheets.SheetType.tableSheet);
tablesheet.options.allowAddNew = false;
this.tablesheet = tablesheet;
//bind a view to the table sheet
myTable.fetch().then((data) => {
var view = myTable.addView("myView", [
// { value: "Id", width: 50 },
{ value: "FirstName", width: 100 },
{ value: "LastName", width: 100 },
{ value: "Title", width: 150 },
{ value: "HomePhone", width: 120 },
{ value: "Address", width: 160 },
{ value: "City", width: 100 },
{ value: "State", width: 100 },
{ value: "PostalCode", width: 100 },
{ value: "Region", width: 100 }
]);
tablesheet.setDataView(view);
this.generateFields(clone({
"FirstName": "",
"LastName": "",
"Title": "",
"HomePhone": "",
"Address": "",
"City": "",
"State": "",
"PostalCode": "",
"Region": ""
}));
});
spread.bind(GC.Spread.Sheets.Events.SelectionChanged, (e, args) => {
this.setState({ selections: args.newSelections });
});
spread.resumePaint();
}
removeRow() {
this.traverseSelectionsRowsWithOperation((row) => {
this.tablesheet.removeRow(row);
});
}
saveRow() {
this.traverseSelectionsRowsWithOperation((row) => {
this.tablesheet.saveRow(row);
});
}
resetRow() {
this.traverseSelectionsRowsWithOperation((row) => {
this.tablesheet.resetRow(row);
});
}
saveAllRows() {
this.spread.commandManager().SaveAll.execute(this.spread, { sheetName: tablesheetName });
}
editRow() {
this.setState({ isEdit: !this.state.isEdit });
}
addRow() {
this.tablesheet.addRow(this.state.fields);
}
generateFields(data) {
this.setState({ fields: data });
}
traverseSelectionsRowsWithOperation(operation) {
var selections = this.state.selections;
if (selections) {
selections.sort(function (a, b) { return b.row - a.row; });
for (var i = 0; i < selections.length; i++) {
var selection = selections[i];
var row = selection.row;
var rowCount = selection.rowCount;
for (var r = row + rowCount - 1; r >= row; r--) {
operation(r);
}
}
}
}
}
function clone(source) {
var result = {};
for (var key in source) {
result[key] = source[key];
}
return result;
}
<!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">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="$DEMOROOT$/spread/source/data/employees.js" type="text/javascript"></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>
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
fieldset {
padding: 6px;
margin: 0;
margin-top: 10px;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
fieldset span,
fieldset input,
fieldset select {
display: inline-block;
text-align: left;
}
fieldset input[type=text] {
width: calc(100% - 58px);
}
fieldset input[type=button] {
width: 100%;
text-align: center;
}
fieldset select {
width: calc(100% - 50px);
}
.field-line {
margin-top: 4px;
}
.field-inline {
display: inline-block;
vertical-align: middle;
}
fieldset label.field-inline {
width: 100px;
}
fieldset input.field-inline {
width: calc(100% - 100px - 12px);
}
.required {
color: red;
font-weight: bold;
}
#fields {
display: none;
}
#fields.show {
display: block;
}
(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);
