Sitelet https://developer.mescius.com/spreadjs/api/classes/GC.Spread.Sheets.TableSheet.TableSheet 
        []
DEMOS DOC DESIGNER API
DOWNLOAD
(Showing Draft Content)

GC.Spread.Sheets.TableSheet.TableSheet

Class: TableSheet

Sheets.TableSheet.TableSheet

Hierarchy

Table of contents

Constructors

Properties

Methods

Constructors

constructor

• new TableSheet(name?, dataView?, options?)

Represents a table sheet with the specified name, data view and options setting.

example

//This example creates a empty TableSheet.
var tableSheet = spread.addSheetTab(0, "", GC.Spread.Sheets.SheetType.tableSheet);

//This example creates a TableSheet with name.
var tableSheet = spread.addSheetTab(0, "tableSheet1", GC.Spread.Sheets.SheetType.tableSheet);

//This example creates a TableSheet with name and data source.
var dataManager = spread.dataManager();
var myTable = dataManager.addTable("myTable", {
    remote: {
        read: {
            url: 'https://demodata.mescius.io/northwind/api/v1/Orders'
        }
    }
});
myTable.fetch().then(function() {
    var myView = myTable.addView("myView");
    var tableSheet = spread.addSheetTab(0, "tableSheet1", GC.Spread.Sheets.SheetType.tableSheet);
    tableSheet.setDataView(myView);
});

//This example creates a TableSheet with name and options.
var style = new GC.Spread.Sheets.Style();
var tableSheet = spread.addSheetTab(0, "tableSheet1", GC.Spread.Sheets.SheetType.tableSheet);
tableSheet.options = {allowAddNew: false, alternatingRowOptions: { step: [1, 1], style: style}};

Parameters

Name Type Description
name? string The table sheet name.
dataView? View The table sheet data view.
options? ITableSheetOptions The initialization options.

Properties

options

• options: ITableSheetOptions

Represents the options of the table sheet.

property [allowAddNew] - Whether to allow to add new empty row.

property [allowSorts] - Whether to allow to sort multiple columns.

property [sheetTabColor] - A color string used to represent the sheet tab color, such as "red", "#FFFF00", "rgb(255,0,0)", "Accent 5", and so on.

property [alternatingRowOptions] - Defines the alternating row style options.

property [defaultStackRowHeight] - Tablesheet default stack row height, will calculate the average height by default.

property [menuItemVisibility] - Tablesheet default stack row height, will calculate the average height by default.

property [showRowNumber] - Whether to show the row number header.

property [enableDefineColumn] - Whether to enable defining column.

property [defineColumnCommand] - Specify the command for defining column options.

property [submitDefineColumnCommand] - Specify the command for submitting defined column options.

property {GC.Spread.Sheets.TableSheet.IColumnTypeItem[]} [columnTypeItems] - Specify the column types.

property [groupLayout] - Specify the options for group layout.

example

//This example changes a TableSheet's options.
tableSheet.options.allowAddNew = false;
tableSheet.options.alternatingRowOptions = { step: [1, 1], style: new GC.Spread.Sheets.Style("lightyellow")};

Methods

addHierarchyItemAbove

▸ addHierarchyItemAbove(row, rowData): void

Add a new row data as the parent of the specified row.

example

//This example adds a new row data as the parent of the specified row.
tableSheet.addHierarchyItemAbove(8, {id: 8, name: "spreadjs"});

Parameters

Name Type Description
row number The row index.
rowData any The row data.

Returns

void

addHierarchyItemAfter

▸ addHierarchyItemAfter(row, rowData): void

Add a new row data after the specified row.

example

//This example adds a new row data after the specified row.
tableSheet.addHierarchyItemAfter(8, {id: 8, name: "spreadjs"});

Parameters

Name Type Description
row number The row index.
rowData any The row data.

Returns

void

addHierarchyItemBefore

▸ addHierarchyItemBefore(row, rowData): void

Add a new row data before the specified row.

example

//This example adds a new row data before the specified row.
tableSheet.addHierarchyItemBefore(8, {id: 8, name: "spreadjs"});

Parameters

Name Type Description
row number The row index.
rowData any The row data.

Returns

void

addHierarchyItemBelow

▸ addHierarchyItemBelow(row, rowData): void

