[]
DEMOS DOC DESIGNER API
DOWNLOAD
(Showing Draft Content)

GC.Spread.Sheets.Tables.Table

Class: Table

Sheets.Tables.Table

Table of contents

Constructors

Methods

Constructors

constructor

• new Table(name?, row?, col?, rowCount?, colCount?, style?)

Represents a table that can be added in a sheet.

Parameters

Name Type Description
name? string The table name.
row? number The table row index.
col? number The table column index.
rowCount? number The table row count.
colCount? number The table column count.
style? string | TableTheme The table style or style name.

Methods

allowAutoExpand

▸ allowAutoExpand(allowAutoExpandState?): boolean | Table

Gets or sets the allowAutoExpandState of the table.

example

console.log(table.allowAutoExpand());
table.allowAutoExpand(true);

Parameters

Name Type
allowAutoExpandState? boolean

Returns

boolean | Table

If there is no allowAutoExpandState set, returns the table allowAutoExpandState; otherwise, returns the table.

autoGenerateColumns

▸ autoGenerateColumns(value?): any

Gets or sets whether to generate columns automatically while binding to a data source.

example

var data = {
    sales: [
        {orderDate: '1/6/2013', item: 'Pencil', units: 95},
        {orderDate: '4/1/2013', item: 'Binder', units: 60},
        {orderDate: '6/8/2013', item: 'Pen Set', units: 16}
    ]
};
var table = sheet.tables.add('tableSales', 0, 0, 5, 1); // only 1 column
var tableColumn1 = new GC.Spread.Sheets.Tables.TableColumn(1, "orderDate", "Order Date", "d/M/yy");
var tableColumn2 = new GC.Spread.Sheets.Tables.TableColumn(2, "item", "Item");
var tableColumn3 = new GC.Spread.Sheets.Tables.TableColumn(3, "units", "Units");

table.autoGenerateColumns(false);
table.bind([tableColumn1, tableColumn2, tableColumn3], 'sales', data);
console.log(table.dataRange().colCount);    // 1

table.autoGenerateColumns(true);
table.bind([tableColumn1, tableColumn2, tableColumn3], 'sales', data);
console.log(table.dataRange().colCount);    // 3

Parameters

Name Type
value? boolean

Returns

any

If no value is set, returns whether to generate columns automatically while binding to a data source; otherwise, returns the table.

bandColumns

▸ bandColumns(value?): any

Gets or sets a value that indicates whether to display an alternating column style.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableThemes.medium2);
sTable.bandColumns(true);
sTable.bandRows(true);

Parameters

Name Type Description
value? boolean Whether to display an alternating column style.

Returns

any

If no value is set, returns whether to display an alternating column style; otherwise, returns the table.

bandRows

▸ bandRows(value?): any

Gets or sets a value that indicates whether to display an alternating row style.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableThemes.medium2);
sTable.bandColumns(true);
sTable.bandRows(true);

Parameters

Name Type Description
value? boolean Whether to display an alternating row style.

Returns

any

If no value is set, returns whether to display an alternating row style; otherwise, returns the table.

bind

▸ bind(columns, path?, dataSource?): Table

Gets or sets the dataSource and binding columns and path for the table.

example

var data = {
  name: 'Jones', region: 'East',
  sales: [
    {orderDate: '1/6/2013', item: 'Pencil', units: 95, cost: 1.99, isMakeMoney: true},
    {orderDate: '4/1/2013', item: 'Binder', units: 60, cost: 4.99, isMakeMoney: false},
    {orderDate: '6/8/2013', item: 'Pen Set', units: 16, cost: 15.99, isMakeMoney: false}
  ]
};
var convert = function (item) {
  return item['cost'] + '$';
}
var table = sheet.tables.add('tableSales', 0, 0, 5, 5);
var tableColumn1 = new GC.Spread.Sheets.Tables.TableColumn(1, "orderDate", "Order Date", "d/M/yy");
var tableColumn2 = new GC.Spread.Sheets.Tables.TableColumn(2, "item", "Item");
var tableColumn3 = new GC.Spread.Sheets.Tables.TableColumn(3, "units", "Units");
var tableColumn4 = new GC.Spread.Sheets.Tables.TableColumn(4, "cost", "Cost", null, null, convert);
var tableColumn5 = new GC.Spread.Sheets.Tables.TableColumn(5, "isMakeMoney", "IsMakeMoney", null, new GC.Spread.Sheets.CellTypes.CheckBox());
table.autoGenerateColumns(false);

