By default, the TableSheet will just interact with the local data manager. If you want to sync the changed data with your remote database, just enable the AutoSync or Batch mode first.
This demo uses Batch mode.
AutoSync Mode
This mode is mainly suitable for low frequency data operation scenarios. Once you use buttons in the row action or APIs to interact with the rows, the request with corresponding changes will be initiated and sent to the server immediately.
To enable the AutoSync mode in table initialization:
Batch Mode
This mode is mainly suitable for scenarios where data is frequently manipulated. It will store each row operation in the order, and then package all the changes into a collection and send it to the server at one time in order to save network resources.
To enable the Batch mode and specify a BatchApiUrl in table initialization:
Then you can submit or discard all the changes:
Get Changes
You can also get the changes before save them to server.
Request and Response
Operation
Request Type
Request Data
Response Data
update
POST
The updated data
The updated data
read
GET
No data
The records array
delete
DELETE
The deleted data or data array
No restrictions
create
POST
The inserted data
The inserted data
getColumns
GET
A column array, where each column contains the properties:
The 'field' property is the name of the column.
The 'dataType' property is the data type of the column.
The 'defaultValue' property is the default value of the record in the column.
The 'isPrimaryKey' property is the primary column.
addColumn
POST
The column change with the column and the data be default value of the column
updateColumn
PUT
The column change with the column and the originalColumn
removeColumn
DELETE
The column change with the column
batch
POST
An object array, where each object contains a 'type' property.
This operation type could be 'update', 'insert', 'delete', 'addColumn', 'updateColumn' or 'removeColumn'.
The 'dataItem' property is the current record.
The 'sourceIndex' property is the record index.
The optional 'oldDataItem' property is the original record.
The optional 'column' property is the current column.
The optional 'data' property is the default value of the current added column.
The optional 'originalColumn' property is the original column.
For example:
[ {"type":"addColumn","column":{...}},
{"type":"updateColumn","column":{...}, "originalColumn":{...}},
{"type":"removeColumn","column":{...}},
{"type":"delete","dataItem":{...}, "sourceIndex":5},
{"type":"insert","dataItem":{...}, "sourceIndex":3},
{"type":"update","dataItem":{...}, "oldDataItem":{...}, "oldDataItem":{...}, "sourceIndex":1}]
An object array, where each object contains a 'succeed' property which indicates an operation's success or failure, and an optional 'data' property, which is the current record and only for the 'insert' operation.
For example:
[{"succeed":true}, {"succeed":false}, {"succeed": true},{"succeed":true}, {"succeed":false}, {"succeed": true}]
/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
var tableName = "DefineEmployee";
var baseApiUrl = getBaseApiUrl();
var apiUrl = baseApiUrl + "/" + tableName;
// var apiColumnUrl = baseApiUrl + "/tables/" + tableName + "/columns";
var batchApiUrl = baseApiUrl + "/" + tableName + 'Collection';
var tablesheetName = 'MyTableSheet';
var spread, sheet, view, selections, table;
window.onload = function () {
spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 });
initSpread(spread);
bindEvents();
};
function initSpread(spread) {
spread.suspendPaint();
spread.clearSheets();
spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;
//init a data manager
var dataManager = spread.dataManager();
myTable = dataManager.addTable("myTable", {
remote: {
read: {
url: apiUrl
},
update: {
url: apiUrl,
method: 'PUT'
},
create: {
url: apiUrl
},
delete: {
url: apiUrl
},
// getColumns: {
// url: apiColumnUrl
// },
// addColumn: {
// url: apiColumnUrl,
// method: 'POST'
// },
// updateColumn: {
// url: apiColumnUrl,
// method: 'PUT'
// },
// removeColumn: {
// url: apiColumnUrl,
// method: 'DELETE'
// },
batch: {
url: batchApiUrl
}
},
batch: true,
schema: {
columns: {
"Id": { dataType: "number" },
"LastName": { dataType: "string" },
"FirstName": { dataType: "string" },
"HomePhone": { dataType: "string" },
"Notes": { dataType: "string" }
}
}
});
table = myTable;
//init a table sheet
sheet = spread.addSheetTab(0, tablesheetName, GC.Spread.Sheets.SheetType.tableSheet);
// sheet.options.enableDefineColumn = true;
var rowActions = GC.Spread.Sheets.TableSheet.BuiltInRowActions;
var options = sheet.rowActionOptions();
options.push(
rowActions.removeRow,
rowActions.saveRow,
rowActions.resetRow,
);
sheet.rowActionOptions(options);
//bind a view to the table sheet
myTable.fetch().then(function () {
view = myTable.addView("myView", [
{ value: "Id", width: 50, caption: "ID" },
{ value: "FirstName", width: 100, caption: "First Name" },
{ value: "LastName", width: 100, caption: "Last Name" },
{ value: "HomePhone", width: 120, caption: "Home Phone" },
{ value: "Title", width: 150, caption: "Title" }
]);
sheet.setDataView(view);
});
selections = [{row: 0, rowCount: 1, col: 0, colCount: 1}];
spread.bind(GC.Spread.Sheets.Events.SelectionChanged, function (e, args) {
selections = args.newSelections;
});
spread.resumePaint();
}
function bindEvents() {
var removeButton = document.getElementById('remove');
removeButton.addEventListener('click', function () {
traverseSelectionsRowsWithOperation(function (row) {
sheet.removeRow(row);
});
});
var saveButton = document.getElementById('save');
saveButton.addEventListener('click', function () {
traverseSelectionsRowsWithOperation(function (row) {
sheet.saveRow(row);
});
});
var resetButton = document.getElementById('reset');
resetButton.addEventListener('click', function () {
traverseSelectionsRowsWithOperation(function (row) {
sheet.resetRow(row);
});
});
var saveAllButton = document.getElementById('save-all');
saveAllButton.addEventListener('click', function () {
spread.commandManager().SaveAll.execute(spread, { sheetName: tablesheetName });
});
var submitButton = document.getElementById('submit');
submitButton.addEventListener('click', function () {
sheet.submitChanges();
});
var discardButton = document.getElementById('discard');
discardButton.addEventListener('click', function () {
sheet.cancelChanges();
});
var getChangesButton = document.getElementById("getChanges");
getChangesButton.addEventListener('click', function () {
var changesPanel = document.getElementById("changesPanel");
var changes = formatChanges(sheet.getChanges());
changesPanel.innerHTML = changes;
});
}
function traverseSelectionsRowsWithOperation(operation) {
if (selections) {
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 getBaseApiUrl() {
return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api';
}
function formatChanges(changes) {
var json = JSON.stringify(changes, function (k, v) {
if (k === "dataItem" || k === "oldDataItem") {
return {
Id: v.Id,
FirstName: v.FirstName,
LastName: v.LastName,
HomePhone: v.HomePhone,
Title: v.Title
};
}
return v;
}, 2);
return json;
}
<!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/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="options-container" class="options-container">
<fieldset>
<legend>Active Row Operations</legend>
<div class="field-line">
<input id="remove" type="button" value="Remove">
</div>
<div class="field-line">
<input id="save" type="button" value="Save">
</div>
<div class="field-line">
<input id="reset" type="button" value="Reset">
</div>
</fieldset>
<fieldset>
<legend>Save All Rows</legend>
<div class="field-line">
<input id="save-all" type="button" value="Save All">
</div>
</fieldset>
<fieldset>
<legend>Batch Operations</legend>
<div class="field-line">
<input type="button" value="Submit" id="submit">
</div>
<div class="field-line">
<input type="button" value="Discard" id="discard">
</div>
</fieldset>
<fieldset>
<legend>Get Changes</legend>
<div class="field-line">
<input type="button" value="Get Changes" id="getChanges">
</div>
<div class="field-line">
<textarea id="changesPanel"></textarea>
</div>
</fieldset>
</div>
</div>
</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;
}
#changesPanel {
width: 100%;
height: 300px;
}
