[]
        
(Showing Draft Content)

GC.Spread.Sheets.Worksheet

Class: Worksheet

Spread.Sheets.Worksheet

Table of contents

Constructors

Properties

Methods

Constructors

constructor

new Worksheet(name)

Represents a worksheet.

Parameters

Name Type Description
name string The name of the Worksheet.

Properties

autoGenerateColumns

autoGenerateColumns: boolean

Indicates whether to generate columns automatically while binding data context.

example

//This example sets a data source for the sheet.
 var test = [
       { "Series0": 2, "Series1": 1 },
       { "Series0": 4, "Series1": 2 },
       { "Series0": 3, "Series1": 4 }
            ];
activeSheet.autoGenerateColumns = true;
activeSheet.setDataSource(test, true);

cellStates

cellStates: CellStateManager

The cellState manager.


charts

charts: ChartCollection

Chart manager for the sheet.

example

//This example shows how to add a chart.
var dataRange = "A1:D4";
var chart = activeSheet.charts.add('Chart1', GC.Spread.Sheets.Charts.ChartType.columnClustered, 250, 20, 600, 400, dataRange);

columnOutlines

columnOutlines: Outline

Indicates the column range group.


comments

comments: CommentManager

Comment manager for the sheet.


conditionalFormats

conditionalFormats: ConditionalFormats

Conditional format manager for the sheet.

example

//This example creates a rule.
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
var rule = new GC.Spread.Sheets.ConditionalFormatting.NormalConditionRule();
rule.ruleType(GC.Spread.Sheets.ConditionalFormatting.RuleType.cellValueRule);
rule.ranges([new GC.Spread.Sheets.Range(0,0,5,1)]);
rule.operator(GC.Spread.Sheets.ConditionalFormatting.ComparisonOperators.between);
rule.style(style);
rule.value1(2);
rule.value2(100);
activeSheet.conditionalFormats.addRule(rule);
activeSheet.setValue(0,0,1,3);
activeSheet.setValue(1,0,45,3);
var ruletest = activeSheet.conditionalFormats.getRules();
alert(ruletest[0].style().backColor);

defaults

defaults: ISheetDefaultOption

Indicates the default row height and column width of the sheet.

example

//This example sets the default row height and column width.
activeSheet.suspendPaint();
activeSheet.defaults.rowHeight = 40;
activeSheet.defaults.colWidth = 30;
activeSheet.resumePaint();

floatingObjects

floatingObjects: FloatingObjectCollection

FloatingObject manager for the sheet.

example

//This example creates a floating object.
var customFloatingObject = new GC.Spread.Sheets.FloatingObjects.FloatingObject("f1", 10, 10, 60, 64);
var btn = document.createElement('button');
btn.style.width = "60px";
btn.style.height = "30px";
btn.innerText = "button";
customFloatingObject.content(btn);
activeSheet.floatingObjects.add(customFloatingObject);

options

options: IWorksheetOptions

Indicates the options of the sheet.

property allowCellOverflow - Indicates whether data can overflow into adjacent empty cells.

property showFormulas - Indicates whether display the formulas string not the formula result.

property showZeros - Indicates whether display the 0 in cells containing zero value. Default is true.

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 frozenlineColor - A color string used to represent the frozen line color, such as "red", "#FFFF00", "rgb(255,0,0)", "Accent 5", and so on.

property clipBoardOptions - The clipboard option.

property gridline - The grid line's options.

property gridline.color - The grid line color

property gridline.showVerticalGridline - Whether to show the vertical grid line.

property gridline.showHorizontalGridline - Whether to show the horizontal grid line.

property rowHeaderVisible - Indicates whether the row header is visible.

property colHeaderVisible - Indicates whether the column header is visible.

property rowHeaderAutoText - Indicates whether the row header displays letters or numbers or is blank.

property colHeaderAutoText - Indicates whether the column header displays letters or numbers or is blank.

property rowHeaderAutoTextIndex - Specifies which row header column displays the automatic text when there are multiple row header columns.

property colHeaderAutoTextIndex - Specifies which column header row displays the automatic text when there are multiple column header rows.

property isProtected - Indicates whether cells on this sheet that are marked as protected cannot be edited.

property protectionOptions - A value that indicates the elements that you want users to be able to change.

property [protectionOptions.allowSelectLockedCells] - True or undefined if the user can select locked cells.

property [protectionOptions.allowSelectUnlockedCells] - True or undefined if the user can select unlocked cells.

property [protectionOptions.allowSort] - True if the user can sort ranges.

property [protectionOptions.allowFilter] - True if the user can filter ranges.

property [protectionOptions.allowEditObjects] - True if the user can edit floating objects.

property [protectionOptions.allowResizeRows] - True if the user can resize rows.

property [protectionOptions.allowResizeColumns] - True if the user can resize columns.

property [protectionOptions.allowDragInsertRows] - True if the user can drag to insert rows.

property [protectionOptions.allowDragInsertColumns] - True if the user can drag to insert columns.

property [protectionOptions.allowInsertRows] - True if the user can insert rows.

property [protectionOptions.allowInsertColumns] - True if the user can insert columns.

property [protectionOptions.allowDeleteRows] - True if the user can delete rows.

property [protectionOptions.allowDeleteColumns] - True if the user can delete columns.

property [protectionOptions.allowOutlineColumns] - True if the user can expand or collapse the column groups.

property [protectionOptions.allowOutlineRows] - True if the user can expand or collapse the row groups.

property selectionBackColor - The selection's background color for the sheet.

property selectionBorderColor - The selection's border color for the sheet.

property sheetAreaOffset - The sheetAreaOffset's options.

property sheetAreaOffset.left - The offset left of sheet from host.

property sheetAreaOffset.top - The offset top of sheet from host.

property keepUnknownFormulas - Indicates whether the unknown formulas could be included in sheet json data.

example

sheet.setRowCount(2,GC.Spread.Sheets.SheetArea.colHeader);
sheet.setColumnCount(2,GC.Spread.Sheets.SheetArea.rowHeader);
sheet.setValue(0, 2,"Column",GC.Spread.Sheets.SheetArea.colHeader);
sheet.options.colHeaderAutoTextIndex = 1;
sheet.options.colHeaderAutoText = GC.Spread.Sheets.HeaderAutoText.numbers;

outlineColumn

outlineColumn: OutlineColumn

Gets the outline column for the sheet.

returns


pictures

pictures: FloatingObjectCollection

Picture manager for the sheet.

deprecated since version 15.2.0, please use 'sheet.shapes' instead

example

//This example adds a picture.
activeSheet.pictures.add("f2","Event.png",2,2,10,10);
var picture = activeSheet.pictures.get("f2");
picture.pictureStretch(GC.Spread.Sheets.ImageLayout.center);
picture.backColor("Blue");
picture.borderWidth(2);
picture.borderColor("Red");
picture.borderStyle("dotted");
picture.borderRadius(5);

pivotTables

pivotTables: PivotTableManager

The pivot table manager.

example

//This example creates a pivot table.
var pivotTableManager = sheet.pivotTables;
var sourceData = [["Date","Buyer","Type","Amount"],["01-Jan","Mom","Fuel",74],["15-Jan","Mom","Food",235],["17-Jan","Dad","Sports",20],["21-Jan","Kelly","Books",125],["02-Feb","Mom","Food",235],["20-Feb","Kelly","Music",20],["25-Feb","Kelly","Tickets",125]];
var options = {showRowHeader: true, showColumnHeader: true};
var myPivotTable = pivotTableManager.add("pivotTable_1", sourceData , 1, 1, GC.Spread.Pivot.PivotTableLayoutType.tabular, GC.Spread.Pivot.PivotTableThemes.medium2, option);

rowOutlines

rowOutlines: Outline

Indicates the row range group.


shapes

shapes: ShapeCollection

Shape manager for the sheet.

example

//This example shows how to add a shape.
var shape = activeSheet.shapes.add("shape1", GC.Spread.Sheets.Shapes.AutoShapeType.heart, 100, 50, 100, 150);

slicers

slicers: SlicerCollection

The slicer manager.

example

//This example adds a slicer.
//create a table
datas = [
    ["1", "NewYork", "1968/6/8", "80", "180"],
    ["4", "NewYork", "1972/7/3", "72", "168"],
    ["4", "NewYork", "1964/3/2", "71", "179"],
    ["5", "Washington", "1972/8/8","80", "171"],
    ["6", "Washington", "1986/2/2", "89", "161"],
    ["7", "Washington", "2012/2/15", "71", "240"]];
var table = activeSheet.tables.addFromDataSource("table1", 2, 2, datas);
dataColumns = ["Name", "City", "Birthday", "Weight", "Height"];
table.setColumnName(0, dataColumns[0]);
table.setColumnName(1, dataColumns[1]);
table.setColumnName(2, dataColumns[2]);
table.setColumnName(3, dataColumns[3]);
table.setColumnName(4, dataColumns[4]);
var style1 = new GC.Spread.Sheets.Slicers.SlicerStyles.light4();
//add a slicer to the sheet and return the slicer instance.
var slicer = activeSheet.slicers.add("slicer1",table.name(),"Height");
 //change the slicer properties.
slicer.position(new GC.Spread.Sheets.Point(100, 200));
slicer.disableResizingAndMoving(true);
slicer.style(style1);

tables

tables: TableManager

The table manager.

example

//This example creates a table.
activeSheet.tables.add("Table1", 0, 0, 3, 3, GC.Spread.Sheets.Tables.TableThemes.dark1);
activeSheet.getCell(0,0).text("Name");
activeSheet.getCell(0,1).text("Value");
activeSheet.getCell(0,2).text("T/F");
activeSheet.getCell(1,0).text("AW");
activeSheet.getCell(1,1).text("5");
activeSheet.getCell(1,2).text("T");

Methods

addColumns

addColumns(col, count, sheetArea?): void

Adds the column or columns to the data model at the specified index.

example

//This example adds columns.
sheet.setValue(0, 0, "value");
sheet.addRows(0, 2);
sheet.addColumns(0, 2);
sheet.setRowHeight(0, 50.0,GC.Spread.Sheets.SheetArea.viewport);
sheet.setColumnWidth(0, 150.0,GC.Spread.Sheets.SheetArea.viewport);
sheet.getRange(0, -1, 1, -1,GC.Spread.Sheets.SheetArea.viewport).backColor("Gray");
sheet.getRange(-1, 0, -1, 1,GC.Spread.Sheets.SheetArea.viewport).backColor ("Brown");

Parameters

Name Type Description
col number Column index at which to add the new columns.
count number The number of columns to add.
sheetArea? SheetArea -

Returns

void


addCustomFunction

addCustomFunction(fn): void

Adds a custom function.

Parameters

Name Type Description
fn Function The function to add.

Returns

void


addCustomName

addCustomName(name, formula, baseRow, baseCol, comment?, isReadOnly?): void

Adds a custom name.

example

//This example creates custom names.
sheet.setValue(0, 0, 1);
sheet.setValue(0, 1, 2);
sheet.setValue(0, 2, 3);
sheet.addCustomName("customName1","=12", 0, 0);
sheet.addCustomName("customName2","Average(20,45)", 0, 0);
sheet.addCustomName("customName3", "=$A$1:$C$1", 0, 0);
sheet.setFormula(1, 0, "customName1");
sheet.setFormula(1, 1, "customName2");
sheet.setFormula(1, 2, "sum(customName3)");

Parameters

Name Type Description
name string The custom name.
formula string The formula.
baseRow number The row index.
baseCol number The column index.
comment? string -
isReadOnly? boolean -

Returns

void


addNamedStyle

addNamedStyle(style): void

Adds a style to the Worksheet named styles collection.

example

var namedStyle = new GC.Spread.Sheets.Style();
namedStyle.name = "style1";
namedStyle.backColor = "green";
activeSheet.addNamedStyle(namedStyle);
activeSheet.setStyleName(1, 1, "style1"); // cell(1,1)'s backColor is  green.
activeSheet.setStyleName(2, 1, "style1");
var style = activeSheet.getNamedStyle("style1");
style.foreColor = "red";    // the namedStyle's foreColor is red.
activeSheet.repaint(); // the foreColor of the cell(1,1) and cell(2,1) is red.
activeSheet.getCell(1,1).value("test");
$("#button1").click(function () {
activeSheet.removeNamedStyle("style1");
    });

Parameters

Name Type Description
style Style The style to be added.

Returns

void


addRows

addRows(row, count, sheetArea?): void

Adds rows in this worksheet.

example

//This example adds rows.
sheet.setValue(0, 0, "value");
sheet.addRows(0, 2);
sheet.addColumns(0, 2);
sheet.setRowHeight(0, 50.0,GC.Spread.Sheets.SheetArea.viewport);
sheet.setColumnWidth(0, 150.0,GC.Spread.Sheets.SheetArea.viewport);
sheet.getRange(0, -1, 1, -1,GC.Spread.Sheets.SheetArea.viewport).backColor("Gray");
sheet.getRange(-1, 0, -1, 1,GC.Spread.Sheets.SheetArea.viewport).backColor ("Brown");

Parameters

Name Type Description
row number The index of the starting row.
count number The number of rows to add.
sheetArea? SheetArea -

