Overview

TableSheet supports hierarchy data that can be configured with the hierarchy options in the data source schema.

The demo is being dynamically compiled to support real-time code editing... For quicker access to features, switch to the "JavaScript" tab for a smoother experience! :)
Description
app.js
index.html
styles.css
Copy to CodeMine

The options in the data source schema are as follows:

interface GC.Data.IDataSourceOption {
    //others options...
    schema?: {
        hierarchy: { // define whether the data source is hierarchical
          type: 'Parent' | 'ChildrenPath' | 'Level' | 'Custom', // the hierarchy type
          column: string // the hierarachy key that will help to build the hierarchical data
          levelOffset?: number; // level offset that can increase/decrease the level, in the usually, the hierarchical level starts with 0
          outlineColumn?: string | GC.Data.IHierarchyOutlineColumnOptions; // the options for self-defined outline
          parse: (options: GC.Data.IHierarchyCustomParseOptions) => any; // parse the primary key of the custom hierarchy type to the parent key
          unparse?: (options: GC.Data.IHierarchyCustomUnparseOptions) => any; // build the primary key of the custom hierarchy type
        }
        //others options...
    }
}
interface GC.Data.IHierarchyCustomParseOptions {
  data: any, 
  index: number, 
}
interface GC.Data.IHierarchyCustomUnparseOptions {
  data: any, 
  index: number, 
  parentData: any
}
interface GC.Data.IOutlineColumnOptions {
    showCheckBox?: boolean;
    showImage?: boolean;
    images?: string[];
    showIndicator?: boolean;
    expandIndicator?: string;
    collapseIndicator?: string;
}
interface GC.Data.IHierarchyOutlineColumnOptions {
    value: string;
    showCheckBox?: boolean;
    showImage?: boolean;
    images?: string[];
    showIndicator?: boolean;
    expandIndicator?: string;
    collapseIndicator?: string;
}
interface GC.Data.IColumn {
  //other options...
  dataType?: "string" | "number" | "boolean" | "object" | "array" | "date" | "rowOrder" | "formula"; // the rowOrder in the dataType is used for the ordering the records, the value of the column should be a number
  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
}

There are 4 types of data in the hierarchy: Parent, Level, ChildrenPath, Custom. Each of them can be configured when adding a Table to data manager:

Configure the parent hierarchy type:

var taskTable = dataManager.addTable("Tasks", {
    remote: { ... },
    schema: {
        hierarchy: {
          type: 'Parent',
          column: 'TaskParentId',
        },
        columns: {
          TaskName: { dataName: 'name' },
          TaskId: { dataName: 'id', isPrimaryKey: true }, // using primary key to indicate the hierarchy id
          TaskParentId: { dataName: 'parentId' },
          TaskRowOrder: { dataName: 'rowOrder', dataType: "rowOrder" }, // the property for adding and moving up/down, it's better to be specified
        }
    }
});
var taskView = taskTable.addView('TaskView',[
  {
    value: 'TaskName',
    // this option indicates the column showing as outline column
    // and the outline column could be customized
    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',
    }
  }
]);

Configure the level hierarchy type:

