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

GC.Spread.Sheets.Worksheet

Class: Worksheet

Spread.Sheets.Worksheet

Hierarchy

Table of contents

Constructors

Properties

Methods

Constructors

constructor

• new Worksheet(name)

Represents a worksheet.

Parameters

Name Type Description
name string The name of the Worksheet.

Properties

autoGenerateColumns

• autoGenerateColumns: boolean

Indicates whether to generate columns automatically while binding data context.

example

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

cellStates

• cellStates: CellStateManager

The cellState manager.

charts

• charts: ChartCollection

Chart manager for the sheet.

example

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

columnOutlines

• columnOutlines: Outline

Indicates the column range group.

comments

• comments: CommentManager

Comment manager for the sheet.

conditionalFormats

• conditionalFormats: ConditionalFormats

Conditional format manager for the sheet.

example

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

defaults

• defaults: ISheetDefaultOption

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

example

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

floatingObjects

• floatingObjects: FloatingObjectCollection

FloatingObject manager for the sheet.

example

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

options

• options: IWorksheetOptions

Indicates the options of the sheet.

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

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

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

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

property sheetTabStyle - The sheet tab style.

property frozenlineColor - A color string used to represent the frozen line color, such as "red", "#FFFF00", "rgb(255,0,0)", "Accent 5", and so on.

property clipBoardOptions - The clipboard option.

property gridline - The grid line's options.

property gridline.color - The grid line color

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

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

property rowHeaderVisible - Indicates whether the row header is visible.

property colHeaderVisible - Indicates whether the column header is visible.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

property sheetAreaOffset - The sheetAreaOffset's options.

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

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

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

example

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

outlineColumn

• outlineColumn: OutlineColumn

Gets the outline column for the sheet.

returns

pictures

• pictures: FloatingObjectCollection

Picture manager for the sheet.

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

example

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

pivotTables

• pivotTables: PivotTableManager

The pivot table manager.

example

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

rowOutlines

• rowOutlines: Outline

Indicates the row range group.

shapes

• shapes: ShapeCollection

Shape manager for the sheet.

example

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

slicers

• slicers: SlicerCollection

The slicer manager.

example

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

tables

• tables: TableManager

The table manager.

example

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

Methods

addColumns

▸ addColumns(col, count, sheetArea?): void

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

example

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

Parameters

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

Returns

void

addCustomFunction

▸ addCustomFunction(fn): void

Adds a custom function.

Parameters

Name Type Description
fn Function The function to add.

Returns

void

addCustomName

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

Adds a custom name.

example

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

Parameters

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

Returns

void

addNamedStyle

▸ addNamedStyle(style): void

Adds a style to the Worksheet named styles collection.

example

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

Parameters

Name Type Description
style Style The style to be added.

Returns

void

addRows

▸ addRows(row, count, sheetArea?): void

Adds rows in this worksheet.

example

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

Parameters

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

Returns

void

addSelection

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

Adds a cell or cells to the selection.

example

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

Parameters

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

Returns

void

addSpan

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

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

example

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

Parameters

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

Returns

void

autoFitColumn

▸ autoFitColumn(column): void

Automatically fits the viewport column.

example

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

Parameters

Name Type Description
column number The column index.

Returns

void

autoFitRow

▸ autoFitRow(row): void

Automatically fits the viewport row.

example

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

Parameters

Name Type Description
row number The row index.

Returns

void

autoMerge

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

Applies auto merge for a range.

example

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

Parameters

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

Returns

IRangeInfo[]

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

bind

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

Binds an event to the sheet.

example

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

Parameters

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

Returns

void

bindColumn

▸ bindColumn(index, column): void

Binds the column using the specified data field.

example

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

Parameters

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

Returns

void

bindColumns

▸ bindColumns(columns): void

Binds the columns using the specified data fields.

example

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

Parameters

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

Returns

void

clear

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

Clears the specified area.

example

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

Parameters

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

Returns

void

clearCustomFunctions

▸ clearCustomFunctions(): void

Clears all custom functions.

example

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

Returns

void

clearCustomNames

▸ clearCustomNames(): void