Returns

void


addSelection

addSelection(row, column, rowCount, columnCount): void

Adds a cell or cells to the selection.

example

//This example adds a selection and uses the selection in a rule.
sheet.setValue(0,0, 1,3);
sheet.setValue(1,0, 50,3);
sheet.setValue(2,0, 100,3);
sheet.setValue(3,0, 2,3);
sheet.setValue(4,0, 60,3);
sheet.setValue(5,0, 90,3);
sheet.setValue(6,0, 3,3);
sheet.setValue(7,0, 40,3);
sheet.setValue(8,0, 70,3);
sheet.setValue(9,0, 5,3);
sheet.setValue(10,0, 35,3);
sheet.addSelection(0,0,11,1);
sheet.conditionalFormats.add3ScaleRule(1, 10, "red", 0, 50, "blue",2, 100, "yellow", sheet.getSelections());

Parameters

Name Type Description
row number The row index of the first cell to add.
column number The column index of the first cell to add.
rowCount number The number of rows to add.
columnCount number The number of columns to add.

Returns

void


addSpan

addSpan(row, col, rowCount, colCount, sheetArea?): void

Adds a span of cells to this sheet in the specified sheet area.

example

//This example creates cell spans.
sheet.setRowCount(4,1);
sheet.setColumnCount(4,2);
sheet.addSpan(0,0,3,3,GC.Spread.Sheets.SheetArea.colHeader);
sheet.addSpan(0,0,3,3,GC.Spread.Sheets.SheetArea.rowHeader);
sheet.addSpan(0,0,3,3,GC.Spread.Sheets.SheetArea.viewport);

Parameters

Name Type Description
row number The row index of the cell at which to start the span.
col number -
rowCount number The number of rows to span.
colCount number The number of columns to span.
sheetArea? SheetArea -

Returns

void


autoFitColumn

autoFitColumn(column): void

Automatically fits the viewport column.

example

//This example sets the column width based on the text.
activeSheet.setValue(0, 1, "testing");
activeSheet.autoFitColumn(1);

Parameters

Name Type Description
column number The column index.

Returns

void


autoFitRow

autoFitRow(row): void

Automatically fits the viewport row.

example

//This example sets the row height based on the text.
activeSheet.setValue(0, 1, "testing\r\nmultiple\r\nlines");
activeSheet.getCell(0,1).wordWrap(true);
activeSheet.autoFitRow(0);

Parameters

Name Type Description
row number The row index.

Returns

void


autoMerge

autoMerge(range, direction?, mode?, sheetArea?, selectionMode?): IRangeInfo[]

Applies auto merge for a range.

example

var range = new GC.Spread.Sheets.Range(-1, 0, -1, 1);
sheet.autoMerge(range);

Parameters

Name Type Description
range Range The auto merge range.
direction? AutoMergeDirection The auto merge direction. If this parameter is not provided, it defaults to column. Specially, if the direction is none, the auto merge for the range will be canceled.
mode? AutoMergeMode The auto merge mode. If this parameter is not provided, it defaults to free.
sheetArea? SheetArea The sheet area of the auto merge range. If this parameter is not provided, it defaults to viewport.
selectionMode? SelectionMode The auto merge selection mode. If this parameter is not provided, it defaults to source.

Returns

IRangeInfo[]

If no parameter is provided, returns all auto merge range infos of current worksheet. Each range info contains range, direction, mode, sheetArea, selection mode.


bind

bind(type, data?, fn?): void

Binds an event to the sheet.

example

//This example binds events.
sheet.bind(GC.Spread.Sheets.Events.LeftColumnChanged,function(event,data)
  {
    var str = "----------------------------------------\n";
    var title = "Event [LeftColumnChanged ] Fired";
    str = str.substr(0, 4) + title + str.substr(4 + title.length);
    if (typeof data == "object") {
        for (var key in data) {
            str += key + " : " + data[key] + "\n";
          }
      } else {
        str += data + "\n";
      }
      alert(str);
  });
sheet.bind(GC.Spread.Sheets.Events.TopRowChanged,function(event,data)
  {
        var str = "----------------------------------------\n";
        var title = "Event [TopRowChanged] Fired";
        str = str.substr(0, 4) + title + str.substr(4 + title.length);
        if (typeof data == "object") {
            for (var key in data) {
                str += key + " : " + data[key] + "\n";
            }
        } else {
            str += data + "\n";
        }
        alert(str);
    });

Parameters

Name Type Description
type string The event type.
data? any Optional. Specifies additional data to pass along to the function.
fn? Function Specifies the function to run when the event occurs.

Returns

void


bindColumn

bindColumn(index, column): void

Binds the column using the specified data field.

example

var test = [
{"Series0":2,"Series1":1},
{"Series0":4,"Series1":2},
{"Series0":3,"Series1":4}
];
sheet.setDataSource(test);
sheet.bindColumn(1,"Series0");
sheet.bindColumn(0,"Series1");

Parameters

Name Type Description
index number The column index.
column string | IColumn Column information with data field. If its type is string, it is regarded as name.

Returns

void


bindColumns

bindColumns(columns): void

Binds the columns using the specified data fields.

example

var datasource = [
                 { name: "Alice", age: 27, birthday: "1985/08/31", position: "Beijing", isMarried: false},
                 { name: "Aimee", age: 28, birthday: "1984/07/31", position: "Xi'An", isMarried: true},
                 { name: "Charles", age: 29, birthday: "1983/03/31", position: "ShangHai", isMarried: true},
            ];
var colInfos = [
                { name: "name", displayName: "Name", size: 70, pageBread: false},
                { name: "age", displayName: "Age", size: 40, resizable: false },
                { name: "birthday", displayName: "Birthday", formatter: "d/M/yy", size: 120 },
                { name: "position", displayName: "Position", size: 50, visible: true, value: function (item){
			             return 'China ' + item['position'];
		           }},
                { name: "isMarried", displayName: "IsMarried", size: 50, visible: true, cellType: new GC.Spread.Sheets.CellTypes.CheckBox()}
            ];
activeSheet.autoGenerateColumns = true;
activeSheet.setDataSource(datasource);
activeSheet.bindColumns(colInfos);

Parameters

Name Type Description
columns IColumn[] The array of column information with data fields. If an item's type is string, the item is regarded as name.

Returns

void


clear

clear(row, column, rowCount, colCount, area, storageType): void

Clears the specified area.

example

//This example clears the data from the specified range.
activeSheet.getCell(0,0).value("A1");
activeSheet.clear(0,0,3,3,GC.Spread.Sheets.SheetArea.viewport,GC.Spread.Sheets.StorageType.data);

Parameters

Name Type Description
row number The start row index.
column number The start column index.
rowCount number The number of rows to clear.
colCount number -
area SheetArea The area to clear.
storageType StorageType The clear type.

Returns

void


clearCustomFunctions

clearCustomFunctions(): void

Clears all custom functions.

example

//This example clears the custom functions from the active sheet.
activeSheet.clearCustomFunctions();

Returns

void


clearCustomNames

clearCustomNames(): void

Clears custom names.

example

//This example creates custom names and then clears them.
activeSheet.setValue(0, 0, 1);
activeSheet.setValue(0, 1, 2);
activeSheet.setValue(0, 2, 3);
activeSheet.addCustomName("customName1","=12", 0, 0);
activeSheet.addCustomName("customName2","Average(20,45)", 0, 0);
activeSheet.addCustomName("customName3", "=$A$1:$C$1", 0, 0);
activeSheet.setFormula(1, 0, "customName1");
activeSheet.setFormula(1, 1, "customName2");
activeSheet.setFormula(1, 2, "sum(customName3)");
activeSheet.clearCustomNames();

Returns

void


clearPendingChanges

clearPendingChanges(clearChangeInfo?): void

Clears the dirty, insert, and delete status from the current worksheet.

example

sheet.clearPendingChanges({clearType: 1, row: 0, rowCount: 3, col: 0, colCount: 4});
sheet.clearPendingChanges({clearType: 2, row: 0, rowCount: 3, col: -1});
sheet.clearPendingChanges({clearType: 4, row: 0, rowCount: 10, col: -1});

Parameters

Name Type
clearChangeInfo? IClearChangeInfo

Returns

void


clearSelection

clearSelection(): void

Clears the selection.

example

//This example clears the selection.
sheet.addSelection(4, 0, 2, 2);
sheet.clearSelection();

Returns

void


copyTo

copyTo(fromRow, fromColumn, toRow, toColumn, rowCount, columnCount, option): void

Copies data from one range to another.

example

//This example copies data to the specified location.
activeSheet.getCell(0,0).value("1");
activeSheet.copyTo(0,0,1,1,2,2,GC.Spread.Sheets.CopyToOptions.value);

Parameters

Name Type Description
fromRow number The source row.
fromColumn number The source column.
toRow number The target row.
toColumn number The target column.
rowCount number The row count.
columnCount number The column count.
option CopyToOptions The copy option.

Returns

void


currentTheme

currentTheme(value?): any

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

any

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


deleteColumns

deleteColumns(col, count, sheetArea?): void

Deletes the columns in this sheet at the specified index.

example

activeSheet.getCell(0,0).value("A1");
activeSheet.getCell(0,4).value("Test")
activeSheet.deleteColumns(0,2);
activeSheet.deleteRows(3,1);

Parameters

Name Type Description
col number The index of the first column to delete.
count number The number of columns to delete.
sheetArea? SheetArea -

Returns

void


deleteRows

deleteRows(row, count, sheetArea?): void

Deletes the rows in this worksheet at the specified index.

example

activeSheet.getCell(0,0).value("A1");
activeSheet.getCell(0,4).value("Test")
activeSheet.deleteColumns(0,2);
activeSheet.deleteRows(3,1);

Parameters

Name Type Description
row number The index of the first row to delete.
count number The number of rows to delete.
sheetArea? SheetArea -

Returns

void


editorStatus

editorStatus(): EditorStatus

Returns the editor's status.

Returns

EditorStatus

The editor status.


endEdit

endEdit(ignoreValueChange?): boolean

Stops editing the active cell.

example

//This example removes the text "123" when typing in a cell.
activeSheet.bind(GC.Spread.Sheets.Events.EditChange, function (sender,args) {
                if (args.editingText === "123") {
                    activeSheet.endEdit(true);
               }
            });

Parameters

Name Type Description
ignoreValueChange? boolean If set to true, does not apply the edited text to the cell.

Returns

boolean

true when able to stop cell editing successfully; otherwise, false.


fillAuto

fillAuto(startRange, wholeRange, options): void

Fills the specified range automatically.

example

activeSheet.setValue(0, 0, 5);
var start = new GC.Spread.Sheets.Range(0, 0, 1, 1);
var r3 = new GC.Spread.Sheets.Range(0, 0, 4, 1);
activeSheet.fillAuto(start,r3, {fillType:GC.Spread.Sheets.Fill.FillType.auto, series:GC.Spread.Sheets.Fill.FillSeries.column, direction:GC.Spread.Sheets.Fill.FillDirection.down});

Parameters

Name Type Description
startRange Range The fill start range.
wholeRange Range The entire range to fill.
options IFillOptions The range fill information.

Returns

void


fromJSON

fromJSON(sheetSettings): void

Loads the object state from the specified JSON string.

example

//This example uses the fromJSON method.
activeSheet.getCell(0,0).value(123);
var jsonStr = null;
//export
jsonStr = JSON.stringify(activeSheet.toJSON());
//import
activeSheet.fromJSON(JSON.parse(jsonStr));
alert(jsonStr);

Parameters

Name Type Description
sheetSettings Object The sheet data from deserialization.

Returns

void


frozenColumnCount

frozenColumnCount(colCount?, leftCol?): any

Gets or sets the number of frozen columns of the sheet.

example

sheet.frozenColumnCount(30, 20);

Parameters

Name Type
colCount? number
leftCol? number

Returns

any

If no value is set, returns the number of frozen columns; otherwise, returns the worksheet.


frozenRowCount

frozenRowCount(rowCount?, topRow?): any

Gets or sets the number of frozen rows of the sheet.

example

sheet.frozenRowCount(60, 50);

Parameters

Name Type
rowCount? number
topRow? number

Returns

any

If no value is set, returns the number of frozen rows; otherwise, returns the worksheet.


frozenTrailingColumnCount

frozenTrailingColumnCount(colCount?, stickToEdge?): any

Gets or sets the number of trailing frozen columns of the sheet.

example

sheet.frozenTrailingColumnCount(1, false);

Parameters

Name Type
colCount? number
stickToEdge? boolean

Returns

any

If no value is set, returns the number of trailing frozen columns; otherwise, returns the worksheet.


frozenTrailingRowCount

frozenTrailingRowCount(rowCount?, stickToEdge?): any

Gets or sets the number of trailing frozen rows of the sheet.

example

sheet.frozenTrailingRowCount(1, false);

Parameters

Name Type
rowCount? number
stickToEdge? boolean

Returns

any

If no value is set, returns the number of trailing frozen rows; otherwise, returns the worksheet.


getActiveColumnIndex

getActiveColumnIndex(): number

