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

GC.Spread.Sheets.Workbook

Class: Workbook

Spread.Sheets.Workbook

Table of contents

Constructors

Properties

Methods

Constructors

constructor

• new Workbook(host?, options?)

Represents a spreadsheet with the specified hosted DOM element or DOM id and options setting.

example

var workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount:3, font:"12pt Arial"});
var workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount:3, newTabVisible:false});
var workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 3, tabEditable: false });
var workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount:3, tabStripVisible:false});
var workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount:3, allowUserResize:false});
var workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 3, allowUserZoom: false});

Parameters

Name Type Description
host? string | HTMLElement The host DOM element or id.
options? IWorkBookDefaultOptions -

Properties

contextMenu

• contextMenu: ContextMenu

ContextMenu for the spread.

example

//This example shows how to get contextMenu's menuData.
var menuData = spread.contextMenu.menuData;

customPivotTableThemes

• customPivotTableThemes: CustomPivotTableThemeManager

Represents the custom pivot table theme manager.

customSlicerThemes

• customSlicerThemes: CustomSlicerThemeManager

Represents the custom item slicer theme manager.

customTableThemes

• customTableThemes: CustomTableThemeManager

Represents the custom table theme manager.

customTimelineThemes

• customTimelineThemes: CustomTimelineThemeManager

Represents the custom timeline slicer theme manager.

docProps

• docProps: IDocProps

Document properties.

name

• name: string

Represents the name of the Spread control.

example

spread.name = "Spread1";

options

• options: IWorkbookOptions

Represents the options of the Spread control.

property allowUserDragMerge - Whether to allow the user to drag merge cells.

property [options.allowDragHeaderToMove] - Specifies how to allow drag header to move.

property allowUserDragDrop - Whether to allow the user to drag and drop range data.

property allowUserDragFill - Whether to allow the user to drag fill a range.

property allowUserZoom - Whether to zoom the display by scrolling the mouse wheel while pressing the Ctrl key.

property allowUserResize - Whether to allow the user to resize columns and rows.

property allowUndo - Whether to allow the user to undo edits.

property allowSheetReorder - Whether the user can reorder the sheets in the Spread component.

property allowContextMenu - Whether to allow the user to open the built-in context menu.

property allowUserDeselect - Whether to allow the user to can use deselect in selection.

property defaultDragFillType - The default fill type.

property showDragFillSmartTag - Whether to display the drag fill dialog.

property showHorizontalScrollbar - Whether to display the horizontal scroll bar.

property showVerticalScrollbar - Whether to display the vertical scroll bar.

property scrollbarShowMax - Whether the displayed scroll bars are based on the entire number of columns and rows in the sheet.

property scrollbarMaxAlign - Whether the scroll bar aligns with the last row and column of the active sheet.

property tabStripVisible - Whether to display the sheet tab strip.

property tabStripRatio - The width of the tab strip expressed as a percentage of the overall horizontal scroll bar width.

property tabStripWidth - The width of the tab strip when it is at the left or right position. The default and minimum is 80.

property tabEditable - Whether to allow the user to edit the sheet tab strip.

property tabStripPosition - The position of tab strip. The default is bottom.

property newTabVisible - Whether the spreadsheet displays the special tab to let users insert new sheets.

property allSheetsListVisible - Whether the spreadsheet shows a special tab to allow the user to open the dialog to display all sheets.

property tabNavigationVisible - Whether to display the sheet tab navigation.

property cutCopyIndicatorVisible - Whether to display an indicator when copying or cutting the selected item.

property cutCopyIndicatorBorderColor - The border color for the indicator displayed when the user cuts or copies the selection.

property backColor - A color string used to represent the background color of the Spread component, such as "red", "#FFFF00", "rgb(255,0,0)", "Accent 5", and so on.

property backgroundImage - The background image of the Spread component.

property backgroundImageLayout - The background image layout for the Spread component.

property grayAreaBackColor - A color string used to represent the background color of the gray area , such as "red", "#FFFF00", "rgb(255,0,0)", "Accent 5", and so on.

property showResizeTip - How to display the resize tip.

property showDragDropTip -Whether to display the drag-drop tip.

property showDragFillTip - Whether to display the drag-fill tip.

property showScrollTip - How to display the scroll tip.

property scrollIgnoreHidden - Whether the scroll bar ignores hidden rows or columns.

property highlightInvalidData - Whether to highlight invalid data.

property useTouchLayout - Whether to use touch layout to present the Spread component.

property hideSelection - Whether to display the selection highlighting when the Spread component does not have focus.

property resizeZeroIndicator - The drawing policy when the row or column is resized to zero.

property allowUserEditFormula - Whether the user can edit formulas in a cell in the spreadsheet.

property enableFormulaTextbox - Whether to enable the formula text box in the spreadsheet.

property autoFitType - Whether content will be formatted to fit in cells or in cells and headers.

property referenceStyle - the style for cell and range references in cell formulas on this sheet.

