Interface ISheetModel

Interface used to access the SheetModel API. When interactive the cells it is advisable to use ISheetRange that is returned from the getRange method.

See

interface ISheetModel {
    addListener: ((listener) => RemoveListener);
    isISheetModel: true;
    addDrawing(options?): IDrawingModel<DrawingJSON>;
    addEventListener(type, listener, options?): RemoveListener;
    addImage(image, options?): Promise<IDrawingImageModel>;
    addRangeListener(address, listener, options?): RemoveListener;
    autoFill(ranges, direction, options?): ((offset) => CellAddressValuePair);
    autoFit(addresses?, options?): void;
    autoFitColumns(addresses?, options?): void;
    autoFitRows(addresses?, options?): void;
    clear(addresses?, clearType?): CellRangeCoords;
    close(): void;
    copyCells(destination, source, options?): void;
    countItems(address?, types?): Record<CountCellItemType, number>;
    createCellTemplate(update, coords?): ICellModel;
    delete(): void;
    emptyCell(): ICellModel;
    find(findText, options?): CoordsValuePair<ICellModel>;
    findNextBoundary(currentCoords, direction, first?, limit?): number;
    findRegionBounds(selection?, expandIfSelected?, alwaysSelectAll?): CellRangeCoords;
    fromJSON(json): void;
    getActiveAddressCoords(): CellCoords;
    getActiveAddressCoordsA1(): string;
    getBounds(address): Bounds;
    getCell(address?): ICellModel;
    getCellBounds(includeHeaders?, includeHidden?): CellRangeCoords;
    getColumnHeaders(): ISheetHeadersModel<IColumnHeader, ColumnHeaderJSON, ColumnHeaderUpdate>;
    getColumnWidthAsFontUnit(pixelWidth): number;
    getCopyableItems(ranges): CopyableItemSource[];
    getDrawingsCount(): number;
    getMaxCellBounds?(): CellRangeCoords;
    getMerges(addresses?): CellRangeCoords[];
    getName(): string;
    getOverflows(address?, includeHidden?): OverflowedCell[];
    getProtectedRanges(addresses?): RangeProtection[];
    getProtection(): Readonly<SheetProtection>;
    getRange(address?): ISheetRange;
    getRowHeaders(): ISheetHeadersModel<IRowHeader, RowHeaderJSON, RowHeaderUpdate>;
    getSelectionBounds?(): CellRangeCoords;
    getTabColor(): AdjustedColor;
    getTables(addresses?, includeHidden?): ITableModel[];
    getView(): ISheetView;
    hideColumns(addresses?, options?): void;
    hideRows(addresses?, options?): void;
    insertCells(addresses?, direction?, options?): void;
    is1904DateSystem(): boolean;
    isClosed(): boolean;
    merge(addresses?, mergeType?): CellRangeCoords;
    protectRanges(addresses?, rangeProtection?): CellRangeCoords;
    pushTransaction(description?): ITransaction;
    removeCells(addresses?, direction?, options?): void;
    removeTables(address?, options?): number;
    searchDrawings(options?): IDrawingModel<DrawingJSON>[];
    setCell(address, value, options?): ICellModel;
    setCellPairs(pairs, options?): void;
    setProtection(protection): void;
    setTabColor(color): void;
    setView(view): void;
    sharedResources(): ISharedResourceCollection;
    sheetStyle(): ISheetStyle;
    sort(address?, options?): void;
    toJSON(): Promise<SheetJSON>;
    unhideColumns(addresses?, options?): void;
    unhideRows(addresses?, options?): void;
    unmerge(addresses?): CellRangeCoords;
    unprotectRanges(addresses?, password?): CellRangeCoords;
    upsertTable(address, update?, options?): ITableModel;
}

Hierarchy (view full)

Implemented by

Properties

addListener isISheetModel

Methods

addDrawing addEventListener addImage addRangeListener autoFill autoFit autoFitColumns autoFitRows clear close copyCells countItems createCellTemplate delete emptyCell find findNextBoundary findRegionBounds fromJSON getActiveAddressCoords getActiveAddressCoordsA1 getBounds getCell getCellBounds getColumnHeaders getColumnWidthAsFontUnit getCopyableItems getDrawingsCount getMaxCellBounds? getMerges getName getOverflows getProtectedRanges getProtection getRange getRowHeaders getSelectionBounds? getTabColor getTables getView hideColumns hideRows insertCells is1904DateSystem isClosed merge protectRanges pushTransaction removeCells removeTables searchDrawings setCell setCellPairs setProtection setTabColor setView sharedResources sheetStyle sort toJSON unhideColumns unhideRows unmerge unprotectRanges upsertTable

Properties

addListener

addListener: ((listener) => RemoveListener)

All for listen to changes to the sheet. To remove listener call the returned function.

Type declaration

Returns

An unsubscribe function

Readonly isISheetModel

isISheetModel: true

Used for runtime reflection.

Methods

addDrawing

addEventListener

addImage