Gets the active column index for this sheet.

example

//This example gets the active column.
sheet.setActiveCell(5,5);
alert(sheet.getActiveColumnIndex());
alert(sheet.getActiveRowIndex());
spread.bind(GC.Spread.Sheets.Events.EnterCell, function (event, data) {
    alert(data.col);
    alert(data.row);
  });
spread.bind(GC.Spread.Sheets.Events.LeaveCell, function (event, data) {
    alert(data.col);
    alert(data.row);
  });

Returns

number

The column index of the active cell.


getActiveRowIndex

getActiveRowIndex(): number

Gets the active row index for this sheet.

example

//This example gets the active row.
sheet.setActiveCell(5,5);
alert(sheet.getActiveColumnIndex());
alert(sheet.getActiveRowIndex());
spread.bind(GC.Spread.Sheets.Events.EnterCell, function (event, data) {
    alert(data.col);
    alert(data.row);
  });
spread.bind(GC.Spread.Sheets.Events.LeaveCell, function (event, data) {
    alert(data.col);
    alert(data.row);
  });

Returns

number

The row index of the active cell.


getActualStyle

getActualStyle(row, column, sheetArea?, sheetStyleOnly?): Style

Gets the actual style information for a specified cell in the specified sheet area.

example

//This example uses the getActualStyle method.
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
style.borderLeft =new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
style.borderTop = new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
style.borderRight = new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
style.borderBottom = new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
activeSheet.setStyle(1,1,style,GC.Spread.Sheets.SheetArea.viewport);
var cstyle = activeSheet.getActualStyle(1,1,GC.Spread.Sheets.SheetArea.viewport, true);
alert(cstyle.backColor);

Parameters

Name Type Description
row number The row index.
column number The column index.
sheetArea? SheetArea -
sheetStyleOnly? boolean -

Returns

Style

Returns the cell style of the specified cell.


getAltText

getAltText(row, col, sheetArea?): any

Gets the alternative text from the specified cell in the specified sheet area.

example

var SpreadIcon = {
    FolderOpen: '\ue685',
    InfoFilled: '\ue718',
    Library: '\ue69d',
    NotebookFilled: '\uD800\uDC0F',
    Browse: '\ue626'
};
activeSheet.setValue(1, 1, SpreadIcon.FolderOpen);
activeSheet.setAltText(1, 1, "Folder Open Icon");
alert(activeSheet.getAltText(1, 1));

Parameters

Name Type Description
row number The row index.
col number The column index.
sheetArea? SheetArea The sheet area. If this parameter is not provided, it defaults to viewport.

Returns

any

Returns the alternative text of the cell.


getArray

getArray(row, column, rowCount, columnCount, getFormula?): any[]

Gets an object array from a specified range of cells.

example

//This example uses the getArray method.
//set value
var array = [[1,2,3],[4,5],[6,7,8,9]];
activeSheet.setArray(1, 2, array);
//set formula
var array = [["=1+1","=2+2","=3+3"],["=4+4","=5+5"],["=6+6","=7+7","=8+8","=9+9"]];
activeSheet.setArray(1, 2, array, true);
//get value
var newArray = activeSheet.getArray(1, 2, 3, 4);
//getformula
var newArray = activeSheet.getArray(1, 2, 3, 4, true);
//alert(newArray[0]);

Parameters

Name Type Description
row number The row index.
column number The column index.
rowCount number The row count.
columnCount number -
getFormula? boolean If true, return formulas; otherwise, return values.

Returns

any[]

The object array from the specified range of cells.


getBindingPath

getBindingPath(row, col): string

Gets the binding path of cell-level binding from the specified cell in the specified sheet area.

example

//This example uses the getBindingPath method.
var person = {name: "Wang feng", age: 25, address: {postcode: "710075"}};
var source = new GC.Spread.Sheets.Bindings.CellBindingSource(person);
activeSheet.setBindingPath(0, 0, "name");
activeSheet.setBindingPath(1, 1, "age");
activeSheet.setBindingPath(3, 3, "address.postcode");
activeSheet.setDataSource(source);
alert(activeSheet.getBindingPath(0, 0, GC.Spread.Sheets.SheetArea.viewport));

Parameters

Name Type Description
row number The row index.
col number The column index.

Returns

string

Returns the binding path of the cell for cell-level binding.


getCell

getCell(row, col, sheetArea?): CellRange

Gets the specified cell in the specified sheet area.

example

//This example gets the cell.
activeSheet.getCell(1,1).text("cell object");

Parameters

Name Type Description
row number The row index.
col number The column index.
sheetArea? SheetArea The sheet area. If this parameter is not given, it defaults toviewport.

Returns

CellRange

The cell.


getCellRect

getCellRect(row, col, rowViewportIndex?, colViewportIndex?): Rect

Gets the rectangle of the cell.

example

//This example uses the getCellRect method.
activeSheet.bind(GC.Spread.Sheets.Events.CellClick, function (e, info) {
               if (info.sheetArea === GC.Spread.Sheets.SheetArea.viewport) {
                   alert("Clicked cell index (" + info.row + "," + info.col + ")");
                   //Acquire the coordinate information of regular cells which exist at the specified index position
                   var cellRect = activeSheet.getCellRect(info.row, info.col);
                   alert("X coordinate:" + cellRect.x);
                   alert("Y coordinate:" + cellRect.y);
                   alert("Cell width:" + cellRect.width);
                   alert("Cell height:" + cellRect.height);
               }
           });

Parameters

Name Type Description
row number The row index.
col number The column index.
rowViewportIndex? number -
colViewportIndex? number -

Returns

Rect

Object that contains the size and location of the cell rectangle.


getCellType

getCellType(row, col, sheetArea?): Base

Gets the cell type.

example

//This example gets the cell type.
var cellType = new GC.Spread.Sheets.CellTypes.Button();
cellType.buttonBackColor("#FFFF00");
cellType.text("this is a button");
activeSheet.getCell(0, 2).cellType(cellType);
var cellType = activeSheet.getCellType(0,2,GC.Spread.Sheets.SheetArea.viewport)
if (cellType instanceof GC.Spread.Sheets.CellTypes.Button) {
      alert("This is a ButtonCellType");
}

Parameters

Name Type Description
row number The row index.
col number The column index.
sheetArea? SheetArea -

Returns

Base

Returns the cell type for the specified cell.


getColumnCount

getColumnCount(sheetArea?): number

Gets the column count in the specified sheet area.

example

//This example gets the number of columns.
var count = activeSheet.getColumnCount(GC.Spread.Sheets.SheetArea.viewport);
alert(count);

Parameters

Name Type
sheetArea? SheetArea

Returns

number

The number of columns.


getColumnPageBreak

getColumnPageBreak(column): boolean

Gets whether a forced page break is inserted before the specified column on this sheet when printing.

Parameters

Name Type Description
column number The column index.

Returns

boolean

true if a forced page break is inserted before the specified column; otherwise, false.


getColumnResizable

getColumnResizable(col, sheetArea?): boolean

Gets a value that indicates whether the user can resize a specified column in the specified sheet area.

example

//This example gets whether the column is resizable.
sheet.setRowCount(10);
sheet.setColumnCount(7);
sheet.setValue(0, 0,"Western");
sheet.setValue(0, 1,"Western");
sheet.setValue(0, 2,"Western");
sheet.setValue(1, 0,"A");
sheet.setValue(1, 1,"B");
sheet.setValue(1, 2,"C");
sheet.setColumnResizable(0,true, GC.Spread.Sheets.SheetArea.colHeader);
sheet.setRowResizable(0,true, GC.Spread.Sheets.SheetArea.rowHeader);
alert( sheet.getColumnResizable(0));
alert( sheet.getRowResizable(0, GC.Spread.Sheets.SheetArea.rowHeader));

Parameters

Name Type Description
col number The column index.
sheetArea? SheetArea -

Returns

boolean

true if the user can resize the specified column; otherwise, false.


getColumnVisible

getColumnVisible(col, sheetArea?): boolean

Gets whether a column in the specified sheet area is displayed.

example

//This example returns the visible and width settings for a column.
var visible = activeSheet.getColumnVisible(1, GC.Spread.Sheets.SheetArea.viewport);
var width = activeSheet.getColumnWidth(1, GC.Spread.Sheets.SheetArea.viewport);
alert(visible);
alert(width);

Parameters

Name Type Description
col number The column index.
sheetArea? SheetArea -

Returns

boolean

true if the column is visible in the sheet area; otherwise, false.


getColumnWidth

getColumnWidth(col, sheetArea?, getDynamicSize?): any

Gets the width in pixels or the dynamic size for the specified column in the specified sheet area.

example

//This example returns the visible and width settings for a column.
var visible = activeSheet.getColumnVisible(1, GC.Spread.Sheets.SheetArea.viewport);
var width = activeSheet.getColumnWidth(1, GC.Spread.Sheets.SheetArea.viewport);
alert(visible);
alert(width);

Parameters

Name Type Description
col number The column index.
sheetArea? SheetArea -
getDynamicSize? boolean -

Returns

any

The column width in pixels or the dynamic size.


getCsv

getCsv(row, column, rowCount, columnCount, rowDelimiter, columnDelimiter): string

Gets delimited text from a range.

Parameters

Name Type Description
row number The start row.
column number The start column.
rowCount number The row count.
columnCount number The column count.
rowDelimiter string The row delimiter that is appended to the end of the row.
columnDelimiter string The column delimiter that is appended to the end of the column.

Returns

string

The text from the range with the specified delimiters.


getCustomFunction

getCustomFunction(name): void

Gets a custom function.

Parameters

Name Type
name string

Returns

void

The custom function.


getCustomName

getCustomName(name): NameInfo

Gets the specified custom name information.

example

//This example gets the custom name and formula.
activeSheet.setValue(0, 0, 1);
activeSheet.setValue(0, 1, 2);
activeSheet.setValue(0, 2, 3);
activeSheet.addCustomName("customName1", "=12", 0, 0);
activeSheet.addCustomName("customName2", "Average(20,45)", 0, 0);
activeSheet.addCustomName("customName3", "=$A$1:$C$1");
activeSheet.setFormula(1, 0, "customName1");
activeSheet.setFormula(1, 1, "customName2");
activeSheet.setFormula(1, 2, "sum(customName3)");
$("#button1").click(function () {
    let cname = activeSheet.getCustomName("customName2");
    if (cname instanceof GC.Spread.Sheets.NameInfo) {
        //get CustomName
        let name = cname.getName();
        //get Expression
        let expression = cname.getExpression();
        //get Expression String
        let expStr = GC.Spread.Sheets.CalcEngine.expressionToFormula(activeSheet, expression, 0, 0);
        alert("Name:" + name + ";Expression: =" + expStr);
    }
});

Parameters

Name Type
name string

Returns

NameInfo

The information for the specified custom name.


getCustomNames

getCustomNames(): NameInfo[]

Gets all custom name information.

Returns

NameInfo[]

The type GC.Spread.Sheets.NameInfo stored in an array.


getDataColumnName

getDataColumnName(column): string

Gets the column name at the specified position.

example

//This example returns the name for the specified bound column.
var test = [
        {"Series0":2,"Series1":1},
        {"Series0":4,"Series1":2},
        {"Series0":3,"Series1":4}
    ];
activeSheet.setDataSource(test);
activeSheet.bindColumn(1,"Series0");
activeSheet.bindColumn(0,"Series1");
var colname = activeSheet.getDataColumnName(0);
alert(colname);

Parameters

Name Type Description
column number The column index for which the name is requested.

Returns

string

The column name for data binding.


getDataItem

getDataItem(row): any

Gets the data item.

example

//This example uses the getDataItem method.
var test = [
      { "Series0": 2, "Series1": 1 },
      { "Series0": 4, "Series1": 2 },
      { "Series0": 3, "Series1": 4 }
           ];
           activeSheet.autoGenerateColumns = true;
           activeSheet.setDataSource(test, false);
           alert(JSON.stringify(activeSheet.getDataItem(0)));

Parameters

Name Type Description
row number The row index.

Returns

any

The row data.


getDataSource

getDataSource(): any

Gets the data source that populates the sheet.

function

example

var test = [
        {"Series0":2,"Series1":1},
        {"Series0":4,"Series1":2},
        {"Series0":3,"Series1":4}
    ];
activeSheet.setDataSource(test);
alert(activeSheet.getDataSource);

Returns

any

Returns the data source.


getDataValidator

getDataValidator(row, col, sheetArea): DefaultDataValidator

Gets the cell data validator.

example

spread.options.highlightInvalidData = true;
var dv = GC.Spread.Sheets.DataValidation.createListValidator("1,2,3");
dv.showInputMessage(true);
dv.inputMessage("Value must be 1,2 or 3");
dv.inputTitle("tip");
activeSheet.setDataValidator(1,1,1,1,dv,GC.Spread.Sheets.SheetArea.viewport);
alert(activeSheet.getDataValidator(1,1).getValidList(activeSheet,1,1));

Parameters

Name Type Description
row number The row index.
col number The column index.
sheetArea SheetArea The sheet area. If this parameter is not provided, it defaults to viewport.

Returns

DefaultDataValidator

