Overview

TableSheet supports automatic and batch updates to data sources using AutoSync and Batch modes.

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; }