table.bind([tableColumn1, tableColumn2, tableColumn3, tableColumn4, tableColumn5], 'sales', data);

Parameters

Name Type Description
columns TableColumn[] The array of table column information with data fields and names. Each item is GC.Spread.Sheets.Tables.TableColumn.
path? string -
dataSource? string | object | Table -

Returns

Table

returns the table.

bindColumns

▸ bindColumns(columns): void

Binds the columns using the specified data fields.

example

var table = sheet.tables.add('tableSales', 0, 0, 5, 2);
var tableColumn1 = new GC.Spread.Sheets.Tables.TableColumn(1, "orderDate", "Order Date", "d/M/yy");
var tableColumn2 = new GC.Spread.Sheets.Tables.TableColumn(2, "item", "Item");
table.bindColumns([tableColumn1, tableColumn2]);

Parameters

Name Type Description
columns TableColumn[] The array of table column information with data fields and names. Each item is GC.Spread.Sheets.Tables.TableColumn.

Returns

void

bindingPath

▸ bindingPath(value?): any

Gets or sets the binding path for cell-level binding in the table.

Parameters

Name Type
value? string

Returns

any

If no value is set, returns the binding path for cell-level binding in the table; otherwise, returns the table.

clearPendingChanges

▸ clearPendingChanges(): void

Clears the dirty status from the current table.

example

// table bind data
var data = {
    sales: [
        {orderDate: '1/6/2013', item: 'Pencil'},
        {orderDate: '4/1/2013', item: 'Binder'},
        {orderDate: '6/8/2013', item: 'Pen Set'}
    ]
};
var table = sheet.tables.add('tableSales', 0, 0, 5, 5);
var tableColumn1 = new GC.Spread.Sheets.Tables.TableColumn(1, "orderDate", "Order Date", "d/M/yy");
var tableColumn2 = new GC.Spread.Sheets.Tables.TableColumn(2, "item", "Item");
table.bind([tableColumn1, tableColumn2], 'sales', data);
sheet.setValue(1, 1, 'dirty data');
console.log(table.getDirtyRows()); // [{row:0, item: ..., originalItem: ...}]

table.clearPendingChanges();
console.log(table.getDirtyRows()); // [ ]

Returns

void

columnLayoutStyle

▸ columnLayoutStyle(tableColumnIndex, value?): Table | ITableLayoutStyle

Gets or sets the layout style to the table column.

example

var table = activeSheet.tables.find(0,0);
var dataStyle = new GC.Spread.Sheets.Style();
dataStyle.backColor = "red";
var footerStyle = new GC.Spread.Sheets.Style();
footerStyle.backColor = "green";
var layoutStyle = {};
layoutStyle.data = dataStyle;
layoutStyle.footer = footerStyle;
table.columnLayoutStyle(1, layoutStyle);

Parameters

Name Type Description
tableColumnIndex number The column index of the table column. The index is zero-based.
value? ITableLayoutStyle -

Returns

Table | ITableLayoutStyle

If no value is set, returns the layout style of the table column. otherwise return table.

dataRange

▸ dataRange(): Range

Gets the cell range for the table data area.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableTheme.medium2);
var drange = sTable.dataRange();
alert(drange.row);

Returns

Range

The table data range.

deleteColumns

▸ deleteColumns(col, count): void

Delete count number columns in the table at the specified table col index.

example

var table = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableTheme.medium2);
table.deleteColumns(3, 1);

Parameters

Name Type Description
col number The index of the first column to delete, based on table index.
count number The number of columns to delete.

Returns

void

deleteRows

▸ deleteRows(row, count): void

Deletes the rows in this table at the specified table row index.

example

var table = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableTheme.medium2);
table.deleteRows(3, 1);

Parameters

Name Type Description
row number The index of the first row to delete, based on table index.
count number The number of rows to delete.

Returns

void

expandBoundRows

▸ expandBoundRows(value?): any

Gets or sets a value that table expand rows mode in binding case.

example