Returns the cell data validator for the specified cell.


getDefaultStyle

getDefaultStyle(sheetArea?): Style

Gets the default style information for the sheet.

example

//This example uses the getDefaultStyle method.
var defaultStyle = new GC.Spread.Sheets.Style();
defaultStyle.backColor = "LemonChiffon";
defaultStyle.foreColor = "Red";
defaultStyle.borderLeft = new GC.Spread.Sheets.LineBorder("Green");
defaultStyle.borderTop = new GC.Spread.Sheets.LineBorder("Green");
defaultStyle.borderRight = new GC.Spread.Sheets.LineBorder("Green");
defaultStyle.borderBottom = new GC.Spread.Sheets.LineBorder("Green");
activeSheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);
var cstyle = activeSheet.getDefaultStyle(GC.Spread.Sheets.SheetArea.viewport);
alert(cstyle.backColor);

Parameters

Name Type
sheetArea? SheetArea

Returns

Style

Returns the sheet's default style.


getDefaultValue

getDefaultValue(row, col): any

Gets the default value from the specified cell.

example

sheet.setDefaultValue(0, 0, 20);
let defaultValue = sheet.getDefaultValue(0, 0);
let value = sheet.getValue(0, 0); // the value equals to defaultValue

Parameters

Name Type Description
row number The row index.
col number The column index.

Returns

any

Returns the default value of cell.


getDeletedRows

getDeletedRows(): any[]

Gets the deleted row collection.

Returns

any[]

The deleted rows collection. the item in array contains two properties, row.row: specifies deleted row index, row.originalItem: specifies deleted data item.


getDependents

getDependents(row, col): ICellsInfo[]

Gets the dependent CellRange information object array of the cell.

example

sheet.getDependents(1, 1);

Parameters

Name Type Description
row number The row index.
col number The column index.

Returns

ICellsInfo[]

Returns dependent cell information object array dependentsInfo.row Indicates the cellRange row index. dependentsInfo.col Indicates the cellRange col index. dependentsInfo.rowCount Indicates the cellRange row count. dependentsInfo.colCount Indicates the cellRange colcount. dependentsInfo.sheetName Indicates the workSheet name.


getDirtyCells

getDirtyCells(row, col, rowCount, colCount): IDirtyCellInfo[]

Gets the dirty cell collection.

Parameters

Name Type Description
row number The row index.
col number The column index.
rowCount number The number of rows in the range of dirty cells.
colCount number The number of columns in the range of dirty cells.

Returns

IDirtyCellInfo[]

The dirty cells.


getDirtyRows

getDirtyRows(): any[]

Gets the dirty row collection.

Returns

any[]

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


getFormatter

getFormatter(row, col, sheetArea): any

Gets the cell formatter.

example

//This example returns the format object for the active sheet.
activeSheet.getCell(0, 1).formatter("M");
activeSheet.setValue(0, 1, new Date(2011, 2, 9));
var style = activeSheet.getFormatter(0,1,GC.Spread.Sheets.SheetArea.viewport);
alert(style);

Parameters

Name Type Description
row number The row index.
col number The column index.
sheetArea SheetArea The sheet area. If this parameter is not provided, it defaults to viewport.

Returns

any

Returns the cell formatter string or object for the specified cell.


getFormula

getFormula(row, col, sheetArea?): string

Gets the formula in the specified cell in this sheet.

example

//This example returns the formula in the specified cell.
activeSheet.setValue(0,0,1);
activeSheet.setValue(0,1,2);
activeSheet.setValue(0,2,10);
activeSheet.getCell(1,1).formula("=SUM(A1:C1)");
let formula = activeSheet.getFormula(1, 1, GC.Spread.Sheets.SheetArea.viewport);
alert(formula);

Parameters

Name Type Description
row number The row index.
col number The column index.
sheetArea? SheetArea -

Returns

string

Returns the formula string.


getFormulaInformation

getFormulaInformation(row, col): IFormulaInfo

Gets the formula detail information in the specified cell in this sheet.

example

activeSheet.setValue(0,0,1);
activeSheet.setValue(0,1,2);
activeSheet.setValue(0,2,10);
activeSheet.getCell(1,1).formula("=SUM(A1:C1)");
let test = activeSheet.getFormulaInformation(1,1, GC.Spread.Sheets.SheetArea.viewport);
alert(test.formula);

Parameters

Name Type Description
row number The row index.
col number The column index.

Returns

IFormulaInfo

formulaInfo - Returns the formula information about the cell. formulaInfo.hasFormula Indicates whether there is a formula in the cell. formulaInfo.isArrayFormula Indicates whether the formula is an array formula. formulaInfo.formula The formula string. formulaInfo.formulaWithCulture The formula string with culture.


getHyperlink(row, col, sheetArea?): IHyperlink

Sets the hyperlink data for the specified cell in the specified sheet area.

example

//This example uses the getHyperlink method.
let firstHyperlinkData = sheet.getHyperlink(0, 2, GC.Spread.Sheets.SheetArea.viewport);
let secondHyperlinkData = sheet.setHyperlink(1, 1, GC.Spread.Sheets.SheetArea.viewport);

Parameters

Name Type Description
row number The row index.
col number The column index.
sheetArea? SheetArea The sheet area. If this parameter is not provided, it defaults to viewport.

Returns

IHyperlink


getInsertRows

getInsertRows(): any[]

Gets the inserted row collection.

Returns

any[]

The inserted rows collection, the item in array contains two properties, row.row: specifies insert row index, row.item: specifies insert data item.


getNamedStyle

getNamedStyle(name): Style

Gets a style from the Worksheet named styles collection which has the specified name.

Parameters

Name Type Description
name string The name of the style to return.

Returns

Style

Returns the specified named style.


getNamedStyles

getNamedStyles(): Style[]

Gets named styles from the Worksheet.

Returns

Style[]

The GC.Spread.Sheets.Style array of named styles.


getParent

getParent(): Workbook

Gets the parent Spread object of the current sheet.

Returns

Workbook

Returns the parent Spread object of the current sheet.


getPrecedents

getPrecedents(row, col): ICellsInfo[]

Gets the precedent CellRange information object array of the cell.

example

sheet.getPrecedents(1, 1);

Parameters

Name Type Description
row number The row index.
col number The column index.

Returns

ICellsInfo[]

Returns precedent cellRange information object array precedentsInfo.row Indicates the cellRange row index. precedentsInfo.col Indicates the cellRange col index. precedentsInfo.rowCount Indicates the cellRange row count. precedentsInfo.colCount Indicates the cellRange colcount. precedentsInfo.sheetName Indicates the workSheet name.


getRange

getRange(row, col, rowCount?, colCount?, sheetArea?): CellRange

Gets a range of cells by row info and column info in the specified sheet area.

Parameters

Name Type Description
row number The row index.
col number The column index.
rowCount? number The row count of the range. If you do not provide this parameter, it defaults to 1.
colCount? number The column count of the range. If you do not provide this parameter, it defaults to 1.
sheetArea? SheetArea The sheet area. If this parameter is not given, it defaults toviewport.

Returns

CellRange

The cellRange. If row is -1 and rowCount is -1, the range represents columns. For example, sheet.getRange(-1,4,-1,6) returns the columns "E:J". If col is -1 and colCount is -1, the range represents rows. For example, sheet.getRange(4,-1,6,-1) returns the rows "5:10".

getRange(address, sheetArea?): CellRange

Gets a range of cells by A1 style address(not support R1C1 style) in the specified sheet area.

example

// Get a single cell, it equals to sheet.getRange(0, 0, 1, 1)
sheet.getRange("A1")
// Get whole columns, it equals to sheet.getRange(-1, 0, -1, 3)
sheet.getRange("A:C")
// Get whole rows, it equals to sheet.getRange(0, -1, 3, -1)
sheet.getRange("1:3")
// Get a range, it equals to sheet.getRange(0, 0, 3, 3)
sheet.getRange("A1:C3")

Parameters

Name Type Description
address string The range address string. For example "C1", "A:C", "A1:C3", "1:3".
sheetArea? SheetArea The sheet area. If this parameter is not given, it defaults toviewport.

Returns

CellRange

The cellRange.


getRowCount

getRowCount(sheetArea?): number

Gets the row count in the specified sheet area.

example

//This example gets the row count.
var count = activeSheet.getRowCount(GC.Spread.Sheets.SheetArea.viewport);
alert(count);

Parameters

Name Type
sheetArea? SheetArea

Returns

number

The number of rows.


getRowHeight

getRowHeight(row, sheetArea?, getDynamicSize?): any

Gets the height in pixels or the dynamic size for the specified row in the specified sheet area.

example

//This example returns the height for the specified row.
var rheight = activeSheet.getRowHeight(1,GC.Spread.Sheets.SheetArea.viewport);
alert(rheight);

Parameters

Name Type Description
row number The row index.
sheetArea? SheetArea -
getDynamicSize? boolean -

Returns

any

The row height in pixels or the dynamic size.


getRowPageBreak

getRowPageBreak(row): boolean

Gets whether a forced page break is inserted before the specified row on this sheet when printing.

Parameters

Name Type Description
row number The row index.

Returns

boolean

true if a forced page break is inserted before the specified row; otherwise, false.


getRowResizable

getRowResizable(row, sheetArea?): boolean

Gets a value that indicates whether users can resize the specified row in the specified sheet area.

example

//This example gets whether the row is resizable.
sheet.setRowCount(10);
sheet.setColumnCount(7);
sheet.setValue(0, 0,"Western");
sheet.setValue(0, 1,"Western");
sheet.setValue(0, 2,"Western");
sheet.setValue(1, 0,"A");
sheet.setValue(1, 1,"B");
sheet.setValue(1, 2,"C");
sheet.setColumnResizable(0,true, GC.Spread.Sheets.SheetArea.colHeader);
sheet.setRowResizable(0,true, GC.Spread.Sheets.SheetArea.rowHeader);
alert( sheet.getColumnResizable(0));
alert( sheet.getRowResizable(0, GC.Spread.Sheets.SheetArea.rowHeader));

Parameters

Name Type Description
row number The row index.
sheetArea? SheetArea -

Returns

boolean

true if the users can resize the specified row; otherwise, false.


getRowVisible

getRowVisible(row, sheetArea?): boolean

Gets whether the control displays the specified row.

example

//This example returns the visible setting for the specified row.
rvisible = activeSheet.getRowVisible(1,GC.Spread.Sheets.SheetArea.viewport);
alert(rvisible);

Parameters

Name Type Description
row number The row index.
sheetArea? SheetArea -

Returns

boolean

true if the row is visible in the sheet area; otherwise, false.


getSelections

getSelections(): Range[]

Gets the selections in the current sheet.

Returns

Range[]

The type GC.Spread.Sheets.Range is stored in an Array.


getSpans

getSpans(range?, sheetArea?): Range[]

Gets the spans in the specified range in the specified sheet area.

Parameters

Name Type Description
range? Range The cell range.
sheetArea? SheetArea -

Returns

Range[]

An array that contains span information whose item type is GC.Spread.Sheets.Range.


getSparkline

getSparkline(row, column): Sparkline

Gets the sparkline for the specified cell.

example

//This example creates and gets a sparkline.
var cellr = new GC.Spread.Sheets.Range(0, 0, 1, 5);
var ex = new GC.Spread.Sheets.Sparklines.SparklineSetting();
ex.options.SeriesColor  = "Aquamarine";
sheet.setValue(0, 0, 2);
sheet.setValue(0, 1, 5);
sheet.setValue(0, 2, 4);
sheet.setValue(0, 3, -1);
sheet.setValue(0, 4, 3);
sheet.setSparkline(0, 5, cellr, GC.Spread.Sheets.Sparklines.DataOrientation.horizontal, GC.Spread.Sheets.Sparklines.SparklineType.column, ex);
alert(sheet.getSparkline(0, 5).toString());
//sheet.removeSparkline(0, 5);

Parameters

Name Type Description
row number The row index.
column number -

Returns

Sparkline

The sparkline for the cell.


getStyle

getStyle(row, column, sheetArea?): Style

Gets the style information for a specified cell in the specified sheet area.

example

//This example uses the getStyle method.
var style = new GC.Spread.Sheets.Style();
style.backColor = "red";
style.borderLeft =new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
style.borderTop = new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
style.borderRight = new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
style.borderBottom = new GC.Spread.Sheets.LineBorder("blue",GC.Spread.Sheets.LineStyle.medium);
activeSheet.setStyle(1,1,style,GC.Spread.Sheets.SheetArea.viewport);
var cstyle = activeSheet.getStyle(1,1,GC.Spread.Sheets.SheetArea.viewport);
alert(cstyle.backColor);

Parameters

Name Type Description
row number The row index.
column number The column index.
sheetArea? SheetArea -

Returns

Style

Returns the cell style of the specified cell.


getStyleName

getStyleName(row, column, sheetArea?): string

Gets the name of the style for a specified cell in the specified sheet area.

example

