Row Operations

TableSheet supports create, update, delete and reset row operations.  With these operations, you can conveniently sync edited row data with your database.

This demo uses the AutoSync mode, which you can find more information about in the AutoSync and Batch Mode demo page. Remove You could click Remove button (which using 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, then click 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, then click 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.
var tableName = "Employee"; var baseApiUrl = getBaseApiUrl(); var apiUrl = baseApiUrl + "/" + tableName; var tablesheetName = 'MyTableSheet'; var spread, sheet, view, data, selections; 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(); var myTable = dataManager.addTable("myTable", { remote: { read: { url: apiUrl }, update: { url: apiUrl, method: 'PUT' }, create: { url: apiUrl }, delete: { url: apiUrl } }, autoSync: true, onFailed: function(type, args) { console.log("error", type, args); } }); //init a table sheet sheet = spread.addSheetTab(0, tablesheetName, GC.Spread.Sheets.SheetType.tableSheet); sheet.options.allowAddNew = false; sheet.actionColumn.options({ visible: false }); //bind a view to the table sheet myTable.fetch().then(function() { view = myTable.addView("myView", [ { value: "Id", width: 50 }, { value: "TitleOfCourtesy", width: 150 }, { value: "FirstName", width: 120 }, { value: "LastName", width: 120 }, { value: "Title", width: 150 }, { value: "Extension", width: 100 }, { value: "HomePhone", width: 120 }, { value: "Address", width: 100 }, { value: "City", width: 50 }, { value: "Region", width: 100 }, { value: "PostalCode", width: 120 }, { value: "Country", width: 100 }, { value: "Notes", width: 100 } ]); //the View has all default columns of the Table sheet.setDataView(view); data = clone(view.getRowData(0)); generateFieldsElement(data); }); spread.bind(GC.Spread.Sheets.Events.SelectionChanged, function(e, args) { selections = args.newSelections; }); spread.resumePaint(); } function generateFieldsElement(sampleData) { var fieldsContainer = document.getElementById('fields'); var fragment = ''; for (var field in sampleData) { var isContinue = field === 'Id'; if (isContinue) { continue; } var isRequired = field === 'FirstName' || field === 'LastName'; fragment += '<div class="field-line">' + ' <label class="field-inline" for="field_' + field + '">' + field + '<span class="required">' + (isRequired ? "(*)" : "") + '</span></label>' + ' <input class="field-inline" id="field_' + field + '" type="text" value="' + sampleData[field] + '">' + '</div>'; } fieldsContainer.innerHTML = fragment; } function bindEvents() { var fieldsContainer = document.getElementById('fields'); var toggleButton = document.getElementById('toggle'); toggleButton.addEventListener('click', function() { if (fieldsContainer.classList.contains('show')) { toggleButton.value = 'Edit Fields'; fieldsContainer.classList.remove('show'); } else { toggleButton.value = 'Hide Fields'; fieldsContainer.classList.add('show'); } }); 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 addButton = document.getElementById('add'); addButton.addEventListener('click', function() { var data = getNewData(); sheet.addRow(data); }); } 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 getNewData() { var newData = {}; for (var key in data) { var newValue = key === 'Id' ? null : getProperty('field_' + key, 'value'); newData[key] = newValue; } return newData; } function getProperty(domId, prop) { return document.getElementById(domId)[prop]; } function clone(source) { var result = {}; for (var key in source) { result[key] = source[key]; } return result; } 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"> <div class="option-row"><label>Use these buttons to perform row operations that interact with our test database.</label> </div> <hr /> <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>Add New Row</legend> <div class="field-line"> <input type="button" value="Edit Fields" id="toggle"> </div> <div class="field-line" id="fields"></div> <div class="field-line"> <input type="button" value="Add" id="add"> </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; }