# API

API is avaible via the ref of Workbook

const ref = useRef<WorkbookInstance>(null);

<Workbook ref={ref} ... />

# Overview

API	Description
applyOp	Applys an array of op to the workbook
getCellValue	Gets the value of a cell
setCellValue	Sets the value of a cell
clearCell	Clear the contents of the cell
setCellFormat	Sets the attributes of a cell
freeze	Freezes row and column
insertRowOrColumn	Inserts rows or columns at specified position
deleteRowOrColumn	Deletes rows or columns at specified range
getRowHeight	Gets row heights in batch
getColumnWidth	Gets column widths in batch
setRowHeight	Sets row heights in batch
setColumnWidth	Sets column widths in batch
getSelection	Gets current selection
getFlattenRange	Expands given range to cells coordinates
getCellsByFlattenRange	Maps cell coordinations to cell objects
getSelectionCoordinates	Gets a list of text representation of selection coordinates
getCellsByRange	Maps range to cell objects
getHtmlByRange	Gets a html representation of the specified range
setSelection	Sets a selected range to the sheet
setCellValuesByRange	Like `setCellValue`, but set multiple cell values in one shot
setCellFormatByRange	Like `setCellFormat`, but set multiple cell formats in one shot
mergeCells	Merges cells according to range and type
cancelMerge	Cancels merge on a range
getAllSheets	Gets all sheets' raw data
getSheet	Gets a sheet's raw data
addSheet	Adds a new sheet to the workbook
deleteSheet	Deletes the specified sheet
activateSheet	Changes the current working sheet to the specified sheet
setSheetName	Sets the name of the specified sheet
setSheetOrder	Sets the orders of the sheets
scroll	Scroll the current sheet to specified position
addPresence	Add a collabrator presence
removePresence	Removes a collabrator presence

# CommonOptions

Many APIs has an optional options param in the end, it is mainly used for selecting sheet.

Param	Type	Description
index	number	index of sheet in the workbook
id	string	id of sheet in the workbook, if index is set, this field does not take effect

# applyOp

applyOp(ops)

Applys an array of op to the workbook.

This api is typically used in online collabration to sync data.

See Collabration demo (opens new window) for an example usage.

Param	Type	Description
ops	Op[]	op list of current action

# getCellValue

getCellValue(row, column, [options]) -> any

Gets the value of a cell.

Param	Type	Description
row	number	Row index
column	number	Column index
[options]	CommonOptions & { type: string }	`type`: attribute of a Cell, default to 'v'

Returns the specified field of a cell, if options.type is not set, returns the value of v of the Cell.

# setCellValue

setCellValue(row, column, value, [options])

Sets the value of a cell.

Param	Type	Description
row	number	Row index
column	number	Column index
value	any	value to be set
[options]	CommonOptions & { type: string }	`type`: attribute of a Cell, default to 'v'

# clearCell

clearCell(row, column, [options])

Clear the contents of the cell.

Param	Type	Description
row	number	Row index
column	number	Column index
[options]	CommonOptions	common options

# setCellFormat

setCellFormat(row, column, attr, value, [options])

Sets the attributes of a cell, such as font style and background.

Param	Type	Description
row	number	Row index
column	number	Column index
attr	string	attribute of cell
value	any	value of `attr` to be set
[options]	CommonOptions	common options

# freeze

freeze(type, range, [options])

Freezes row and column.

Param	Type	Description
type	string	One of "row", "column" or "both"
range	{ row: number, column: number }	`row`: row index to be freezed `column`: column index to be freezed
[options]	CommonOptions	common options

# insertRowOrColumn

insertRowOrColumn(type, index, count, direction, [options])

Inserts rows or columns at specified position.

Param	Type	Description
type	string	One of "row", "column"
index	number	The starting row or column index of insertion
count	number	Amount of row or column to be inserted
direction	string	One of 'lefttop' or 'rightbottom', default to 'rightbottom'. `lefttop`: insert row to the top of `index` row, or insert column to the left of `index` column. `rightbottom`: insert row to the bottom of `index` row, or insert column to the right of `index` column.
[options]	CommonOptions	common options

# deleteRowOrColumn

deleteRowOrColumn(type, start, end, [options])

Deletes rows or columns at specified range.

Param	Type	Description
type	string	One of "row", "column"
start	number	The starting row or column index to be deleted
end	number	The ending row or column index to be deleted
[options]	CommonOptions	common options

# getRowHeight

getRowHeight(rows, [options])

Gets row heights in batch.

Param	Type	Description
rows	number[]	A list of row indexes, e.g. [1, 4]
[options]	CommonOptions	common options

Returns a map of specified row heights, e.g. { "1": 150, "4": 200 }

# getColumnWidth

getColumnWidth(columns, [options])

Gets column widths in batch.

Param	Type	Description
columns	number[]	A list of column indexes, e.g. [1, 4]
[options]	CommonOptions	common options

Returns a map of specified column widths, e.g. { "1": 150, "4": 200 }

# setRowHeight

setRowHeight(rowInfo, [options])

Sets row heights in batch.

Param	Type	Description
rowInfo	object	A map in the form of [row index]: height. Example: { "1": 150, "4": 200 } means setting height of row index 1 to 150 and height of row index 4 to 200
[options]	CommonOptions	common options

# setColumnWidth

setColumnWidth(columnInfo, [options])

Sets column widths in batch.

Param	Type	Description
columnInfo	object	A map in the form of [column index]: width. Example: { "1": 150, "4": 200 } means setting width of column index 1 to 150 and width of column index 4 to 200
[options]	CommonOptions	common options