//This example uses the getStyleName method.
var namedStyle = new GC.Spread.Sheets.Style();
namedStyle.name = "style1";
namedStyle.backColor = "green";
activeSheet.addNamedStyle(namedStyle);
activeSheet.setStyleName(1, 1, "style1"); // cell(1,1)'s backColor is  green.
activeSheet.setStyleName(2, 1, "style1");
alert(activeSheet.getStyleName(1,1,GC.Spread.Sheets.SheetArea.viewport));

Parameters

Name Type Description
row number The row index.
column number The column index.
sheetArea? SheetArea -

Returns

string

Returns the name string for the style.


getTag

getTag(row, col, sheetArea?): any

Gets the tag value from the specified cell in the specified sheet area.

example

//This example adds and gets a cell tag.
activeSheet.getRange(1, -1, 1, -1).tag("row tag");
alert(activeSheet.getTag(1,-1,GC.Spread.Sheets.SheetArea.viewport));

Parameters

Name Type Description
row number The row index.
col number The column index.
sheetArea? SheetArea The sheet area. If this parameter is not provided, it defaults to viewport.

Returns

any

Returns the tag value of the cell.


getText

getText(row, col, sheetArea?): string

Gets the formatted text in the cell in the specified sheet area.

example

activeSheet.getText(1, 0);

Parameters

Name Type Description
row number The row index.
col number The column index.
sheetArea? SheetArea -

Returns

string

Returns the formatted text of the cell.


getUsedRange

getUsedRange(type?): Range

Get the range used in the sheet

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:1});
var sheet = spread.getSheet(0);
sheet.setValue(2, 2, "value");
sheet.setValue(4, 2, "value");
sheet.setValue(6, 8, "value");
sheet.setValue(10, 15, "value");
sheet.setFormula(5, 5, "=C3");
sheet.setFormula(8, 8, "=I6");
sheet.setStyle(30,4, new GC.Spread.Sheets.Style())
sheet.setStyle(3, 24, new GC.Spread.Sheets.Style())
sheet.setRowHeight(8, 40);
sheet.getUsedRange(GC.Spread.Sheets.UsedRangeType.all);

Parameters

Name Type
type? UsedRangeType

Returns

Range

If no value is set, returns null; otherwise, returns a used range.


getValue

getValue(row, col, sheetArea?, valueType?): any

Gets the unformatted data from the specified cell in the specified sheet area.

example

activeSheet.getValue(1, 1, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.ValueType.richText);

Parameters

Name Type Description
row number The row index.
col number The column index.
sheetArea? SheetArea -
valueType? ValueType -

Returns

any

Returns the value of the cell.


getViewportBottomRow

getViewportBottomRow(rowViewportIndex): number

Gets the index of the bottom row in the viewport.

example

//This example returns the indices for the bottom and top rows and left and right columns in the current sheet view.
var brow = activeSheet.getViewportBottomRow(1);
var lcol = activeSheet.getViewportLeftColumn(1);
var rcol = activeSheet.getViewportRightColumn(1);
var trow = activeSheet.getViewportTopRow(1);
alert(brow);
alert(lcol);
alert(rcol);
alert(trow);

Parameters

Name Type Description
rowViewportIndex number The index of the viewport.

Returns

number

The index of the bottom row in the viewport.


getViewportHeight

getViewportHeight(rowViewportIndex): number

Gets the height of the specified viewport row for the active sheet.

example

//This example uses the getViewportHeight method.
alert(activeSheet.getViewportHeight(1));

Parameters

Name Type Description
rowViewportIndex number The index of the row viewport.

Returns

number

The height of the viewport.


getViewportLeftColumn

getViewportLeftColumn(columnViewportIndex): number

Gets the index of the left column in the viewport.

example

//This example returns the indices for the bottom and top rows and left and right columns in the current sheet view.
var brow = activeSheet.getViewportBottomRow(1);
var lcol = activeSheet.getViewportLeftColumn(1);
var rcol = activeSheet.getViewportRightColumn(1);
var trow = activeSheet.getViewportTopRow(1);
alert(brow);
alert(lcol);
alert(rcol);
alert(trow);

Parameters

Name Type Description
columnViewportIndex number The index of the viewport.

Returns

number

The index of the left column in the viewport.


getViewportRightColumn

getViewportRightColumn(columnViewportIndex): number

Gets the index of the right column in the viewport.

example

//This example returns the indices for the bottom and top rows and left and right columns in the current sheet view.
var brow = activeSheet.getViewportBottomRow(1);
var lcol = activeSheet.getViewportLeftColumn(1);
var rcol = activeSheet.getViewportRightColumn(1);
var trow = activeSheet.getViewportTopRow(1);
alert(brow);
alert(lcol);
alert(rcol);
alert(trow);

Parameters

Name Type Description
columnViewportIndex number The index of the viewport.

Returns

number

The index of the right column in the viewport.


getViewportTopRow

getViewportTopRow(rowViewportIndex): number

Gets the index of the top row in the viewport.

example

//This example returns the indices for the bottom and top rows and left and right columns in the current sheet view.
var brow = activeSheet.getViewportBottomRow(1);
var lcol = activeSheet.getViewportLeftColumn(1);
var rcol = activeSheet.getViewportRightColumn(1);
var trow = activeSheet.getViewportTopRow(1);
alert(brow);
alert(lcol);
alert(rcol);
alert(trow);

Parameters

Name Type Description
rowViewportIndex number The index of the viewport.

Returns

number

The index of the top row in the viewport.


getViewportWidth

getViewportWidth(columnViewportIndex): number

Gets the width of the specified viewport column for the active sheet.

example

//This example uses the getViewportWidth method.
alert(activeSheet.getViewportWidth(1));

Parameters

Name Type Description
columnViewportIndex number The index of the column viewport.

Returns

number

The width of the viewport


groupSparkline

groupSparkline(sparklines): SparklineGroup

Groups the sparklines.

example

//This example groups a sparkline.
sheet.setValue(0, 0, "Data Range is A2-A9");
sheet.setValue(1, 0, 1);
sheet.setValue(2, 0, -2);
sheet.setValue(3, 0, -1);
sheet.setValue(4, 0, 6);
sheet.setValue(5, 0, 4);
sheet.setValue(6, 0, -4);
sheet.setValue(7, 0, 3);
sheet.setValue(8, 0, 8);
var data = new GC.Spread.Sheets.Range(1, 0, 8, 1);
var setting = new GC.Spread.Sheets.Sparklines.SparklineSetting();
var s1=  sheet.setSparkline(13, 0, data
        , GC.Spread.Sheets.Sparklines.DataOrientation.vertical
        , GC.Spread.Sheets.Sparklines.SparklineType.line
        , setting
        );
var s2 =sheet.setSparkline(13, 3, data
        , GC.Spread.Sheets.Sparklines.DataOrientation.vertical
        , GC.Spread.Sheets.Sparklines.SparklineType.column
        , setting
        );
var s3=  sheet.setSparkline(13, 6, data
        , GC.Spread.Sheets.Sparklines.DataOrientation.vertical
        , GC.Spread.Sheets.Sparklines.SparklineType.winloss
        , setting
        );
var group = sheet.groupSparkline([s1,s2,s3]);

Parameters

Name Type Description
sparklines Sparkline[] The sparklines to group.

Returns

SparklineGroup

The sparkline group.


hasPassword

hasPassword(): boolean

Get whether worksheet has set a password for protection.

example

let password = "fe4c4be8"
sheet.protect(password);
if(sheet.hasPassword()) {
    sheet.unprotect(password);
} else {
    sheet.unprotect();
}

Returns

boolean

Returns true if the password is set, otherwise false


hasPendingChanges

hasPendingChanges(): boolean

Gets whether there is a dirty, insert, or delete status for the specified range.

Returns

boolean

true if any of the rows or cells in the range are dirty, or have been inserted or deleted; otherwise, false.


hitTest

hitTest(x, y): IHitTestInformation

Performs a hit test.

Parameters

Name Type Description
x number The x-coordinate.
y number The y-coordinate.

Returns

IHitTestInformation

The hit test information.


invalidateLayout

invalidateLayout(): void

Invalidates the sheet layout.

example

//This example updates the layout.
activeSheet.columnOutlines.group(0, 1);
activeSheet.invalidateLayout();
activeSheet.repaint();

Returns

void


isDirtySuspended

isDirtySuspended(): boolean

Gets whether recording the dirty data is suspended.

example

//This example uses the isDirtySuspended method.
var customers = [
               { ID: 0, Name: 'A', Info1: 'Info0' },
               { ID: 1, Name: 'B', Info1: 'Info1' },
               { ID: 2, Name: 'C', Info1: 'Info2' },
            ];
activeSheet.setDataSource(customers);
activeSheet.suspendDirty();
alert(activeSheet.isDirtySuspended());
activeSheet.resumeDirty();
alert(activeSheet.isDirtySuspended());

Returns

boolean

Whether the dirty data is suspended.


isEditing

isEditing(): boolean

Gets whether the sheet is in edit mode.

example

//This example uses the isEditing method.
alert(activeSheet.isEditing());

Returns

boolean

true if the sheet is in edit mode; otherwise, false.


isPaintSuspended

isPaintSuspended(): boolean

Get if sheet paint is suspended.

Returns

boolean


isPrintLineVisible

isPrintLineVisible(value?): boolean

Gets or sets whether display a print line for the sheet.

Parameters

Name Type
value? boolean

Returns

boolean

If no value is set, returns a value indicating whether the print line is displayed


isSelected

isSelected(selectedState?): any

Gets or sets the selected state of the worksheet.

example

spread.sheets[0].isSelected();
spread.sheets[1].isSelected(true);

Parameters

Name Type
selectedState? boolean

Returns

any

If no selectedState is set, returns the worksheet selected state; otherwise, returns the worksheet.


isValid

isValid(row, column, value): boolean

Determines whether the cell value is valid.

example

//This example uses the isValid method.
alert(activeSheet.isValid(0, 0, 10));

Parameters

Name Type Description
row number The row index.
column number The column index.
value Object The cell value.

Returns

boolean

true if the value is valid; otherwise, false.


moveTo

moveTo(fromRow, fromColumn, toRow, toColumn, rowCount, columnCount, option): void

Moves data from one range to another.

example

//This example moves the data to the specified location.
activeSheet.getCell(0,0).value("A1");
activeSheet.getCell(1,1).value("Test")
activeSheet.moveTo(0,0,3,3,2,2,GC.Spread.Sheets.CopyToOptions.value);

Parameters

Name Type Description
fromRow number The source row.
fromColumn number The source column.
toRow number The target row.
toColumn number The target column.
rowCount number The row count.
columnCount number The column count.
option CopyToOptions The copy option.

Returns

void


name

name(value?): any

Gets or sets the name of the worksheet.

example

spread.sheets[0].name("The first sheet");
spread.sheets[1].name( "The second sheet");

Parameters

Name Type
value? string

Returns

any

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


printInfo

printInfo(value?): any

Gets or sets the print information for the sheet.

Parameters

Name Type
value? PrintInfo

Returns

any

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


protect

protect(password?): void

Protects a worksheet. Do nothing if the worksheet has already been protected.

example

let password = "fe4c4be8"
sheet.protect(password);

Parameters

Name Type Description
password? string Sheet protection password.

Returns

void


recalcAll

recalcAll(refreshAll?): void

Recalculates all the formulas in the sheet.

deprecated since version 16.2.0, please use 'spread.calculate' instead

example

//This example uses the recalcAll method.
activeSheet.setValue(0,0,1);
activeSheet.setValue(0,1,2);
activeSheet.setValue(0,2,10);
activeSheet.getCell(1,1).formula("=SUM(A1:C1)");
activeSheet.recalcAll();

Parameters

Name Type Description
refreshAll? boolean Specifies whether to rebuild all fromula reference, custom name and custom functions.

Returns

void


removeCustomFunction

removeCustomFunction(name): void

Removes a custom function.

example

//This example uses the removeCustomFunction method.
// Add Custom function
// Type =myfunc(1)
// in a cell to see the result
function myfunc() {}
myfunc.prototype = new GC.Spread.CalcEngine.Functions.Function("myfunc", 0, 0, {name: "myfunc",description: "This is my first function"});
myfunc.prototype.evaluate = function (args) {
    return 100;
}
spread.addCustomFunction(new myfunc());
//spread.removeCustomFunction("myfunc");

Parameters

Name Type
name string

Returns

void


removeCustomName

removeCustomName(name): void

Removes the specified custom name.

example

//This example uses the removeCustomName method.
activeSheet.setValue(0, 0, 1);
activeSheet.setValue(0, 1, 2);
activeSheet.setValue(0, 2, 3);
activeSheet.addCustomName("customName1","=12", 0, 0);
activeSheet.addCustomName("customName2","Average(20,45)", 0, 0);
activeSheet.addCustomName("customName3", "=$A$1:$C$1", 0, 0);
activeSheet.setFormula(1, 0, "customName1");
activeSheet.setFormula(1, 1, "customName2");
activeSheet.setFormula(1, 2, "sum(customName3)");
//activeSheet.removeCustomName("customName3");

Parameters

Name Type
name string

Returns

void


removeNamedStyle

removeNamedStyle(name): void

Removes a style from the Worksheet named styles collection which has the specified name.

Parameters

Name Type Description
name string The name of the style to remove.

