Custom

Users can customize the column types or add some special column types by passing the self-defined commands to the options (defineColumnCommand, submitDefineColumnCommand) and updating the columnTypeItems which has the existed column types.

The TableSheet lets users change the built-in commands to define their column type select UI based on DOM, define special column type or change the built-in column type definition: The defineColumnCommand to specify the command to replace the default command which opens defined column popup when clicking the add column button or modify column menu item. The submitDefineColumnCommand to specify the command for submitting defined column options when clicking the save button on the column defined popup. And it could add some special column types or update the built-in column types:
/*REPLACE_MARKER*/ /*DO NOT DELETE THESE COMMENTS*/ window.onload = function() { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 }); initSpread(spread); }; function customizingSpecifiedColumnTypes(spread, tableSheet) { // It could add some special column types or update the built-in column types const items = tableSheet.options.columnTypeItems.filter((item) => item.name === 'Select' || item.name === 'Attachment' || item.name === 'Barcode' || item.name === 'CreatedTime' || item.name === 'ModifiedTime' || item.name === 'Currency'); tableSheet.options.columnTypeItems = tableSheet.options.columnTypeItems.slice(0, 4).concat(items); tableSheet.options.columnTypeItems.unshift({ name: 'Progress', text: 'Progress', iconClass: 'gc-defined-column-type-icon-number' }); // Handle submitting defined column options tableSheet.options.submitDefineColumnCommand = 'submitDefineColumnCustom'; spread.commandManager().register('submitDefineColumnCustom', { canUndo: false, execute: function (context, options) { if (options.command === 'DefineColumn' && options.column.type === 'Progress') { options.column.defaultValue = 0.1; options.column.style = { formatter: "=HBARSPARKLINE([@"+options.column.value+"], \"#66B032\")" }; } options.cmd = options.command; spread.commandManager().execute(options); } }); // Handle opening defined column popup if it's necessary to provide a self-defined UI // Otherwise ignoring the command const defineCommand = tableSheet.options.defineColumnCommand; tableSheet.options.defineColumnCommand = 'defineColumnCustom'; spread.commandManager().register('defineColumnCustom', { canUndo: false, execute: function (context, options) { // it could build self-defined UI on the modify/add column triggered // opening the default UI for the sample options.cmd = defineCommand; spread.commandManager().execute(options); } }); } function initSpread(spread) { spread.suspendPaint(); spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader; spread.clearSheets(); var dataManager = spread.dataManager(); initDefineOrderTable(spread, dataManager); spread.resumePaint(); } function initDefineOrderTable(spread, dataManager) { spread.options.allowDynamicArray = true; var apiUrl = getApiUrl("DefineOrder"), apiColumnUrl = getColumnApiUrl("DefineOrder"); var orderTable = dataManager.addTable("orderTable", { 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: apiUrl + 'Collection' // } }, schema: { columns: { Id: { isPrimaryKey: true }, OrderDate: { dataType: 'date' }, RequiredDate: { dataType: 'date' }, ShippedDate: { dataType: 'date' }, ShipVia: { dataType: 'number', type: 'Select', style: { cellType: { type: 'combobox', editorValueType: 'value', items: [ { text: 'Speedy Express', value: 1 }, { text: 'United Package', value: 2 }, { text: 'Federal Shipping', value: 3 } ] } }, }, Freight: { dataType: 'number', type: 'Currency', style: { formatter: '[$$-409]#,##0.00' }, }, CreatedTime: { type: "CreatedTime", dataType: 'date', trigger: { when: "onNew", formula: "=NOW()" }, readonly: true, defaultValue: '=NOW()', style: { formatter: "m/d/yy h:mm;@" }, }, ModifiedTime: { type: "ModifiedTime", dataType: 'date', trigger: { when: "onNewAndUpdate", formula: "=NOW()", fields: "*" }, readonly: true, style: { formatter: "[$-409]m/d/yy h:mm AM/PM;@" }, }, OrderAttachment: { type: 'Attachment', style: { cellType: { type: 'fileUpload' } }, }, OrderCode: { type: 'Barcode', defaultValue: '=FLOOR.MATH(RAND()*100000000)', style: { formatter: '=BC_GS1_128([@OrderCode],"#000000","#FFFFFF",false,,,,,,,,,,,)' }, }, } }, autoSync: true, // batch: true, }); var employeeTable = dataManager.addTable("employeeTable", { remote: { read: { url: getApiUrl("Employee") } }, schema: { columns: { Id: { isPrimaryKey: true }, BirthDate: { dataType: 'date' }, HireDate: { dataType: 'date' }, } }, autoSync: true }); var customerTable = dataManager.addTable("customerTable", { remote: { read: { url: getApiUrl("Customer") } }, schema: { columns: { Id: { isPrimaryKey: true }, } }, autoSync: true }); dataManager.addRelationship(orderTable, "EmployeeId", "employee", employeeTable, "Id", "orders"); dataManager.addRelationship(orderTable, "CustomerId", "customer", customerTable, "Id", "orders"); var tableSheet = spread.addSheetTab(0, "Orders", GC.Spread.Sheets.SheetType.tableSheet); tableSheet.options.allowAddNew = true; tableSheet.options.enableDefineColumn = true; customizingSpecifiedColumnTypes(spread, tableSheet); var rowActions = GC.Spread.Sheets.TableSheet.BuiltInRowActions; var options = tableSheet.rowActionOptions(); options.push( rowActions.removeRow, rowActions.saveRow, rowActions.resetRow, ); tableSheet.rowActionOptions(options); var orderView = orderTable.addView("orderView", [ { value: "Id", width: 50 }, { value: "CustomerId", defaultValue: 'ALFKI', visible: false }, // just for demo { value: "EmployeeId", defaultValue: 1, visible: false }, // just for demo { value: "customer.ContactName", caption: 'Contact Name' }, { value: '=CONCAT([@employee.FirstName], " ", [@employee.LastName])', caption: 'Employee Name', width: 160 }, { value: "OrderDate", width: 160, style: { formatter: "m/d/yyyy" } }, { value: "ShipVia", width: 140 }, { value: "Freight", width: 80, defaultValue: 0 }, { value: "ShipName" }, { value: '=CONCAT([@ShipState], ", ", [@ShipCity], ", ", [@ShipRegion])', caption: 'Ship State', visible: false }, { value: 'OrderAttachment', caption: 'Attachment' }, { value: 'OrderCode', caption: 'Logistic Code', width: 160 }, { value: 'CreatedTime', caption: 'Created Time', width: 160, visible: false }, { value: 'ModifiedTime', caption: 'Modified Time', width: 160, visible: false }, ], null, { defaultColumnWidth: 120 }); orderView.fetch().then(function () { tableSheet.setDataView(orderView); }); } function getBaseApiUrl() { return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api'; } function getApiUrl(tableName) { return getBaseApiUrl() + "/" + tableName; } function getColumnApiUrl(tableName) { return getBaseApiUrl() + "/tables/" + tableName + "/columns"; }
<!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-barcode/dist/gc.spread.sheets.barcode.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> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; }