var spread = GC.Spread.Sheets.findControl("ss") || GC.Spread.Sheets.findControl("sampleDiv");
var sheet = spread.getActiveSheet();
var data = {
    name: 'Jones', region: 'East',
    sales: [
        { orderDate: '1/6/2013', item: 'Pencil', units: 95, cost: 1.99 },
        { orderDate: '4/1/2013', item: 'Binder', units: 60, cost: 4.99 },
        { orderDate: '6/8/2013', item: 'Pen Set', units: 16, cost: 15.99 },
        { orderDate: '8/1/2013', item: 'Pencil', units: 20, cost: 24.99 },
        { orderDate: '10/8/2013', item: 'Binder', units: 31, cost: 16.99 }
    ]
};
var table1 = sheet.tables.add('tableRecords', 0, 0, 4, 4);
var table2 = sheet.tables.add('tableBelow', 4, 0, 4, 7);
table1.bindingPath('sales');
var dataSource = new GC.Spread.Sheets.Bindings.CellBindingSource(data);
table1.expandBoundRows(true);
sheet.setDataSource(dataSource);

Parameters

Name Type
value? boolean

Returns

any

If no value is set, returns whether to expand by sheet or table insert/delete rows; otherwise, returns the table.

filterButtonVisible

▸ filterButtonVisible(tableColumnIndex?, value?): any

Gets or sets whether the table column's filter button is displayed.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableTheme.medium2);
sTable.bandColumns(true);
sTable.bandRows(true);
sTable.filterButtonVisible(2, false);
alert(sTable.filterButtonVisible(2));

Parameters

Name Type
tableColumnIndex? number | boolean
value? boolean

Returns

any

The table column's filter button display state. If no parameter is set, returns false if all filter buttons are invisible, otherwise, true. If only a number is set, returns whether the specified table column' filter button is displayed. If only a boolean that indicates whether to display filter buttons is set, applies to all filter buttons and returns the table. If two parameters are provided, applies to the specified table columns' filter button and returns the table.

footerIndex

▸ footerIndex(): number

Gets the footer index in the sheet.

example

var sTable = sheet.tables.add("table1", 0, 0, 10, 5, GC.Spread.Sheets.Tables.TableTheme.medium2);
sTable.showFooter(true);

var footerIndex = sTable.footerIndex();
console.log(footerIndex) // 10
// Cause the table's footer is on row 11.

Returns

number

The footer index.

getColumnFormula

▸ getColumnFormula(tableColumnIndex): string

Gets the table footer formula with the specified index.

example

var sTable = sheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableTheme.medium2);
//set footer formula
sTable.setColumnFormula(4, "SUM(F3:F11)");

//get footer formula
var columnFormula = sTable.getColumnFormula(4);
console.log(columnFormula); // "SUM(F3:F11)"

Parameters

Name Type Description
tableColumnIndex number The column index of the table footer. The index is zero-based.

Returns

string

The table footer formula.

getColumnName

▸ getColumnName(tableColumnIndex): string

Gets the table header text with the specified table index.

example

var sTable = sheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableTheme.medium2);
sTable.setColumnName(4, "SUM");

var value = sTable.getColumnName(4);
console.log(value); // "SUM"

Parameters

Name Type Description
tableColumnIndex number The column index of the table header. The index is zero-based.

Returns

string

The header text of the specified column by index.

getColumnValue

▸ getColumnValue(tableColumnIndex): string

Gets the table footer value with the specified index.

example

var sTable = sheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableTheme.medium2);
// set footer value
sTable.setColumnValue(0, "Total");

// get footer value
var value = sTable.getColumnValue(0);
console.log(value) // "Total"

Parameters

Name Type Description
tableColumnIndex number The column index of the table footer. The index is zero-based.

Returns

string

The table footer value.

getDirtyRows

▸ getDirtyRows(): IRowState[]

Get array of dirty rows.

example

// table bind data
var data = {
    sales: [
        {orderDate: '1/6/2013', item: 'Pencil'},
        {orderDate: '4/1/2013', item: 'Binder'},
        {orderDate: '6/8/2013', item: 'Pen Set'}
    ]
};
var table = sheet.tables.add('tableSales', 0, 0, 5, 5);
var tableColumn1 = new GC.Spread.Sheets.Tables.TableColumn(1, "orderDate", "Order Date", "d/M/yy");
var tableColumn2 = new GC.Spread.Sheets.Tables.TableColumn(2, "item", "Item");
table.bind([tableColumn1, tableColumn2], 'sales', data);

console.log(table.getDirtyRows()); // [ ]