Returns

void


removeSpan

removeSpan(row, col, sheetArea?): void

Removes the span that contains a specified anchor cell in the specified sheet area.

example

activeSheet.addSpan(0,0,3,3,GC.Spread.Sheets.SheetArea.viewport);
//activeSheet.removeSpan(0, 0, GC.Spread.Sheets.SheetArea.viewport);

Parameters

Name Type Description
row number The row index of the anchor cell for the span (at which spanned cells start).
col number The column index of the anchor cell for the span (at which spanned cells start).
sheetArea? SheetArea -

Returns

void


removeSparkline

removeSparkline(row, col): void

Removes the sparkline for the specified cell.

example

//This example removes a sparkline.
var cellr = new GC.Spread.Sheets.Range(0, 0, 1, 5);
var ex = new GC.Spread.Sheets.Sparklines.SparklineSetting();
ex.options.SeriesColor  = "Aquamarine";
sheet.setValue(0, 0, 2);
sheet.setValue(0, 1, 5);
sheet.setValue(0, 2, 4);
sheet.setValue(0, 3, -1);
sheet.setValue(0, 4, 3);
sheet.setSparkline(0, 5, cellr, GC.Spread.Sheets.Sparklines.DataOrientation.horizontal, GC.Spread.Sheets.Sparklines.SparklineType.column, ex);
alert(sheet.getSparkline(0, 5).toString());
//sheet.removeSparkline(0, 5);

Parameters

Name Type Description
row number The row index.
col number The column index.

Returns

void


repaint

repaint(clipRect?): void

Repaints the specified rectangle.

example

//This example causes a repaint.
var cellrange =new GC.Spread.Sheets.Range(0, 0, 5, 1);
var hideRowFilter =new GC.Spread.Sheets.Filter.HideRowFilter(cellrange);
sheet.rowFilter(hideRowFilter);
sheet.resumePaint();
sheet.repaint();

Parameters

Name Type Description
clipRect? Rect The rectangle to repaint.

Returns

void


reset

reset(): void

Resets the sheet.

example

//This example uses the reset method.
activeSheet.reset();

Returns

void


resumeCalcService

resumeCalcService(recalcAll?): void

Resumes the calculation service.

example

//This example uses the resumeCalcService method.
activeSheet.suspendCalcService(false);
activeSheet.setValue(0,0,1);
activeSheet.setValue(0,1,2);
activeSheet.setValue(0,2,10);
activeSheet.getCell(1,1).formula("=SUM(A1:C1)");
activeSheet.resumeCalcService(true);

Parameters

Name Type Description
recalcAll? boolean Specifies whether to recalculate all formulas.

Returns

void


resumeDirty

resumeDirty(): void

Resumes recording the dirty data.

example

//This example uses the resumeDirty method.
 var customers = [
               { ID: 0, Name: 'A', Info1: 'Info0' },
               { ID: 1, Name: 'B', Info1: 'Info1' },
               { ID: 2, Name: 'C', Info1: 'Info2' },
            ];
activeSheet.setDataSource(customers);
activeSheet.suspendDirty();
alert(activeSheet.isDirtySuspended());
activeSheet.resumeDirty();
alert(activeSheet.isDirtySuspended());

Returns

void


resumeEvent

resumeEvent(): void

Resumes the event.

example

//This example suspends and resumes the event.
 activeSheet.bind(GC.Spread.Sheets.Events.CellChanged, function (sender, args) {
    if (args.propertyName === "value") {
        alert(activeSheet.getValue(args.row, args.col));
    }
});
activeSheet.suspendEvent();
activeSheet.setValue(0, 0, "111");
activeSheet.resumeEvent();
activeSheet.setValue(1, 1, "222");

Returns

void


resumePaint

resumePaint(): void

Resumes the paint.

Returns

void


rowFilter

rowFilter(value?): RowFilterBase

Gets or sets the row filter for the sheet.

example

//This example creates a row filter.
sheet.rowFilter(new GC.Spread.Sheets.Filter.HideRowFilter(new GC.Spread.Sheets.Range(1,1,10,3)));

Parameters

Name Type Description
value? RowFilterBase The row filter for the sheet.

Returns

RowFilterBase

invoking the method without parameter, will return the filter instance, otherwise, return the worksheet instance.


scroll

scroll(vPixels, hPixels): void

Scrolls the sheet by specified pixels. When vPixels is positive, worksheet will scroll down; when vPixels is negative, worksheet will scroll up; when vPixels is 0, worksheet won't scroll in vertical direction. When hPixels is positive, worksheet will scroll right; when hPixels is negative, worksheet will scroll left; when hPixels is 0, worksheet won't scroll in horizontal direction. When Workbook's option scrollByPixel is true, worksheet will scroll to new top row/left column index and new top row/left column offset; When Workbook's option scrollByPixel is false, worksheet will scroll to new top row/left column index, and new top row/left column offset will be always 0.

example

//This example scrolls down the sheet 10 pixels and scrolls right the sheet 5 pixels.
activeSheet.scroll(10, 5);

Parameters

Name Type Description
vPixels number The pixels to scroll in vertical direction.
hPixels number The pixels to scroll in horizontal direction.

Returns

void


search(searchCondition): SearchResult

Searches the specified content.

example

//This example uses the search method.
activeSheet.getCell(2,3).value("testSearch");
var searchCondition = new GC.Spread.Sheets.Search.SearchCondition();
searchCondition.searchString = "testSearch";
searchCondition.startSheetIndex = spread.getActiveSheetIndex();
searchCondition.endSheetIndex = spread.getActiveSheetIndex();
searchCondition.searchOrder = GC.Spread.Sheets.Search.SearchOrder.nOrder;
searchCondition.searchTarget = GC.Spread.Sheets.Search.SearchFoundFlags.cellText;
searchCondition.searchFlags = GC.Spread.Sheets.Search.SearchFlags.ignoreCase| GC.Spread.Sheets.Search.SearchFlags.useWildCards;
var searchresult= activeSheet.search(searchCondition);
var str ="[searchFoundFlag:"+ searchresult.searchFoundFlag+",\r\n foundSheetIndex:"+searchresult.foundSheetIndex+",foundRowIndex:" +
searchresult.foundRowIndex+", foundColumnIndex:"+searchresult.foundColumnIndex+", foundString:"+searchresult.foundSheetIndex+"]";
alert(str);

Parameters

Name Type Description
searchCondition SearchCondition The search condition.

Returns

SearchResult

The search result.


selectionPolicy

selectionPolicy(value?): any

Gets or sets whether users can select ranges of items on a sheet.

example

//This example uses the selectionPolicy method.
activeSheet.selectionUnit(GC.Spread.Sheets.SelectionUnit.row);
activeSheet.selectionPolicy(GC.Spread.Sheets.SelectionPolicy.range);

Parameters

Name Type
value? SelectionPolicy

Returns

any

If no value is set, returns the selection policy setting; otherwise, returns the sheet.


selectionUnit

selectionUnit(value?): any

Gets or sets whether users can select cells, rows, or columns on a sheet.

example

//This example uses the selectionUnit method.
activeSheet.selectionUnit(GC.Spread.Sheets.SelectionUnit.row);
activeSheet.selectionPolicy(GC.Spread.Sheets.SelectionPolicy.range);

Parameters

Name Type
value? SelectionUnit

Returns

any

If no value is set, returns the selection unit setting; otherwise, returns the sheet.


setActiveCell

setActiveCell(row, col): void

Sets the active cell for this sheet.

example

//This example sets the active cell.
sheet.setActiveCell(5,5);
alert(sheet.getActiveColumnIndex());
alert(sheet.getActiveRowIndex());
spread.bind(GC.Spread.Sheets.Events.EnterCell, function (event, data) {
    alert(data.col);
    alert(data.row);
  });
spread.bind(GC.Spread.Sheets.Events.LeaveCell, function (event, data) {
    alert(data.col);
    alert(data.row);
  });

Parameters

Name Type Description
row number The row index of the cell.
col number The column index of the cell.

Returns

void


setAltText

setAltText(row, col, value, sheetArea?): void

Sets the alternative text for the specified cell in the specified sheet area.

example

var SpreadIcon = {
    FolderOpen: '\ue685',
    InfoFilled: '\ue718',
    Library: '\ue69d',
    NotebookFilled: '\uD800\uDC0F',
    Browse: '\ue626'
};
activeSheet.setValue(1, 1, SpreadIcon.FolderOpen);
activeSheet.setAltText(1, 1, "Folder Open Icon");

// Besides plain text, the alternative text could also contain placeholder {value} or {formatted}, which represents cell value or cell formatted value.
// For example, if the cell value is 1000, and the alt text is "Sales amount is {value}", the final accessible content should be "Sales amount is 1000".
activeSheet.setValue(1, 1, 1000);
activeSheet.setAltText(1, 1, "Sales amount is {value}");

Parameters

Name Type Description
row number The row index.
col number The column index.
value string The alternative text to set for the specified cell.
sheetArea? SheetArea The sheet area. If this parameter is not provided, it defaults to viewport.

Returns

void


setArray

setArray(row, column, array, setFormula?): void

Sets the values in the specified two-dimensional array of objects into the specified range of cells on this sheet.

example

//This example uses the setArray method.
//set value
var array = [[1,2,3],[4,5],[6,7,8,9]];
activeSheet.setArray(1, 2, array);
//set formula
var array = [["=1+1","=2+2","=3+3"],["=4+4","=5+5"],["=6+6","=7+7","=8+8","=9+9"]];
activeSheet.setArray(1, 2, array, true);
//get value
var newArray = activeSheet.getArray(1, 2, 3, 4);
//getformula
var newArray = activeSheet.getArray(1, 2, 3, 4, true);
//alert(newArray[0]);

Parameters

Name Type Description
row number The row index.
column number The column index.
array any[] The array from which to set values.
setFormula? boolean -

Returns

void


setArrayFormula

setArrayFormula(row, col, rowCount, colCount, value, sheetArea?): void

Sets a formula in a specified cell in the specified sheet area.

example

//This example uses the setArrayFormula method.
activeSheet.getCell(1,1).value(3);
activeSheet.getCell(2,1).value(1);
activeSheet.getCell(3,1).value(3);
activeSheet.getCell(4,1).value(7);
activeSheet.getCell(1,2).value(7);
activeSheet.getCell(2,2).value(7);
activeSheet.getCell(3,2).value(7);
activeSheet.getCell(4,2).value(7);
spread.allowUserEditFormula(true);
activeSheet.setArrayFormula(0, 3, 4, 1, "B2:B5*C2:C5", GC.Spread.Sheets.SheetArea.viewport);

Parameters

Name Type Description
row number The start row index.
col number The start column index.
rowCount number The number of rows in range.
colCount number The number of columns in range.
value string The array formula to place in the specified range.
sheetArea? SheetArea -

Returns

void


setBindingPath

setBindingPath(row, col, path): Worksheet

Sets the binding path for cell-level binding in a specified cell in the specified sheet area.

example

//This example binds a cell.
var test = {name: "John", gender: "male"};
sheet.setBindingPath(0, 0, "name");
sheet.setBindingPath(0, 1, "gender");
sheet.setDataSource(new GC.Spread.Sheets.Bindings.CellBindingSource(test));

Parameters

Name Type Description
row number The row index.
col number The column index.
path string The binding path for the cell binding source.

Returns

Worksheet


setCellType

setCellType(row, col, value, sheetArea?): void

Sets the cell type.

example

//This example uses the setCellType method.
var cellType = new GC.Spread.Sheets.CellTypes.Button();
cellType.buttonBackColor("#FFFF00");
cellType.text("this is a button");
activeSheet.setCellType(1,1,cellType);
spread.bind(GC.Spread.Sheets.Events.ButtonClicked, function (e, args) {
    var sheet = args.sheet, row = args.row, col = args.col;
    var cellType = activeSheet.getCellType(row, col);
    if (cellType instanceof GC.Spread.Sheets.CellTypes.Button) {
        alert("Button Clicked");
    }
});

Parameters

Name Type Description
row number The row index.
col number The column index.
value Base The cell type.
sheetArea? SheetArea -

Returns

void


setColumnCount

setColumnCount(colCount, sheetArea?, guardContent?): void

Sets the column count in the specified sheet area.

example

//This example sets the number of columns.
sheet.setRowCount(4,1);
sheet.setColumnCount(4,2);
sheet.addSpan(0,0,3,3,GC.Spread.Sheets.SheetArea.colHeader);
sheet.addSpan(0,0,3,3,GC.Spread.Sheets.SheetArea.rowHeader);
sheet.addSpan(0,0,3,3,GC.Spread.Sheets.SheetArea.viewport);

Parameters

Name Type Description
colCount number The column count.
sheetArea? SheetArea -
guardContent? UsedRangeType -

Returns

void


setColumnPageBreak

setColumnPageBreak(column, value): void

Sets whether a forced page break is inserted before the specified column on this sheet when printing.

example

//This example sets the page break.
activeSheet.setColumnPageBreak(5, true);

Parameters

Name Type Description
column number The column index.
value boolean Set to true to force a page break before the specified column on this sheet when printing.

Returns

void


