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;
}
