Interface ExcelWorksheet

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

Hierarchy

ExcelWorksheet

Properties

getName

getName: (() => Promise<string>)

Type declaration

- (): Promise<string>
- Gets the worksheet name.
  
  Returns
  The worksheet name.
  
  Throws
  AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.
  
  Returns Promise<string>

setName

setName: ((newWorksheetName: string) => Promise<void>)

Type declaration

- (newWorksheetName: string): Promise<void>
- Sets the worksheet name.
  
  Throws
  ParameterError if an invalid name value is provided.
  
  Throws
  AdapterError if an exception is thrown by the adapter process, typically by the Excel PIA.
  Parameters
  - newWorksheetName: string
  Returns Promise<void>

Methods

activate

activate(): Promise<void>
Activates the worksheet bringing it in to focus (equivalent to clicking the sheet's tab in Excel).

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>
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>
addEventListener(eventName: "activate", listener: WorksheetActivatedEventListener): 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>
addEventListener(eventName: "change", listener: WorksheetChangedEventListener): 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>
addEventListener(eventName: "deactivate", listener: WorksheetDeactivatedEventListener): 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 automatic calculation is turned off.

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, repeater: (() => Promise<CellValue>), updateInterval?: number): DataStream
Creates a data stream that updates the value(s) of a cell range at a given interval.

Returns
The data stream object enabling control of the stream.

Throws
ApiError if an exception is thrown when registering the data stream.
Parameters
- cellRange: string
  
  Any valid cell notation (addresses, names, etc) that defines the cells that the data stream will be output to.
- 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>
- Optional updateInterval: number
  
  The time (in milliseconds) between updates (defaults to 1 second).
Returns DataStream

delete

delete(): Promise<void>
Deletes the worksheet.

After calling this function do not attempt to use this worksheet again. Doing so will likely result in an AdapterError since the underlying object/file is no longer available.

Attempting to delete the only worksheet in a workbook will result in an AdapterError being thrown.

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

Returns Promise<void>

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.

Returns
The cells defined by the range.

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[]>

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, newCellRangeName: string): Promise<void>
Assigns a name to a range of cells in the worksheet which can then be used to specify the same range in other functions.

Adding a name does not remove any other names that have already been set for the specified range.

Throws
ParameterError if an invalid name value is provided.

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).
- newCellRangeName: string
  
  The name to assign to the cell range.
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). The provided cell range determines the starting point for the update (i.e. the first cell in the range).

If only a single value is provided in the values map then the entire provided cell range will be updated with this value.

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. The first cell in the range will be used as the starting point for the update.
- 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>

Interface ExcelWorksheet

Hierarchy

Index

Properties

Methods

Properties

getName

Type declaration

Returns

Throws

Returns Promise<string>

setName

Type declaration

Throws

Throws

Parameters

newWorksheetName: string

Returns Promise<void>

Methods

activate

Throws

Returns Promise<void>

addEventListener

Throws

Throws

Parameters

eventName: string

listener: ExcelWorksheetEventListener

Returns Promise<void>

Throws

Parameters

eventName: "activate"

listener: WorksheetActivatedEventListener

Returns Promise<void>

Throws

Parameters

eventName: "change"

listener: WorksheetChangedEventListener

Returns Promise<void>

Throws

Parameters

eventName: "deactivate"

listener: WorksheetDeactivatedEventListener

Returns Promise<void>

calculate

Throws

Returns Promise<void>

clearAllCellFormatting

Throws

Returns Promise<void>

clearAllCellValues

Throws

Returns Promise<void>

clearAllCells

Throws

Returns Promise<void>

clearCellFormatting

Throws

Parameters

cellRange: string

Returns Promise<void>

clearCellValues

Throws

Parameters

cellRange: string

Returns Promise<void>

clearCells

Throws

Parameters

cellRange: string

Returns Promise<void>

createDataStream

Returns

Throws

Parameters

cellRange: string

repeater: (() => Promise<CellValue>)

Returns Promise<CellValue>

Optional updateInterval: number

Returns DataStream

`Optional` updateInterval: number

`Optional` criteria1: string

`Optional` criteria2: string

`Optional` visibleDropDown: boolean