概述
本 Demo 展示了如何自定义 TableSheet 的列类型功能,包括筛选内置列类型、添加自定义列类型,以及通过命令模式自定义列定义的交互行为。Demo 中添加了一个自定义的 Progress 列类型,并使用横向条形图迷你图来可视化进度值。
实现思路
启用 TableSheet 的列定义功能 (enableDefineColumn)
筛选 columnTypeItems 数组,保留部分内置列类型
添加自定义的 Progress 列类型到 columnTypeItems
注册 submitDefineColumnCommand 命令,处理 Progress 类型的默认值和样式设置
注册 defineColumnCommand 命令,可在此自定义列定义弹窗的 UI(Demo 中使用默认 UI)
代码解析
筛选和自定义列类型选项
这段代码首先筛选出指定的内置列类型,保留前 4 个基础类型并添加筛选出的类型。然后使用 unshift 方法在数组开头添加自定义的 Progress 列类型。
注册提交命令处理自定义列类型
这段代码注册了 submitDefineColumnCustom 命令。当用户保存列定义时,如果是 Progress 类型,会自动设置默认值为 0.1,并使用 HBARSPARKLINE 公式创建横向条形图样式,将数值可视化显示为绿色进度条。
注册打开列定义弹窗命令
这段代码注册了 defineColumnCustom 命令。当用户点击添加列或修改列菜单项时会触发此命令。Demo 中保留了默认 UI 行为,开发者可以在此扩展自定义的列定义界面。
运行效果
点击 TableSheet 的添加列按钮或修改列菜单项时,会打开列定义弹窗
在列类型下拉列表中可以看到自定义的 Progress 类型(显示在列表最上方)
列表只显示了筛选后的内置类型(如 Select、Attachment、Barcode 等)
当选择 Progress 类型并保存时,列会自动应用横向条形图样式,以绿色进度条形式显示数值
API 参考
ITableSheetOptions 接口
enableDefineColumn - 是否启用列定义功能
columnTypeItems - 列类型选项数组
defineColumnCommand - 打开列定义弹窗的命令名
submitDefineColumnCommand - 提交列定义的命令名
IColumnTypeItem 接口
CommandManager.register 方法
name - 命令名称
command.canUndo - 是否支持撤销
command.execute - 执行函数,接收 context 和 options 参数
/*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\/SpreadJSTutorial\//)[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" />
<meta name="spreadjs culture" content="zh-cn" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/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$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-barcode/dist/gc.spread.sheets.barcode.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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;
}