var taskTable = dataManager.addTable("Tasks", {
    remote: { ... },
    schema: {
        hierarchy: {
          type: 'Level',
          column: 'TaskLevel',
          outlineColumn: {
            value: 'TaskName',
            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: {
          TaskName: {dataName: 'name' },
          TaskId: { dataName: 'id', isPrimaryKey: true }, // using primary key to indicate the hierarchy id if exists
          TaskLevel: { dataName: 'level' },
        }
    }
});

Configure the children path hierarchy type:

var taskTable = dataManager.addTable("Tasks", {
    remote: { ... },
    schema: {
        hierarchy: {
          type: 'ChildrenPath',
          column: 'TaskChildren',
          outlineColumn: 'TaskName'
        },
        columns: {
          TaskName: {dataName: 'name' },
          TaskChildren: { dataName: 'children' },
          // other columns in the child
        }
    }
});

Configure the custom hierarchy type:

var taskTable = dataManager.addTable("Tasks", {
    remote: { ... },
    schema: {
        hierarchy: {
          type: 'Custom',
          column: 'TaskId',
          outlineColumn: 'TaskName',
          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 if exists
        }
    }
});

There are some operations to update the hierarchy records in the TableSheet:

  1. Promote a record:
/**
 * Promote the hierarchy data level of the specified row.
 * @param {number} row - The row index.
 * @returns {void}
 */
function promoteHierarchyLevel(row: number): void
  1. Demote a record:
/**
 * Demote the hierarchy data level of the specified row.
 * @param {number} row - The row index.
 * @param {boolean} withChildren - Optional, the children will be demoted with the record by default.
 * @returns {void}
 */
function  demoteHierarchyLevel(row: number, withChildren?: boolean): void
  1. Move up:
/**
  * Move the hierarchy data by the specified row up.
  * @param {number} row - The row index.
  * @returns {void}
 */
function moveUp(row: number): void
  1. Move down:
/**
  * Move the hierarchy data by the specified row down.
  * @param {number} row - The row index.
  * @returns {void}
 */
function moveDown(row: number): void
  1. Add a new row data before the specified row:
/**
  * Add a new row data before the specified row.
  * @param {number} row - The row index.
  * @param {Object} rowData - The row data.
  * @returns {void}
 */
function addHierarchyItemBefore(row: number, rowData: any): void
  1. Add a new row data after the specified row:
/**
  * Add a new row data after the specified row.
  * @param {number} row - The row index.
  * @param {Object} rowData - The row data.
  * @returns {void}
 */
function addHierarchyItemAfter(row: number, rowData: any): void
  1. Add a new row data as the parent of the specified row:
/**
  * Add a new row data as the parent of the specified row.
  * @param {number} row - The row index.
  * @param {Object} rowData - The row data.
  * @returns {void}
 */
function addHierarchyItemAbove(row: number, rowData: any): void
  1. Add a new row data as the child of the specified row:
/**
  * Add a new row data as the child of the specified row.
  * @param {number} row - The row index.
  * @param {Object} rowData - The row data.
  * @returns {void}
 */
function addHierarchyItemBelow(row: number, rowData: any): void
  1. Expand the all hierarchy levels:
/**
  * Expand the all hierarchy levels.
  * @returns {void}
 */
function expandAllHierarchyLevels(): void
  1. Collapse the all hierarchy levels:
/**
  * Collapse the all hierarchy levels.
  * @returns {void}
 */
function collapseAllHierarchyLevels(): void
  1. Expand the hierarchy data by specified level:
/**
  * Expand the hierarchy data by specified level.
  * @param {number} level - The level to expand.
  * @returns {void}
 */
function expandHierarchyLevel(level: number): void

There are some options to toggle the visibility of the menu items for the hierarchical records in the TableSheet options:

interface GC.Spread.Sheets.TableSheet.ITableSheetOptions {
  // other properties...
  menuItemVisibility?: {
        promoteMenuItemVisible?: boolean;
        demoteMenuItemVisible?: boolean;
        moveUpMenuItemVisible?: boolean;
        moveDownMenuItemVisible?: boolean;
        addBeforeMenuItemVisible?: boolean;
        addAfterMenuItemVisible?: boolean;
        addAboveMenuItemVisible?: boolean;
        addBelowMenuItemVisible?: boolean;
        expandAllLevelMenuItemVisible?: boolean;
        collapseAllLevelMenuItemVisible?: boolean;
        expandToLevelMenuItemVisible?: boolean;
    };
}

To show the hierarchy menu items in the TableSheet:

    tableSheet.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,
    }
The options in the data source schema are as follows: There are 4 types of data in the hierarchy: Parent, Level, ChildrenPath, Custom. Each of them can be configured when adding a Table to data manager: Configure the parent hierarchy type: Configure the level hierarchy type: Configure the children path hierarchy type: Configure the custom hierarchy type: There are some operations to update the hierarchy records in the TableSheet: Promote a record: Demote a record: Move up: Move down: Add a new row data before the specified row: Add a new row data after the specified row: Add a new row data as the parent of the specified row: Add a new row data as the child of the specified row: Expand the all hierarchy levels: Collapse the all hierarchy levels: Expand the hierarchy data by specified level: There are some options to toggle the visibility of the menu items for the hierarchical records in the TableSheet options: To show the hierarchy menu items in the 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\/demos\//)[0] + 'server/api'; }
<!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" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.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$/en/purejs/node_modules/@mescius/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; }