GC.Spread.Sheets.Worksheet

Class: Worksheet

Spread.Sheets.Worksheet

Constructors

constructor

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 to`viewport`.

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

▸ 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 to`viewport`.

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 to`viewport`.

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

▸ 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

▸ 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.

GC.Spread.Sheets.Worksheet

Class: Worksheet

Table of contents

Constructors

Properties

Methods

Constructors

constructor

Parameters

Properties

autoGenerateColumns

cellStates

charts

columnOutlines

comments

conditionalFormats

defaults

floatingObjects

options

outlineColumn

pictures

pivotTables

rowOutlines

shapes

slicers

tables

Methods

addColumns

Parameters

Returns

addCustomFunction

Parameters

Returns

addCustomName

Parameters

Returns

addNamedStyle

Parameters

Returns

addRows

Parameters

Returns

addSelection

Parameters

Returns

addSpan

Parameters

Returns

autoFitColumn

Parameters

Returns

autoFitRow

Parameters

Returns

autoMerge

Parameters

Returns

bind

Parameters

Returns

bindColumn

Parameters

Returns

bindColumns

Parameters

Returns

clear

Parameters

Returns

clearCustomFunctions

Returns

clearCustomNames

Returns

clearPendingChanges

Parameters

Returns

clearSelection

Returns

copyTo

Parameters