property allowDynamicArray - Whether to enable dynamic array.

property iterativeCalculation - Whether to enable the iterative calculation.

property iterativeCalculationMaximumIterations - The Maximum Iterations when iterative calculation.

property iterativeCalculationMaximumChange - The Maximum Change when iterative calculation.

property calcOnDemand - Whether to calculate formulas only when they are demanded.

property incrementalCalculation - Whether to incremental calculate formulas without blocking UI.

property dynamicReferences - Whether to calculate functions with dynamic reference.

property allowCopyPasteExcelStyle - Whether the user can copy style from Spread Sheets then paste to Excel, or copy style from Excel then paste to Spread Sheets.

property allowExtendPasteRange - Whether extend paste range if the paste range is not enough for pasting.

property copyPasteHeaderOptions - Which headers are included when data is copied to or pasted.

property calculationMode - The recalculation behavior of the workbook. The default is auto.

property scrollByPixel - Whether to enable the precision scrolling by pixel.

property scrollPixel - Decides scrolling by that number of pixels at a time when scrollByPixel is true. The final scrolling pixels are the result of scrolling delta multiply scrollPixel. For example, the scrolling delta is 3, and the scrollPixel is 5, the final scrolling pixels are 15.

property enableAccessibility - Whether to enable the accessibility support in the spreadsheet.

property allowAutoCreateHyperlink - Whether to enable auto creating hyperlink in the spreadsheet.

property columnResizeMode - Specifies the way to resize column.

property rowResizeMode - Specifies the way to resize row.

property customList - The list for user to customize drag fill, prioritize matching this list in each fill. Each array item is type of string array.

property scrollbarAppearance - The scrollbar appearance, contains skin and mobile two enums. Default is skin.

property pasteSkipInvisibleRange - Whether paste skip invisible range. Default is false.

property allowAutoExtendFilterRange - Whether allow auto extend filter range like excel. Default is false.

property allowInvalidFormula - Whether allow input invalid formula string. Default is false.

property formulaFormatHint - Whether automatically generate the format when formula inputs. Default is true.

property pivotAreaReference - Whether automatically generate the getPivotData formula or cell reference when choose pivot table data area. Default is getPivotData.

property defaultSheetTabStyles - All default state styles for sheet tabs.

property [options.builtInFileIcons] - All built-in file icons.

example

// var workbook = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:5,showHorizontalScrollbar:false});
var workbook = new GC.Spread.Sheets.Workbook("ss",{sheetCount:5,showHorizontalScrollbar:false});
workbook.options.allowUserDragDrop = false;
workbook.options.allowUserZoom = false;

sheetTabStyles

• sheetTabStyles: SheetTabStyleManager

A state style manager representing worksheet tabs.

sheets

• sheets: Worksheet[]

Represents the sheet collection.

touchToolStrip

• touchToolStrip: TouchToolStrip

Represents the touch toolstrip.

Methods

addCustomFunction

▸ addCustomFunction(fn): void

Adds a custom function.

Parameters

Name Type Description
fn Function The function to add.

Returns

void

addCustomName

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

Adds a custom name.

Parameters

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

Returns

void

addNamedStyle

▸ addNamedStyle(style): void

Adds a style to the Workbook named styles collection.

Parameters

Name Type Description
style Style The style to be added.

Returns

void

addSheet

▸ addSheet(index, sheet?): void

Inserts a sheet at the specific index.

example

//This example adds a sheet to the spreadsheet.
spread.addSheet(0,new GC.Spread.Sheets.Worksheet("custom"));

Parameters

Name Type Description
index number The index at which to add a sheet.
sheet? Worksheet The sheet to be added.

Returns

void

addSheetTab

▸ addSheetTab(index, name, type): any

Inserts a sheet tab at the specific index.

Parameters

Name Type Description
index number The index at which to add a sheet tab.
name string The name of sheet tab to be added.
type SheetType The type of sheet tab to be added.

Returns

any

The added sheet tab.

addSparklineEx

▸ addSparklineEx(sparklineEx): void

Adds a SparklineEx to the SparklineEx collection.

Parameters

Name Type Description
sparklineEx SparklineEx The SparklineEx to be added. window.MySparklineEx = function(color) { GC.Spread.Sheets.Sparklines.SparklineEx.apply(this, arguments); this.typeName = 'MySparklineEx'; this.color = color; } MySparklineEx.prototype = new GC.Spread.Sheets.Sparklines.SparklineEx(); MySparklineEx.prototype.createFunction = function () { var func = new GC.Spread.CalcEngine.Functions.Function('CIRCLE', 0, 0); func.evaluate = function (args) { return ; }; return func; }; MySparklineEx.prototype.paint = function (context, value, x, y, width, height) { context.beginPath(); context.arc(x + width / 2, y + height / 2, (Math.min(width, height) - 6) / 2, 0, Math.PI * 2); context.strokeStyle = this.color; context.stroke(); }; spread.addSparklineEx(new MySparklineEx('green'));