sheet.setValue(1, 1, 'dirty data');
console.log(table.getDirtyRows()); // [{row:0, item: ..., originalItem: ...}]

Returns

IRowState[]

The dirty rows collection. The item in array contains three properties, row: specifies table row index, item: specifies data item of current row, originalItem: specifies original data item of the row.

getSlicerData

▸ getSlicerData(): TableSlicerData

Gets the slicer data of the table.

example

var activeSheet = spread.getActiveSheet();
var dataSource = [
    { Name: "Bob", City: "NewYork", Birthday: "1968/6/8" },
    { Name: "Betty", City: "NewYork", Birthday: "1972/7/3" },
    { Name: "Alice", City: "Washington", Birthday: "2012/2/15" },
];
var table = activeSheet.tables.addFromDataSource("table1", 1, 1, dataSource);
var slicerData = table.getSlicerData();
console.log(slicerData instanceof GC.Spread.Sheets.Slicers.TableSlicerData); // true
console.log(table.getSlicerData() === slicerData); // true

Returns

TableSlicerData

The slicer data of the table.

getStyleName

▸ getStyleName(): undefined | string

Gets or sets a style name for the table.

Returns

undefined | string

returns the table style name.

headerIndex

▸ headerIndex(): number

Gets the header index in the sheet.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableTheme.medium2);
var hindex = sTable.headerIndex();
alert(hindex);

Returns

number

The header index.

highlightFirstColumn

▸ highlightFirstColumn(value?): any

Gets or sets a value that indicates whether to highlight the first column.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableThemes.medium2);
sTable.highlightFirstColumn(true);
sTable.highlightLastColumn(true);

Parameters

Name Type Description
value? boolean Whether to highlight the first column.

Returns

any

If no value is set, returns whether to highlight the first column; otherwise, returns the table.

highlightLastColumn

▸ highlightLastColumn(value?): any

Gets or sets a value that indicates whether to highlight the last column.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableThemes.medium2);
sTable.highlightFirstColumn(true);
sTable.highlightLastColumn(true);

Parameters

Name Type Description
value? boolean Whether to highlight the last column.

Returns

any

If no value is set, returns whether to highlight the last column; otherwise, returns the table.

insertColumns

▸ insertColumns(col, count, isInsertAfter?): void

Insert count number columns in this table before the specified table col index.

example

var table = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableTheme.medium2);
table.insertColumns(3, 1);

Parameters

Name Type Description
col number Column index at which to add the new columns, based on table index.
count number The number of columns to add.
isInsertAfter? boolean -

Returns

void

insertRows

▸ insertRows(row, count, isInsertAfter?): void

Insert rows in this table before the specified table row index.

example

var table = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableTheme.medium2);
table.insertRows(3, 1);

Parameters

Name Type Description
row number The index of the starting row to insert, based on table index.
count number The number of rows to add.
isInsertAfter? boolean -

Returns

void

layoutStyle

▸ layoutStyle(value?): Table | ITableLayoutStyle

Gets or sets a style to the table's data area.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableThemes.medium2);
var dataStyle = new GC.Spread.Sheets.Style();
dataStyle.backColor = "red";
var footerStyle = new GC.Spread.Sheets.Style();
footerStyle.backColor = "green";
var layoutStyle = {};
layoutStyle.data = dataStyle;
layoutStyle.footer = footerStyle;
table.layoutStyle(layoutStyle);

Parameters

Name Type
value? ITableLayoutStyle

Returns

Table | ITableLayoutStyle

If no value is set, returns the style of the table's data area; otherwise, returns the table.

name

▸ name(value?): any

Gets or sets the table name.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableTheme.medium2);
var tname = sTable.name();
alert(tname);

Parameters

Name Type Description
value? string The table name.

Returns

any

If no value is set, returns the table name; otherwise, returns the table.

range

▸ range(): Range

Gets the range for the entire table.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableTheme.medium2);
var drange = sTable.range();
alert(drange.row);

Returns

Range

The whole table range.

rowFilter

▸ rowFilter(): HideRowFilter

Gets the row filter for the table.

example

var table = sheet.tables.add('table1', 0, 0, 5, 5);

let rowFilter = table.rowFilter();
console.log(rowFilter.range);

Returns

HideRowFilter

The row filter.

setColumnDataFormula