Clears custom names.

example

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

Returns

void

clearPendingChanges

▸ clearPendingChanges(clearChangeInfo?): void

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

example

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

Parameters

Name Type
clearChangeInfo? IClearChangeInfo

Returns

void

clearSelection

▸ clearSelection(): void

Clears the selection.

example

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

Returns

void

copyTo

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

Copies data from one range to another.

example

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

Parameters

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

Returns

void

currentTheme

▸ currentTheme(value?): any

Gets or sets the current theme for the sheet.

example

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

Parameters

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

Returns

any

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

deleteColumns

▸ deleteColumns(col, count, sheetArea?): void

Deletes the columns in this sheet at the specified index.

example

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

Parameters

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

Returns

void

deleteRows

▸ deleteRows(row, count, sheetArea?): void

Deletes the rows in this worksheet at the specified index.

example

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

Parameters

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

Returns

void

editorStatus

▸ editorStatus(): EditorStatus

Returns the editor's status.

Returns

EditorStatus

The editor status.

endEdit

▸ endEdit(ignoreValueChange?): boolean

Stops editing the active cell.

example

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

Parameters

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

Returns

boolean

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

fillAuto

▸ fillAuto(startRange, wholeRange, options): void

Fills the specified range automatically.

example

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

Parameters

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

Returns

void

fromJSON

▸ fromJSON(sheetSettings): void

Loads the object state from the specified JSON string.

example

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

Parameters

Name Type Description
sheetSettings Object The sheet data from deserialization.

Returns

void

frozenColumnCount

▸ frozenColumnCount(colCount?, leftCol?): any

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

example

sheet.frozenColumnCount(30, 20);

Parameters

Name Type
colCount? number
leftCol? number

Returns

any

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

frozenRowCount

▸ frozenRowCount(rowCount?, topRow?): any

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

example

sheet.frozenRowCount(60, 50);

Parameters

Name Type
rowCount? number
topRow? number

Returns

any

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

frozenTrailingColumnCount

▸ frozenTrailingColumnCount(colCount?, stickToEdge?): any

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

example

sheet.frozenTrailingColumnCount(1, false);

Parameters

Name Type
colCount? number
stickToEdge? boolean

Returns

any

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

frozenTrailingRowCount

▸ frozenTrailingRowCount(rowCount?, stickToEdge?): any

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

example

sheet.frozenTrailingRowCount(1, false);

Parameters

Name Type
rowCount? number
stickToEdge? boolean

Returns

any

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

getActiveColumnIndex

▸ getActiveColumnIndex(): number

Gets the active column index for this sheet.

example

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

Returns

number

The column index of the active cell.

getActiveRowIndex

▸ getActiveRowIndex(): number

Gets the active row index for this sheet.

example

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

Returns

number

The row index of the active cell.

getActualStyle

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

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

example

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

Parameters

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

Returns

Style

Returns the cell style of the specified cell.

getAltText

▸ getAltText(row, col, sheetArea?): any

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

example

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

Parameters

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

Returns

any

Returns the alternative text of the cell.

getArray

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

Gets an object array from a specified range of cells.

example

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

Parameters

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

Returns

any[]

The object array from the specified range of cells.

getBindingPath

▸ getBindingPath(row, col): string

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

example

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

Parameters

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

Returns

string

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

getCell

▸ getCell(row, col, sheetArea?): CellRange

Gets the specified cell in the specified sheet area.

example

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

Parameters

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

Returns

CellRange

The cell.

getCellRect

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

Gets the rectangle of the cell.

example

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

Parameters

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

Returns

Rect

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

getCellType

▸ getCellType(row, col, sheetArea?): Base

Gets the cell type.

example

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

Parameters

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

Returns

Base

Returns the cell type for the specified cell.

getColumnCount

▸ getColumnCount(sheetArea?): number

Gets the column count in the specified sheet area.

example

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

Parameters

Name Type
sheetArea? SheetArea

Returns

number

The number of columns.

getColumnPageBreak

▸ getColumnPageBreak(column): boolean

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

Parameters

Name Type Description
column number The column index.

