Options
All
  • Public
  • Public/Protected
  • All
Menu

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

Hierarchy

  • ExcelWorksheet

Index

Methods

  • 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>

  • 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(): 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(): 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(): 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(): 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(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(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(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(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.

    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.

    • Optional updateInterval: number

      The time (in milliseconds) between updates (defaults to 1 second).

    Returns DataStream

    The data stream object enabling control of the stream.

  • 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(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(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(): Promise<string>
  • Gets the worksheet name.

    throws

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

    Returns Promise<string>

    The worksheet name.

  • 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>

  • 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

    Returns Promise<void>

  • 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(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(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>

  • setName(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>

Generated using TypeDoc