Returns

void

applyOp

▸ applyOp(changeSet): void

Only used in collaboration case, to apply doc's op.

Parameters

Name Type Description
changeSet Object change set

Returns

void

bind

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

Binds an event to the Workbook.

example

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

Parameters

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

Returns

void

calculate

▸ calculate(type?, address?): void

Rebuild, mark dirty, broadcast dirty and recalculate the formulas. First rebuild and mark dirty in the address by the calculation type. Then broadcast dirty will recursively set the dependents of dirty cells to dirty in the workbook. In automatic mode, all the dirty cells will be calculated. In the manual mode, only the cells in the address are calculated, other cells keep the dirty state. If the CalcService is suspended, the recalculate will be skipped.

example

spread.sheets[0].setFormula(0,0,"RAND()");
spread.sheets[0].setFormula(1,0,"=Sheet2!A1");
spread.sheets[0].setFormula(2,0,"=1+2");
spread.sheets[1].setFormula(0,0,"RAND()");
spread.sheets[1].setFormula(1,0,"=Sheet1!A1");

// All the cell is recalculated.
spread.calculate();
spread.calculate(GC.Spread.Sheets.CalculationType.regular);

// Sheet1!A1 Sheet2!A2 are evaluated to new number, and Sheet1!A2 Sheet1!A3 are evaluated.
spread.calculate(GC.Spread.Sheets.CalculationType.all, "Sheet1");

// Sheet1!A1 Sheet2!A2 are evaluated to new number.
spread.calculate(GC.Spread.Sheets.CalculationType.regular, "Sheet1!A1");

// No cells are evaluated.
spread.calculate(GC.Spread.Sheets.CalculationType.regular, "Sheet1!A2");

spread.options.calculationMode = GC.Spread.Sheets.CalculationMode.manual; // Switch to manual mode

// Sheet1!A1 is evaluated to new number, Sheet1!A2 Sheet1!A3 are evaluated but don't changed, Sheet2!A2 keeps dirty in manual mode.
spread.calculate(GC.Spread.Sheets.CalculationType.all, "Sheet1");

// Sheet1!A1 is evaluated to new number, Sheet2!A2 keeps dirty in manual mode.
spread.calculate(GC.Spread.Sheets.CalculationType.regular, "Sheet1");

// Sheet2!A2 is evaluated because it is dirty.
spread.calculate(GC.Spread.Sheets.CalculationType.minimal);

Parameters

Name Type Description
type? CalculationType Specifies the rebuild and mark dirty types of calculation.
address? string Specifies the sheet or range to mark dirty and calculate. It will be the workbook if it's omitted. Notice that dirty dependencies out of the range will be calculated in automatic mode.

Returns

void

changeSheetIndex

▸ changeSheetIndex(sheetName, targetIndex): boolean

Change sheet index and reorder sheets.

example

//This example show how to change sheet index.
var spread = GC.Spread.Sheets.findControl(ss);
spread.setSheetCount(5); // The sheets sequence should be "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5".
spread.changeSheetIndex("Sheet1", 3); // The sheets sequence should be "Sheet2", "Sheet3", "Sheet4", "Sheet1", "Sheet5".

Parameters

Name Type Description
sheetName string The sheet name.
targetIndex number The target index.

Returns

boolean

changeSheetPosition

▸ changeSheetPosition(sheetName, targetPosition): boolean

Change sheet position and reorder sheets.

example

//This example show how to change sheet position.
var spread = GC.Spread.Sheets.findControl(ss);
spread.setSheetCount(5);
spread.addSheetTab(0, "tableSheet1", GC.Spread.Sheets.SheetType.tableSheet); // The sheets sequence should be "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "tableSheet1".
spread.changeSheetPosition("tableSheet1", 3); // The sheets sequence should be "Sheet1", "Sheet2", "Sheet3", "tableSheet1", "Sheet4", "Sheet5".

Parameters

Name Type Description
sheetName string The sheet name.
targetPosition number The target position.

Returns

boolean

clearCustomFunctions

▸ clearCustomFunctions(): void

Clears all custom functions.

Returns

void

clearCustomNames

▸ clearCustomNames(): void

Clears custom names.

Returns

void

clearSheetTabs

▸ clearSheetTabs(): void

Clears all sheet tabs in the control.

Returns

void

clearSheets

▸ clearSheets(): void

Clears all sheets in the control.

example

//This example uses the clearSheets method.
spread.clearSheets();

Returns

void

commandManager

▸ commandManager(): CommandManager

Gets the command manager.

example

//This example executes a command that performs a specified action.
spread.options.allowUndo = true;
spread.commandManager().execute({cmd: "outlineRow", sheetName: "Sheet1", index: 3, count: 5});

Returns

CommandManager

The command manager.

dataManager

▸ dataManager(): DataManager

Gets the data manager.

Returns

DataManager

Returns the data manager.

defaultPivotTableTheme

▸ defaultPivotTableTheme(themeName?): undefined | PivotTableTheme

