The TableSheet lets users enable defining column by the options which will toggle the visibility of the add column button, modify column, remove column and set primary key context menu on the column header:
Column Types
The descriptions of the supported column types:
Column Type
Data Type
Description
Number
number
For most numerical values with specified formatting
Text
string
For the common text
Formula
depends on the result
To compute a value based on other fields in the record.
Lookup
depends on the related
To look up a specific field in a related record.
Date
date
To easily enter a date value
Checkbox
boolean
Useful to check/uncheck with a TRUE/FALSE data type
Select
depends on the option
Useful to select an option from a preset list
Currency
number
Indicates the currency with culture formatting
Percent
number
Indicates the number formatted as the percent
Phone
string
Indicates the digit string of numbers with mask validation
Email
string
Indicates the email address with mask validation
URL
string
Indicates the URL text
CreatedTime
date
To set a date when the record create
ModifiedTime
date
To set a date when the fields updated in the record
Attachment
object
Allows to attach a file directly on the record
Barcode
depends on the input
Generates a specified barcode from the field
Configuration
To communicate with the table, it should configure the remote APIs:
Column API
Description
getColumns
Retrieve the columns from the table
addColumn
Add a column to the table
updateColumn
Update the properties of a column to the table
removeColumn
Remove a column from the table
batch
The changes will post together in the batch mode
And the column remote APIs could be handled by the self-defined functions which are similar as the Handle Requests.
Request and Response
Operation
Request Data
Response Data
getColumns
None
Column[]
addColumn
ColumnWithDefaultData
ColumnData
updateColumn
ModifiedColumnData
ModifiedColumnData
removeColumn
ColumnData
ColumnData
batch
BatchItemData[]
BatchResultData[]
The Commands
Besides defining through UI, there are also the commands to define the columns:
DefineColumn
The DefineColumn command could add a column:
ModifyColumn
The ModifyColumn command could change the column properties:
RemoveColumn
The RemoveColumn command could remove the column:
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import './styles.css';
import { AppFunc } from './app-func';
import { App } from './app-class';
// 1. Functional Component sample
createRoot(document.getElementById('app')).render(<AppFunc />);
// 2. Class Component sample
// createRoot(document.getElementById('app')).render(<App />);
/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-barcode";
import "@mescius/spread-sheets-tablesheet";
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
export function AppFunc() {
let spread = null;
let initSpread = function(value) {
spread = value;
spread.suspendPaint();
spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;
spread.clearSheets();
//init a data manager
var dataManager = spread.dataManager();
initDefineOrderTable(spread, dataManager);
spread.resumePaint();
}
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => initSpread(spread)}>
</SpreadSheets>
</div>
</div>
);
}
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: {
// It could define some options for the existed column on the table
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;
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', width: 100 },
{ value: '=CONCAT([@employee.FirstName], " ", [@employee.LastName])', caption: 'Employee', width: 110 },
{ value: "OrderDate", width: 100, 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', visible: false },
{ value: 'OrderCode', caption: 'Logistic Code', width: 160, visible: false },
{ 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";
}
/*REPLACE_MARKER*/
/*DO NOT DELETE THESE COMMENTS*/
import * as React from 'react';
import { createRoot } from 'react-dom/client';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-barcode";
import "@mescius/spread-sheets-tablesheet";
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
import './styles.css';
const Component = React.Component;
function _getElementById(id) {
return document.getElementById(id);
}
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
}
render() {
return (
<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
</SpreadSheets>
</div>
</div>
);
}
initSpread(spread) {
this.spread = spread;
spread.suspendPaint();
spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader;
spread.clearSheets();
//init a data manager
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: {
// It could define some options for the existed column on the table
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;
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', width: 100 },
{ value: '=CONCAT([@employee.FirstName], " ", [@employee.LastName])', caption: 'Employee', width: 110 },
{ value: "OrderDate", width: 100, 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', visible: false },
{ value: 'OrderCode', caption: 'Logistic Code', width: 160, visible: false },
{ 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/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app" style="height: 100%;"></div>
</body>
</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;
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-barcode': 'npm:@mescius/spread-sheets-barcode/index.js',
'@mescius/spread-sheets-tablesheet': 'npm:@mescius/spread-sheets-tablesheet/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/cjs/react.production.js',
'react-dom': 'npm:react-dom/cjs/react-dom.production.js',
'react-dom/client': 'npm:react-dom/cjs/react-dom-client.production.js',
'scheduler': 'npm:scheduler/cjs/scheduler.production.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
