AutoSync and Batch Updates

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 action column 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: Request and Response Operation Request Type Request Data Response Data update POST The updated data No restrictions read GET No data The records array delete DELETE The deleted data or data array No restrictions create POST The inserted data No restrictions batch POST An object array, where each object contains a 'type' property. This operation type could be 'update', 'insert', or 'delete'. The 'dataItem' property is the current record. The 'sourceIndex' property is the record index. The optional 'oldDataItem' property is the original record. For example: [ {"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}]
var tableName = "Employee"; var baseApiUrl = getBaseApiUrl(); var apiUrl = baseApiUrl + "/" + tableName; 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 }, batch: { url: batchApiUrl } }, batch: true, onFailed: function(type, args) { console.log("error", type, args); } }); table = myTable; //init a table sheet sheet = spread.addSheetTab(0, tablesheetName, GC.Spread.Sheets.SheetType.tableSheet); sheet.actionColumn.options({ visible: true, title: "Actions", width: 90, actionButtons: { remove: { visible: true, title: "", icon: GC.Spread.Sheets.ButtonImageType.minus }, save: { visible: true, title: "", icon: GC.Spread.Sheets.ButtonImageType.ok }, reset: { visible: true, title: "", icon: GC.Spread.Sheets.ButtonImageType.clear } } }); //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: 100, caption: "Phone" }, { value: "Notes", width: 100, caption: "Notes" } ]); //the View has all default columns of the Table sheet.setDataView(view); }); 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(); }); } 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'; }
<!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/@grapecity/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/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/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> </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; }