Returns

boolean

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

getColumnResizable

▸ getColumnResizable(col, sheetArea?): boolean

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

example

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

Parameters

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

Returns

boolean

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

getColumnVisible

▸ getColumnVisible(col, sheetArea?): boolean

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

example

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

Parameters

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

Returns

boolean

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

getColumnWidth

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

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

example

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

Parameters

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

Returns

any

The column width in pixels or the dynamic size.

getCsv

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

Gets delimited text from a range.

Parameters

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

Returns

string

The text from the range with the specified delimiters.

getCustomFunction

▸ getCustomFunction(name): void

Gets a custom function.

Parameters

Name Type
name string

Returns

void

The custom function.

getCustomName

▸ getCustomName(name): NameInfo

Gets the specified custom name information.

example

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

Parameters

Name Type
name string

Returns

NameInfo

The information for the specified custom name.

getCustomNames

▸ getCustomNames(): NameInfo[]

Gets all custom name information.

Returns

NameInfo[]

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

getDataColumnName

▸ getDataColumnName(column): string

Gets the column name at the specified position.

example

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

Parameters

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

Returns

string

The column name for data binding.

getDataItem

▸ getDataItem(row): any

Gets the data item.

example

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

Parameters

Name Type Description
row number The row index.

Returns

any

The row data.

getDataSource

▸ getDataSource(): any

Gets the data source that populates the sheet.

function

example

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

Returns

any

Returns the data source.

getDataValidator

▸ getDataValidator(row, col, sheetArea): DefaultDataValidator

Gets the cell data validator.

example

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

Parameters

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

Returns

DefaultDataValidator

Returns the cell data validator for the specified cell.

getDefaultStyle

▸ getDefaultStyle(sheetArea?): Style

Gets the default style information for the sheet.

example

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

Parameters

Name Type
sheetArea? SheetArea

Returns

Style

Returns the sheet's default style.

getDefaultValue

▸ getDefaultValue(row, col): any

Gets the default value from the specified cell.

example

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

Parameters

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

Returns

any

Returns the default value of cell.

getDeletedRows

▸ getDeletedRows(): any[]

Gets the deleted row collection.

Returns

any[]

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

getDependents

▸ getDependents(row, col): ICellsInfo[]

Gets the dependent CellRange information object array of the cell.

example

sheet.getDependents(1, 1);

Parameters

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

Returns

ICellsInfo[]

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

getDirtyCells

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

Gets the dirty cell collection.

Parameters

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

Returns

IDirtyCellInfo[]

The dirty cells.

getDirtyRows

▸ getDirtyRows(): any[]

Gets the dirty row collection.

Returns

any[]

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

getFormatter

▸ getFormatter(row, col, sheetArea): any

Gets the cell formatter.

example

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

Parameters

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

Returns

any

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

getFormula

▸ getFormula(row, col, sheetArea?): string

Gets the formula in the specified cell in this sheet.

example

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

Parameters

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

Returns

string

Returns the formula string.

getFormulaInformation

▸ getFormulaInformation(row, col): IFormulaInfo

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

example

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

Parameters

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

Returns

IFormulaInfo

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

▸ getHyperlink(row, col, sheetArea?): IHyperlink

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

example

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

Parameters

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

Returns

IHyperlink

getInsertRows

▸ getInsertRows(): any[]

Gets the inserted row collection.

Returns

any[]

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

getNamedStyle

▸ getNamedStyle(name): Style

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

Parameters

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

Returns

Style

Returns the specified named style.

getNamedStyles

▸ getNamedStyles(): Style[]

Gets named styles from the Worksheet.

Returns

Style[]

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

getParent

▸ getParent(): Workbook

Gets the parent Spread object of the current sheet.

Returns

Workbook

Returns the parent Spread object of the current sheet.

getPrecedents

▸ getPrecedents(row, col): ICellsInfo[]

Gets the precedent CellRange information object array of the cell.

example

sheet.getPrecedents(1, 1);

Parameters

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

Returns

ICellsInfo[]

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

getRange

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

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

Parameters

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

Returns

CellRange

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

