概览

TableSheet支持层次数据,可以通过数据源模型中的层次选项来配置。

关于层次数据的配置,在数据源的模型中可以看到如下配置: SpreadJS TableSheet的层次数据支持有四种不同的层次结构: Parent(父子), Level(等级层次), ChildrenPath(子路径), Custom(自定义). 在向数据管理器添加表的时候,可以对每一项进行配置。 配置父子层次结构类型: 配置等级层次结构类型: 配置子路径层次结构类型: 配置自定义层次结构类型: 有一些操作可以更新TableSheet中的层次结构记录。 1.提升一个数据的层级: 2.降低一个数据的层级: 3.上移: 4.下移: 5.在指定行前插入新数据: 6.在指定行后插入新数据: 7.添加一个新的行数据作为指定行的父行: 8.添加一个新的行数据作为指定行的子行。: 9.展开所有层级: 10.收起所有层级: 11.按指定层级展开: 在TableSheet选项中,有一些选项可以切换分层记录的菜单项的可见性。 要在TableSheet中显示层次结构菜单项:
window.onload = function() { var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), { sheetCount: 0 }); initSpread(spread); }; function initSpread(spread) { spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader; spread.clearSheets(); var dataManager = spread.dataManager(); initHierarchyParentType(spread, dataManager); initHierarchyLevelType(spread, dataManager); initHierarchyCustomType(spread, dataManager); initHierarchyChildrenType(spread, dataManager); } function initHierarchyParentType(spread, dataManager) { var apiUrl = getBaseApiUrl() + "/Hierarchy_Data"; var table = dataManager.addTable("hierarchyParentTable", { remote: { read: { url: apiUrl } }, autoSync: true, schema: { hierarchy: { type: 'Parent', // config the parent hierarchy type column: 'TaskParentId', // specify the column that could be used for building hierarchical view }, columns: { 'TaskId':{ dataName: 'id', isPrimaryKey: true}, // the primary key is required 'TaskParentId':{ dataName: 'parentId'}, 'TaskTitle':{ dataName: 'title'}, 'TaskOwner':{ dataName: 'owner'}, 'StartDate':{ dataName: 'startDate'}, 'DueDate':{ dataName: 'dueDate'}, 'TaskComplete':{ dataName: 'complete'}, 'TaskOrder':{ dataName: 'rowOrder', dataType: 'rowOrder'}, // if dataType be rowOrder, the records could be moved and added } } }); var sheet = spread.addSheetTab(0, "HierarchyParent", GC.Spread.Sheets.SheetType.tableSheet); sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader); sheet.options.allowAddNew = true; // 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, }; table.fetch().then(function () { var myView = table.addView("myView", [ { value: "TaskTitle", width: 200, outlineColumn: { showImage: true, images: ['$DEMOROOT$/spread/source/images/task-1.png', '$DEMOROOT$/spread/source/images/task-2.png', '$DEMOROOT$/spread/source/images/task-3.png'], expandIndicator: '$DEMOROOT$/spread/source/images/increaseIndicator.png', collapseIndicator: '$DEMOROOT$/spread/source/images/decreaseIndicator.png', } }, { value: "TaskOwner", width: 200 }, { value: "StartDate", width: 200 }, { value: "DueDate", width: 200 }, { value: "TaskComplete", width: 200 }, ]); spread.suspendPaint(); sheet.setDataView(myView); spread.resumePaint(); }); } function initHierarchyLevelType(spread, dataManager) { var apiUrl = getBaseApiUrl() + "/Hierarchy_Data/level"; var table = dataManager.addTable("hierarchyLevelTable", { remote: { read: { url: apiUrl }, create: { url: apiUrl }, update: { url: apiUrl, method: 'PUT' }, delete: { url: apiUrl }, batch: { url: apiUrl } }, batch: true, // autoSync: true, schema: { hierarchy: { type: 'Level', // config the level hierarchy type column: 'level', outlineColumn: { // the outline column could be set in the hierarchy value: "TaskTitle", showImage: true, images: ['$DEMOROOT$/spread/source/images/task-1.png', '$DEMOROOT$/spread/source/images/task-2.png', '$DEMOROOT$/spread/source/images/task-3.png'], expandIndicator: '$DEMOROOT$/spread/source/images/increaseIndicator.png', collapseIndicator: '$DEMOROOT$/spread/source/images/decreaseIndicator.png', } }, columns: { 'TaskTitle': { dataName: 'title' }, 'TaskOwner': { dataName: 'owner' }, 'StartDate': { dataName: 'startDate' }, 'DueDate': { dataName: 'dueDate' }, 'TaskComplete': { dataName: 'complete' }, } } }); var sheet = spread.addSheetTab(1, "HierarchyLevel", GC.Spread.Sheets.SheetType.tableSheet); sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader); sheet.options.allowAddNew = true; sheet.options.menuItemVisibility = { promoteMenuItemVisible: true, demoteMenuItemVisible: true, expandAllLevelMenuItemVisible: true, collapseAllLevelMenuItemVisible: true, expandToLevelMenuItemVisible: true, moveUpMenuItemVisible: true, moveDownMenuItemVisible: true, addBeforeMenuItemVisible: true, addAfterMenuItemVisible: true, addAboveMenuItemVisible: true, addBelowMenuItemVisible: true, }; table.fetch().then(function () { var myView = table.addView("myView", [ { value: "TaskTitle", width: 200 }, { value: "TaskOwner", width: 200 }, { value: "StartDate", width: 200 }, { value: "DueDate", width: 200 }, { value: "TaskComplete", width: 200 }, ]); spread.suspendPaint(); sheet.setDataView(myView); spread.resumePaint(); }); } function initHierarchyChildrenType(spread, dataManager) { var apiUrl = getBaseApiUrl() + "/Hierarchy_Data/children"; var table = dataManager.addTable("hierarchyChildrenTable", { remote: { read: { url: apiUrl }, create: { url: apiUrl }, update: { url: apiUrl, method: 'PUT' }, delete: { url: apiUrl }, batch: { url: apiUrl } }, batch: true, // autoSync: true, schema: { hierarchy: { type: 'ChildrenPath', // config the children path hierarchy type column: 'children', }, columns: { 'TaskTitle':{ dataName: 'title'}, 'TaskOwner':{ dataName: 'owner'}, 'StartDate':{ dataName: 'startDate'}, 'DueDate':{ dataName: 'dueDate'}, 'TaskComplete':{ dataName: 'complete'}, } } }); var sheet = spread.addSheetTab(2, "HierarchyChildren", GC.Spread.Sheets.SheetType.tableSheet); sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader); sheet.options.allowAddNew = true; sheet.options.menuItemVisibility = { promoteMenuItemVisible: true, demoteMenuItemVisible: true, expandAllLevelMenuItemVisible: true, collapseAllLevelMenuItemVisible: true, expandToLevelMenuItemVisible: true, moveUpMenuItemVisible: true, moveDownMenuItemVisible: true, addBeforeMenuItemVisible: true, addAfterMenuItemVisible: true, addAboveMenuItemVisible: true, addBelowMenuItemVisible: true, }; table.fetch().then(function () { var myView = table.addView("myView", [ { value: "TaskTitle", width: 200, outlineColumn: true }, { value: "TaskOwner", width: 200 }, { value: "StartDate", width: 200 }, { value: "DueDate", width: 200 }, { value: "TaskComplete", width: 200 }, ]); spread.suspendPaint(); sheet.setDataView(myView); spread.resumePaint(); }); } function initHierarchyCustomType(spread, dataManager) { var apiUrl = getBaseApiUrl() + "/Hierarchy_Data"; var table = dataManager.addTable("hierarchyCustomTable", { remote: { read: { url: apiUrl } }, autoSync: true, schema: { hierarchy: { type: 'Custom', // config the custom hierarchy type column: 'WBSNumber', parse: function (options) { // parsing the key to the parent key that be similar as the parent hierarchy type let segments = options.data.WBSNumber.split('.'); return segments.slice(0, segments.length - 1).join('.'); }, unparse: function (options) { // if the key should be updated, unparse the related data to the key let parentIds, currentIndex = options.index, parentData = options.parentData, parentKey = parentData && parentData.WBSNumber; if (parentKey) { let sp = parentKey.split('.'); parentIds = sp; } else { parentIds = []; } parentIds.push(currentIndex + 1); return parentIds.join('.'); } }, columns: { 'WBSNumber': { dataName: 'wbs', isPrimaryKey: true }, 'TaskTitle': { dataName: 'title' }, 'TaskOwner': { dataName: 'owner' }, 'StartDate': { dataName: 'startDate' }, 'DueDate': { dataName: 'dueDate' }, 'TaskComplete': { dataName: 'complete' }, } } }); var sheet = spread.addSheetTab(3, "HierarchyCustom", GC.Spread.Sheets.SheetType.tableSheet); sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader); sheet.options.allowAddNew = true; sheet.options.menuItemVisibility = { promoteMenuItemVisible: true, demoteMenuItemVisible: true, expandAllLevelMenuItemVisible: true, collapseAllLevelMenuItemVisible: true, expandToLevelMenuItemVisible: true, }; table.fetch().then(function () { var myView = table.addView("myView", [ { value: "WBSNumber", width: 200, outlineColumn: true }, { value: "TaskTitle", width: 200 }, { value: "TaskOwner", width: 200 }, { value: "StartDate", width: 200 }, { value: "DueDate", width: 200 }, { value: "TaskComplete", width: 200 }, ]); spread.suspendPaint(); sheet.setDataView(myView); spread.resumePaint(); }); } function _getElementById(id) { return document.getElementById(id); } function getBaseApiUrl() { return window.location.href.match(/http.+spreadjs\/SpreadJSTutorial\//)[0] + 'server/api'; }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta name="spreadjs culture" content="zh-cn" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <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-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="$DEMOROOT$/spread/source/data/outlineColumn-wbs.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="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> </body> </html>
.colorLabel { background-color: #F4F8EB; } .rightAlignLabel { width: 120px; text-align: right; margin-right: 8px; } input[type="text"] { width: 190px; } .sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .option-row { font-size: 14px; padding: 5px; margin-top: 10px; } label { margin-bottom: 6px; } input { padding: 4px 6px; } input[type=button] { margin-top: 6px; width: 190px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }