[{"id":"b66f8b1e-cc14-4e89-9679-abd5687d283d","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"569bb90a-ea68-46c6-96f1-ab151c120714","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"5eb52f08-2d1a-4362-9ffc-4871bdc10f3f","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"341dd607-b97d-4d70-bde2-53acda6b6c95","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"05349273-414f-4208-9ea2-c4fc8f4ea2cb","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"86089f76-b778-4d52-821e-6f27de3df613","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b81e4fd6-1fc5-43a0-a258-b6e16a5cbec6","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"8aa8ce31-43e4-438e-951f-241608435260","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"37343f41-6ec2-4c7e-b21d-2cc18d5ce1e0","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"5915e52f-64f8-4146-b8bd-81bead6324a3","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"836ba889-af9e-460d-a4cc-c24d922795f2","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4ae14b06-bb68-4394-a210-a46b8f028346","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d8f42066-e9dc-4411-bdcf-43b1a203370c","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"7ef86f16-b1a0-49f7-9592-612b9be02b25","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b29c7775-a9a4-451e-a1b5-01d19ed5ca5e","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"e56f3989-8f81-46af-90fa-a4813eeb976f","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"2526c963-f170-45a8-923e-91b0712a9810","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"24575cf0-501a-44f9-8426-c40f8f4b5552","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"1a6f8d7f-acd6-42be-8c4f-f464c6218381","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"82b176fd-5cab-498c-909e-8fa7d29c38d8","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"1708b3a9-4f37-44a8-8f0e-f9a2d2e5d940","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"c9ac246e-29fb-4bc4-8231-8439795bb590","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"94f2a5c3-2539-436a-af75-23fbbd1a3957","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d99594f4-2d40-4df4-9419-ba2ca6aa3f7f","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"fd66e72d-0f10-4f57-9807-6db26290ab2e","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"8f344863-503d-4bc3-a594-3815e7d55f5c","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"9be82601-de9d-4c18-948a-23ab6f4dd431","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"6495f3c0-b463-47e0-b08a-ca949672211e","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"b4bede08-3f08-4839-ba4a-abc7ac195bde","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4afcdfc0-3ff5-4f2b-a223-f8fc042a5bbe","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"422062c4-fa40-4771-a86f-008efe6d86e5","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"d87e8013-86a7-4840-8d25-6f62e14eb4ac","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"a6acedfd-4043-4c64-a5d1-aec3326df9e7","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"dbdc0b24-06c4-48b9-8d6c-7455119dc773","tags":[{"product":null,"links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60","name":"\u65B0\u589E","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"a3856849-954a-4cfc-96a6-382e530d3638","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]},{"id":"4a39306b-ffa5-433d-80a3-28e41f929b72","tags":[{"product":null,"links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf","name":"\u66F4\u65B0","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97"}]}]
SpreadJS中的TableSheet允许您添加、更新或删除具有有意义列类型的列,以便在运行时设计表格。
要在最后一列后显示“添加列”按钮,请将enableDefineColumn TableSheet选项设置为true。右键单击时,将显示上下文菜单,其中包含修改、删除和设置列的主键的选项,如下图所示。
以下示例代码显示如何在TableSheet中启用“添加列”按钮。
tableSheet.options.enableDefineColumn = true;
TableSheet允许您定义具有预定义列属性的列类型,这些属性根据列的类型进行分类。以下是内置列类型的列表:数字、文本、公式、复选框、日期、货币、百分比、电话、电子邮件、URL、查找、创建时间、修改时间、附件、选择和条形码。
主要的列类型属性如下:
Value(必填):包括列值,但如果其类型不是公式或查找类型,则此字段不能包含公式或关系。
Caption(可选):包括列标题的显示名称。
Type(必填):允许选择内置列类型。以下是一些内置列的示例图。
所有支持的列类型的数据类型和示例列选项如下表所示。
列类型 | 数据类型 | 示例列选项 |
---|---|---|
Number | number | { value: "Number", type: "Number", caption: "Number", dataType: "number", defaultValue: 0, style: {formatter: "0.00", mask: { pattern: "0{0,}" } } } |
Text | string | { value: "Text", type: "Text", caption: "Text", dataType: "string" } |
Formula | depends | { name: "NamedFormula", value: "=[@Amount]*[@UnitPrice]", type: "Formula", caption: "Total Sales", dataType: "formula", style: {formatter: "0.00"}, readonly: true } |
Lookup | depends | { value: "customer.ContactName", type: "Lookup", caption: "Customer", readonly: true } |
Date | date | { value: "Date", type: "Date", caption: "Date", dataType: "date", style: { formatter: "m/d/yyyy", cellButtons: [{ buttonBackColor: "transparent", command: "openDateTimePicker", enabled: true, visibility: 'always', imageType: "dropdown", useButtonStyle: false }], dropDowns: [{ type: "dateTimePicker", option: { calendarPage: "day", showTime: false, startDay: "sunday" } }] } } |
Checkbox | boolean | { value: "Checkbox", type: "Checkbox", caption: "Checkbox", dataType: "boolean", style: { cellType: { type: "checkbox" } } } |
Select | depends | { value: "Select", type: "Select", caption: "Select", dataType: "number", style: { cellType: { type: "combobox" } } } |
Currency | number | { value: "Currency", type: "Currency", caption: "Currency", dataType: "number", style: {formatter: "[$$-409]#,##0.00"} } |
Percent | number | { value: "Percent", type: "Percent", caption: "Percent", dataType: "number", style: {formatter: "0%"} } |
Phone | string | { value: "Phone", type: "Phone", caption: "Phone", dataType: "string", style: { mask: { pattern: "1 (0{3}) 0{3}-0{4}" } } } |
string | { value: "Email", type: "Email", caption: "Email", dataType: "string", style: { mask: { pattern: "[a0.\-]{1,}@[a0.\\-]{1,}.(com|cn|gov|edu)" } } } | |
URL | string | { value: "URL", type: "URL", caption: "URL", dataType: "string", style: { cellType: { type: "hyperlink" } } } |
CreatedTime | date | { value: "CreatedTime", type: "CreatedTime", caption: "CreatedTime", dataType: "date", defaultValue: "=NOW()", readonly: true, trigger: { when: "onNew", formula: "=NOW()" }, style: { formatter: "m/d/yyyy", cellButtons: [{ buttonBackColor: "transparent", command: "openDateTimePicker", enabled: true, visibility: 'always', imageType: "dropdown", useButtonStyle: false }], dropDowns: [{ type: "dateTimePicker", option: { calendarPage: "day", showTime: false, startDay: "sunday" } }] } } |
ModifiedTime | date | { value: "ModifiedTime", type: "ModifiedTime", caption: "ModifiedTime", dataType: "date", readonly: true, trigger: { when: "onNewAndUpdate", formula: "=NOW()", fields: "*" }, style: { formatter: "m/d/yyyy", cellButtons: [{ buttonBackColor: "transparent", command: "openDateTimePicker", enabled: true, visibility: 'always', imageType: "dropdown", useButtonStyle: false }], dropDowns: [{ type: "dateTimePicker", option: { calendarPage: "day", showTime: false, startDay: "sunday" } }] } } |
Attachment | object | { value: "Attachment", type: "Attachment", caption: "Attachment", style: { cellType: { type: "fileUpload" } } } |
Barcode | depends | { value: "Barcode", type: "Barcode", caption: "Barcode", defaultValue: 0, style: { formatter: "=BC_QRCODE([@Barcode],"#000000","#FFFFFF","L",2,"auto","auto",false,0,,"UTF-8",,,,)"} } |
SpreadJS允许使用自定义函数与远程表格进行通信,如下所述。
自定义函数 | 描述 | 示例定义 |
---|---|---|
getColumns | 检索表中的列 | getColumns: { url: apiColumnUrl } |
addColumn | 添加列 | addColumn: { url: apiColumnUrl, method: 'POST' } |
updateColumn | 更新列属性 | updateColumn: { url: apiColumnUrl, method: 'PUT' } |
removeColumn | 删除列 | removeColumn: { url: apiColumnUrl, method: 'DELETE' } |
batch | 批量提交更改 | batch: { url: apiUrl + 'Collection' } |
在交互过程中,自定义函数的请求和响应数据如下:
自定义函数 | 请求数据 | 响应数据 |
---|---|---|
getColumns | 无 | Column[] |
addColumn | ColumnWithDefaultData | ColumnData |
updateColumn | ModifiedColumnData | ModifiedColumnData |
removeColumn | ColumnData | ColumnData |
batch | BatchItemData[] | BatchResultData[] |
远程API的列处理可以通过自定义函数类似于以下自动同步和批处理模式进行处理:
var orderTable = dataManager.addTable("orderTable", {
remote: {
read: function() {
return Promise.resolve([...])
},
getColumns: function() {
return Promise.resolve([...])
},
addColumn: function(change) {
return Promise.resolve()
},
updateColumn: function(change) {
return Promise.resolve();
},
removeColumn: function(change) {
return Promise.resolve();
},
// batch: function(changes) {
// return Promise.resolve([...]);
// },
},
autoSync: true,
// batch: true
});
启用autoSync模式时,TableSheet列的任何更改将立即同步到远程表格。但是,在启用batch模式时,列的更改将存储在本地。在调用submitChange命令时,更改的列信息和更改的数据信息将一起发送到远程表格。
[
{ "type": "updateColumn", "column": { field: 'columnName', dataType: 'number', ...otherproperties }, "originalColumn": { field: 'columnName0', ...otherproperties } },
{ "type": "addColumn", "column": { field: 'columnName1', dataType: 'number', ...otherproperties }, },
{ "type": "removeColumn", "column": { field: 'columnName2', ...otherproperties }, },
{ "type": "delete", "dataItem": { ...} },
{ "type": "insert", "dataItem": { ...} },
{ "type": "update", "dataItem": { ...}, "oldDataItem": { ...}, "sourceIndex": 1 }
]
在调用cancelChange命令时,它会取消所有更改。对于批处理模式,TableSheet上的getChanges命令会同时检索列和数据的更改。
SpreadJS还提供使用DefineColumn、ModifyColumn和RemoveColumn等命令来添加、更新或删除列的功能。这些命令允许您通过实现IColumn接口中的属性(如value、dataType、defaultValue、isPrimaryKey、type或name)来添加、更新和删除表中的列。
让我们讨论一些实现这些命令的代码示例。
以下示例代码展示了如何实现DefineColumn命令。
spread.commandManager().execute({
cmd: "DefineColumn",
sheetName: spread.getActiveSheetTab().name(),
// col: 3, // 可选
column: {
type: 'Number', // 指定列类型
value: 'Number1', // 列实例的列标识符
caption: '# Number1', // 该列在文化中显示的标题
style: { formatter: '#,##0.00_);[Red](#,##0.00)' } // 格式化器可以指定或通过UI操作转换
} as GC.Data.IColumn,
});
以下示例代码展示了如何实现ModifyColumn命令。
spread.commandManager().execute({
cmd: "ModifyColumn",
sheetName: spread.getActiveSheetTab().name(),
col: 3,
column: {
type: 'Text', // 指定列类型
value: 'Text1', // 列实例的列标识符
caption: 'A Text1', // 该列在文化中显示的标题
} as GC.Data.IColumn,
});
以下示例代码展示了如何实现RemoveColumn命令。
spread.commandManager().execute({
cmd: "RemoveColumn",
sheetName: spread.getActiveSheetTab().name(),
col: 3,
});
定义自定义列
TableSheet提供了自定义列类型或使用自定义命令(如defineColumnCommand、submitDefineColumnCommand)添加一些特殊列类型的功能,或使用columnTypeItems命令更新内置列。
以下代码示例展示了如何添加一些特殊列类型或更新内置列类型。
// 选择一些列类型来展示
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: 'CustomizingType',
text: 'Progress',
iconClass: 'gc-defined-column-type-icon-number'
});
defineColumnCommand指定了用于替换默认命令的命令,该命令会在添加列按钮或修改列菜单项时打开定义的列弹出窗口。
以下示例代码展示了如何实现defineColumnCommand。
// 处理打开定义列弹出窗口
const defineCommand = tableSheet.options.defineColumnCommand;
tableSheet.options.defineColumnCommand = 'defineColumnCustom';
spread.commandManager().register('defineColumnCustom', {
canUndo: false,
execute: function (context, options) {
// 可以在修改/添加列时构建自定义UI
// 示例中打开默认UI
options.cmd = defineCommand;
spread.commandManager().execute(options);
}
});
submitDefineColumnCommand指定了在选择定义列弹出窗口的提交按钮时提交定义列选项的命令。
以下示例代码展示了如何实现submitDefineColumnCustom。
// 处理提交定义列选项
tableSheet.options.submitDefineColumnCommand = 'submitDefineColumnCustom';
spread.commandManager().register('submitDefineColumnCustom', {
canUndo: false,
execute: function (context, options) {
if (options.command === 'DefineColumn' && options.column.type === 'CustomizingType') {
options.column.defaultValue = 0.1;
options.column.style = {
formatter: "=HBARSPARKLINE([@" + options.column.value + "], \"#66B032\")"
};
}
options.cmd = options.command;
spread.commandManager().execute(options);
}
});
SpreadJS Designer组件还提供了通过从TABLE SHEET DESIGN > Options组中选择Enable Define Column复选框来定义列的功能。
Enable Define Column复选框
您还可以配置远程API,以便在SpreadJS Designer组件中与远程数据表通信,前提是远程数据表支持此功能。