Options
All
  • Public
  • Public/Protected
  • All
Menu

Interface ExcelWorksheet

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

Hierarchy

  • ExcelWorksheet

Index

Methods

Methods

activate

  • activate(): Promise<void>

  • Activates the worksheet and the brings 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 native adapter process, typically by the Excel PIA.

    Returns Promise<void>

addEventListener

  • Adds a listener for the specified worksheet-level event.

    throws

    AdapterError if an exception is thrown by the native 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 native 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 native 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 native 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 native 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 native 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 native 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 native 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 native 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 native 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 native 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>

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 native 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 native 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 native 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.

    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 native 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 native adapter process, typically by the Excel PIA.

    Returns Promise<void>

removeEventListener

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

    throws

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

    throws

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

    Parameters

    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 native 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 native 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: string[][]): 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 native 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: string[][]

      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>

Generated using TypeDoc