set or get the default pivot table theme.

example

// set Default pivot table theme name as "custom0"
spread.defaultPivotTableTheme("custom0");
let newPivotTable = activeSheet.pivotTables.add("pivotTable1", 1, 1, 10, 5);
// The pivot table above will use "custom0" as the theme since no specific theme has been specified.

// get default pivot table theme
spread.defaultPivotTableTheme();

Parameters

Name Type
themeName? string

Returns

undefined | PivotTableTheme

defaultSlicerTheme

▸ defaultSlicerTheme(themeName?): undefined | SlicerStyle

set or get the default slicer theme.

example

// set default slicer theme name as "custom0"
spread.defaultSlicerTheme("custom0");
let newSlicer = activeSheet.slicers.add("slicer1", 1, 1, 10, 5);
// The slicer above will use "custom0" as the theme since no specific theme has been specified.

// get default slicer theme
spread.defaultSlicerTheme();

Parameters

Name Type
themeName? string

Returns

undefined | SlicerStyle

defaultTableTheme

▸ defaultTableTheme(themeName?): undefined | TableTheme

set or get the default table theme.

example

// set default table theme name as "custom0"
spread.defaultTableTheme("custom0");
let newable = activeSheet.tables.add("table1", 1, 1, 10, 5);
// The table above will use "custom0" as the theme since no specific theme has been specified.

// get the default table theme
spread.defaultTableTheme();

Parameters

Name Type
themeName? string

Returns

undefined | TableTheme

defaultTimelineTheme

▸ defaultTimelineTheme(themeName?): undefined | TimelineStyle

set or get the default timeLine theme.

example

// set default timeLine theme name as "custom0"
spread.defaultTimelineTheme("custom0");
let newTimeline = activeSheet.timeLines.add("timeLine1", 1, 1, 10, 5);
// The timeLine above will use "custom0" as the theme since no specific theme has been specified.

// get default timeLine theme
spread.defaultTimelineTheme();

Parameters

Name Type
themeName? string

Returns

undefined | TimelineStyle

destroy

▸ destroy(): void

Destroys the workbook and all sheets it contains.

example

//This example destroys the workbook instance.
spread.destroy();

Returns

void

export

▸ export(successCallBack?, errorCallBack?, exportOptions?): void

Exports the object state to excel or ssjson file or csv file.

example

spread.export(function (blob) {
   // save blob to a file
   saveAs(blob, fileName);
}, function (e) {
   console.log(e);
}, {
   fileType: GC.Spread.Sheets.FileType.excel,
   includeBindingSource: true
});

Parameters

Name Type
successCallBack? Function
errorCallBack? Function
exportOptions? ExportOptions

Returns

void

focus

▸ focus(focusIn?): void

Makes the Workbook component get focus or lose focus.

example

//This example sets focus to the Spread control.
$("#button1").click(function () {
spread.focus(true);
   });

Parameters

Name Type Description
focusIn? boolean false makes the Workbook component lose the focus; otherwise, get focus.

Returns

void

fromJSON

▸ fromJSON(workbookData, deserializationOptions?): Promise<any>

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(spread.toJSON());
//import
var fromJSONPromise = spread.fromJSON(JSON.parse(jsonStr));
fromJSONPromise.then(() => {
     alert(jsonStr);
});

Parameters

Name Type Description
workbookData Object The spreadsheet data from deserialization.
deserializationOptions? IDeserializationOptions -

Returns

Promise<any>

  • The fromJSON promise.

fromSnapshot

▸ fromSnapshot(snapshot): void

Only used in collaboration case, to restore the snapshot to workbook state.

Parameters

Name Type Description
snapshot Object snapshot object

Returns

void

getActiveSheet

▸ getActiveSheet(): Worksheet

Gets the active sheet.

Returns

Worksheet

The active sheet instance.

getActiveSheetIndex

▸ getActiveSheetIndex(): number

Gets the active sheet index of the control.

example

//This example uses the getActiveSheetIndex method.
var index = spread.getActiveSheetIndex();
alert(index);

Returns

number

The active sheet index.

getActiveSheetTab

▸ getActiveSheetTab(): any

Gets the active sheet tab.

Returns

any

The active sheet tab instance.

getActiveSheetTabIndex

▸ getActiveSheetTabIndex(): number

Gets the active sheet tab index of the control.

Returns

number

The active sheet tab index.

getCircularReference

▸ getCircularReference(): ICellsInfo[]

Gets the all the Circular Reference cell information in the workbook.

example

spread.getCircularReference();

Returns

ICellsInfo[]

Returns circular reference cell information object array cellsInfo.row Indicates the cellRange row index. cellsInfo.col Indicates the cellRange col index. cellsInfo.rowCount Indicates the cellRange row count. cellsInfo.colCount Indicates the cellRange col count. cellsInfo.sheetName Indicates the workSheet name.

getCustomFunction

▸ getCustomFunction(name): void

Gets a custom function.

Parameters