▸ getRange(address, sheetArea?): CellRange

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

example

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

Parameters

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

Returns

CellRange

The cellRange.

getRowCount

▸ getRowCount(sheetArea?): number

Gets the row count in the specified sheet area.

example

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

Parameters

Name Type
sheetArea? SheetArea

Returns

number

The number of rows.

getRowHeight

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

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

example

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

Parameters

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

Returns

any

The row height in pixels or the dynamic size.

getRowPageBreak

▸ getRowPageBreak(row): boolean

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

Parameters

Name Type Description
row number The row index.

Returns

boolean

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

getRowResizable

▸ getRowResizable(row, sheetArea?): boolean

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

example

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

Parameters

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

Returns

boolean

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

getRowVisible

▸ getRowVisible(row, sheetArea?): boolean

Gets whether the control displays the specified row.

example

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

Parameters

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

Returns

boolean

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

getSelections

▸ getSelections(): Range[]

Gets the selections in the current sheet.

Returns

Range[]

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

getSortState

▸ getSortState(): ISortState

Get last sort state in this sheet.

example

//This example get sort state.
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");
sheet.sortRange(0, 0, 6, 1, true, [{index:0, ascending:true}]);
let sortState = sheet.getSortState();

Returns

ISortState

the last sort state info .

getSpans

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

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

Parameters

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

Returns

Range[]

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

getSparkline

▸ getSparkline(row, column): Sparkline

Gets the sparkline for the specified cell.

example

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

Parameters

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

Returns

Sparkline

The sparkline for the cell.

getStyle

▸ getStyle(row, column, sheetArea?): Style

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

example

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

Parameters

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

Returns

Style

Returns the cell style of the specified cell.

getStyleName

▸ getStyleName(row, column, sheetArea?): string

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

example

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

Parameters

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

Returns

string

Returns the name string for the style.

getTag

▸ getTag(row, col, sheetArea?): any

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

example

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

Parameters

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

Returns

any

Returns the tag value of the cell.

getText

▸ getText(row, col, sheetArea?): string

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

example

activeSheet.getText(1, 0);

Parameters

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

Returns

string

Returns the formatted text of the cell.

getUsedRange

▸ getUsedRange(type?): Range

Get the range used in the sheet

example

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

Parameters

Name Type
type? UsedRangeType

Returns

Range

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

getValue

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

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

example

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

Parameters

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

Returns

any

Returns the value of the cell.

getViewportBottomRow

▸ getViewportBottomRow(rowViewportIndex): number

Gets the index of the bottom row in the viewport.

example

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

Parameters

Name Type Description
rowViewportIndex number The index of the viewport.

Returns

number

The index of the bottom row in the viewport.

getViewportHeight

▸ getViewportHeight(rowViewportIndex): number

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

example

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

Parameters

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

Returns

number

The height of the viewport.

getViewportLeftColumn

▸ getViewportLeftColumn(columnViewportIndex): number

Gets the index of the left column in the viewport.

example

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

Parameters

Name Type Description
columnViewportIndex number The index of the viewport.

Returns

number

The index of the left column in the viewport.

getViewportRightColumn

▸ getViewportRightColumn(columnViewportIndex): number

Gets the index of the right column in the viewport.

example

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

Parameters

Name Type Description
columnViewportIndex number The index of the viewport.

Returns

number

The index of the right column in the viewport.

getViewportTopRow

▸ getViewportTopRow(rowViewportIndex): number

Gets the index of the top row in the viewport.

example

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

Parameters

Name Type Description
rowViewportIndex number The index of the viewport.

Returns

number

The index of the top row in the viewport.

getViewportWidth

▸ getViewportWidth(columnViewportIndex): number

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

example

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

Parameters

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

Returns

number

The width of the viewport

groupSparkline

▸ groupSparkline(sparklines): SparklineGroup

Groups the sparklines.

example

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

Parameters

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

Returns

SparklineGroup

The sparkline group.

hasPassword

▸ hasPassword(): boolean

Get whether worksheet has set a password for protection.

example

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

Returns

boolean

Returns true if the password is set, otherwise false