Add a new row data as the child of the specified row.

example

//This example adds a new row data as the child of the specified row.
tableSheet.addHierarchyItemBelow(8, {id: 8, name: "spreadjs"});

Parameters

Name Type Description
row number The row index.
rowData any The row data.

Returns

void

addRow

▸ addRow(rowData): Promise<void>

Adds a new row to table sheet.

example

//This example adds a new row with data.
tableSheet.addRow({id: 8, name: "spreadjs"});

Parameters

Name Type Description
rowData any The row data.

Returns

Promise<void>

applyFreeHeaderArea

▸ applyFreeHeaderArea(sheetJson?): any

Applies a Worksheet JSON to a TableSheet column header free layout area.

Parameters

Name Type Description
sheetJson? any a Worksheet JSON, which can be gotten easily by Worksheet's toJSON method.

Returns

any

applyNamedCellTemplate

▸ applyNamedCellTemplate(name, fieldNames): void

Apply a named cell template to columns on this table sheet.

example

// Apply template to a single column by field name
tableSheet.applyNamedCellTemplate("MyTemplate", ["productName"]);
// Apply template to multiple columns by field names
tableSheet.applyNamedCellTemplate("MyTemplate", ["price", "quantity"]);

Parameters

Name Type Description
name string The name of a registered template (must be registered in workbook.namedCellTemplates).
fieldNames string[] The target column(s) specified as an array of field names from the data source.

Returns

void

applyTableTheme

▸ applyTableTheme(tableTheme): void

Applies a table theme to current TableSheet.

Parameters

Name Type Description
tableTheme TableTheme A table theme instance.

Returns

void

cancelChanges

▸ cancelChanges(): void

Cancels the changes of data manager in batch mode.

example

//This example cancel changes manually in batch mode.
tableSheet.cancelChanges();

Returns

void

collapseAllHierarchyLevels

▸ collapseAllHierarchyLevels(): void

Collapse the all hierarchy levels.

example

//This example collapse the all hierarchy levels.
tableSheet.collapseAllHierarchyLevels();

Returns

void

currentTheme

▸ currentTheme(value?): void | Theme

Gets or sets the current theme for the sheet.

example

//This example sets a theme.
sheet.currentTheme("Civic");

Parameters

Name Type Description
value? string | Theme The theme name or the theme.

Returns

void | Theme

If no value is set, returns the current theme; otherwise, returns void.

demoteHierarchyLevel

▸ demoteHierarchyLevel(row, withChildren?): void

Demote the hierarchy data level of the specified row.

example

//This example demote the hierarchy data level by specified index.
tableSheet.demoteHierarchyLevel(8);

Parameters

Name Type Description
row number The row index.
withChildren? boolean Optional, the children will be demoted with the record by default.

Returns

void

detailColumnsVisible

▸ detailColumnsVisible(value?): boolean

Whether to show or hide the detail columns after Table Sheet is grouped.

example

//This sample shows how to hide the detail columns.
tableSheet.detailColumnsVisible(false);

Parameters

Name Type Description
value? boolean A boolean value which indicates the visibility of the detail columns.

Returns

boolean

Returns the visibility of the detail columns.

expandAllHierarchyLevels

▸ expandAllHierarchyLevels(): void

Expand the all hierarchy levels.

example

//This example expand the all hierarchy levels.
tableSheet.expandAllHierarchyLevels();

Returns

void

expandGroup

▸ expandGroup(field, expand): void

Expands or collapses one level grouping by the specified field.

example

//This sample shows how to collapse one level grouping, which is grouped by "LastName".
tableSheet.expandGroup("LastName", false);

Parameters

Name Type Description
field string The field name.
expand boolean A boolean value, true means expanding the grouping, false means collapsing the grouping.

Returns

void

expandGroupItem

▸ expandGroupItem(level, index, expand): void

Expands or collapses one grouping by the specified level and index.

example

//This sample shows how to collapse one grouping, which level is 1 and start index is 10.
tableSheet.expandGroupItem(1, 10, false);

Parameters

Name Type Description
level number The grouping level.
index number The grouping start index.
expand boolean A boolean value, true means expanding the grouping, false means collapsing the grouping.

Returns

void

expandHierarchyLevel

▸ expandHierarchyLevel(level): void