Name Type Description
name string The custom function name.

Returns

void

The custom function.

getCustomName

▸ getCustomName(name): NameInfo

Gets the specified custom name information.

Parameters

Name Type Description
name string The custom name.

Returns

NameInfo

The information for the specified custom name.

getCustomNames

▸ getCustomNames(): NameInfo[]

Gets all custom name information.

Returns

NameInfo[]

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

getExternalReferences

▸ getExternalReferences(includeItemDetail?): IExternalReference[]

Get the cross workbook reference list of current workbook.

example

//This example gets and update the used data source.
spread.getActiveSheet().setFormula(0, 0, "='[Jackson.xlsx]Sheet1'!B6+SUM('[Petrosky.xlsx]Sheet2'!B7:B8)");
spread.getExternalReferences().forEach(item=>{
     spread.updateExternalReference(item.name, spreadList[item.name], item.filePath);
})

Parameters

Name Type Description
includeItemDetail? boolean Specifies whether to include the target cell and the source ranges. Default value is false.

Returns

IExternalReference[]

The externalReference array.
externalReference.name The file name of cross workbook reference.
externalReference.filePath The file path of cross workbook reference.

getHost

▸ getHost(): HTMLElement

Gets the host element of the current Workbook instance.

Returns

HTMLElement

host The host element of the current Workbook instance.

getNamedStyle

▸ getNamedStyle(name): Style

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

Parameters

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

Returns

Style

Returns the specified named style.

getNamedStyles

▸ getNamedStyles(): Style[]

Gets named styles from the Workbook.

Returns

Style[]

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

getSheet

▸ getSheet(index): Worksheet

Gets the specified sheet.

example

//This example gets the sheet and sets the cell forecolor.
var sheet1 = spread.getSheet(1);
sheet1.getCell(0,0).value("A1").foreColor("red");

Parameters

Name Type Description
index number The index of the sheet to return.

Returns

Worksheet

The specified sheet.

getSheetCount

▸ getSheetCount(): number

Gets the number of sheets.

example

//This example uses the getSheetCount method.
var index = spread.getSheetCount();
alert(index);

Returns

number

The number of sheets.

getSheetFromName

▸ getSheetFromName(name): Worksheet

Gets the sheet with the specified name.

example

//This example gets the sheet and sets the cell forecolor.
var sheet1 = spread.getSheetFromName("Sheet2");
sheet1.getCell(0,0).value("A1").foreColor("red");

Parameters

Name Type Description
name string The sheet name.

Returns

Worksheet

The sheet with the specified name.

getSheetIndex

▸ getSheetIndex(name): number

Gets the sheet index with the specified name.

example

//This example uses the getSheetIndex method.
spread.setSheetCount(5);
var index = spread.getSheetIndex("Sheet2");
alert(index); // 1

Parameters

Name Type Description
name string The sheet name.

Returns

number

The sheet index, based on the Worksheet collection.

getSheetPosition

▸ getSheetPosition(name): number

Gets the sheet tab position with the specified name.

example

//This example uses the getSheetPosition method.
spread.setSheetCount(5);
spread.addSheetTab(0, "tableSheet1", GC.Spread.Sheets.SheetType.tableSheet);
var position = spread.getSheetPosition("tableSheet1");
alert(position); // 5

Parameters

Name Type Description
name string The sheet tab name.

Returns

number

The sheet tab position, based on the Worksheet and TableSheet collection.

getSheetTab

▸ getSheetTab(indexOrName): any

Gets the specified sheet tab by index or name.

Parameters

Name Type Description
indexOrName string | number The index or name of the sheet tab to return.

Returns

any

The specified sheet tab.

getSheetTabCount

▸ getSheetTabCount(): number

Gets the number of sheet tabs.

Returns

number

The number of sheet tabs.

getSheetTabIndex

▸ getSheetTabIndex(name): number

Gets the sheet tab index with the specified name.

example

//This example uses the getSheetTabIndex method.
spread.setSheetCount(5);
spread.addSheetTab(0, "tableSheet1", GC.Spread.Sheets.SheetType.tableSheet);
var index = spread.getSheetTabIndex("tableSheet1");
alert(index); // 0

Parameters

Name Type Description
name string The sheet tab name.

Returns

number

The sheet tab index, based on the TabSheet collection.

hitTest

▸ hitTest(x, y): IWorkbookHitTestInformation

Performs a hit test.

example