hasPendingChanges

▸ hasPendingChanges(): boolean

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

Returns

boolean

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

hitTest

▸ hitTest(x, y): IHitTestInformation

Performs a hit test.

Parameters

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

Returns

IHitTestInformation

The hit test information.

invalidateLayout

▸ invalidateLayout(): void

Invalidates the sheet layout.

example

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

Returns

void

isDirtySuspended

▸ isDirtySuspended(): boolean

Gets whether recording the dirty data is suspended.

example

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

Returns

boolean

Whether the dirty data is suspended.

isEditing

▸ isEditing(): boolean

Gets whether the sheet is in edit mode.

example

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

Returns

boolean

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

isPaintSuspended

▸ isPaintSuspended(): boolean

Get if sheet paint is suspended.

Returns

boolean

isPrintLineVisible

▸ isPrintLineVisible(value?): boolean

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

Parameters

Name Type
value? boolean

Returns

boolean

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

isSelected

▸ isSelected(selectedState?): any

Gets or sets the selected state of the worksheet.

example

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

Parameters

Name Type
selectedState? boolean

Returns

any

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

isValid

▸ isValid(row, column, value): boolean

Determines whether the cell value is valid.

example

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

Parameters

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

Returns

boolean

true if the value is valid; otherwise, false.

moveTo

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

Moves data from one range to another.

example

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

Parameters

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

Returns

void

name

▸ name(value?): any

Gets or sets the name of the worksheet.

example

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

Parameters

Name Type
value? string

Returns

any

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

printInfo

▸ printInfo(value?): any

Gets or sets the print information for the sheet.

Parameters

Name Type
value? PrintInfo

Returns

any

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

protect

▸ protect(password?): void

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

example

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

Parameters

Name Type Description
password? string Sheet protection password.

Returns

void

recalcAll

▸ recalcAll(refreshAll?): void

Recalculates all the formulas in the sheet.

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

example

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

Parameters

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

Returns

void

removeCustomFunction

▸ removeCustomFunction(name): void

Removes a custom function.

example

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

Parameters

Name Type
name string

Returns

void

removeCustomName

▸ removeCustomName(name): void

Removes the specified custom name.

example

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

Parameters

Name Type
name string

Returns

void

removeNamedStyle

▸ removeNamedStyle(name): void

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

Parameters

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

Returns

void

removeSpan

▸ removeSpan(row, col, sheetArea?): void

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

example

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

Parameters

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

Returns

void

removeSparkline

▸ removeSparkline(row, col): void

Removes the sparkline for the specified cell.

example

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

Parameters

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

Returns

void

repaint

▸ repaint(clipRect?): void

Repaints the specified rectangle.

example

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

Parameters

Name Type Description
clipRect? Rect The rectangle to repaint.

Returns

void

reset

▸ reset(): void

Resets the sheet.

example

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

Returns

void

resumeCalcService

▸ resumeCalcService(recalcAll?): void

Resumes the calculation service.

example

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

Parameters

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

Returns

void

resumeDirty

▸ resumeDirty(): void

Resumes recording the dirty data.

example

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

Returns

void

resumeEvent

▸ resumeEvent(): void

Resumes the event.

example

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

Returns

void

resumePaint

▸ resumePaint(): void

Resumes the paint.

Returns

void

rowFilter

▸ rowFilter(value?): RowFilterBase

Gets or sets the row filter for the sheet.

example

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

Parameters

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

Returns

RowFilterBase

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

scroll

▸ scroll(vPixels, hPixels): void

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

example

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

Parameters

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

Returns

void

▸ search(searchCondition): SearchResult

Searches the specified content.

example

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

Parameters

Name Type Description
searchCondition SearchCondition The search condition.

Returns

SearchResult

The search result.

selectionPolicy

▸ selectionPolicy(value?): any

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

example

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

Parameters

Name Type
value? SelectionPolicy

Returns

any

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

selectionUnit

▸ selectionUnit(value?): any

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

example

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

Parameters

Name Type
value? SelectionUnit

Returns

any

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

setActiveCell

▸ setActiveCell(row, col): void

