ExcelWorksheet | OpenFin Excel Integration API Reference

An object that represents a worksheet in an open workbook in Excel.

Hierarchy

ExcelWorksheet

Index

Methods

activate

activate(): Promise<void>

Activates the worksheet bringing it in to focus (equivalent to clicking the sheet's tab in Excel).

Note: activating a worksheet deactivates the currently activated worksheet.

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

Returns Promise<void>

addEventListener

addEventListener(eventName: string, listener: ExcelWorksheetEventListener): Promise<void>
addEventListener(eventName: "activate", listener: WorksheetActivatedEventListener): Promise<void>
addEventListener(eventName: "change", listener: WorksheetChangedEventListener): Promise<void>
addEventListener(eventName: "deactivate", listener: WorksheetDeactivatedEventListener): Promise<void>

Adds a listener for the specified worksheet-level event.

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

throws
EventError if specified event is not supported.

Parameters
- eventName: string
  
  Type of the worksheet-level event to listen for.
- listener: ExcelWorksheetEventListener
  
  Function called when the specified event occurs.
Returns Promise<void>
Adds a listener that is called when the worksheet is activated.

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

Parameters
- eventName: "activate"
  
  Name of the event fired when the worksheet is activated.
- listener: WorksheetActivatedEventListener
  
  Listener function called when the event occurs.
Returns Promise<void>
Adds a listener that is called when when cells in the worksheet are changed by the user or by an external link (but not during a recalculation).

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

Parameters
- eventName: "change"
  
  Name of the event fired when the worksheet is changed.
- listener: WorksheetChangedEventListener
  
  Listener function called when the event occurs.
Returns Promise<void>
Adds a listener that is called when the worksheet is deactivated (i.e. another worksheet is activated).

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

Parameters
- eventName: "deactivate"
  
  Name of the event fired when the worksheet is deactivated.
- listener: WorksheetDeactivatedEventListener
  
  Listener function called when the event occurs.
Returns Promise<void>

calculate

calculate(): Promise<void>

Triggers calculation for the worksheet.

Only necessary when the calculation mode is not set to Automatic.

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

Returns Promise<void>

clearAllCellFormatting

clearAllCellFormatting(): Promise<void>

Clears formatting for all of the cells in the worksheet.

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

Returns Promise<void>

clearAllCellValues

clearAllCellValues(): Promise<void>

Clears values for all of the cells in the worksheet.

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

Returns Promise<void>

clearAllCells

clearAllCells(): Promise<void>

Clears values and formatting for all of the cells in the worksheet.

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

Returns Promise<void>

clearCellFormatting

clearCellFormatting(cellRange: string): Promise<void>

Clears formatting for a range of cells in the worksheet.

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

Parameters
- cellRange: string
  
  Any valid cell notation (addresses, names, etc) that defines one or more cells in the worksheet. Multiple cell ranges can be provided separated by commas (e.g. A1,B2:C3).
Returns Promise<void>

clearCellValues

clearCellValues(cellRange: string): Promise<void>

Clears values for a range of cells in the worksheet.

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

Parameters
- cellRange: string
  
  Any valid cell notation (addresses, names, etc) that defines one or more cells in the worksheet. Multiple cell ranges can be provided separated by commas (e.g. A1,B2:C3).
Returns Promise<void>

clearCells

clearCells(cellRange: string): Promise<void>

Clears values and formatting for a range of cells in the worksheet.

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

Parameters
- cellRange: string
  
  Any valid cell notation (addresses, names, etc) that defines one or more cells in the worksheet. Multiple cell ranges can be provided separated by commas (e.g. A1,B2:C3).
Returns Promise<void>

createDataStream

createDataStream(cellRange: string, updateInterval: number, repeater: () => Promise<CellValue>): DataStream

Creates a datastream that updates the value(s) of a cell range at a given interval.

throws
ApiError if an exception is thrown when registering the datastream.

Parameters
- cellRange: string
  
  Any valid cell notation (addresses, names, etc) that defines the cells that the datastream will be output to.
- updateInterval: number
  
  The time (in milliseconds) between updates.
- repeater: () => Promise<CellValue>
  
  Function that is run after every interval, returns the value that the cellRange will be updated with.
  - - (): Promise<CellValue>
    - Returns Promise<CellValue>
Returns DataStream
The datastream object enabling control of the stream.

filterCells

filterCells(cellRange: string, columnIndex: number, filterOperator: ExcelFilterOperator, criteria1?: string, criteria2?: string, visibleDropDown?: boolean): Promise<void>

Filters a table of cells in the worksheet.
The cellRange should define the table headers, and columnIndex will determine which column to apply the filter to (1 being the left-most column in the range).

Set filterOperator to And and Or with criteria1 and criteria2 to construct compound criteria. When using only a single criteria, filterOperator should be set to Or otherwise the filter may not display the expected results. When setting criteria use simple logic rules e.g. >100, <=50000, =Account1, <>Account2.

Alternatively use the other filter operators for predefined criteria:
- Top10Items/Bottom10Items: Highest/lowest valued items displayed (specify number of items in criteria1).
- Top10Percent/Bottom10Percent: Highest/lowest valued items displayed (specify percentage in criteria1).
- FilterValues: Display cells that have a specific value (specify value in criteria1).
throws
AdapterError if filter parameters have been incorrectly specified, or if an exception is thrown by the adapter process, typically by the Excel PIA.
Parameters
- cellRange: string
  
  Any valid cell notation (addresses, names, etc) that defines the cells or column headers that the filter will be applied to.
- columnIndex: number
  
  The column on which the filter will be applied (1 being the left-most column in the range).
- filterOperator: ExcelFilterOperator
  
  The type of operator to apply to the filter.
- Optional criteria1: string
  
  The first criteria to apply.
- Optional criteria2: string
  
  The second criteria to apply (use with criteria1 and filterOperator to construct compound criteria).
- Optional visibleDropDown: boolean
  
  Determines whether to display the drop down icon against the filtered column (defaults to true).
Returns Promise<void>

getCells

getCells(cellRange: string): Promise<Cell[]>

Get a range of cells in the worksheet.

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

Parameters
- cellRange: string
  
  Any valid cell notation (addresses, names, etc) that defines one or more cells in the worksheet. Multiple cell ranges can be provided separated by commas (e.g. A1,B2:C3).
Returns Promise<Cell[]>
The cells defined by the range.

getName

getName(): Promise<string>

Gets the name of the worksheet.

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

Returns Promise<string>
The worksheet name.

getProperty

getProperty<T>(propertyName: string): Promise<null | T>

Gets the value of a property from the Excel PIA Worksheet interface.

Refer to the Worksheet interface properties documentation for a list of valid property names. The property name is case-sensitive so be sure to enter it exactly as it appears in the documentation.

Note: only properties that are a value type or a string are supported and can be retrieved using this function.

throws
AdapterError if the requested property is not a supported type, or if an exception is thrown by the adapter process, typically by the Excel PIA.

Type parameters
- T = any
  
  The type of the property whose value to get.
Parameters
- propertyName: string
  
  The name of the property whose value to get.
Returns Promise<null | T>
The value of the property or null if the requested property does not exist.

protect

protect(): Promise<void>

Protects the worksheet so that it cannot be modified.

throws
AdapterError if the requested property is not a supported type, or if an exception is thrown by the adapter process, typically by the Excel PIA.

Returns Promise<void>

removeEventListener

removeEventListener(listener: ExcelWorksheetEventListener): Promise<void>

Removes a previously added listener for the specified worksheet-level event.

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

throws
EventError if provided listener function does not match any added event listener.

Parameters
- listener: ExcelWorksheetEventListener
  
  Function that was provided as the listener when addEventListener was called.
Returns Promise<void>

setCellFormatting

setCellFormatting(cellRange: string, formatting: CellFormatting): Promise<void>

Sets the formatting for a range of cells in the worksheet.

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

Parameters
- cellRange: string
  
  Any valid cell notation (addresses, names, etc) that defines one or more cells in the worksheet. Multiple cell ranges can be provided separated by commas (e.g. A1,B2:C3).
- formatting: CellFormatting
  
  An object that defines the formatting options to set on the cells.
Returns Promise<void>

setCellName

setCellName(cellRange: string, name: string): Promise<void>

Sets the name of a range of cells in the worksheet.

Once set, the name can then be used as a cell range in other functions.

throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.

Parameters
- cellRange: string
  
  Any valid cell notation (addresses, names, etc) that defines one or more cells in the worksheet. Multiple cell ranges can be provided separated by commas (e.g. A1,B2:C3).
- name: string
  
  The value to use as the name.
Returns Promise<void>

setCellValues

setCellValues(cellRange: string, valuesMap: CellValue[][]): Promise<void>

Sets the values for a range of cells in the worksheet.
The function takes a values map which is a two-dimensional array where the outer array defines the number of rows, and the inner arrays define the values for each column (cell).

For example, to set the cell values across two rows and two columns:
```
await worksheet.setCells('A1:B2', [['Account 1', 1500], ['Account 2', 3000]]);
```
throws
AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.
Parameters
- cellRange: string
  
  Any valid cell notation (addresses, names, etc) that defines one or more cells in the worksheet. Multiple cell ranges can be provided separated by commas (e.g. A1,B2:C3).
- valuesMap: CellValue[][]
  
  A two-dimensional array where the outer array defines the number of rows, and the inner arrays define the values for each column (cell). *
Returns Promise<void>

Hierarchy

Index

Methods

Methods

activate

Returns Promise<void>

addEventListener

Parameters

eventName: string

listener: ExcelWorksheetEventListener

Returns Promise<void>

Parameters

eventName: "activate"

listener: WorksheetActivatedEventListener

Returns Promise<void>

Parameters

eventName: "change"

listener: WorksheetChangedEventListener

Returns Promise<void>

Parameters

eventName: "deactivate"

listener: WorksheetDeactivatedEventListener

Returns Promise<void>

calculate

Returns Promise<void>

clearAllCellFormatting

Returns Promise<void>

clearAllCellValues

Returns Promise<void>

clearAllCells

Returns Promise<void>

clearCellFormatting

Parameters

cellRange: string

Returns Promise<void>

clearCellValues

Parameters

cellRange: string

Returns Promise<void>

clearCells

Parameters

cellRange: string

Returns Promise<void>

createDataStream

Parameters

cellRange: string

updateInterval: number

repeater: () => Promise<CellValue>

Returns Promise<CellValue>

Returns DataStream

filterCells

Parameters

cellRange: string

columnIndex: number

filterOperator: ExcelFilterOperator

Optional criteria1: string

Optional criteria2: string

Optional visibleDropDown: boolean

Returns Promise<void>

getCells

Parameters

cellRange: string

Returns Promise<Cell[]>

getName

Returns Promise<string>

getProperty

Type parameters

T = any

Parameters

propertyName: string

Returns Promise<null | T>

protect

Returns Promise<void>

removeEventListener

Parameters

listener: ExcelWorksheetEventListener

Returns Promise<void>

setCellFormatting

Parameters

cellRange: string