Row Operations

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

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.
/*REPLACE_MARKER*/ /*DO NOT DELETE THESE COMMENTS*/ 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", { data: employees }); //init a table sheet sheet = spread.addSheetTab(0, tablesheetName, GC.Spread.Sheets.SheetType.tableSheet); sheet.options.allowAddNew = false; //bind a view to the table sheet myTable.fetch().then(function() { 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 } ]); sheet.setDataView(view); data = { "FirstName": "", "LastName": "", "Title": "", "HomePhone": "", "Address": "", "City": "", "State": "", "PostalCode": "", "Region": "" }; 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) { 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 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]; }
<!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="$DEMOROOT$/spread/source/data/employees.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.</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; }