setColumnResizable

setColumnResizable(col, value, sheetArea?): void

Sets whether users can resize the specified column in the specified sheet area.

example

//This example sets the setColumnResizable method.
sheet.setRowResizable(3,false,GC.Spread.Sheets.SheetArea.viewport);
sheet.setColumnResizable(3,false,GC.Spread.Sheets.SheetArea.viewport);
sheet.getRange(1,-1, 1, -1, GC.Spread.Sheets.SheetArea.viewport).resizable(false);
sheet.getRange(-1, 1, -1, 1, GC.Spread.Sheets.SheetArea.viewport).resizable(false);

Parameters

Name Type Description
col number The column index.
value boolean Set to true to allow users to resize the column.
sheetArea? SheetArea -

Returns

void


setColumnVisible

setColumnVisible(col, value, sheetArea?): void

Sets whether a column in the specified sheet area is displayed.

example

//This example sets the specified column to be hidden.
activeSheet.setColumnVisible(2,false,GC.Spread.Sheets.SheetArea.viewport);

Parameters

Name Type Description
col number The column index.
value boolean Whether to display the column.
sheetArea? SheetArea -

Returns

void


setColumnWidth

setColumnWidth(col, value, sheetArea?): void

Sets the width in pixels or dynamic size for the specified column in the specified sheet area.

example

//This example sets the column width.
sheet.setValue(0, 0, "value");
sheet.addRows(0, 2);
sheet.addColumns(0, 2);
sheet.setRowHeight(0, 50.0, GC.Spread.Sheets.SheetArea.viewport);
sheet.setColumnWidth(0, 150.0, GC.Spread.Sheets.SheetArea.viewport);
sheet.setColumnWidth(1, "2*", GC.Spread.Sheets.SheetArea.viewport);
sheet.getRange(0, -1, 1, -1, GC.Spread.Sheets.SheetArea.viewport).backColor("Gray");
sheet.getRange(-1, 0, -1, 1, GC.Spread.Sheets.SheetArea.viewport).backColor ("Brown");

Parameters

Name Type Description
col number The column index.
value string | number The width in pixels, or use the string with "*" to represent the dynamic size.
sheetArea? SheetArea -

Returns

void


setCsv

setCsv(row, column, text, rowDelimiter, columnDelimiter): void

Sets delimited text (CSV) in the sheet.

Parameters

Name Type Description
row number The start row.
column number The start column.
text string The delimited text.
rowDelimiter string The row delimiter.
columnDelimiter string The column delimiter.

Returns

void


setDataSource

setDataSource(data, reset?): void

Sets the data source that populates the sheet.

example

var test = [
       { "Series0": 2, "Series1": 1 },
       { "Series0": 4, "Series1": 2 },
       { "Series0": 3, "Series1": 4 }
            ];
activeSheet.autoGenerateColumns = true;
activeSheet.setDataSource(test, true);

Parameters

Name Type Description
data any The data source.
reset? boolean true if the sheet is reset; otherwise, false.

Returns

void


setDataValidator

setDataValidator(row, col, value, sheetArea?): void

Sets the cell data validator.

example

spread.options.highlightInvalidData = true;
var dv = GC.Spread.Sheets.DataValidation.createListValidator("1,2,3");
dv.showInputMessage(true);
dv.inputMessage("Value must be 1,2 or 3");
dv.inputTitle("tip");
activeSheet.setDataValidator(1,1,dv,GC.Spread.Sheets.SheetArea.viewport);
alert(activeSheet.getDataValidator(1,1).getValidList(activeSheet,1,1));

Parameters

Name Type Description
row number The row index.
col number The column index.
value DefaultDataValidator The data validator.
sheetArea? SheetArea The sheet area. If this parameter is not provided, it defaults to viewport.

Returns

void

setDataValidator(row, col, rowCount, colCount, value, sheetArea?): void

Sets the cell data validator.

example

spread.options.highlightInvalidData = true;
var dv = GC.Spread.Sheets.DataValidation.createListValidator("1,2,3");
dv.showInputMessage(true);
dv.inputMessage("Value must be 1,2 or 3");
dv.inputTitle("tip");
activeSheet.setDataValidator(1,1,1,1,dv,GC.Spread.Sheets.SheetArea.viewport);
alert(activeSheet.getDataValidator(1,1).getValidList(activeSheet,1,1));

Parameters

Name Type Description
row number The row index.
col number The column index.
rowCount number The row count.
colCount number The column count.
value DefaultDataValidator The data validator.
sheetArea? SheetArea The sheet area. If this parameter is not provided, it defaults to viewport.

Returns

void


setDefaultStyle

setDefaultStyle(style, sheetArea?): void

Sets the default style information for the sheet.

Parameters

Name Type Description
style Style The style to set.
sheetArea? SheetArea -

Returns

void


setDefaultValue

setDefaultValue(row, col, value): void

Sets the default value to the cell, it can be the value data or the formula string. It works when the cell has no data.

example

sheet.setDefaultValue(0, 0, "name");
sheet.setDefaultValue(0, 1, "=A1");

Parameters

Name Type Description
row number The row index.
col number The column index.
value any The default value of the cell.

Returns

void


setFormatter

setFormatter(row, col, value, sheetArea): void

Sets the cell formatter.

example

//This example sets the format object for the active sheet.
activeSheet.setValue(2, 3, new Date(2011, 2, 9));
activeSheet.setFormatter(2,3,"M",GC.Spread.Sheets.SheetArea.viewport);

Parameters

Name Type Description
row number The row index.
col number The column index.
value string | FormatterBase The formatter string or object.
sheetArea SheetArea The sheet area. If this parameter is not provided, it defaults to viewport.

Returns

void


setFormula

setFormula(row, col, value, sheetArea?): void

Sets a formula in a specified cell in the specified sheet area.

example

//This example sets the formula for the specified cell.
activeSheet.setValue(0, 2, 3);
activeSheet.setFormula(1,1,"C1+D1",GC.Spread.Sheets.SheetArea.viewport);

Parameters

Name Type Description
row number The row index.
col number The column index.
value string The formula to place in the specified cell.
sheetArea? SheetArea -

Returns

void


setHyperlink(row, col, value, sheetArea?): void

Sets the hyperlink data for the specified cell in the specified sheet area.

example

//This example uses the setHyperlink method.
sheet.setHyperlink(0, 2, {
   url: 'https://www.spreadjs.com',
   tooltip: 'baidu',
   linkColor: 'blue',
   visitedLinkColor: 'red',
   target: GC.Spread.Sheets.Hyperlink.HyperlinkTargetType.blank,
   command: 'navigationLeft'
}, GC.Spread.Sheets.SheetArea.viewport);
sheet.setHyperlink(1, 1, {
   url: 'https://www.spreadjs.com',
   tooltip: 'baidu',
   setUnderlineToStyle: true;
   setForeColorToStyle: true;
   target: GC.Spread.Sheets.Hyperlink.HyperlinkTargetType.top,
   command: function() { console.log('Only show this message when click the hyperlink.')}
}, GC.Spread.Sheets.SheetArea.viewport);

Parameters

Name Type Description
row number The row index.
col number The column index.
value IHyperlink The hyperlink data to set for the specified cell.
sheetArea? SheetArea The sheet area. If this parameter is not provided, it defaults to viewport.

Returns

void


setRowCount

setRowCount(rowCount, sheetArea?, guardContent?): void

Sets the row count in the specified sheet area.

example

//This example sets the row count.
sheet.setRowCount(4,1);
sheet.setColumnCount(4,2);
sheet.addSpan(0,0,3,3,GC.Spread.Sheets.SheetArea.colHeader);
sheet.addSpan(0,0,3,3,GC.Spread.Sheets.SheetArea.rowHeader);
sheet.addSpan(0,0,3,3,GC.Spread.Sheets.SheetArea.viewport);

Parameters

Name Type Description
rowCount number The row count.
sheetArea? SheetArea -
guardContent? UsedRangeType -

Returns

void


setRowHeight

setRowHeight(row, value, sheetArea?): void

Sets the height in pixels or dynamic size for the specified row in the specified sheet area.

example

//This example sets the row height.
sheet.setValue(0, 0, "value");
sheet.addRows(0, 2);
sheet.addColumns(0, 2);
sheet.setRowHeight(0, 50.0, GC.Spread.Sheets.SheetArea.viewport);
sheet.setRowHeight(1, "3*", GC.Spread.Sheets.SheetArea.viewport);
sheet.setColumnWidth(0, 150.0, GC.Spread.Sheets.SheetArea.viewport);
sheet.getRange(0, -1, 1, -1, GC.Spread.Sheets.SheetArea.viewport).backColor("Gray");
sheet.getRange(-1, 0, -1, 1, GC.Spread.Sheets.SheetArea.viewport).backColor ("Brown");

Parameters

Name Type Description
row number The row index.
value string | number The height in pixels, or use the string with "*" to represent the dynamic size.
sheetArea? SheetArea -

Returns

void


setRowPageBreak

setRowPageBreak(row, value): void

Sets whether a forced page break is inserted before the specified row on this sheet when printing.

example

activeSheet.setRowPageBreak(3, true);

Parameters

Name Type Description
row number The row index.
value boolean Set to true to force a page break before the specified row on this sheet when printing.

Returns

void


setRowResizable

setRowResizable(row, value, sheetArea?): void

Sets whether users can resize the specified row in the specified sheet area.

example

//This example prevents certain rows and columns from being resized.
sheet.setRowResizable(3,false,GC.Spread.Sheets.SheetArea.viewport);
sheet.setColumnResizable(3,false,GC.Spread.Sheets.SheetArea.viewport);
sheet.getRange(1,-1, 1, -1, GC.Spread.Sheets.SheetArea.viewport).resizable(false);
sheet.getRange(-1, 1, -1, 1, GC.Spread.Sheets.SheetArea.viewport).resizable(false);

Parameters

Name Type Description
row number The row index.
value boolean Set to true to let the users resize the specified row.
sheetArea? SheetArea -

Returns

void


setRowVisible

setRowVisible(row, value, sheetArea?): void

Sets whether the control displays the specified row in the specified sheet area.

example

//This example sets the specified row to be hidden.
activeSheet.setRowVisible(1,false,GC.Spread.Sheets.SheetArea.viewport);

Parameters

Name Type Description
row number The row index.
value boolean Set to true to display the specified row.
sheetArea? SheetArea -

Returns

void


setSelection

setSelection(row, column, rowCount, columnCount): void

Sets the selection to a cell or a range and sets the active cell to the first cell.

example

//This example selects a range of cells.
sheet.setValue(0,0, 1,3);
sheet.setValue(1,0, 50,3);
sheet.setValue(2,0, 100,3);
sheet.setValue(3,0, 2,3);
sheet.setValue(4,0, 60,3);
sheet.setValue(5,0, 90,3);
sheet.setValue(6,0, 3,3);
sheet.setValue(7,0, 40,3);
sheet.setValue(8,0, 70,3);
sheet.setValue(9,0, 5,3);
sheet.setValue(10,0, 35,3);
sheet.setSelection(0,0,11,1);
sheet.conditionalFormats.add3ScaleRule(1, 10, "red", 0, 50, "blue",2, 100, "yellow", sheet.getSelections());

Parameters

Name Type Description
row number The row index of the first cell to add.
column number The column index of the first cell to add.
rowCount number The number of rows to add.
columnCount number The number of columns to add.

Returns

void


setSparkline

setSparkline(row, col, dataRange, dataOrientation, sparklineType, sparklineSetting, dateAxisRange?, dateAxisOrientation?): Sparkline

Sets the sparkline for a cell.

example

//This example creates a sparkline for the specified range.
var data = new GC.Spread.Sheets.Range(1, 0, 8, 1);
var setting = new GC.Spread.Sheets.Sparklines.SparklineSetting();
setting.options.showMarkers = true;
setting.options.lineWeight = 3;
setting.options.displayXAxis = true;
setting.options.showFirst = true;
setting.options.showLast = true;
setting.options.showLow = true;
setting.options.showHigh = true;
setting.options.showNegative = true;
setting.options.seriesColor = "Text 2 1";
setting.options.firstMarkerColor = "Text 2 3";
setting.options.negativeColor = "Accent 2 1";
setting.options.markersColor = "Accent 3 1";
setting.options.lowMarkerColor = "Accent 4 1";
setting.options.highMarkerColor = "Accent 6 1";
setting.options.lastMarkerColor = "Accent 6 6";
setting.options.axisColor ="Text 1 1";
sheet.addSpan(13, 0, 4, 3, null);
sheet.setSparkline(13, 0, data, GC.Spread.Sheets.Sparklines.DataOrientation.Vertical, GC.Spread.Sheets.Sparklines.SparklineType.line, setting);
sheet.setValue(1, 0, 1);
sheet.setValue(2, 0, -2);
sheet.setValue(3, 0, -1);
sheet.setValue(4, 0, 6);
sheet.setValue(5, 0, 4);
sheet.setValue(6, 0, -4);
sheet.setValue(7, 0, 3);
sheet.setValue(8, 0, 8);

Parameters