//This example uses the hitTest method.
      window.onload = function(){
          var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
          var activeSheet = spread.getActiveSheet();
          $("#ss").click(function (e) {
              //Acquire cell index from mouse-clicked point of regular cells which are neither fixed rows/columns nor row/column headers.
              var offset = $("#ss").offset();
              var x = e.pageX - offset.left;
              var y = e.pageY - offset.top;
              var target = spread.hitTest(x, y);
              if(target.worksheetHitInfo) {
                  if(target.worksheetHitInfo.hitTestType === 0) {
                      str = 'corner';
                  } else if (target.worksheetHitInfo.hitTestType === 1) {
                      str = 'colHeader';
                  } else if (target.worksheetHitInfo.hitTestType === 2) {
                      str = 'rowHeader';
                  } else {
                      str = 'viewport';
                  }
              } else if(target.tabStripHitInfo) {
                  if(target.tabStripHitInfo.navButton){
                      str = target.tabStripHitInfo.navButton;
                  } else if(target.tabStripHitInfo.sheetTab) {
                      str = target.tabStripHitInfo.sheetTab.sheetName;
                  } else if(target.tabStripHitInfo.resize === true) {
                      str = "resize";
                  } else {
                      str = "blank";
                  }
              } else if(target.horizontalScrollBarHitInfo) {
                  str = target.horizontalScrollBarHitInfo.element;
              } else if(target.verticalScrollBarHitInfo) {
                  str = target.verticalScrollBarHitInfo.element;
              } else if(target.footerCornerHitInfo) {
                  str = target.footerCornerHitInfo.element;
              }
        alert(str);
});
}

Parameters

Name Type Description
x number The x-coordinate, x relative to spread horizontal axis.
y number The y-coordinate, y relative to spread vertical axis.

Returns

IWorkbookHitTestInformation

The hit test information. If selecting the worksheet, the worksheet information is returned. The information contains x, y, and worksheetHitInfo; If selecting the sheetsTabStrip, the sheetsTabStrip information is returned. This information contains x, y, and tabStripHitInfo; If selecting the horizontalScrollbar, the horizontalScrollbar information is returned. This information contains x, y, and horizontalScrollBarHitInfo; If selecting the verticalScrollbar, the verticalScrollbar information is returned. This information contains x, y, and verticalScrollBarHitInfo; If selecting the footerCorner, the footerCorner information is returned. This information contains x, y, and footerCornerHitInfo.

import

▸ import(file, successCallback?, errorCallback?, importOptions?): void

Imports the object state from the excel or ssJson or csv file or javascript file.

example

//This example uses the import method.
//Get file blob.
var file = document.getElementById("importFileName").files[0];
// import
spread.import(file, function () {
   // success callback to do something
}, function (e) {
   console.log(e); // error callback
}, {
   fileType: GC.Spread.Sheets.FileType.excel
});

Parameters

Name Type Description
file File The ssJson or csv or Excel file or javascript file for import.
successCallback? Function -
errorCallback? Function -
importOptions? ImportOptions -

Returns

void

invalidateLayout

▸ invalidateLayout(): void

Updates the control layout information.

example

//This example updates the layout.
spread.invalidateLayout();
spread.repaint();

Returns

void

isPaintSuspended

▸ isPaintSuspended(): boolean

Get if spread paint is suspended.

Returns

boolean

nextControl

▸ nextControl(value?): any

Gets or sets the next control used by GC.Spread.Sheets.Actions.selectNextControl and GC.Spread.Sheets.Actions.moveToNextCellThenControl.

Parameters

Name Type Description
value? HTMLElement The next control. The control must have a focus method.

Returns

any

If no value is set, returns the next control; otherwise, returns the spreadsheet.

onOp

▸ onOp(onOpHandler): void

Only used in collaboration case, to watch op

Parameters

Name Type
onOpHandler Function

Returns

void

open

▸ open(file, successCallback?, errorCallback?, openOptions?): void

Loads the object state from the sjs zipped file.

example

//This example uses the open method.
//Get file blob.
var file = document.getElementById("importFileName").files[0];
// import
spread.open(file, function () {
   // success callback to do something
}, function (e) {
 console.log(e); // error callback
}, { openMode: GC.Spread.Sheets.OpenMode.lazy });

Parameters

Name Type Description
file File The zipped spreadsheet data file.
successCallback? Function -
errorCallback? Function -
openOptions? OpenOptions -

Returns

void

pageInfo

▸ pageInfo(sheetIndex?): any

Get a page info for a sheet

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
spread.suspendPaint();
var sheet = spread.getActiveSheet();
for(var i=0;i<20;i++){
   for(var j=0;j<20;j++){
       sheet.setValue(i,j,"Row"+i+"_Column"+j);
   }
}
var pageInfos =  spread.pageInfo(0);
console.table(pageInfos.pages);

Parameters

Name Type Description
sheetIndex? number The sheet index.

Returns

any

return a page info for a sheet, If the sheet index is ignored return all sheet's page info in an array

previousControl

▸ previousControl(value?): any

Gets or sets the previous control used by GC.Spread.Sheets.Actions.selectPreviousControl and GC.Spread.Sheets.Actions.moveToPreviousCellThenControl.

Parameters

Name Type Description
value? HTMLElement The previous control. The control must have a focus method.

Returns

any

If no value is set, returns the previous control; otherwise, returns the spreadsheet.

print

▸ print(sheetIndex?): void

Prints the specified sheet.

Parameters

Name Type Description
sheetIndex? number The sheet index. If the sheet index is ignored, prints all visible sheets.