addRangeListener

autoFill

autoFit

autoFitColumns

autoFitRows

clear

close

  • close(): void

  • Free all resources associated with the sheet and mark as read-only.

    Returns void

    Remarks

    This is called by workbooks when the workbook is is closed. This is different than delete which removes the sheet from the workbook.

    See

    ISheetModel.addListener for closeEvent

copyCells

countItems

  • countItems(address?, types?): Record<CountCellItemType, number>

  • Returns a list of items for the given types. If no options is provided then it will return counts for the select range. If there given address is empty then this will return null.

    Parameters

    • Optional address: string | CellCoords | Partial<CellRangeCoords>
    • Optional types: Partial<Record<CountCellItemType, number | boolean>>

      The types will be used to filter the results. If a number is provided this will be a max number If a boolean is provided this will be a statement to include/exclude

    Returns Record<CountCellItemType, number>

    Remarks

    Some uses cases for this are checking for data to delete a sheet, checking for multiple values for merges, or displaying stats.

createCellTemplate

  • createCellTemplate(update, coords?): ICellModel

  • Creates a cell that is not including at a coordinate but adopts all rendering. Useful for previewing values such as tooltip. If the coords are passed it will return a disconnect cell update for that value.

    Parameters

    Returns ICellModel

    a detached ICellModel

    Remarks

    This does not add to the sheet but provides a standalone ICellModel.

delete

  • delete(): void

  • This removes from a container if one is in use.

    Returns void

emptyCell

find

findNextBoundary

  • findNextBoundary(currentCoords, direction, first?, limit?): number

  • Returns the next coord in a given direction that has a value within the sheet.

    Parameters

    Returns number

    Remarks

    This will skip cells with styling but no value.

findRegionBounds

  • findRegionBounds(selection?, expandIfSelected?, alwaysSelectAll?): CellRangeCoords

  • Returns a CellRangeCoords that represents the 'closest' contentful data

    Parameters

    • Optional selection: CellSelection
    • Optional expandIfSelected: boolean
    • Optional alwaysSelectAll: boolean

    Returns CellRangeCoords

fromJSON

  • fromJSON(json): void

  • Load the

    Parameters

    Returns void

    See

    SheetJSON state into memory. The current state will be lost.

getActiveAddressCoords

getActiveAddressCoordsA1

  • getActiveAddressCoordsA1(): string

  • Returns the active address as an A1 style reference

    Returns string

getBounds

getCell

  • getCell(address?): ICellModel

  • Returns an immutable cell from the specified address. This can be either a CellCoord or a case insensitive 'a1' style string reference. If no address is specified then the current selection is used.

    Parameters

    Returns ICellModel

    Remarks

    Will never return null.

getCellBounds

  • getCellBounds(includeHeaders?, includeHidden?): CellRangeCoords

  • Returns the minimum bounds that includes all cell data.

    Parameters

    • Optional includeHeaders: boolean

      include custom headers in sizing.

    • Optional includeHidden: boolean

      include cells in hidden headers.

    Returns CellRangeCoords

    Remarks

    This is guaranteed to always return at least a single cell. This will returns cells that have either styling or a value.

    Default Value

    false

    Default Value

    false

getColumnHeaders

getColumnWidthAsFontUnit

  • getColumnWidthAsFontUnit(pixelWidth): number

  • Returns the column width as a font unit. This is used as an informative tooltip

    Parameters

    • pixelWidth: number

    Returns number

getCopyableItems

getDrawingsCount

  • getDrawingsCount(): number

  • Return the total drawings available. This is useful for quick escape of searching drawings.

    Returns number

Optional getMaxCellBounds

getMerges

getName

  • getName(): string

  • A display name for the sheet.

    Returns string

    Remarks

    This is readonly from the sheet and is set via the container, usually an IWorkbook.

getOverflows

  • getOverflows(address?, includeHidden?): OverflowedCell[]

  • Returns all overflowed cells with the given range. If a null range is provided then all overflowed calls will be returned.

    Parameters

    • Optional address: CellRangeCoordsAddress

      A string or CellRangeCoords.

    • Optional includeHidden: boolean

      defaults to false. If overflowed should also return hidden. TODO - This needs to be moved to view since it's dependant on scale

    Returns OverflowedCell[]

getProtectedRanges

getProtection

getRange

  • getRange(address?): ISheetRange

  • Returns a range of cells for the specified range. These allow for interaction with the cells.

    If no address is specified then this will use the intersection of the cell bounds and the selection bounds.

    Parameters

    Returns ISheetRange

    See

    ISheetRange

getRowHeaders

Optional getSelectionBounds

getTabColor

getTables

getView

hideColumns

hideRows

insertCells

  • insertCells(addresses?, direction?, options?): void

  • insert cells at the range. By default the values will direction in whichever direction the selection shape is smaller. For example a selection with a 5 column by 1 row will direction Down (rows). Note - This will error if direction data will fall outsize of the max sheet size

    Parameters

    Returns void

    Default Value

    'based on the selection shape'