Expand the hierarchy data by specified level.

example

//This example expand the hierarchy data by specified level.
tableSheet.expandHierarchyLevel(8);

Parameters

Name Type Description
level number The level to expand.

Returns

void

filter

▸ filter(filterInfos?): ITableSheetFilterInfo[]

Gets or sets the active filters of the table sheet.

example

//This example uses the filter method.
tableSheet.filter([{field: 'state', values: [{value: 'New York'}]}]);
let filterInfos = tableSheet.filter();

Parameters

Name Type Description
filterInfos? ITableSheetFilterInfo[] The filter definitions.

Returns

ITableSheetFilterInfo[]

Returns the active filter definitions.

getChanges

▸ getChanges(): IDataViewChanges[]

Gets the changes of data manager in autoSync or batch mode.

example

//This example get changes manually in autoSync or batch mode, including updated rows, inserted rows, deleted rows, add column, update column, remove column.
tableSheet.getChanges();

Returns

IDataViewChanges[]

Returns a object array, each object could contain "type", "dataItem", "oldDataItem", "column", "originalColumn", and "index". The "type" is the change type, could be one of "insert", "update", "delete", "addColumn", "updateColumn", "removeColumn". the "dataItem" is the current row data. The "oldDataItem" is the original row data, only used for "update". The "column" is the current column. The "data" is the default value of the current added column. The "originalColumn" is the original column, only used for "update column". The "index" is the view index of table sheet.

getDataView

▸ getDataView(): View

Gets the data view of table sheet.

example

//This example gets the data source.
tableSheet.getDataView();

Returns

View

Returns the data view.

getTableTheme

▸ getTableTheme(): TableTheme

get the current table theme of the TableSheet.

Returns

TableTheme

groupBy

▸ groupBy(options?): IGroupByOptions[]

group the table sheet by options

example

//This example sets group options and group the table sheet.
tablesheet.groupBy([
     {
         caption: 'Category',
         field: 'category',
         summaryFields:[
             {
                 caption: 'SUM(Quantity)', width: 120, style: { formatter: "$ #,##0.00" }
                 formula: '=SUM([Quantity])',
                 slice: 'Office'
             }
         ]
     }
 ])

Parameters

Name Type Description
options? IGroupByOptions[] The options of the groupBy

Returns

IGroupByOptions[]

groupOutlinePosition

▸ groupOutlinePosition(value?): boolean

Whether to show or hide the grouping outline in viewport area and column header area, or row header area after Table Sheet is grouped.

example

//This sample shows how to hide the grouping outline.
tableSheet.groupOutlinePosition(GC.Spread.Sheets.TableSheet.GroupOutlinePosition.none);

Parameters

Name Type Description
value? GroupOutlinePosition A value which indicates the position of the grouping outline.

Returns

boolean

Returns the position of the grouping outline.

hasRowState

▸ hasRowState(type, row): boolean

Judges the row with the specified state.

example

//This example judges the row with the specified state.
tableSheet.hasRowState(GC.Data.RowColumnStates.selected, 0);

Parameters

Name Type Description
type RowColumnStates The type of row states.
row number The row index.

Returns

boolean

moveDown

▸ moveDown(row): void

Move the hierarchy data by the specified row down.

example

//This example move the hierarchy data by specified index down.
tableSheet.moveDown(8);

Parameters

Name Type Description
row number The row index.

Returns

void

moveUp

▸ moveUp(row): void

Move the hierarchy data by the specified row up.

example

//This example move the hierarchy data by specified index up.
tableSheet.moveUp(8);

Parameters

Name Type Description
row number The row index.

Returns

void

printInfo

▸ printInfo(value?): any

Gets or sets the print information for the table sheet.

example

//This example sets print info.
var printInfo = tableSheet.printInfo();
printInfo.bestFitRows(true);
printInfo.bestFitColumns(true);
tableSheet.printInfo(printInfo);

Parameters

Name Type Description
value? PrintInfo The print information for the table sheet.

Returns

any

If no value is set, returns the print information for the table sheet; otherwise, returns the table sheet.

promoteHierarchyLevel

▸ promoteHierarchyLevel(row): void

Promote the hierarchy data level of the specified row.

example