Name Type Description
row number The row index.
col number The column index.
dataRange string | Range The data range.
dataOrientation DataOrientation The data orientation.
sparklineType SparklineType The sparkline type.
sparklineSetting SparklineSetting The sparkline setting.
dateAxisRange? string | Range The date axis range.
dateAxisOrientation? DataOrientation The date axis range orientation.

Returns

Sparkline

The sparkline.


setStyle

setStyle(row, col, value, sheetArea?): void

Sets the style information for a specified cell in the specified sheet area.

example

//This example uses the setStyle method.
var style = new GC.Spread.Sheets.Style();
style.backColor = "lightgreen";
style.backgroundImage = "./css/images/quarter1.png";
activeSheet.setStyle(1,1,style,GC.Spread.Sheets.SheetArea.viewport);

Parameters

Name Type Description
row number The row index.
col number -
value Style The cell style.
sheetArea? SheetArea -

Returns

void


setStyleName

setStyleName(row, column, value, sheetArea?): void

Sets the specified style name for a specified cell in the specified sheet area.

example

var namedStyle = new GC.Spread.Sheets.Style();
namedStyle.name = "style1";
namedStyle.backColor = "green";
activeSheet.addNamedStyle(namedStyle);
activeSheet.setStyleName(1, 1, "style1"); // cell(1,1)'s backColor is  green.
activeSheet.setStyleName(2, 1, "style1");

Parameters

Name Type Description
row number The row index.
column number The column index.
value string The name of the style to set.
sheetArea? SheetArea -

Returns

void


setTag

setTag(row, col, tag, sheetArea?): void

Sets the tag value for the specified cell in the specified sheet area.

example

activeSheet.setTag(1,1,"test");

Parameters

Name Type Description
row number The row index.
col number The column index.
tag any The tag value to set for the specified cell.
sheetArea? SheetArea The sheet area without corner. If this parameter is not provided, it defaults to viewport.

Returns

void


setText

setText(row, col, value, sheetArea?): void

Sets the formatted text in the cell in the specified sheet area.

example

activeSheet.setText(1, 0, "10");

Parameters

Name Type Description
row number The row index.
col number The column index.
value string The text for the specified cell.
sheetArea? SheetArea -

Returns

void


setValue

setValue(row, col, value, sheetArea?, ignoreRecalc?): void

Sets the value for the specified cell in the specified sheet area.

example

//This example uses the setValue method.
sheet.setValue(0,2,"ColumnHeader", GC.Spread.Sheets.SheetArea.colHeader);
sheet.setValue(2,0,{richText:[{style:{font:'bold 24px Arial'},text:'SpreadJS'}]}, GC.Spread.Sheets.SheetArea.rowHeader);
sheet.setValue(1, 1, {richText:[{style:{vertAlign: GC.Spread.Sheets.VertAlign.subscript},text:'SpreadJS'}]}, GC.Spread.Sheets.SheetArea.viewport);

Parameters

Name Type Description
row number The row index.
col number The column index.
value any The value to set for the specified cell. if the value is rich text format, should include a richText field which type is a rich text style array.
sheetArea? SheetArea -
ignoreRecalc? boolean Whether to ignore recalculation.

Returns

void


showCell

showCell(row, col, verticalPosition, horizontalPosition): void

Moves the view of a cell to the specified position in the viewport.

example

//This example uses the showCell method.
//Set cell (3,3) as the active cell
activeSheet.setActiveCell(3, 3);
//Display the active cell at top left
activeSheet.showCell(3, 3, GC.Spread.Sheets.VerticalPosition.top, GC.Spread.Sheets.HorizontalPosition.left);

Parameters

Name Type Description
row number The row index.
col number The column index.
verticalPosition VerticalPosition The vertical position in which to display the cell.
horizontalPosition HorizontalPosition The horizontal position in which to display the cell.

Returns

void


showColumn

showColumn(col, horizontalPosition): void

Moves the view of a column to the specified position in the viewport.

example

activeSheet.showColumn(9, GC.Spread.Sheets.HorizontalPosition.left);

Parameters

Name Type Description
col number The column index.
horizontalPosition HorizontalPosition The horizontal position in which to display the column.

Returns

void


showColumnOutline

showColumnOutline(value?): any

Gets or sets whether the column outline (range group) is visible.

example

//This example uses the showColumnOutline method.
activeSheet.showColumnOutline(false);

Parameters

Name Type Description
value? boolean Whether to display the column outline.

Returns

any

If no value is set, returns a value that indicates whether the column outline is displayed on this sheet; otherwise, returns the worksheet.


showRow

showRow(row, verticalPosition): void

Moves the view of a row to the specified position in the viewport.

example

activeSheet.showRow(9, GC.Spread.Sheets.VerticalPosition.top);

Parameters

Name Type Description
row number The row index.
verticalPosition VerticalPosition The vertical position in which to display the row.

Returns

void


showRowOutline

showRowOutline(value?): any

Gets or sets whether the row outline (range group) is visible.

example

//This example uses the showRowOutline method.
activeSheet.showRowOutline(false);

Parameters

Name Type Description
value? boolean Whether to display the row outline.

Returns

any

If no value is set, returns a value that indicates whether the row outline is displayed on this sheet; otherwise, returns the worksheet.


sortRange

sortRange(row, column, rowCount, columnCount, byRows, sortInfo, sortOption?): boolean

Sorts a range of cells in this sheet in the data model.

example

//This example sorts a range.
sheet.setValue(0,0,"112");
sheet.setValue(1,0,"10");
sheet.setValue(2,0,"223");
sheet.setValue(3,0,"20");
sheet.setValue(4,0,"334");
sheet.setValue(5,0,"30");
function pinyinCompare (obj1, obj2) {
    return obj1.toString().localeCompare(obj2.toString(), 'zh');
}
sheet.sortRange(0, 0, 6, 1, true, [
                {index:0, ascending:true, compareFunction: pinyinCompare}
                ], {groupSort: GC.Spread.Sheets.GroupSort.full, ignoreHidden: true});

Parameters

Name Type Description
row number The index of the starting row of the block of cells to sort.
column number The index of the starting column of the block of cells to sort.
rowCount number The number of rows in the block of cells.
columnCount number The number of columns in the block of cells.
byRows boolean Set to true to sort by rows, and false to sort by columns.
sortInfo (ICellColorSortInfo | ICustomSortInfo | IFontColorSortInfo | IValueSortInfo)[] The SortInfo object with sort criteria and information about how to perform the sort. For example, [{index:0,ascending:true}]
sortOption? ISortOptions The sortOption indicate the detail performance of the sort.

Returns

boolean

true if the data is sorted successfully; otherwise, false.


startEdit

startEdit(selectAll?, defaultText?): void

Starts to edit the cell.

example

//This example uses the startEdit method.
activeSheet.setActiveCell(5,5);
activeSheet.startEdit(true, "Test");

Parameters

Name Type Description
selectAll? boolean Set to true to select all the text in the cell.
defaultText? string The default text to display while editing the cell.

Returns

void


suspendCalcService

suspendCalcService(ignoreDirty?): void

Suspends the calculation service.

example

//This example uses the suspendCalcService method.
activeSheet.suspendCalcService(false);
activeSheet.setValue(0,0,1);
activeSheet.setValue(0,1,2);
activeSheet.setValue(0,2,10);
activeSheet.getCell(1,1).formula("=SUM(A1:C1)");
activeSheet.resumeCalcService(true);

Parameters

Name Type Description
ignoreDirty? boolean Specifies whether to invalidate the dependency cells.

Returns

void


suspendDirty

suspendDirty(): void

Suspends recording the dirty data.

example

//This example uses the suspendDirty method.
 var customers = [
               { ID: 0, Name: 'A', Info1: 'Info0' },
               { ID: 1, Name: 'B', Info1: 'Info1' },
               { ID: 2, Name: 'C', Info1: 'Info2' },
            ];
activeSheet.setDataSource(customers);
activeSheet.suspendDirty();
alert(activeSheet.isDirtySuspended());
activeSheet.resumeDirty();
alert(activeSheet.isDirtySuspended());

Returns

void


suspendEvent

suspendEvent(): void

Suspends the event.

example

//This example suspends and resumes the event.
activeSheet.bind(GC.Spread.Sheets.Events.CellChanged, function (sender, args) {
    if (args.propertyName === "value") {
        alert(activeSheet.getValue(args.row, args.col));
    }
});
activeSheet.suspendEvent();
activeSheet.setValue(0, 0, "111");
activeSheet.resumeEvent();
activeSheet.setValue(1, 1, "222");

Returns

void


suspendPaint

suspendPaint(): void

Suspends the paint.

Returns

void


tag

tag(value?): any

Gets or sets the tag value for the current sheet.

example

//This example sets the sheet tag.
activeSheet.tag("test");
alert(activeSheet.tag());

Parameters

Name Type Description
value? any The tag value to set for the current sheet.

Returns

any

If no value is set, returns the tag value of the current sheet; otherwise, returns the worksheet.


toJSON

toJSON(serializationOption?): Object

Saves the object state to a JSON string.

example

//This example uses the toJSON method.
activeSheet.getCell(0,0).value(123);
var jsonStr = null;
//export
jsonStr = JSON.stringify(activeSheet.toJSON());
//import
activeSheet.fromJSON(JSON.parse(jsonStr));
alert(jsonStr);

Parameters

Name Type Description
serializationOption? Object Serialization option that contains the includeBindingSource argument. See the Remarks for more information.

Returns

Object

The sheet data.


unbind

unbind(type, fn?): void

Removes the binding of an event to the sheet.

example

//This example unbinds the event after setting the first value.
activeSheet.bind(GC.Spread.Sheets.Events.CellChanged, function (sender, args) {
    if (args.propertyName === "value") {
        alert(activeSheet.getValue(args.row, args.col));
    }
});
activeSheet.setValue(0, 0, "111");
activeSheet.unbind(GC.Spread.Sheets.Events.CellChanged);
activeSheet.setValue(1, 0, "222");
activeSheet.setValue(2, 0, "333");
activeSheet.setValue(3, 0, "444");

Parameters

Name Type Description
type string The event type.
fn? Function Specifies the function for which to remove the binding.

Returns

void


unbindAll

unbindAll(): void

Removes the binding of all events to the sheet.

example

//This example cancels monitoring of all events.
activeSheet.bind(GC.Spread.Sheets.Events.CellChanged, function (sender, args) {
    if (args.propertyName === "value") {
        alert(activeSheet.getValue(args.row, args.col));
    }
});
activeSheet.setValue(0, 0, "111");
activeSheet.unbindAll(); //cancel monitoring of all events.
activeSheet.setValue(1, 0, "222");
activeSheet.setValue(2, 0, "333");
activeSheet.setValue(3, 0, "444");

Returns

void


ungroupSparkline

ungroupSparkline(group): void

Ungroups the sparklines in the specified group.

example

//This example uses the ungroupSparkline method.
activeSheet.setValue(0, 0, "Data Range is A2-A9");
activeSheet.setValue(1, 0, 1);
activeSheet.setValue(2, 0, -2);
activeSheet.setValue(3, 0, -1);
activeSheet.setValue(4, 0, 6);
activeSheet.setValue(5, 0, 4);
activeSheet.setValue(6, 0, -4);
activeSheet.setValue(7, 0, 3);
activeSheet.setValue(8, 0, 8);
var data = new GC.Spread.Sheets.Range(1, 0, 8, 1);
var setting = new GC.Spread.Sheets.Sparklines.SparklineSetting();
var s1=  activeSheet.setSparkline(11, 0, data, GC.Spread.Sheets.Sparklines.DataOrientation.Vertical, GC.Spread.Sheets.Sparklines.SparklineType.line, setting);
var s2 =activeSheet.setSparkline(11, 3, data, GC.Spread.Sheets.Sparklines.DataOrientation.Vertical, GC.Spread.Sheets.Sparklines.SparklineType.column, setting);
var s3=  activeSheet.setSparkline(11, 6, data, GC.Spread.Sheets.Sparklines.DataOrientation.Vertical, GC.Spread.Sheets.Sparklines.SparklineType.winloss, setting);
var group = activeSheet.groupSparkline([s1,s2,s3]);
//activeSheet.ungroupSparkline(group);

Parameters

Name Type Description
group SparklineGroup The sparkline group.

Returns

void


unprotect

unprotect(password?): boolean

Unprotects a worksheet.

example

let password = "fe4c4be8"
sheet.protect(password);
sheet.unprotect(password);

Parameters

Name Type Description
password? string Sheet protection password.

Returns

boolean

Return true if password is correct or isProtect is false, otherwise return false.


visible

visible(value?): any

Sets whether the worksheet is displayed.

example

activeSheet.visible(false);

Parameters

Name Type
value? boolean | SheetTabVisible

Returns

any

If you call this function without a parameter, it returns a boolean indicating whether the sheet is visible; otherwise, it returns the current worksheet object.


zoom

zoom(factor?): any

Gets or sets the zoom factor for the sheet.

example

//This example zooms the sheet.
spread.options.allowUserZoom = false;
sheet.zoom(3);

Parameters

Name Type
factor? number

Returns

any

If no value is set, returns the zoom factor; otherwise, returns the worksheet.