[{"id":"1b88a165-2563-437e-99bb-ae30bd4b56db","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"a244ead7-a2c6-47a3-ac17-c5dbfa337362","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"33638a1c-7196-42c1-a96d-38b2d9ba8ac4","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"8e72e33e-b4ab-4fb3-98fc-a0b148134aed","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"86662220-9b9e-4940-9ced-d22642ea49a8","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"55fd3bb8-18d4-4edb-9640-ca3a365b798f","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"760b37c7-c713-4b24-b9ba-4bfe7d8437a6","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"3a083583-1d5f-492b-b450-34b2b5c775b8","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"04e73782-aa78-4dfe-a4f9-e72ed4c78a11","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"57c94653-8893-403b-a5b1-0d1e33a0bd0f","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"09a66339-64c0-415c-b142-0691587a8e4a","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"5b7aeac5-c755-426a-95c7-1ae8e547179a","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"790bc77a-9216-48fd-b8f9-fbc374ebb155","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"900e6cab-065d-4f1c-844d-efa3c074e270","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"0baaf91d-84f6-404c-a487-735226b6d5b6","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"5d37413b-3600-4da9-9700-feea54355f59","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"7ded3a22-15eb-49b8-a488-e83c2cd872eb","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"89c707ed-9841-4e53-96fb-940cc3214804","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"a570c8e7-07a2-47da-965b-da44fd1fa5cf","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"a5a73576-16ea-4cbc-925c-ef547389eaa5","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"69e79655-e015-4f9a-a230-2a25c988c926","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"adf3817f-7667-4a4c-8a5f-767b0b7e1e3e","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"1126cfab-6210-4e28-bee0-02c113fb7a0c","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"63c577e6-6cf9-497e-94e1-2307f7d3f498","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"24769ecd-2b08-4a85-b318-4f533bbf8393","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"2605431b-dc80-491d-886e-28981595d277","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]},{"id":"4f41e22e-eb51-49e5-aeae-a42dd6bf352c","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"aa71a884-c5bc-4842-8d6a-873dfd645167","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"b0576ca2-cb84-4390-9f95-9354ec20eda5","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"dacc7931-6785-4675-be31-80930403cf7b","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"c90d5fdf-420f-4978-8bf5-c9a2bb4334b3","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"6e6ac5b1-1501-4e28-89cc-525139488537","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"ff052704-1389-4029-bcdd-73c6cbe9f807","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"21c6131f-0f2d-41d1-9284-6ad9ee803c1f","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"64e2f4a3-2303-4bfa-8a93-6c23ef01de58","tags":[{"name":"新增","color":"DarkGreen","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"9e117e35-984a-4c14-95ca-ef0ec7b9fb60"}]},{"id":"4a0842a3-20b1-40c3-8e00-cd5941ffdf53","tags":[{"name":"更新","color":"Coral","productId":"098be112-50ec-44e4-b746-6bc8bf76af97","links":null,"id":"a2f84374-4a3f-4d22-96fd-765e9de495bf"}]}]
SpreadJS 在数据模式中提供层次结构选项来定义数据源是层次结构的。
hierarchy选项继承自GC.Data.IDataSourceOption接口,提供如下参数:
type:定义层次结构类型。 它可以是以下值之一:Parent、ChildrenPath、Level 或 Custom。
column:指定将有助于构建分层数据的层次结构键。
levelOffset:表示电平偏移量,可以增加或减少电平。 通常,层次级别从 0 开始。
outlineColumn:继承自GC.Data.IHierarchyOutlineColumnOptions,它指定了可以显示为层次结构的列。
summaryFields:继承自GC.Data.IHierarchySummaryFieldCollection,定义字段的公式。
parse:继承自GC.Data.IHierarchyCustomParseOptions,将自定义层级类型的主键解析为父键。
unparse:继承自GC.Data.IHierarchyCustomUnparseOptions,构建自定义层级类型的主键。
层次结构有四种类型,Parent、Level、ChildrenPath 和 Custom。 在将表添加到数据管理器时,可以配置它们中的每一个:
在Parent hierarchy中,使用层次结构类型'Parent',主键用于指示层次结构id。
以下代码示例显示了如何在集算表中设置父层次结构:
<script>
var spread = new GC.Spread.Sheets.Workbook(document.querySelector('#ss'), { sheetCount: 0 });
initSpread(spread)
function initSpread(spread) {
var dataManager = spread.dataManager();
initHierarchyParentType(spread, dataManager);
}
function initHierarchyParentType(spread, dataManager) {
var table = dataManager.addTable("hierarchyParentTable", {
data: initHierarchyParentData(),
schema: {
hierarchy: {
type: 'Parent', // config the parent hierarchy type
column: 'parentId', // specify the column that could be used for building hierarchical view
},
columns: {
'id': { dataName: 'id', isPrimaryKey: true }, // the primary key is required
'parentId': { dataName: 'parentId', dataType: "reorder" },
}
}
});
var sheet = spread.addSheetTab(0, "HierarchyParent", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = true;
table.fetch().then(function () {
var myView = table.addView("myView", [
{
value: "parentId", width: 200,
outlineColumn: {
showImage: true,
images: ['httpss://en.onboarding.grapecitydev.com/spreadjs/demos/spread/source/images/task-1.png', 'httpss://en.onboarding.grapecitydev.com/spreadjs/demos/spread/source/images/task-2.png', 'httpss://en.onboarding.grapecitydev.com/spreadjs/demos/spread/source/images/task-3.png'],
expandIndicator: 'httpss://en.onboarding.grapecitydev.com/spreadjs/demos/spread/source/images/increaseIndicator.png',
collapseIndicator: 'httpss://en.onboarding.grapecitydev.com/spreadjs/demos/spread/source/images/decreaseIndicator.png',
},
},
{ value: "id", width: 200 },
]);
sheet.setDataView(myView);
});
}
function initHierarchyParentData() {
var data = [
{ id: 1, parentId: -1 },
{ id: 2, parentId: -1 },
{ id: 3, parentId: 1 },
{ id: 4, parentId: 1 },
{ id: 5, parentId: 2 },
{ id: 6, parentId: 2 },
{ id: 6, parentId: 2 },
];
return data;
}
</script>
在 ChildrenPath 层次结构中,使用层次结构类型“ChildrenPath”。 在这种情况下,主键不是必需的,但是,最好为孩子指明主键。
以下代码示例显示了如何在集算表中设置 ChildrenPath 层次结构:
<script>
var spread = new GC.Spread.Sheets.Workbook(document.querySelector('#ss'), { sheetCount: 0 });
initSpread(spread)
function initSpread(spread) {
var dataManager = spread.dataManager();
initHierarchyParentType(spread, dataManager);
}
function initHierarchyParentType(spread, dataManager) {
var taskTable = dataManager.addTable("Tasks", {
data: initHierarchyChildData(),
schema: {
hierarchy: {
type: 'ChildrenPath',
column: 'TaskChildren',
},
columns: {
TaskName: { dataName: 'name' },
TaskChildren: { dataName: 'children' },
// other columns in the child
}
}
});
var sheet = spread.addSheetTab(0, "HierarchyParent", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = true;
taskTable.fetch().then(r => {
var taskView = taskTable.addView('TaskView', [
{
value: 'TaskName', outlineColumn: true, width: "*" // this option indicates the column showing as outline column
}
]);
sheet.setDataView(taskView);
})
}
function initHierarchyChildData() {
var data = [
{
name: 'USA',
children: [
{
name: 'Texas',
children: [
{
name: 'Houston',
},
{
name: 'Dallas',
},
{
name: 'San Antonio',
}
]
}
]
},
{
name: 'India',
children: [
{
name: 'UP',
children: [
{ name: 'Noida' },
{ name: 'Ghaziabad' },
{ name: 'Agra' },
]
}
]
}
]
return data;
}
</script>
在级别层次结构中,使用层次结构类型“级别”。 在这种情况下,也不需要主键,但最好指明主键。
以下代码示例显示了如何在集算表中设置级别层次结构:
<script>
var spread = new GC.Spread.Sheets.Workbook(document.querySelector('#ss'), { sheetCount: 0 });
initSpread(spread)
function initSpread(spread) {
var dataManager = spread.dataManager();
initHierarchyParentType(spread, dataManager);
}
function initHierarchyParentType(spread, dataManager) {
var taskTable = dataManager.addTable("Tasks", {
data: initHierarchyLevelData(),
schema: {
hierarchy: {
type: 'Level',
column: 'TaskLevel',
},
columns: {
TaskName: { dataName: 'name' },
TaskId: { dataName: 'id', isPrimaryKey: true }, // using primary key to indicate the hierarchy id optionally if exist
TaskLevel: { dataName: 'level' },
}
}
});
var sheet = spread.addSheetTab(0, "HierarchyParent", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = true;
taskTable.fetch().then(r => {
var taskView = taskTable.addView('TaskView', [
{
value: 'TaskName', outlineColumn: true// this option indicates the column showing as outline column
}
]);
sheet.setDataView(taskView);
})
}
function initHierarchyLevelData() {
var data = [
{ name: 'USA', level: -1, id: 1 },
{ name: 'Texas', level: 0, id: 2 },
{ name: 'Houston', level: 1, id: 3 },
{ name: 'California', level: 0, id: 4 },
{ name: 'San Francisco', level: 1, id: 5 },
{ name: 'Los Angeles', level: 1, id: 6 },
];
return data;
}
</script>
在自定义层次结构中,使用层次结构类型“自定义”。 在这里,主键是可选的。
以下代码示例显示了如何在表格中设置自定义层次结构:
<script>
var spread = new GC.Spread.Sheets.Workbook(document.querySelector('#ss'), { sheetCount: 0 });
initSpread(spread)
function initSpread(spread) {
var dataManager = spread.dataManager();
initHierarchyParentType(spread, dataManager);
}
function initHierarchyParentType(spread, dataManager) {
var taskTable = dataManager.addTable("Tasks", {
data: initHierarchyChildData(),
schema: {
hierarchy: {
type: 'Custom',
column: 'id',
parse: function (options) {
// parse the primary key "1.1.1" to "1.1"
// the returned value will be treated as parentId
let seg = options.data.TaskId.split('.');
return seg.slice(0, seg.length - 1).join('.')
},
unparse: function (options) {
let parentIds, currentIndex = options.index, parentData = options.parentData, parentKey = parentData && parentData.TaskId;
if (parentKey) {
let sp = parentKey.split('.');
parentIds = sp;
} else {
parentIds = [];
}
parentIds.push(currentIndex + 1);
return parentIds.join('.');
}
},
columns: {
TaskName: { dataName: 'name' },
TaskId: { dataName: 'id', isPrimaryKey: true }, // using primary key to indicate the hierarchy id optionally
}
}
});
var sheet = spread.addSheetTab(0, "HierarchyParent", GC.Spread.Sheets.SheetType.tableSheet);
sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.allowAddNew = true;
taskTable.fetch().then(r => {
var taskView = taskTable.addView('TaskView', [
{
value: 'TaskName', outlineColumn: true, width: "*" // this option indicates the column showing as outline column
}
]);
sheet.setDataView(taskView);
})
}
function initHierarchyChildData() {
var data = [
{
id: '1', name: "1"
},
{ id: '2', name: '2' },
{ id: '1.1', name: "1.1" },
{ id: '1.1.1', name: '1.1.1' },
{ id: '2.1', name: '2.1' }
]
return data;
}
</script>
提升指定行的层级数据层级
TableSheets 允许用户使用 promoteHierarchyLevel(row: number) 方法通过指定索引提升层次结构数据级别。
降低指定行的层级数据级别
TableSheets 允许用户使用 demoteHierarchyLevel(row: number, withChildren?: boolean) 方法降低指定行的层次结构数据级别。
将层级数据向上移动指定行
TableSheets 允许用户使用 moveUp(row: number) 方法按指定索引向上移动层次结构数据。
将层级数据向下移动指定行
TableSheets 允许用户使用 moveDown(row: number) 方法将层次结构数据向上移动指定的索引。
在所选行后添加一条记录
TableSheets 允许用户使用 addHierarchyItemAfter(row: number, rowData: any) 方法在指定行之后添加新行。
在所选行上方添加一条记录
TableSheets 允许用户使用 addHierarchyItemAbove(row: number, rowData: any) 方法在所选行上方添加记录。 添加的记录将替换所选记录的位置,所选记录将成为添加记录的子记录。
在所选行下方添加一条记录
Tablesheet 允许用户使用 addHierarchyItemBelow(row: number, rowData: any) 方法添加新行数据作为指定行的子项。 您可以在所选行下方添加一条记录,添加的记录将是所选记录的最后一个子记录。
删除一条记录
TableSheet 让用户删除子记录和它自己。
展开一条记录
单击列标题时会出现此菜单项。 它使用 expandAllHierarchyLevels() 方法扩展所有层级。
折叠记录
单击列标题时出现菜单项,使用 expandHierarchyLevel(level: number) 方法最多支持 9 级菜单项。
升级、降级、上移/下移、添加前/后/上/下、展开/折叠级别的菜单项可以在运行时显示或隐藏。
下图展示了单击行标题时显示以下菜单项。
下图展示了在右键单击列标题时显示以下菜单项。
代码示例显示了如何切换菜单项可见性。
// show the menu items for hierarchical records
sheet.options.menuItemVisibility = {
// the options below be on the row header
promoteMenuItemVisible: true,
demoteMenuItemVisible: true,
// the options below be on the column header
expandAllLevelMenuItemVisible: true,
collapseAllLevelMenuItemVisible: true,
expandToLevelMenuItemVisible: true,
// the options below be on the row header
// and the menu items be enable for the dataType of the column be rowOrder
moveUpMenuItemVisible: true,
moveDownMenuItemVisible: true,
addBeforeMenuItemVisible: true,
addAfterMenuItemVisible: true,
addAboveMenuItemVisible: true,
addBelowMenuItemVisible: true,
};
使用数据源和 TableSheet 功能区的列选项卡,在 SpreadJS Designer 中也支持层次结构数据。 您可以配置层次结构类型、汇总公式、大纲列图像等。
层次结构部分提供以下选项:
层次结构类型:类型可以是 Parent、ChildrenPath 或 Level。
汇总公式:用户可以为表格的指定列输入层级汇总公式。 要了解有关汇总公式的更多信息,请参阅公式主题。
大纲栏:大纲栏可用于自定义大纲栏的显示。