找回密码
 立即注册

QQ登录

只需一步,快速开始

KevinChen 讲师达人认证 悬赏达人认证 SpreadJS 开发认证
论坛元老   /  发表于:2022-11-12 22:24  /   查看:2033  /  回复:0
SpreadJS V16 新增了对分层数据的支持,并且可以根据用户提供的分层数据自动生成对应的树形表格。


对分层数据的支持是在 SpreadJS 的 DataManager 中实现的,并通过集算表进行展示和交互。
相关信息请了解集算表相关特性:
https://demo.grapecity.com.cn/sp ... eet/overview/purejs

分层数据源类型:

1. 记录具有id和parentId的属性:

  1. <div>[
  2.   { id: 1, parentId: -1 },
  3.   { id: 2, parentId: -1 },
  4.   { id: 3, parentId:  1 },
  5.   { id: 4, parentId:  1 },
  6.   { id: 5, parentId:  2 }
  7. ]</div>
复制代码


2. 记录有属性表示层级,层级由记录的顺序确定,每条记录都可以有这个属性作为主键:


  1. <div>[
  2.   { name: 'USA', level: -1, id: 1 },
  3.   { name: 'Texas', level: 0, id: 2 },
  4.   { name: 'Houston', level: 1, id: 3 },
  5.   { name: 'California', level: 0, id: 4 },
  6.   { name: 'San Francisco', level: 1, id: 5 },
  7.   { name: 'Los Angeles', level: 1, id: 6 },
  8. ]</div>
复制代码


3. 记录有属性表示分层子级,记录一直是没有根记录的树数据,每个子级都可以有该属性作为主键:


  1. <div>[
  2.     {
  3.         name: 'USA',
  4.         children: [
  5.             {
  6.                 name: 'Texas',
  7.                 children: [
  8.                     {
  9.                         name: 'Houston',
  10.                     }
  11.                 ]
  12.             }
  13.         ]
  14.     }
  15. ]</div>
复制代码


4. 记录有主键,键可以通过自定义函数解析为层次结构:


  1. <div>[
  2.   { id: '1' },
  3.   { id: '2' },
  4.   { id: '1.1' },
  5.   { id: '1.1.1' },
  6.   { id: '2.1' }
  7. ]</div>
复制代码


分层数据源配置
1. 数据源选项可以配置分层选项:

  1. <div>interface GC.Data.IDataSourceOption {
  2.     //others options...
  3.     schema?: {
  4.         hierarchy: GC.Data.IHierarchyOption // define whether the data source is hierarchical
  5.         //others options...
  6.     }
  7. }
  8. interface GC.Data.IHierarchyOption {
  9.   type: 'Parent' | 'ChildrenPath' | 'Level' | 'Custom', // the hierarchy type
  10.   column: string // the hierarachy key that will help to build the hierarchical data
  11.   outlineColumn?: string | GC.Data.IHierarchyOutlineColumnOptions; // the options for self-defined outline
  12.   summaryFields: GC.Data.IHierarchySummaryFieldCollection // define the formulas for the fields
  13.   parse: (options: GC.Data.IHierarchyCustomParseOptions) => any; // parse the primary key of the custom hierarchy type to the parent key
  14.   unparse?: (options: GC.Data.IHierarchyCustomUnparseOptions) => any; // build the primary key of the custom hierarchy type
  15. }
  16. interface GC.Data.IHierarchySummaryFieldCollection {
  17.     [key: string]: string; // the key is the field name, and the value is the formula
  18. }
  19. interface GC.Data.IHierarchyCustomParseOptions {
  20.   data: any,
  21.   index: number,
  22. }
  23. interface GC.Data.IHierarchyCustomUnparseOptions {
  24.   data: any,
  25.   index: number,
  26.   parentData: any
  27. }
  28. interface GC.Data.IOutlineColumnOptions {
  29.     showCheckBox?: boolean;
  30.     showImage?: boolean;
  31.     images?: string[];
  32.     showIndicator?: boolean;
  33.     expandIndicator?: string;
  34.     collapseIndicator?: string;
  35. }
  36. interface GC.Data.IHierarchyOutlineColumnOptions {
  37.     value: string; // specify the column name that will be outline column
  38.     showCheckBox?: boolean;
  39.     showImage?: boolean;
  40.     images?: string[];
  41.     showIndicator?: boolean;
  42.     expandIndicator?: string;
  43.     collapseIndicator?: string;
  44. }
  45. interface GC.Data.IColumn {
  46.   //other options...
  47.   // the rowOrder in the dataType is used for the ordering the records, the value of the column should be a number,
  48.   // it's used by the data manager when moving or inserting
  49.   // it's better to hide the column for the end user
  50.   dataType?: "string" | "number" | "boolean" | "object" | "array" | "date" | "rowOrder";
  51.   outlineColumn?: boolean | GC.Data.IOutlineColumnOptions // indicates the column be an outline column or the outline column options, and there should be only one outline column in the columns
  52. }
  53. </div>