Returns

void

refresh

▸ refresh(): void

Manually refreshes the layout and rendering of the Workbook object.

example

//This example uses the refresh method.
spread.refresh();

Returns

void

removeCustomFunction

▸ removeCustomFunction(name): void

Removes a custom function.

Parameters

Name Type Description
name string The custom function name.

Returns

void

removeCustomName

▸ removeCustomName(name): void

Removes the specified custom name.

example

//This example uses the removeCustomName method.
activeSheet.setValue(0, 0, 1);
activeSheet.setValue(0, 1, 2);
activeSheet.setValue(0, 2, 3);
spread.addCustomName("customName1","=12", 0, 0);
activeSheet.setFormula(1, 0, "customName1");
//spread.removeCustomName("customName1");

Parameters

Name Type Description
name string The custom name.

Returns

void

removeNamedStyle

▸ removeNamedStyle(name): void

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

example

var namedStyle = new GC.Spread.Sheets.Style();
namedStyle.name = "style1";
namedStyle.backColor = "green";
spread.addNamedStyle(namedStyle);
activeSheet.setStyleName(1, 1, "style1"); // cell(1,1)'s backColor is  green.
activeSheet.setStyleName(2, 1, "style1");
var style = spread.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 () {
     spread.removeNamedStyle("style1");
});

Parameters

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

Returns

void

removeSheet

▸ removeSheet(index): void

Removes the specified sheet.

example

//This example removes a sheet from the spreadsheet.
spread.setSheetCount(5);
spread.removeSheet(0);

Parameters

Name Type Description
index number The index of the sheet to remove.

Returns

void

removeSheetTab

▸ removeSheetTab(indexOrName): void

Removes the specified sheet tab by index or name.

Parameters

Name Type Description
indexOrName string | number The index or name of the sheet tab to remove.

Returns

void

removeSparklineEx

▸ removeSparklineEx(name): void

Removes a SparklineEx from the SparklineEx collection.

Parameters

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

Returns

void

repaint

▸ repaint(): void

Repaints the Workbook control.

example

//This example updates the layout.
spread.invalidateLayout();
spread.repaint();

Returns

void

resumeCalcService

▸ resumeCalcService(recalcAll?): void

Resumes the calculation service.

example

//This example uses the resumeCalcService method.
spread.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)");
spread.resumeCalcService(true);

Parameters

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

Returns

void

resumeEvent

▸ resumeEvent(): void

Resumes the event.

example

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

Returns

void

resumePaint

▸ resumePaint(): void

Resumes the paint of active sheet and tab strip.

Returns

void

save

▸ save(successCallBack?, errorCallBack?, saveOptions?): void

Saves the spreadJS state to a sjs file.

example

spread.save(function (blob) {
   // save blob to a file
   saveAs(blob, fileName);
}, function (e) {
   console.log(e);
}, { includeUnusedNames: false });

Parameters

Name Type Description
successCallBack? Function The success callback when save spreadJS state complete, accept Blob as argument.
errorCallBack? Function The error callback when save spreadJS state got error.
saveOptions? SaveOptions The save options.

Returns

void

savePDF

▸ savePDF(successCallback, errorCallback, options?, sheetIndex?): void

Exports the specified sheet to PDF.

Parameters

Name Type Description
successCallback Function Call this function after successfully export. function (blob) .
errorCallback Function Call this function if an error occurs. The exception parameter object structure { errorCode: GC.Spread.Sheets.PDF.ErrorCode, errorMessage: string}.
options? Object -
sheetIndex? number The sheet index. If the sheet index is ignored, exports all visible sheets.

Returns

void

▸ search(searchCondition): SearchResult

Searches the text in the cells in the specified sheet for the specified string with the specified criteria.

example

//This example searches the active sheet using the specified search condition.
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= spread.search(searchCondition);
var str ="[searchFoundFlag:"+ searchresult.searchFoundFlag+",\r\n foundSheetIndex:"+searchresult.foundSheetIndex+",foundRowIndex:" +
searchresult.foundRowIndex+", foundColumnIndex:"+searchresult.foundColumnIndex+", foundString:"+searchresult.foundSheetIndex+"]";
alert(str);

Parameters

Name Type Description
searchCondition SearchCondition The search conditions.

Returns

SearchResult

The search result.

setActiveSheet

▸ setActiveSheet(name): void

Sets the active sheet by name.

example

//This example sets the active sheet.
spread.setSheetCount(3);
spread.setActiveSheet("Sheet2");

Parameters

Name Type Description
name string The name of the sheet to make the active sheet.

Returns

void

setActiveSheetIndex

▸ setActiveSheetIndex(value): void

Sets the active sheet index for the control.

example

//This example uses the setActiveSheetIndex method.
spread.setActiveSheetIndex(1);

Parameters

Name Type Description
value number The active sheet index.

Returns

void

setActiveSheetTab

▸ setActiveSheetTab(indexOrName): void

Sets the active sheet tab by index or name.