# getSelection

getSelection()

Gets current selection.

Returns an array of current selection.

# getFlattenRange

getFlattenRange(range)

Expands given range to cells coordinates.

Param	Type	Description
range	{ row: number[], column: number[] }	A range object

Example:

Input:

{"row": [0, 1], "column": [0, 2]}

Output:

[
	{"r": 0, "c": 0},
	{"r": 0, "c": 1},
	{"r": 0, "c": 2},
	{"r": 1, "c": 0},
	{"r": 1, "c": 1},
	{"r": 1, "c": 2},
]

# getCellsByFlattenRange

getCellsByFlattenRange(range)

Maps cell coordinations to cell objects.

Param	Type	Description
range	{ r: number, c: number }[]	A list of cell coordinations

Returns A list of cell objects according to specified coordinations.

# getSelectionCoordinates

getSelectionCoordinates()

Gets a list of text representation of selection coordinates.

Example output:

["E10:E14", "A7:B13", "C4", "A3", "C6:D9"]

# getCellsByRange

getCellsByRange(range, [options])

Maps range to cell objects.

Param	Type	Description
range	{ row: number[], column: number[] }[]	A list of ranges
[options]	CommonOptions	common options

Returns A list of cell objects according to specified coordinations.

# getHtmlByRange

getHtmlByRange(range, [options])

Gets a html representation of the specified range, typically used for pasting into other excel applications.

Param	Type	Description
range	{ row: number[], column: number[] }[]	A list of ranges
[options]	CommonOptions	common options

Returns HTML string.

# setSelection

setSelection(range, [options])

Sets a selected range to the sheet.

Param	Type	Description
range	{ row: number[], column: number[] }	A list of ranges
[options]	CommonOptions	common options

# setCellValuesByRange

setCellValuesByRange(data, range, [options])

Like setCellValue, but set multiple cell values in one shot.

Param	Type	Description
data	any[][]	two-dimension array of cell values
range	{ row: number[], column: number[] }	range of cells to be set
[options]	CommonOptions	common options

Note: size of data and range must be the same.

# setCellFormatByRange

setCellFormatByRange(attr, value, range, [options])

Like setCellFormat, but set multiple cell formats in one shot.

Param	Type	Description
attr	string	attribute of cell
value	any	value of `attr` to be set
range	{ row: number[], column: number[] }	range of cells to be set
[options]	CommonOptions	common options

# mergeCells

mergeCells(ranges, type, [options])

Merges cells according to range and type.

Param	Type	Description
ranges	{ row: number[], column: number[] }[]	list of ranges of cells to be merged
type	string	One of "merge-all", "merge-horizontal", "merge-vertical"
[options]	CommonOptions	common options

# cancelMerge

cancelMerge(ranges, [options])

Cancels merge on a range.

Param	Type	Description
ranges	{ row: number[], column: number[] }[]	list of ranges of cells to be canceled
[options]	CommonOptions	common options

# getAllSheets

getAllSheets()

Returns all sheets' raw data.

# getSheet

getSheet(options)

Gets a sheet's raw data.

Param	Type	Description
[options]	CommonOptions	common options

Returns a single sheet's raw data.

# addSheet

addSheet()

Adds a new sheet to the workbook.

# deleteSheet

deleteSheet(options)

Deletes the specified sheet.

Param	Type	Description
[options]	CommonOptions	common options

# activateSheet

activateSheet(options)

Changes the current working sheet to the specified sheet.

Param	Type	Description
[options]	CommonOptions	common options

# setSheetName

setSheetName(options)

Sets the name of the specified sheet.

Param	Type	Description
[options]	CommonOptions	common options

# setSheetOrder

setSheetOrder(orderList)

Sets the orders of the sheets.

Param	Type	Description
orderList	object	a map of key: sheet id, value: order number

Example input:


{
	"id_of_sheet_1": 1,
	"id_of_sheet_2": 0,
	"id_of_sheet_3": 2
}

Note: This api will reorder all sheets starting from 0. So, you can just specify the sheet you want to move, by setting a middle order number. For example, assume current sheet order is:

{
	"id_of_sheet_1": 0,
	"id_of_sheet_2": 1,
	"id_of_sheet_3": 2,
}

You want id_of_sheet_3 to be moved between id_of_sheet_1 and id_of_sheet_2, then call this API with:

{
	"id_of_sheet_3": 0.5
}

And the resulting order number will be:

{
	"id_of_sheet_1": 0,
	"id_of_sheet_3": 1,
	"id_of_sheet_2": 2,
}

# scroll

scroll(options)

Scroll the current sheet to specified position.

Param	Type	Description
options	{ scrollLeft?: number, scrollTop?: number, targetRow?: number, targetColumn?: number }	position to be scrolled, all four params are optional

# addPresence

addPresence(presence)

Add a collabrator presence, the collabrator's focusing cell will be shown.

Param	Type	Description
presence	Presence	a Presence object

The Presence object:

Field	Type	Description
sheetId	string	id of the sheet
username	string	username to be shown on the sheet
userId	string	(optional) user id to be used for comparison, if not set, uses `username`
color	string	color of the focusing rect
selection	{ r: number, c: number }	position of the focusing rect

# removePresence

removePresence(presence)

Removes a collabrator presence.

Param	Type	Description
presence	Presence	a Presence object, see addPresence, but only `username` or `userId` is required

← Operation FAQ →