复制代码

  1. <div>var taskTable = dataManager.addTable("Tasks", {
  2.     remote: { ... },
  3.     schema: {
  4.         hierarchy: {
  5.           type: 'Parent',
  6.           column: 'TaskParentId',
  7.         },
  8.         columns: {
  9.           TaskName: {dataName: 'name' },
  10.           TaskId: { dataName: 'id', isPrimaryKey: true }, // using primary key to indicate the hierarchy id
  11.           TaskParentId: { dataName: 'parentId' },
  12.           TaskRowOrder: { dataName: 'rowOrder', dataType: "rowOrder" }, // optional property for inserting and moving up/down
  13.         }
  14.     }
  15. });

  16. var taskView = taskTable.addView('TaskView',[
  17.   {
  18.     value: 'TaskName', outlineColumn: true // this option indicates the column showing as outline column
  19.   }
  20. ]);
  21. </div>
复制代码

  1. <div>var taskTable = dataManager.addTable("Tasks", {
  2.     remote: { ... },
  3.     schema: {
  4.         hierarchy: {
  5.           type: 'Level',
  6.           column: 'TaskLevel',
  7.         },
  8.         columns: {
  9.           TaskName: {dataName: 'name' },
  10.           TaskId: { dataName: 'id', isPrimaryKey: true }, // using primary key to indicate the hierarchy id optionally if exist
  11.           TaskLevel: { dataName: 'level' },
  12.           TaskRowOrder: { dataName: 'rowOrder', dataType: "rowOrder" }, // optional property for inserting and moving up/down
  13.         }
  14.     }
  15. });

  16. var taskView = taskTable.addView('TaskView',[
  17.   {
  18.     value: 'TaskName', outlineColumn: true// this option indicates the column showing as outline column
  19.   }
  20. ]);
  21. </div>
复制代码

  1. <div>var taskTable = dataManager.addTable("Tasks", {
  2.     remote: { ... },
  3.     schema: {
  4.         hierarchy: {
  5.           type: 'ChildrenPath',
  6.           column: 'TaskChildren',
  7.         },
  8.         columns: {
  9.           TaskName: {dataName: 'name' },
  10.           TaskChildren: { dataName: 'children' },
  11.           // other columns in the child
  12.         }
  13.     }
  14. });
  15. var taskView = taskTable.addView('TaskView',[
  16.   {
  17.     value: 'TaskName', outlineColumn: true // this option indicates the column showing as outline column
  18.   }
  19. ]);
  20. </div>
复制代码

  1. <div>var taskTable = dataManager.addTable("Tasks", {
  2.     remote: { ... },
  3.     schema: {
  4.         hierarchy: {
  5.           type: 'Custom',
  6.           column: 'TaskId',
  7.           parse: function(options){
  8.             // parse the primary key "1.1.1" to "1.1"
  9.             // the returned value will be treated as parentId
  10.             let seg = options.data.TaskId.split('.');
  11.             return seg.slice(0,seg.length-1).join('.')
  12.           },
  13.           unparse: function(options){
  14.             let parentIds, currentIndex = options.index, parentData = options.parentData, parentKey = parentData && parentData.TaskId;
  15.             if (parentKey) {
  16.               let sp = parentKey.split('.');
  17.               parentIds = sp;
  18.             } else {
  19.               parentIds = [];
  20.             }
  21.             parentIds.push(currentIndex + 1);
  22.             return parentIds.join('.');
  23.           }
  24.         },
  25.         columns: {
  26.           TaskName: {dataName: 'name' },
  27.           TaskId: { dataName: 'id', isPrimaryKey: true }, // using primary key to indicate the hierarchy id optionally
  28.         }
  29.     }
  30. });
  31. var taskView = taskTable.addView('TaskView',[
  32.   {
  33.     value: 'TaskName', outlineColumn: true // this option indicates the column showing as outline column
  34.   }
  35. ]);
  36. </div>
复制代码



image.png664732970.png

2. 降级记录,将当前记录的 parentId 替换为其上面记录的 id,如果 withChildren 为 true,则当前记录的子级将降低:



  1. <div>    /**
  2.      * Demote the hierarchy data level of the specified row.
  3.      * @param {number} row - The row index.
  4.      * @param {boolean} withChildren - Optional, the children will be demoted with the record by default.
  5.      * @returns {void}
  6.      * @example
  7.      * //This example demote the hierarchy data level by specified index.
  8.      * tableSheet.demoteHierarchyLevel(8);
  9.      */
  10.      function demoteHierarchyLevel(row: number, withChildren?: boolean): void
  11. </div>