Sets the active cell for this sheet.

example

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

Parameters

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

Returns

void

setAltText

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

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

example

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

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

Parameters

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

Returns

void

setArray

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

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

example

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

Parameters

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

Returns

void

setArrayFormula

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

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

example

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

Parameters

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

Returns

void

setBindingPath

▸ setBindingPath(row, col, path): Worksheet

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

example

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

Parameters

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

Returns

Worksheet

setCellType

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

Sets the cell type.

example

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

Parameters

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

Returns

void

setColumnCount

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

Sets the column count in the specified sheet area.

example

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

Parameters

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

Returns

void

setColumnPageBreak

▸ setColumnPageBreak(column, value): void

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

example

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

Parameters

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

Returns

void

setColumnResizable

▸ setColumnResizable(col, value, sheetArea?): void

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

example

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

Parameters

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

Returns

void

setColumnVisible

▸ setColumnVisible(col, value, sheetArea?): void

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

example

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

Parameters

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

Returns

void

setColumnWidth

▸ setColumnWidth(col, value, sheetArea?): void

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

example

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

Parameters

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

Returns

void

setCsv

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

Sets delimited text (CSV) in the sheet.

Parameters

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

Returns

void

setDataSource

▸ setDataSource(data, reset?): void

Sets the data source that populates the sheet.

example

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

Parameters

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

Returns

void

setDataValidator

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

Sets the cell data validator.

example

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

Parameters

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

Returns

void

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

Sets the cell data validator.

example

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

Parameters

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

Returns

void

setDefaultStyle

▸ setDefaultStyle(style, sheetArea?): void

Sets the default style information for the sheet.

Parameters

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

Returns

void

setDefaultValue

▸ setDefaultValue(row, col, value): void

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

example

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

Parameters

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

Returns

void

setFormatter

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

Sets the cell formatter.

example

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

Parameters

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

Returns

void

setFormula

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

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

example

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

Parameters

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

Returns

void

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

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

example

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

Parameters

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

Returns

void

setRowCount

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

Sets the row count in the specified sheet area.

example

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

Parameters

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

Returns

void

setRowHeight

▸ setRowHeight(row, value, sheetArea?): void

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

example

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

Parameters

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

Returns

void

setRowPageBreak

▸ setRowPageBreak(row, value): void

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

example

activeSheet.setRowPageBreak(3, true);

Parameters

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

Returns

void

setRowResizable

▸ setRowResizable(row, value, sheetArea?): void

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

example

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

Parameters

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

Returns

void

setRowVisible

▸ setRowVisible(row, value, sheetArea?): void

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

example

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

Parameters

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

Returns

void

setSelection

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

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

example

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

Parameters

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

Returns

void

setSparkline

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

Sets the sparkline for a cell.

example

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

Parameters

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

Returns

Sparkline

The sparkline.

setStyle

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

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

example

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

Parameters

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

Returns

void

setStyleName

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

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

example

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

Parameters

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

Returns

void

setTag

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

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

example

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

Parameters

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

Returns

void

setText

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

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

example

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

Parameters

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

Returns

void

setValue

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

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

example

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

Parameters

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

Returns

void

showCell

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

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

example

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

Parameters

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

Returns

void

showColumn

▸ showColumn(col, horizontalPosition): void

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

example

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

Parameters

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

Returns

void

showColumnOutline

▸ showColumnOutline(value?): any

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

example

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

Parameters

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

Returns

any

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

showRow

▸ showRow(row, verticalPosition): void

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

example

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

Parameters

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

Returns

void

showRowOutline

▸ showRowOutline(value?): any

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

example

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

Parameters

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

Returns

any

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

sortRange

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

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

example

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

Parameters

Name Type Description
row number The index of the starting row of the block of cells to sort.
column number The index of the starting column of the block of cells to sort.
rowCount number The number of rows in the block of cells.
columnCount number The number of columns in the block of cells.
byRows boolean Set to true to sort by rows, and false to sort by columns.
sortInfo (IValueSortInfo | ICellColorSortInfo | IFontColorSortInfo | ICustomSortInfo)[] 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.