[]
Worksheet
• new Worksheet(name
)
Represents a worksheet.
Name | Type | Description |
---|---|---|
name |
string |
The name of the Worksheet. |
• 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: CellStateManager
The cellState manager.
• 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: Outline
Indicates the column range group.
• comments: CommentManager
Comment manager for the sheet.
• 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: 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: 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: IWorksheetOptions
Indicates the options of the sheet.
property
allowCellOverflow - Indicates whether data can overflow into adjacent empty cells.
property
showFormulas - Indicates whether display the formulas string not the formula result.
property
showZeros - Indicates whether display the 0 in cells containing zero value. Default is true.
property
sheetTabColor - A color string used to represent the sheet tab color, such as "red", "#FFFF00", "rgb(255,0,0)", "Accent 5", and so on.
property
frozenlineColor - A color string used to represent the frozen line color, such as "red", "#FFFF00", "rgb(255,0,0)", "Accent 5", and so on.
property
clipBoardOptions - The clipboard option.
property
gridline - The grid line's options.
property
gridline.color - The grid line color
property
gridline.showVerticalGridline - Whether to show the vertical grid line.
property
gridline.showHorizontalGridline - Whether to show the horizontal grid line.
property
rowHeaderVisible - Indicates whether the row header is visible.
property
colHeaderVisible - Indicates whether the column header is visible.
property
rowHeaderAutoText - Indicates whether the row header displays letters or numbers or is blank.
property
colHeaderAutoText - Indicates whether the column header displays letters or numbers or is blank.
property
rowHeaderAutoTextIndex - Specifies which row header column displays the automatic text when there are multiple row header columns.
property
colHeaderAutoTextIndex - Specifies which column header row displays the automatic text when there are multiple column header rows.
property
isProtected - Indicates whether cells on this sheet that are marked as protected cannot be edited.
property
protectionOptions - A value that indicates the elements that you want users to be able to change.
property
[protectionOptions.allowSelectLockedCells] - True or undefined if the user can select locked cells.
property
[protectionOptions.allowSelectUnlockedCells] - True or undefined if the user can select unlocked cells.
property
[protectionOptions.allowSort] - True if the user can sort ranges.
property
[protectionOptions.allowFilter] - True if the user can filter ranges.
property
[protectionOptions.allowEditObjects] - True if the user can edit floating objects.
property
[protectionOptions.allowResizeRows] - True if the user can resize rows.
property
[protectionOptions.allowResizeColumns] - True if the user can resize columns.
property
[protectionOptions.allowDragInsertRows] - True if the user can drag to insert rows.
property
[protectionOptions.allowDragInsertColumns] - True if the user can drag to insert columns.
property
[protectionOptions.allowInsertRows] - True if the user can insert rows.
property
[protectionOptions.allowInsertColumns] - True if the user can insert columns.
property
[protectionOptions.allowDeleteRows] - True if the user can delete rows.
property
[protectionOptions.allowDeleteColumns] - True if the user can delete columns.
property
[protectionOptions.allowOutlineColumns] - True if the user can expand or collapse the column groups.
property
[protectionOptions.allowOutlineRows] - True if the user can expand or collapse the row groups.
property
selectionBackColor - The selection's background color for the sheet.
property
selectionBorderColor - The selection's border color for the sheet.
property
sheetAreaOffset - The sheetAreaOffset's options.
property
[sheetAreaOffset.left] - The offset left of sheet from host.
property
[sheetAreaOffset.top] - The offset top of sheet from host.
property
keepUnknownFormulas - Indicates whether the unknown formulas could be included in sheet json data.
property
[addRowButtonOption] - The add row button's options.
property
[addColumnButtonOption] - The add column button's options.
property
rightToLeft - Indicates whether the sheet is rendered from right to left.
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
Gets the outline column for the sheet.
returns
• 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: 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: Outline
Indicates the row range group.
• 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: 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: 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");
▸ 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");
Name | Type | Description |
---|---|---|
col |
number |
Column index at which to add the new columns. |
count |
number |
The number of columns to add. |
sheetArea? |
SheetArea |
- |
void
▸ addCustomFunction(fn
): void
Adds a custom function.
Name | Type | Description |
---|---|---|
fn |
Function |
The function to add. |
void
▸ 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)");
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 |
- |
void
▸ 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");
});
Name | Type | Description |
---|---|---|
style |
Style |
The style to be added. |
void
▸ 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");
Name | Type | Description |
---|---|---|
row |
number |
The index of the starting row. |
count |
number |
The number of rows to add. |
sheetArea? |
SheetArea |
- |
void
▸ 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());
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. |
void
▸ 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);
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 |
- |
void
▸ addSpans(spans
, sheetArea?
): void
Adds a lot of spans to this sheet in the specified sheet area.
example
//This example creates cell spans.
sheet.addSpans([{row: 0, col: 0, rowCount: 2, colCount: 2}, {row: 3, col: 0, rowCount: 3, colCount: 3}, {row: 7, col: 0, rowCount: 4, colCount: 4}], GC.Spread.Sheets.SheetArea.viewport);
Name | Type | Description |
---|---|---|
spans |
IRange [] |
The span ranges, each range contains row, col, rowCount, colCount. |
sheetArea? |
SheetArea |
- |
void
▸ 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);
Name | Type | Description |
---|---|---|
column |
number |
The column index. |
void
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
void
▸ 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);
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 . |
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(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);
});
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. |
void
▸ 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");
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. |
void
▸ 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);
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. |
void
▸ 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);
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. |
void
▸ clearCustomFunctions(): void
Clears all custom functions.
example
//This example clears the custom functions from the active sheet.
activeSheet.clearCustomFunctions();
void
▸ 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();
void
▸ 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});
Name | Type |
---|---|
clearChangeInfo? |
IClearChangeInfo |
void
▸ clearSelection(): void
Clears the selection.
example
//This example clears the selection.
sheet.addSelection(4, 0, 2, 2);
sheet.clearSelection();
void
▸ 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);
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. |
void
▸ currentTheme(value?
): any
Gets or sets the current theme for the sheet.
example
//This example sets a theme.
sheet.currentTheme("Civic");
Name | Type |
---|---|
value? |
string | Theme |
any
If no value is set, returns the current theme; otherwise, returns the worksheet.
▸ 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);
Name | Type | Description |
---|---|---|
col |
number |
The index of the first column to delete. |
count |
number |
The number of columns to delete. |
sheetArea? |
SheetArea |
- |
void
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The index of the first row to delete. |
count |
number |
The number of rows to delete. |
sheetArea? |
SheetArea |
- |
void
▸ editorStatus(): EditorStatus
Returns the editor's status.
The editor status.
▸ 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);
}
});
Name | Type | Description |
---|---|---|
ignoreValueChange? |
boolean |
If set to true , does not apply the edited text to the cell. |
boolean
true
when able to stop cell editing successfully; otherwise, false
.
▸ 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});
Name | Type | Description |
---|---|---|
startRange |
Range |
The fill start range. |
wholeRange |
Range |
The entire range to fill. |
options |
IFillOptions |
The range fill information. |
void
▸ 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);
Name | Type | Description |
---|---|---|
sheetSettings |
Object |
The sheet data from deserialization. |
void
▸ frozenColumnCount(colCount?
, leftCol?
): any
Gets or sets the number of frozen columns of the sheet.
example
sheet.frozenColumnCount(30, 20);
Name | Type |
---|---|
colCount? |
number |
leftCol? |
number |
any
If no value is set, returns the number of frozen columns; otherwise, returns the worksheet.
▸ frozenRowCount(rowCount?
, topRow?
): any
Gets or sets the number of frozen rows of the sheet.
example
sheet.frozenRowCount(60, 50);
Name | Type |
---|---|
rowCount? |
number |
topRow? |
number |
any
If no value is set, returns the number of frozen rows; otherwise, returns the worksheet.
▸ frozenTrailingColumnCount(colCount?
, stickToEdge?
): any
Gets or sets the number of trailing frozen columns of the sheet.
example
sheet.frozenTrailingColumnCount(1, false);
Name | Type |
---|---|
colCount? |
number |
stickToEdge? |
boolean |
any
If no value is set, returns the number of trailing frozen columns; otherwise, returns the worksheet.
▸ frozenTrailingRowCount(rowCount?
, stickToEdge?
): any
Gets or sets the number of trailing frozen rows of the sheet.
example
sheet.frozenTrailingRowCount(1, false);
Name | Type |
---|---|
rowCount? |
number |
stickToEdge? |
boolean |
any
If no value is set, returns the number of trailing frozen rows; otherwise, returns the worksheet.
▸ 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);
});
number
The column index of the active cell.
▸ 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);
});
number
The row index of the active cell.
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
column |
number |
The column index. |
sheetArea? |
SheetArea |
- |
sheetStyleOnly? |
boolean |
- |
Returns the cell style of the specified cell.
▸ 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));
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 . |
any
Returns the alternative text of the cell.
▸ 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]);
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. |
any
[]
The object array from the specified range of cells.
▸ 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));
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
string
Returns the binding path of the cell for cell-level binding.
▸ 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");
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 . |
The cell.
▸ 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);
}
});
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
rowViewportIndex? |
number |
- |
colViewportIndex? |
number |
- |
Object that contains the size and location of the cell rectangle.
▸ 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");
}
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
sheetArea? |
SheetArea |
- |
Returns the cell type for the specified cell.
▸ 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);
Name | Type |
---|---|
sheetArea? |
SheetArea |
number
The number of columns.
▸ getColumnPageBreak(column
): boolean
Gets whether a forced page break is inserted before the specified column on this sheet when printing.
Name | Type | Description |
---|---|---|
column |
number |
The column index. |
boolean
true
if a forced page break is inserted before the specified column; otherwise, false
.
▸ 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));
Name | Type | Description |
---|---|---|
col |
number |
The column index. |
sheetArea? |
SheetArea |
- |
boolean
true
if the user can resize the specified column; otherwise, false
.
▸ 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);
Name | Type | Description |
---|---|---|
col |
number |
The column index. |
sheetArea? |
SheetArea |
- |
boolean
true
if the column is visible in the sheet area; otherwise, false
.
▸ 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);
Name | Type | Description |
---|---|---|
col |
number |
The column index. |
sheetArea? |
SheetArea |
- |
getDynamicSize? |
boolean |
- |
any
The column width in pixels or the dynamic size.
▸ getCsv(row
, column
, rowCount
, columnCount
, rowDelimiter
, columnDelimiter
): string
Gets delimited text from a range.
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. |
string
The text from the range with the specified delimiters.
▸ getCustomFunction(name
): void
Gets a custom function.
Name | Type |
---|---|
name |
string |
void
The custom function.
▸ 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);
}
});
Name | Type |
---|---|
name |
string |
The information for the specified custom name.
▸ getCustomNames(): NameInfo
[]
Gets all custom name information.
NameInfo
[]
The type GC.Spread.Sheets.NameInfo stored in an array.
▸ 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);
Name | Type | Description |
---|---|---|
column |
number |
The column index for which the name is requested. |
string
The column name for data binding.
▸ 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)));
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
any
The row data.
▸ 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);
any
Returns the data source.
▸ 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));
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 the cell data validator for the specified cell.
▸ 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);
Name | Type |
---|---|
sheetArea? |
SheetArea |
Returns the sheet's default style.
▸ 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
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
any
Returns the default value of cell.
▸ getDeletedRows(): any
[]
Gets the deleted row collection.
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(row
, col
): ICellsInfo
[]
Gets the dependent CellRange information object array of the cell.
example
sheet.getDependents(1, 1);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
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(row
, col
, rowCount
, colCount
): IDirtyCellInfo
[]
Gets the dirty cell collection.
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. |
The dirty cells.
▸ getDirtyRows(): any
[]
Gets the dirty row collection.
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(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);
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 . |
any
Returns the cell formatter string or object for the specified cell.
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
sheetArea? |
SheetArea |
- |
string
Returns the formula string.
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
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);
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 . |
▸ getInsertRows(): any
[]
Gets the inserted row collection.
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(name
): Style
Gets a style from the Worksheet named styles collection which has the specified name.
Name | Type | Description |
---|---|---|
name |
string |
The name of the style to return. |
Returns the specified named style.
▸ getNamedStyles(): Style
[]
Gets named styles from the Worksheet.
Style
[]
The GC.Spread.Sheets.Style array of named styles.
▸ getParent(): Workbook
Gets the parent Spread object of the current sheet.
Returns the parent Spread object of the current sheet.
▸ getPrecedents(row
, col
): ICellsInfo
[]
Gets the precedent CellRange information object array of the cell.
example
sheet.getPrecedents(1, 1);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
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(row
, col
, rowCount?
, colCount?
, sheetArea?
): CellRange
Gets a range of cells by row info and column info in the specified sheet area.
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 . |
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")
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 . |
The cellRange.
▸ 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);
Name | Type |
---|---|
sheetArea? |
SheetArea |
number
The number of rows.
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
sheetArea? |
SheetArea |
- |
getDynamicSize? |
boolean |
- |
any
The row height in pixels or the dynamic size.
▸ getRowPageBreak(row
): boolean
Gets whether a forced page break is inserted before the specified row on this sheet when printing.
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
boolean
true
if a forced page break is inserted before the specified row; otherwise, false
.
▸ 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));
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
sheetArea? |
SheetArea |
- |
boolean
true
if the users can resize the specified row; otherwise, false
.
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
sheetArea? |
SheetArea |
- |
boolean
true
if the row is visible in the sheet area; otherwise, false
.
▸ getSelections(): Range
[]
Gets the selections in the current sheet.
Range
[]
The type GC.Spread.Sheets.Range is stored in an Array.
▸ 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();
the last sort state info .
▸ getSpans(range?
, sheetArea?
): Range
[]
Gets the spans in the specified range in the specified sheet area.
Name | Type | Description |
---|---|---|
range? |
Range |
The cell range. |
sheetArea? |
SheetArea |
- |
Range
[]
An array that contains span information whose item type is GC.Spread.Sheets.Range.
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
column |
number |
- |
The sparkline for the cell.
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
column |
number |
The column index. |
sheetArea? |
SheetArea |
- |
Returns the cell style of the specified cell.
▸ 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));
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
column |
number |
The column index. |
sheetArea? |
SheetArea |
- |
string
Returns the name string for the style.
▸ 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));
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 . |
any
Returns the tag value of the cell.
▸ getText(row
, col
, sheetArea?
): string
Gets the formatted text in the cell in the specified sheet area.
example
activeSheet.getText(1, 0);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
sheetArea? |
SheetArea |
- |
string
Returns the formatted text of the cell.
▸ 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);
Name | Type |
---|---|
type? |
UsedRangeType |
If no value is set, returns null; otherwise, returns a used range.
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
sheetArea? |
SheetArea |
- |
valueType? |
ValueType |
- |
any
Returns the value of the cell.
▸ 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);
Name | Type | Description |
---|---|---|
rowViewportIndex |
number |
The index of the viewport. |
number
The index of the bottom row in the viewport.
▸ 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));
Name | Type | Description |
---|---|---|
rowViewportIndex |
number |
The index of the row viewport. |
number
The height of the viewport.
▸ 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);
Name | Type | Description |
---|---|---|
columnViewportIndex |
number |
The index of the viewport. |
number
The index of the left column in the viewport.
▸ 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);
Name | Type | Description |
---|---|---|
columnViewportIndex |
number |
The index of the viewport. |
number
The index of the right column in the viewport.
▸ 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);
Name | Type | Description |
---|---|---|
rowViewportIndex |
number |
The index of the viewport. |
number
The index of the top row in the viewport.
▸ 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));
Name | Type | Description |
---|---|---|
columnViewportIndex |
number |
The index of the column viewport. |
number
The width of the viewport
▸ 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]);
Name | Type | Description |
---|---|---|
sparklines |
Sparkline [] |
The sparklines to group. |
The sparkline group.
▸ 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();
}
boolean
Returns true if the password is set, otherwise false
▸ hasPendingChanges(): boolean
Gets whether there is a dirty, insert, or delete status for the specified range.
boolean
true
if any of the rows or cells in the range are dirty, or have been inserted or deleted; otherwise, false
.
▸ hitTest(x
, y
): IHitTestInformation
Performs a hit test.
Name | Type | Description |
---|---|---|
x |
number |
The x-coordinate. |
y |
number |
The y-coordinate. |
The hit test information.
▸ invalidateLayout(): void
Invalidates the sheet layout.
example
//This example updates the layout.
activeSheet.columnOutlines.group(0, 1);
activeSheet.invalidateLayout();
activeSheet.repaint();
void
▸ 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());
boolean
Whether the dirty data is suspended.
▸ isEditing(): boolean
Gets whether the sheet is in edit mode.
example
//This example uses the isEditing method.
alert(activeSheet.isEditing());
boolean
true
if the sheet is in edit mode; otherwise, false
.
▸ isPaintSuspended(): boolean
Get if sheet paint is suspended.
boolean
▸ isPrintLineVisible(value?
): boolean
Gets or sets whether display a print line for the sheet.
Name | Type |
---|---|
value? |
boolean |
boolean
If no value is set, returns a value indicating whether the print line is displayed
▸ isSelected(selectedState?
): any
Gets or sets the selected state of the worksheet.
example
spread.sheets[0].isSelected();
spread.sheets[1].isSelected(true);
Name | Type |
---|---|
selectedState? |
boolean |
any
If no selectedState is set, returns the worksheet selected state; otherwise, returns the worksheet.
▸ 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));
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
column |
number |
The column index. |
value |
Object |
The cell value. |
boolean
true
if the value is valid; otherwise, false
.
▸ 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);
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. |
void
▸ 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");
Name | Type |
---|---|
value? |
string |
any
If no value is set, returns the worksheet name; otherwise, returns the worksheet.
▸ printInfo(value?
): any
Gets or sets the print information for the sheet.
Name | Type |
---|---|
value? |
PrintInfo |
any
If no value is set, returns the print information for the sheet; otherwise, returns the sheet.
▸ protect(password?
): void
Protects a worksheet. Do nothing if the worksheet has already been protected.
example
let password = "fe4c4be8"
sheet.protect(password);
Name | Type | Description |
---|---|---|
password? |
string |
Sheet protection password. |
void
▸ 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();
Name | Type | Description |
---|---|---|
refreshAll? |
boolean |
Specifies whether to rebuild all fromula reference, custom name and custom functions. |
void
▸ 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");
Name | Type |
---|---|
name |
string |
void
▸ 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");
Name | Type |
---|---|
name |
string |
void
▸ removeNamedStyle(name
): void
Removes a style from the Worksheet named styles collection which has the specified name.
Name | Type | Description |
---|---|---|
name |
string |
The name of the style to remove. |
void
▸ 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);
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 |
- |
void
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
void
▸ 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();
Name | Type | Description |
---|---|---|
clipRect? |
Rect |
The rectangle to repaint. |
void
▸ reset(): void
Resets the sheet.
example
//This example uses the reset method.
activeSheet.reset();
void
▸ 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);
Name | Type | Description |
---|---|---|
recalcAll? |
boolean |
Specifies whether to recalculate all formulas. |
void
▸ 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());
void
▸ 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");
void
▸ resumePaint(): void
Resumes the paint.
void
▸ rowFilter(value?
): any
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)));
Name | Type |
---|---|
value? |
RowFilterBase |
any
invoking the method without parameter, will return the filter instance, otherwise, return the worksheet instance.
▸ 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);
Name | Type | Description |
---|---|---|
vPixels |
number |
The pixels to scroll in vertical direction. |
hPixels |
number |
The pixels to scroll in horizontal direction. |
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);
Name | Type | Description |
---|---|---|
searchCondition |
SearchCondition |
The search condition. |
The search result.
▸ 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);
Name | Type |
---|---|
value? |
SelectionPolicy |
any
If no value is set, returns the selection policy setting; otherwise, returns the sheet.
▸ 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);
Name | Type |
---|---|
value? |
SelectionUnit |
any
If no value is set, returns the selection unit setting; otherwise, returns the sheet.
▸ 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);
});
Name | Type | Description |
---|---|---|
row |
number |
The row index of the cell. |
col |
number |
The column index of the cell. |
void
▸ 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}");
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 . |
void
▸ 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]);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
column |
number |
The column index. |
array |
any [] |
The array from which to set values. |
setFormula? |
boolean |
- |
void
▸ 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);
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 |
- |
void
▸ 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));
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
path |
string |
The binding path for the cell binding source. |
▸ 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");
}
});
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
value |
Base |
The cell type. |
sheetArea? |
SheetArea |
- |
void
▸ 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);
Name | Type | Description |
---|---|---|
colCount |
number |
The column count. |
sheetArea? |
SheetArea |
- |
guardContent? |
UsedRangeType |
- |
void
▸ 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);
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. |
void
▸ 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);
Name | Type | Description |
---|---|---|
col |
number |
The column index. |
value |
boolean |
Set to true to allow users to resize the column. |
sheetArea? |
SheetArea |
- |
void
▸ 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);
Name | Type | Description |
---|---|---|
col |
number |
The column index. |
value |
boolean |
Whether to display the column. |
sheetArea? |
SheetArea |
- |
void
▸ 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");
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 |
- |
void
▸ setCsv(row
, column
, text
, rowDelimiter
, columnDelimiter
): void
Sets delimited text (CSV) in the sheet.
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. |
void
▸ 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);
Name | Type | Description |
---|---|---|
data |
any |
The data source. |
reset? |
boolean |
true if the sheet is reset; otherwise, false . |
void
▸ 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));
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
value |
DefaultDataValidator |
The data validator. |
sheetArea? |
SheetArea |
- |
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));
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 |
- |
void
▸ setDefaultStyle(style
, sheetArea?
): void
Sets the default style information for the sheet.
Name | Type | Description |
---|---|---|
style |
Style |
The style to set. |
sheetArea? |
SheetArea |
- |
void
▸ 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");
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
value |
any |
The default value of the cell. |
void
▸ 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);
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 . |
void
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
value |
null | string |
The formula to place in the specified cell. |
sheetArea? |
SheetArea |
- |
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);
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 . |
void
▸ 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);
Name | Type | Description |
---|---|---|
rowCount |
number |
The row count. |
sheetArea? |
SheetArea |
- |
guardContent? |
UsedRangeType |
- |
void
▸ 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");
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 |
- |
void
▸ 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);
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. |
void
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
value |
boolean |
Set to true to let the users resize the specified row. |
sheetArea? |
SheetArea |
- |
void
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
value |
boolean |
Set to true to display the specified row. |
sheetArea? |
SheetArea |
- |
void
▸ 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());
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. |
void
▸ 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);
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. |
The sparkline.
▸ 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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
- |
value |
Style |
The cell style. |
sheetArea? |
SheetArea |
- |
void
▸ 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");
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
column |
number |
The column index. |
value |
string |
The name of the style to set. |
sheetArea? |
SheetArea |
- |
void
▸ 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");
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 . |
void
▸ setText(row
, col
, value
, sheetArea?
): void
Sets the formatted text in the cell in the specified sheet area.
example
activeSheet.setText(1, 0, "10");
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
col |
number |
The column index. |
value |
string |
The text for the specified cell. |
sheetArea? |
SheetArea |
- |
void
▸ 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);
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. |
void
▸ 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);
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. |
void
▸ 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);
Name | Type | Description |
---|---|---|
col |
number |
The column index. |
horizontalPosition |
HorizontalPosition |
The horizontal position in which to display the column. |
void
▸ showColumnOutline(value?
): any
Gets or sets whether the column outline (range group) is visible.
example
//This example uses the showColumnOutline method.
activeSheet.showColumnOutline(false);
Name | Type | Description |
---|---|---|
value? |
boolean |
Whether to display the column outline. |
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(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);
Name | Type | Description |
---|---|---|
row |
number |
The row index. |
verticalPosition |
VerticalPosition |
The vertical position in which to display the row. |
void
▸ showRowOutline(value?
): any
Gets or sets whether the row outline (range group) is visible.
example
//This example uses the showRowOutline method.
activeSheet.showRowOutline(false);
Name | Type | Description |
---|---|---|
value? |
boolean |
Whether to display the row outline. |
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(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});
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. |
boolean
true
if the data is sorted successfully; otherwise, false
.
▸ startEdit(selectAll?
, defaultText?
): void
Starts to edit the cell.
example
//This example uses the startEdit method.
activeSheet.setActiveCell(5,5);
activeSheet.startEdit(true, "Test");
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. |
void
▸ 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);
Name | Type | Description |
---|---|---|
ignoreDirty? |
boolean |
Specifies whether to invalidate the dependency cells. |
void
▸ 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());
void
▸ 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");
void
▸ suspendPaint(): void
Suspends the paint.
void
▸ 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());
Name | Type | Description |
---|---|---|
value? |
any |
The tag value to set for the current sheet. |
any
If no value is set, returns the tag value of the current sheet; otherwise, returns the worksheet.
▸ 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);
Name | Type | Description |
---|---|---|
serializationOption? |
Object |
Serialization option that contains the includeBindingSource argument. See the Remarks for more information. |
Object
The sheet data.
▸ 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");
Name | Type | Description |
---|---|---|
type |
string |
The event type. |
fn? |
Function |
Specifies the function for which to remove the binding. |
void
▸ 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");
void
▸ 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);
Name | Type | Description |
---|---|---|
group |
SparklineGroup |
The sparkline group. |
void
▸ unprotect(password?
): boolean
Unprotects a worksheet.
example
let password = "fe4c4be8"
sheet.protect(password);
sheet.unprotect(password);
Name | Type | Description |
---|---|---|
password? |
string |
Sheet protection password. |
boolean
Return true if password is correct or isProtect is false, otherwise return false.
▸ visible(value?
): any
Sets whether the worksheet is displayed.
example
activeSheet.visible(false);
Name | Type |
---|---|
value? |
boolean | SheetTabVisible |
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(factor?
): any
Gets or sets the zoom factor for the sheet.
example
//This example zooms the sheet.
spread.options.allowUserZoom = false;
sheet.zoom(3);
Name | Type |
---|---|
factor? |
number |
any
If no value is set, returns the zoom factor; otherwise, returns the worksheet.