复制代码


image.png722710571.png

3. 上移一条记录:
当列的 dataType 为 rowOrder 且列的 value 类型为 number 时,记录可以上下移动。
在层次数据中,向上移动只能在同一父级下的记录之间移动,
当父记录移动时,子记录将移动。

  1. <div>    /**
  2.      * Move the hierarchy data by the specified row up.
  3.      * @param {number} row - The row index.
  4.      * @returns {void}
  5.      * @example
  6.      * //This example move the hierarchy data by specified index up.
  7.      * tableSheet.moveUp(8);
  8.      */
  9.      function moveUp(row: number): void</div>
复制代码

image.png770778503.png

4. 向下移动一个记录,它与向上移动相同:

    /**
     * Move the hierarchy data by the specified row down.
     * @param {number} row - The row index.
     * @returns {void}
     * @example
     * //This example move the hierarchy data by specified index down.
     * tableSheet.moveDown(8);
     */
     function moveDown(row: number): void

image.png408199462.png

5. 添加记录:
默认情况下,它将在选择位置之前插入一条记录,并且插入的记录应该是选择记录的兄弟。

image.png677301330.png

5.1 选中前添加记录:

  1. <div>    /**
  2.      * Add a new row data before the specified row.
  3.      * @param {number} row - The row index.
  4.      * @param {Object} rowData - The row data.
  5.      * @returns {void}
  6.      * @example
  7.      * //This example adds a new row data before the specified row.
  8.      * tableSheet.addHierarchyItemBefore(8, {id: 8, name: "grapecity"});
  9.      */
  10.      function addHierarchyItemBefore(row: number, rowData: any): void</div>
复制代码


5.2 选中后添加记录:



  1. <div>    /**
  2.      * Add a new row data after the specified row.
  3.      * @param {number} row - The row index.
  4.      * @param {Object} rowData - The row data.
  5.      * @returns {void}
  6.      * @example
  7.      * //This example adds a new row data after the specified row.
  8.      * tableSheet.addHierarchyItemAfter(8, {id: 8, name: "grapecity"});
  9.      */
  10.      function addHierarchyItemAfter(row: number, rowData: any): void</div>
复制代码


5.3 在选中的上面添加记录,添加的记录会替换选中记录的位置,选中的记录会是被添加记录的子:

  1. <div>    /**
  2.      * Add a new row data as the parent of the specified row.
  3.      * @param {number} row - The row index.
  4.      * @param {Object} rowData - The row data.
  5.      * @returns {void}
  6.      * @example
  7.      * //This example adds a new row data as the parent of the specified row.
  8.      * tableSheet.addHierarchyItemAbove(8, {id: 8, name: "grapecity"});
  9.      */
  10.      function addHierarchyItemAbove(row: number, rowData: any): void</div>
复制代码


5.4 在选中的下方添加记录,添加的记录将是选中记录的最后一个子记录:

  1. <div>    /**
  2.      * Add a new row data as the child of the specified row.
  3.      * @param {number} row - The row index.
  4.      * @param {Object} rowData - The row data.
  5.      * @returns {void}
  6.      * @example
  7.      * //This example adds a new row data as the child of the specified row.
  8.      * tableSheet.addHierarchyItemBelow(8, {id: 8, name: "grapecity"});
  9.      */
  10.      function addHierarchyItemBelow(row: number, rowData: any): void</div>
复制代码

6. 删除一条记录,它会删除子记录和它自己。
7. 展开所有级别:
单击列标题时将显示菜单项。

  1. <div>    /**
  2.      * Expand the all hierarchy levels.
  3.      * @returns {void}
  4.      * @example
  5.      * //This example expand the all hierarchy levels.
  6.      * tableSheet.expandAllHierarchyLevels();
  7.      */
  8.      function expandAllHierarchyLevels(): void
  9. </div>
复制代码


image.png320039672.png

8. 折叠所有级别:

  1. <div>    /**
  2.      * Collapse the all hierarchy levels.
  3.      * @returns {void}
  4.      * @example
  5.      * //This example collapse the all hierarchy levels.
  6.      * tableSheet.collapseAllHierarchyLevels();
  7.      */
  8.      function collapseAllHierarchyLevels(): void</div>
复制代码

image.png974017333.png

9. 将记录扩展到指定级别:

单击列标题时会显示菜单,最多支持9级菜单项。

  1. <div>    /**
  2.      * Expand the hierarchy data by specified level.
  3.      * @param {number} level - The level to expand.
  4.      * @returns {void}
  5.      * @example
  6.      * //This example expand the hierarchy data by specified level.
  7.      * tableSheet.expandHierarchyLevel(8);
  8.      */
  9.      function expandHierarchyLevel(level: number): void</div>