//This example promote the hierarchy data level by specified index.
tableSheet.promoteHierarchyLevel(8);

Parameters

Name Type Description
row number The row index.

Returns

void

removeFilter

▸ removeFilter(): void

Remove the active filters of the table sheet.

example

//This example uses the removeFilter method.
tableSheet.removeFilter();

Returns

void

removeGroupBy

▸ removeGroupBy(): void

remove group status of the table sheet

example

//This example shows removing the group status of the table sheet.
tablesheet.removeGroupBy()

Returns

void

removeRow

▸ removeRow(row): Promise<void>

Removes the specified row from table sheet.

example

//This example removes a row by specified index.
//The changes will be synchronized when the autoSync be true.
tableSheet.removeRow(8);

Parameters

Name Type Description
row number The row index.

Returns

Promise<void>

removeSort

▸ removeSort(): void

Remove the active sorts of the table sheet.

example

//This example uses the removeSort method.
tableSheet.removeSort();

Returns

void

resetRow

▸ resetRow(row): void

Resets the changes of the specified row of table sheet.

example

//This example resets a row by specified index.
tableSheet.resetRow(8);

Parameters

Name Type Description
row number The row index.

Returns

void

rowActionOptions

▸ rowActionOptions(options?): IRowActionOptions[]

Gets or sets the options of the row action.

example

//This example set the options of the row action.
let options = tableSheet.rowActionOptions();
options.push({
     icons: ["./comment.png"],
     iconSelector: (item) => {
         return item.comment && item.comment.length > 0;
     }
 });
tableSheet.rowActionOptions(options);

Parameters

Name Type Description
options? IRowActionOptions[] The options of the row action.

Returns

IRowActionOptions[]

saveRow

▸ saveRow(row): Promise<void>

Saves the changes of the specified row of table sheet to data manager, including updated row or and inserted row.

example

//This example saves a row by specified index.
//The changes will be synchronized when the autoSync be true.
tableSheet.saveRow(8);

Parameters

Name Type Description
row number The row index.

Returns

Promise<void>

setDataView

▸ setDataView(dataView): void

Sets the data view of table sheet.

example

//This example sets the data source.
var dataManager = spread.dataManager();
var myTable = dataManager.addTable("myTable", {
    remote: {
        read: {
            url: 'https://demodata.mescius.io/northwind/api/v1/Orders'
        }
    }
});
myTable.fetch().then(function() {
    var myView = myTable.addView("myView");
    tableSheet.setDataView(myView);
});

Parameters

Name Type Description
dataView View The data view to bind.

Returns

void

setDefaultRowHeight

▸ setDefaultRowHeight(value, sheetArea?): void

Sets the default height in pixels for the all rows in the viewport area.

example

//This example sets the default row height in pixels.
tableSheet.setDefaultRowHeight(50);

Parameters

Name Type Description
value number The height in pixels.
sheetArea? SheetArea The sheet area.

Returns

void

sort

▸ sort(sortInfos?): ITableSheetSortInfo[]

Gets or sets the active sorts of the table sheet.

example

//This example uses the sort method.
tableSheet.sort([{field: 'state', ascending: true}]);
let sortInfos = tableSheet.sort();

Parameters

Name Type Description
sortInfos? ITableSheetSortInfo[] The sort definitions.

Returns

ITableSheetSortInfo[]

Returns the active sort definitions.

submitChanges

▸ submitChanges(): Promise<void>

Submits the changes of data manager to server in batch mode, including updated rows, inserted rows and deleted rows.

example

//This example submit changes manually in batch mode.
tableSheet.submitChanges();

Returns

Promise<void>

togglePinnedColumns

▸ togglePinnedColumns(indexes): number

Pin or unpin columns with specified column index array.

example

//This example pin several columns.
tableSheet.togglePinnedColumns([1,2,4]);

Parameters

Name Type Description
indexes number[] The array of column indexes to do pin or unpin.

Returns

number

Returns the count of the pinned columns.

togglePinnedRows

▸ togglePinnedRows(indexes): void

Pin or unpin rows with specified row index array.

example

//This example pin several rows.
tableSheet.togglePinnedRows([1,2,4]);

Parameters

Name Type Description
indexes number[] The array of row indexes to do pin or unpin.

Returns

void

Returns the count of the pinned rows.