is1904DateSystem

  • is1904DateSystem(): boolean

  • Determine if dates should be 1904 instead of 1900 based

    Returns boolean

isClosed

  • isClosed(): boolean

  • Returns true if the sheet has been closed.

    Returns boolean

merge

protectRanges

pushTransaction

  • pushTransaction(description?): ITransaction

  • Begins a transaction but puts it onto a global stack. This will batch all 'transactional' changes until they have all be committed or rolled back.

    Parameters

    • Optional description: string

    Returns ITransaction

    Remarks

    Any changes that are made to this object will be reflected in the local sheet but not committed to the transactional store until the batch is popped.

removeCells

  • removeCells(addresses?, direction?, options?): void

  • Remove cells at a range. This is different than clearing cells in that it direction value up or left.

    Parameters

    Returns void

    Default Value

    'based on the selection shape'

removeTables

searchDrawings

setCell

setCellPairs

  • setCellPairs(pairs, options?): void

  • Sets a collection of Cells in a single batch/transaction.

    This allows for an array of different values to be set as a single update. This is useful for non-contiguous updates and greatly improves performance over setting individually.

    Parameters

    Returns void

    See

    CellAddressValuePair

setProtection

  • setProtection(protection): void

  • Set the protection. This will merge with existing protection.

    Parameters

    Returns void

    Remarks

    Pass null to clear all protections.

setTabColor

  • setTabColor(color): void

  • See the tab color for the sheet.

    Parameters

    Returns void

    Remarks

    The tab color is not used by the sheet but rather by the workbook.

setView

  • setView(view): void

  • Set the view specific properties. This will merge with existing view.

    Parameters

    Returns void

sharedResources

sheetStyle

sort

  • sort(address?, options?): void

  • Sort the given range.

    Parameters

    Returns void

    Remarks

    The sorting of various data types has a default priority of of boolean > string > number and dates are treated as numbers.

    See

    RangeAutoFillOptions

toJSON

  • toJSON(): Promise<SheetJSON>

  • Returns a JSON representation of the sheet. This can be used with

    Returns Promise<SheetJSON>

    See

    fromJSON to reload or passed into the constructor.

unhideColumns

unhideRows

unmerge

  • unmerge(addresses?): CellRangeCoords

  • UnMerges all cells within the selection ranges. If the ranges don't contain merges or the ranges overlap then this operation will throw an exception.

    Parameters

    Returns CellRangeCoords

    the intersection of all ranges applied

    Default Value

    to the current selection

unprotectRanges

upsertTable

  • upsertTable(address, update?, options?): ITableModel

  • Create a table at the given range. If the range is already a table then it will be updated.

    Parameters

    • address: CellRangeCoordsAddress
    • Optional update: Partial<{
          autoFilter?: string | AutoFilter | Partial<{
              filterColumns?: any;
              ref?: string;
              sortState?: any;
          }>;
          columns?: string | TableColumn[] | (string | TableColumn | Partial<{
              name: string;
          }>)[];
          displayName?: string;
          headerRowCount?: string | number;
          name: string;
          ref: string | CellRangeCoords | Partial<{
              colEnd: string | number;
              colStart: string | number;
              rowEnd: string | number;
              rowStart: string | number;
          }>;
          sortState?: any;
          styleInfo?: string | TableStyleInfo | Partial<{
              name: string;
              showColumnStripes?: string | Partial<boolean>;
              showFirstColumn?: string | Partial<boolean>;
              showLastColumn?: string | Partial<boolean>;
              showRowStripes?: string | Partial<boolean>;
          }>;
          totalsRowCount?: string | number;
      }>
    • Optional options: ModifyTableSetOptions

    Returns ITableModel

    Remarks

    If no update is passed in then a default table will be create. If a ref is not specified then the range will be used as the ref. If a ref is specified then the rowStart of ref must match the range or an error will occur.

Settings

Member Visibility

Theme

On This Page

addListenerisISheetModeladdDrawingaddEventListeneraddImageaddRangeListenerautoFillautoFitautoFitColumnsautoFitRowsclearclosecopyCellscountItemscreateCellTemplatedeleteemptyCellfindfindNextBoundaryfindRegionBoundsfromJSONgetActiveAddressCoordsgetActiveAddressCoordsA1getBoundsgetCellgetCellBoundsgetColumnHeadersgetColumnWidthAsFontUnitgetCopyableItemsgetDrawingsCountgetMaxCellBoundsgetMergesgetNamegetOverflowsgetProtectedRangesgetProtectiongetRangegetRowHeadersgetSelectionBoundsgetTabColorgetTablesgetViewhideColumnshideRowsinsertCellsis1904DateSystemisClosedmergeprotectRangespushTransactionremoveCellsremoveTablessearchDrawingssetCellsetCellPairssetProtectionsetTabColorsetViewsharedResourcessheetStylesorttoJSONunhideColumnsunhideRowsunmergeunprotectRangesupsertTable