Handle Requests

TableSheet supports a self-defined function to configure the remote request options.

Description
app.js
index.html
styles.css
Copy to CodeMine

By default, the TableSheet will configure the remote requests by an object. If you want to handle the request manually, just replace the object of the function, and the data and column changes will be passed to the function.

The details of the data changes are in the Request and Response section of the Overview.

AutoSync Mode

Handle requests in the AutoSync mode:

function requestToServer(params?): Promise<any> {
    // api of request to server
}

var myTable = dataManager.addTable("myTable", {
    // ... other settings
    autoSync: true // enable the autoSync mode
    remote: {
        read: function (): Promise<any[]>{
            return requestToServer();
        },
        update: function (item): Promise<any>{ // the updated data change
            return requestToServer(item);
        },
        create: function (item): Promise<any>{ // the created data change
            return requestToServer(item);
        },
        delete: function (item): Promise<boolean>{ // the deleted data change
            return requestToServer(item);
        },
        // // not necessary if want to define the column
        // getColumns: function (): Promise<any[]>{
        //     return requestToServer();
        // },
        // addColumn: function (change): Promise<any>{ // the added column change
        //     return requestToServer(change);
        // },
        // updateColumn: function (change): Promise<any>{ // the updated column change
        //     return requestToServer(change);
        // },
        // removeColumn: function (change): Promise<boolean>{ // the removed column change
        //     return requestToServer(change);
        // },
    }
});

Batch Mode

Handle requests in the Batch mode:

function requestToServer(params?): Promise<any> {
    // api of request to server
}

var myTable = dataManager.addTable("myTable", {
    // ... other settings
    batch: true, // enable the batch mode
    remote: {
        // ... other settings
        batch: function (changes: any[]): Promise<any[]>{ // the added, updated, removed column changes and the created, updated, deleted data changes
            return requestToServer(changes);
        }
    }
});

Change data source options

When a Table uses CRUD functions instead of CRUD options, these functions can't be saved and restored after JSON serialization / deserialization because of security issue. Setting the Table’s data source options after the fromJSON method is invoked could make the Table working again.

// 1. invoke Workbook's fromJSON
spread.fromJSON(spreadJson);
// 2. update table options
let myTable = spread.dataManager().tables["myTable"];
myTable.options = {
    remote: {
        read: function () {
            return Promise.resolve(dataSource);
        }
    }
};
//// make sure myTable.options be reset if just updating the properties of the options
// let options = myTable.options;
// options.remote.read.url = './new-url';
//// It could specify the GC.Data.IRemoteFetchOption(similar as RequestInit) to the options of the read property for the fetch options
// options.remote.read.options = { headers: { 'AuthenticationToken': 'fake_token' } };
// myTable.options = options; // It's important to reset the options to update


// 3. invoke table's fetch then setDataView
myTable.fetch(true).then(function() {
    let myView = myTable.views["myView"];
    let sheet = spread.getActiveSheetTab();
    sheet.setDataView(myView);
});

Reload data

When a Table’s fetch method is invoked and its reload argument is true, the Table will load data again.

class GC.Data.Table {
    /**
     * Requests the table data from local data source or remote data source by the data source option.
     * @param {boolean} [reload] Whether to reload the data from server side forcibly.
     * @returns {Promise} The resolving Promise thenable. You could get the data in Promise.then().
     */
    fetch (reload?: boolean): Promise<any>;
}

And a View’s fetch method has same reload argument with a Table’s fetch method.

class GC.Data.View {
    /**
     * Requests the view data from its host table and related table.
     * @param {boolean} [reload] Whether to reload the data from server side forcibly.
     * @returns {Promise} The resolving Promise thenable. You could get the data in Promise.then().
     */
    fetch (reload?: boolean): Promise<any>;
}
By default, the TableSheet will configure the remote requests by an object. If you want to handle the request manually, just replace the object of the function, and the data and column changes will be passed to the function. The details of the data changes are in the Request and Response section of the Overview. AutoSync Mode Handle requests in the AutoSync mode: Batch Mode Handle requests in the Batch mode: Change data source options When a Table uses CRUD functions instead of CRUD options, these functions can't be saved and restored after JSON serialization / deserialization because of security issue. Setting the Table’s data source options after the fromJSON method is invoked could make the Table working again. Reload data When a Table’s fetch method is invoked and its reload argument is true, the Table will load data again. And a View’s fetch method has same reload argument with a Table’s fetch method.
/*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 sendRequest(url, options) { options.method = options.method || 'POST'; options.headers = { 'Content-Type': 'application/json; charset=utf-8' }; if (options.body) { options.body = JSON.stringify(options.body); } return fetch(url, options).then(resp => { if (resp.ok) { return resp.json(); } else { throw resp.statusText; } }); } 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: function () { return sendRequest(apiUrl, { method: 'GET' }); }, // update: function (item) { // return sendRequest(apiUrl, { body: item, method: 'PUT' }); // }, // create: function (item) { // return sendRequest(apiUrl, { body: item }); // }, // delete: function (item) { // return sendRequest(apiUrl, { body: item, method: 'DELETE' }); // }, // getColumns: function () { // return sendRequest(apiColumnUrl, { method: 'GET' }); // }, // updateColumn: function (change) { // return sendRequest(apiColumnUrl, { body: change, method: 'PUT' }); // }, // addColumn: function (change) { // return sendRequest(apiColumnUrl, { body: change }); // }, // removeColumn: function (change) { // return sendRequest(apiColumnUrl, { body: change, method: 'DELETE' }); // }, batch: function (changes) { return sendRequest(batchApiUrl, { body: changes }); } }, 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: 100, caption: "Phone" }, { value: "Notes", width: 100, caption: "Notes" } ]); 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/@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> </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; }