[]
• 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});
Name | Type | Description |
---|---|---|
host? |
string | HTMLElement |
The host DOM element or id. |
options? |
IWorkBookDefaultOptions |
- |
• contextMenu: ContextMenu
ContextMenu for the spread.
example
//This example shows how to get contextMenu's menuData.
var menuData = spread.contextMenu.menuData;
• customPivotTableThemes: CustomPivotTableThemeManager
Represents the custom pivot table theme manager.
• customSlicerThemes: CustomSlicerThemeManager
Represents the custom item slicer theme manager.
• customTableThemes: CustomTableThemeManager
Represents the custom table theme manager.
• customTimelineThemes: CustomTimelineThemeManager
Represents the custom timeline slicer theme manager.
• docProps: IDocProps
Document properties.
• name: string
Represents the name of the Spread control.
example
spread.name = "Spread1";
• 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: SheetTabStyleManager
A state style manager representing worksheet tabs.
• sheets: Worksheet
[]
Represents the sheet collection.
• touchToolStrip: TouchToolStrip
Represents the touch toolstrip.
▸ 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.
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 Workbook named styles collection.
Name | Type | Description |
---|---|---|
style |
Style |
The style to be added. |
void
▸ 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"));
Name | Type | Description |
---|---|---|
index |
number |
The index at which to add a sheet. |
sheet? |
Worksheet |
The sheet to be added. |
void
▸ addSheetTab(index
, name
, type
): any
Inserts a sheet tab at the specific index.
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. |
any
The added sheet tab.
▸ addSparklineEx(sparklineEx
): void
Adds a SparklineEx to the SparklineEx collection.
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')); |
void
▸ 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);
});
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. |
void
▸ 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);
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. |
void
▸ 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".
Name | Type | Description |
---|---|---|
sheetName |
string |
The sheet name. |
targetIndex |
number |
The target index. |
boolean
▸ 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".
Name | Type | Description |
---|---|---|
sheetName |
string |
The sheet name. |
targetPosition |
number |
The target position. |
boolean
▸ clearCustomFunctions(): void
Clears all custom functions.
void
▸ clearCustomNames(): void
Clears custom names.
void
▸ clearSheetTabs(): void
Clears all sheet tabs in the control.
void
▸ clearSheets(): void
Clears all sheets in the control.
example
//This example uses the clearSheets method.
spread.clearSheets();
void
▸ 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});
The command manager.
▸ dataManager(): DataManager
Gets the data manager.
Returns the data manager.
▸ 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();
Name | Type |
---|---|
themeName? |
string |
undefined
| PivotTableTheme
▸ 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();
Name | Type |
---|---|
themeName? |
string |
undefined
| SlicerStyle
▸ 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();
Name | Type |
---|---|
themeName? |
string |
undefined
| TableTheme
▸ 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();
Name | Type |
---|---|
themeName? |
string |
undefined
| TimelineStyle
▸ destroy(): void
Destroys the workbook and all sheets it contains.
example
//This example destroys the workbook instance.
spread.destroy();
void
▸ 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
});
Name | Type |
---|---|
successCallBack? |
Function |
errorCallBack? |
Function |
exportOptions? |
ExportOptions |
void
▸ 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);
});
Name | Type | Description |
---|---|---|
focusIn? |
boolean |
false makes the Workbook component lose the focus; otherwise, get focus. |
void
▸ 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);
});
Name | Type | Description |
---|---|---|
workbookData |
Object |
The spreadsheet data from deserialization. |
deserializationOptions? |
Object |
- |
Promise
<any
>
▸ getActiveSheet(): Worksheet
Gets the active sheet.
The active sheet instance.
▸ getActiveSheetIndex(): number
Gets the active sheet index of the control.
example
//This example uses the getActiveSheetIndex method.
var index = spread.getActiveSheetIndex();
alert(index);
number
The active sheet index.
▸ getActiveSheetTab(): any
Gets the active sheet tab.
any
The active sheet tab instance.
▸ getActiveSheetTabIndex(): number
Gets the active sheet tab index of the control.
number
The active sheet tab index.
▸ getCircularReference(): ICellsInfo
[]
Gets the all the Circular Reference cell information in the workbook.
example
spread.getCircularReference();
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(name
): void
Gets a custom function.
Name | Type | Description |
---|---|---|
name |
string |
The custom function name. |
void
The custom function.
▸ getCustomName(name
): NameInfo
Gets the specified custom name information.
Name | Type | Description |
---|---|---|
name |
string |
The custom name. |
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.
▸ 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);
})
Name | Type | Description |
---|---|---|
includeItemDetail? |
boolean |
Specifies whether to include the target cell and the source ranges. Default value is false. |
The externalReference array.
externalReference.name The file name of cross workbook reference.
externalReference.filePath The file path of cross workbook reference.
▸ getHost(): HTMLElement
Gets the host element of the current Workbook instance.
HTMLElement
host The host element of the current Workbook instance.
▸ getNamedStyle(name
): Style
Gets a style from the Workbook 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 Workbook.
Style
[]
The GC.Spread.Sheets.Style array of named styles.
▸ 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");
Name | Type | Description |
---|---|---|
index |
number |
The index of the sheet to return. |
The specified sheet.
▸ getSheetCount(): number
Gets the number of sheets.
example
//This example uses the getSheetCount method.
var index = spread.getSheetCount();
alert(index);
number
The number of sheets.
▸ 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");
Name | Type | Description |
---|---|---|
name |
string |
The sheet name. |
The sheet with the specified name.
▸ 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
Name | Type | Description |
---|---|---|
name |
string |
The sheet name. |
number
The sheet index, based on the Worksheet collection.
▸ 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
Name | Type | Description |
---|---|---|
name |
string |
The sheet tab name. |
number
The sheet tab position, based on the Worksheet and TableSheet collection.
▸ getSheetTab(indexOrName
): any
Gets the specified sheet tab by index or name.
Name | Type | Description |
---|---|---|
indexOrName |
string | number |
The index or name of the sheet tab to return. |
any
The specified sheet tab.
▸ getSheetTabCount(): number
Gets the number of sheet tabs.
number
The number of sheet tabs.
▸ 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
Name | Type | Description |
---|---|---|
name |
string |
The sheet tab name. |
number
The sheet tab index, based on the TabSheet collection.
▸ 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);
});
}
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. |
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(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
});
Name | Type | Description |
---|---|---|
file |
File |
The ssJson or csv or Excel file or javascript file for import. |
successCallback? |
Function |
- |
errorCallback? |
Function |
- |
importOptions? |
ImportOptions |
- |
void
▸ invalidateLayout(): void
Updates the control layout information.
example
//This example updates the layout.
spread.invalidateLayout();
spread.repaint();
void
▸ isPaintSuspended(): boolean
Get if spread paint is suspended.
boolean
▸ nextControl(value?
): any
Gets or sets the next control used by GC.Spread.Sheets.Actions.selectNextControl and GC.Spread.Sheets.Actions.moveToNextCellThenControl.
Name | Type | Description |
---|---|---|
value? |
HTMLElement |
The next control. The control must have a focus method. |
any
If no value is set, returns the next control; otherwise, returns the spreadsheet.
▸ 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 });
Name | Type | Description |
---|---|---|
file |
File |
The zipped spreadsheet data file. |
successCallback? |
Function |
- |
errorCallback? |
Function |
- |
openOptions? |
OpenOptions |
- |
void
▸ 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);
Name | Type | Description |
---|---|---|
sheetIndex? |
number |
The sheet index. |
any
return a page info for a sheet, If the sheet index is ignored return all sheet's page info in an array
▸ previousControl(value?
): any
Gets or sets the previous control used by GC.Spread.Sheets.Actions.selectPreviousControl and GC.Spread.Sheets.Actions.moveToPreviousCellThenControl.
Name | Type | Description |
---|---|---|
value? |
HTMLElement |
The previous control. The control must have a focus method. |
any
If no value is set, returns the previous control; otherwise, returns the spreadsheet.
▸ print(sheetIndex?
): void
Prints the specified sheet.
Name | Type | Description |
---|---|---|
sheetIndex? |
number |
The sheet index. If the sheet index is ignored, prints all visible sheets. |
void
▸ refresh(): void
Manually refreshes the layout and rendering of the Workbook object.
example
//This example uses the refresh method.
spread.refresh();
void
▸ removeCustomFunction(name
): void
Removes a custom function.
Name | Type | Description |
---|---|---|
name |
string |
The custom function name. |
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);
spread.addCustomName("customName1","=12", 0, 0);
activeSheet.setFormula(1, 0, "customName1");
//spread.removeCustomName("customName1");
Name | Type | Description |
---|---|---|
name |
string |
The custom name. |
void
▸ 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");
});
Name | Type | Description |
---|---|---|
name |
string |
The name of the style to remove. |
void
▸ removeSheet(index
): void
Removes the specified sheet.
example
//This example removes a sheet from the spreadsheet.
spread.setSheetCount(5);
spread.removeSheet(0);
Name | Type | Description |
---|---|---|
index |
number |
The index of the sheet to remove. |
void
▸ removeSheetTab(indexOrName
): void
Removes the specified sheet tab by index or name.
Name | Type | Description |
---|---|---|
indexOrName |
string | number |
The index or name of the sheet tab to remove. |
void
▸ removeSparklineEx(name
): void
Removes a SparklineEx from the SparklineEx collection.
Name | Type | Description |
---|---|---|
name |
string |
The name of the SparklineEx to remove. |
void
▸ repaint(): void
Repaints the Workbook control.
example
//This example updates the layout.
spread.invalidateLayout();
spread.repaint();
void
▸ 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);
Name | Type | Description |
---|---|---|
recalcAll? |
boolean |
Specifies whether to recalculate all formulas. |
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));
}
});
spread.suspendEvent();
activeSheet.setValue(0, 0, "111");
spread.resumeEvent();
activeSheet.setValue(1, 1, "222");
void
▸ resumePaint(): void
Resumes the paint of active sheet and tab strip.
void
▸ 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 });
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. |
void
▸ savePDF(successCallback
, errorCallback
, options?
, sheetIndex?
): void
Exports the specified sheet to PDF.
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. |
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);
Name | Type | Description |
---|---|---|
searchCondition |
SearchCondition |
The search conditions. |
The search result.
▸ setActiveSheet(name
): void
Sets the active sheet by name.
example
//This example sets the active sheet.
spread.setSheetCount(3);
spread.setActiveSheet("Sheet2");
Name | Type | Description |
---|---|---|
name |
string |
The name of the sheet to make the active sheet. |
void
▸ setActiveSheetIndex(value
): void
Sets the active sheet index for the control.
example
//This example uses the setActiveSheetIndex method.
spread.setActiveSheetIndex(1);
Name | Type | Description |
---|---|---|
value |
number |
The active sheet index. |
void
▸ setActiveSheetTab(indexOrName
): void
Sets the active sheet tab by index or name.
Name | Type | Description |
---|---|---|
indexOrName |
string | number |
The index or name of the sheet tab to make the active sheet tab. |
void
▸ setSheetCount(count
): void
Sets the number of sheets.
example
spread.setSheetCount(5);
Name | Type | Description |
---|---|---|
count |
number |
The number of sheets. |
void
▸ startSheetIndex(value?
): any
Gets or sets the index of the first sheet to display in the spreadsheet.
Name | Type | Description |
---|---|---|
value? |
number |
The index of the first sheet to display in the spreadsheet. |
any
If no value is set, returns the index of the first sheet displayed in the spreadsheet; otherwise, returns the spreadsheet.
▸ 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);
Name | Type | Description |
---|---|---|
ignoreDirty? |
boolean |
Specifies whether to invalidate the dependency cells. |
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));
}
});
spread.suspendEvent();
activeSheet.setValue(0, 0, "111");
spread.resumeEvent();
activeSheet.setValue(1, 1, "222");
void
▸ suspendPaint(): void
Suspends the paint of active sheet and tab strip.
void
▸ 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);
Name | Type | Description |
---|---|---|
serializationOption? |
Object |
The serialization options. |
Object
▸ 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");
Name | Type | Description |
---|---|---|
type |
string |
The event type. |
fn? |
Function |
Specifies the function to run when the event occurs. |
void
▸ 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");
void
▸ undoManager(): UndoManager
Gets the undo manager.
The undo manager.
▸ 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);
});
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. |
void