Parameters

Name Type Description
indexOrName string | number The index or name of the sheet tab to make the active sheet tab.

Returns

void

setSheetCount

▸ setSheetCount(count): void

Sets the number of sheets.

example

spread.setSheetCount(5);

Parameters

Name Type Description
count number The number of sheets.

Returns

void

startSheetIndex

▸ startSheetIndex(value?): any

Gets or sets the index of the first sheet to display in the spreadsheet.

Parameters

Name Type Description
value? number The index of the first sheet to display in the spreadsheet.

Returns

any

If no value is set, returns the index of the first sheet displayed in the spreadsheet; otherwise, returns the spreadsheet.

suspendCalcService

▸ suspendCalcService(ignoreDirty?): void

Suspends the calculation service.

example

//This example uses the suspendCalcService method.
spread.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)");
spread.resumeCalcService(true);

Parameters

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

Returns

void

suspendEvent

▸ suspendEvent(): void

Suspends the event.

example

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

Returns

void

suspendPaint

▸ suspendPaint(): void

Suspends the paint of active sheet and tab strip.

Returns

void

toJSON

▸ toJSON(serializationOption?): Object

Saves the object state to a JSON string.

example

activeSheet.getCell(0,0).value(123);
var jsonStr = null;
//export
jsonStr = JSON.stringify(spread.toJSON());
//import
spread.fromJSON(JSON.parse(jsonStr));
alert(jsonStr);

Parameters

Name Type Description
serializationOption? ISerializationOption The serialization options.

Returns

Object

The spreadsheet data.

toSnapshot

▸ toSnapshot(): Object

Only used in collaboration case, to save workbook state to snapshot.

Returns

Object

snapshot - snapshot object

unbind

▸ unbind(type, fn?): void

Removes the binding of an event to Workbook.

example

//This example removes the event binding.
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");
spread.unbind(GC.Spread.Sheets.Events.CellChanged);
//spread.unbindAll(); //cancel monitoring of all events.
activeSheet.setValue(1, 0, "222");
activeSheet.setValue(2, 0, "333");
activeSheet.setValue(3, 0, "444");

Parameters

Name Type Description
type string The event type.
fn? Function Specifies the function to run when the event occurs.

Returns

void

unbindAll

▸ unbindAll(): void

Removes the binding of all events to Workbook.

example

//This example removes the event binding. Uncomment the unbindAll method to remove all event binding.
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");
spread.unbind(GC.Spread.Sheets.Events.CellChanged);
//spread.unbindAll(); //cancel monitoring of all events.
activeSheet.setValue(1, 0, "222");
activeSheet.setValue(2, 0, "333");
activeSheet.setValue(3, 0, "444");

Returns

void

undoManager

▸ undoManager(): UndoManager

Gets the undo manager.

Returns

UndoManager

The undo manager.

updateExternalReference

▸ updateExternalReference(linkInfo, data?, filePath?, isMergeUpdate?): void

Sets or update the data of external source with the workbook json or the external values from the successCallback result of GC.Spread.Sheets.IO.getPartialValues.

example

// This example sets the external source with workbook JSON.
spread.getActiveSheet().setFormula(0, 0, "[calc.xlsx]Sheet1!A1");  // spread cell A1 value is #REF!
spread2.getActiveSheet().setFormula(0, 0, "=123+1"); \xa0// spread2 cell A1 value is 124
spread.updateExternalReference("calc.xlsx", spread2.toJSON());  // spread cell A1 value is 124
// Set the data with simple JSON data:
spread.getActiveSheet().setFormula(0, 0, "=SUM([calc.xlsx]Sheet1!A1:B2"); \xa0// spread cell A1 value is #REF!
spread.updateExternalReference("calc.xlsx", {"Sheet1":[[1, 2],[1, 3]]}); \xa0// spread cell A1 value is 7
spread.updateExternalReference("calc.xlsx", {"Sheet1":{0:{0:10, 1:10}, 1:{0:10, 1:10}}}); \xa0// spread cell A1 value is 40
spread.updateExternalReference("calc.xlsx", {"Sheet1":{1:{0:2, 1:2}}}, null, true); \xa0// spread cell A1 value is 24 after the merge update.
// This example sets the external values with the successCallback result of GC.Spread.Sheets.IO.getPartialValues
let refList = spread.getExternalReferences(true);
GC.Spread.Sheets.IO.getPartialValues(refList, getFile, (externalValues) => {
  // successCallback
  spread.updateExternalReference(externalValues);
}, (errorMsg) => {
  // errorCallback
  console.log(errorMsg);
});

Parameters

Name Type Description
linkInfo string | ExternalPartialValues The string type is the file name of the external source, mostly ends with ".xlsx". Another type should be an array of external partial values get from the callback of GC.Spread.Sheets.IO.getPartialValues.
data? object -
filePath? string -
isMergeUpdate? boolean Indicates whether to update by merging. If it's not true, the whole external sheet data will be overwritten.

Returns

void