复制代码

image.png73657530.png

10. 切换菜单项的可见性:

提升、降级、上移/下移、前/后/上/下添加、展开/折叠级别的菜单项可以通过选项显示或隐藏:


  1. <div>interface GC.Spread.Sheets.TableSheet.ITableSheetOptions {
  2.   // other properties...
  3.   menuItemVisibility?: {
  4.         // the options below be on the row header
  5.         promoteMenuItemVisible?: boolean;
  6.         demoteMenuItemVisible?: boolean;
  7.         // the options below be on the row header
  8.         // and the menu items be enable for the dataType of the column be rowOrder
  9.         moveUpMenuItemVisible?: boolean;
  10.         moveDownMenuItemVisible?: boolean;
  11.         addBeforeMenuItemVisible?: boolean;
  12.         addAfterMenuItemVisible?: boolean;
  13.         addAboveMenuItemVisible?: boolean;
  14.         addBelowMenuItemVisible?: boolean;
  15.         // the options below be on the column header
  16.         expandAllLevelMenuItemVisible?: boolean;
  17.         collapseAllLevelMenuItemVisible?: boolean;
  18.         expandToLevelMenuItemVisible?: boolean;
  19.     };
  20. }

  21. tableSheet.options.menuItemVisibility = {
  22.         promoteMenuItemVisible: true,
  23.         demoteMenuItemVisible: true,
  24.         moveUpMenuItemVisible: true,
  25.         moveDownMenuItemVisible: true,
  26.         addBeforeMenuItemVisible: true,
  27.         addAfterMenuItemVisible: true,
  28.         addAboveMenuItemVisible: true,
  29.         addBelowMenuItemVisible: true,
  30.         expandAllLevelMenuItemVisible: true,
  31.         collapseAllLevelMenuItemVisible: true,
  32.         expandToLevelMenuItemVisible: true,
  33.     }</div>
复制代码


11. 对层次记录进行排序意味着按级别对记录的任何属性进行排序。

12. 过滤结果将显示父记录及其本身,如果记录有孩子,孩子也会显示。

13. 自定义轮廓列,带有outlineColumn选项的列将在表格中显示轮廓样式,并且只有一列是轮廓:

13.1 . 在架构中:



  1. <div>var taskTable = dataManager.addTable("Tasks", {
  2.     remote: { ... },
  3.     schema: {
  4.         hierarchy: {
  5.           type: 'Parent',
  6.           column: 'TaskParentId',
  7.           outlineColumn: {
  8.               value: 'TaskName', // specify the column name that will be outline column
  9.               showCheckBox: true
  10.             }
  11.         },
  12.         columns: {
  13.           TaskName: {dataName: 'name' },
  14.           TaskId: { dataName: 'id', isPrimaryKey: true }, // using primary key to indicate the hierarchy id
  15.           TaskParentId: { dataName: 'parentId' },
  16.           TaskRowOrder: { dataName: 'rowOrder', dataType: "rowOrder" }, // optional property for inserting and moving up/down
  17.         }
  18.     }
  19. });</div>
复制代码


13.2. 在视图列中,优先级更高:



  1. <div>var taskTable = dataManager.addTable("Tasks", {
  2.     remote: { ... },
  3.     schema: {
  4.         hierarchy: {
  5.           type: 'Parent',
  6.           column: 'TaskParentId',
  7.           outlineColumn: 'TaskName'
  8.         },
  9.         columns: {
  10.           TaskName: {dataName: 'name' },
  11.           TaskId: { dataName: 'id', isPrimaryKey: true }, // using primary key to indicate the hierarchy id
  12.           TaskParentId: { dataName: 'parentId' },
  13.           TaskRowOrder: { dataName: 'rowOrder', dataType: "rowOrder" }, // optional property for inserting and moving up/down
  14.         }
  15.     }
  16. });

  17. var taskView = taskTable.addView('TaskView',[
  18.   {
  19.     value: 'TaskName', outlineColumn: { showCheckBox: true } // this option indicates the column showing as outline column
  20.   }
  21. ]);</div>
复制代码


14. 该功能可能与层次结构发生冲突
14.1 . groupBy 不能处理层次结构数据;
14.2. 固定行不能与层次结构数据一起使用;
14.3 . 对于仅影响当前行数据的特征,resetRow 不能很好地处理层次数据,但一些用于层次特征的属性不会受到影响。

层次结构远程接口
在父层级类型中,远程接口和之前一样,
但如果它是级别或子路径层次结构类型,每个远程接口都会将整个记录发送到远程而不被删除,
因为它不能识别插入记录的父记录。

0 个回复

您需要登录后才可以回帖 登录 | 立即注册
返回顶部