▸ setColumnDataFormula(tableColumnIndex, formula): Table

Sets a formula to the table's data range with the specified index.

example

//This example uses a structured reference formula in the table.
activeSheet.tables.add("Table1", 0, 0, 4, 3, GC.Spread.Sheets.Tables.TableTheme.dark1);
activeSheet.getCell(0,0).text("Value1");
activeSheet.getCell(0,1).text("Value2");
activeSheet.getCell(0,2).text("Total");
activeSheet.getCell(1,0).text("1");
activeSheet.getCell(2,0).text("2");
activeSheet.getCell(3,0).text("3");
activeSheet.getCell(1,1).text("5");
activeSheet.getCell(2,1).text("5");
activeSheet.getCell(3,1).text("5");
activeSheet.tables.findByName("Table1").setColumnDataFormula(2, "=[Value1]*[Value2]");
activeSheet.tables.findByName("Table1").setColumnDataFormula(2, null); // to clear the column data formula

Parameters

Name Type Description
tableColumnIndex number The column index of the table. The index is zero-based.
formula null | string The data range formula.

Returns

Table

The table.

setColumnFormula

▸ setColumnFormula(tableColumnIndex, formula): Table

Sets the table footer formula with the specified index.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableThemes.medium2);
sTable.showFooter(true);
//set footer value
sTable.setColumnValue(0, "Total");
//set footer formula
sTable.setColumnFormula(4, "SUM(F3:F11)");

Parameters

Name Type Description
tableColumnIndex number The column index of the table footer. The index is zero-based.
formula string The table footer formula.

Returns

Table

The table.

setColumnName

▸ setColumnName(tableColumnIndex, name): Table

Sets the table header text with the specified table index.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableThemes.medium2);
sTable.showFooter(true);
sTable.setColumnName(4, "SUM");
//set footer value
sTable.setColumnValue(0, "Total");
//set footer formula
sTable.setColumnFormula(4, "SUM(F3:F11)");

Parameters

Name Type Description
tableColumnIndex number The column index of the table header. The index is zero-based.
name string The header text.

Returns

Table

The table.

setColumnValue

▸ setColumnValue(tableColumnIndex, value): Table

Sets the table footer value with the specified index.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableThemes.medium2);
sTable.showFooter(true);
//set footer value
sTable.setColumnValue(0, "Total");
//set footer formula
sTable.setColumnFormula(4, "SUM(F3:F11)");

Parameters

Name Type Description
tableColumnIndex number The column index of the table footer. The index is zero-based.
value Object The table footer value.

Returns

Table

The table.

showFooter

▸ showFooter(value?, isFooterInserted?): any

Gets whether table footer is show. Or sets whether show table footer with insert mode or override mode.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableThemes.medium2);
sTable.showFooter(true);
//set footer value
sTable.setColumnValue(0, "Total");
//set footer formula
sTable.setColumnFormula(4, "SUM(F3:F11)");

Parameters

Name Type
value? boolean
isFooterInserted? boolean

Returns

any

If no value is set, returns whether to display a footer; otherwise, returns the table.

showHeader

▸ showHeader(value?): any

Gets or sets whether to display table header.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableThemes.medium2);
sTable.showHeader(true);

Parameters

Name Type
value? boolean

Returns

any

If no value is set, returns whether to display a header; otherwise, returns the table.

showResizeHandle

▸ showResizeHandle(value?): any

Gets or sets a value that indicates whether to display the resize handle for table.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableThemes.medium2);
sTable.showResizeHandle(true);

Parameters

Name Type
value? boolean

Returns

any

If no value is set, returns whether to display the resize handle for table, by default is false; otherwise, returns the table.

style

▸ style(value?): any

Gets or sets a style for the table.

Parameters

Name Type Description
value? string | TableTheme The style or style name for the table.

Returns

any

If no value is set, returns the table style; otherwise, returns the table.

useFooterDropDownList

▸ useFooterDropDownList(value?): any

Gets or sets a value that indicates whether to use the footer dropdown list for a total row.

example

var sTable = activeSheet.tables.add("table1", 1, 1, 10, 5, GC.Spread.Sheets.Tables.TableThemes.medium2);
sTable.useFooterDropDownList(true);

Parameters

Name Type
value? boolean

Returns

any

If no value is set, returns whether to use the footer dropdown list for a total row, by default is false; otherwise, returns the table.