Imperative Spreadsheet API

As an escape hatch, we bundle an imperative API for the Spreadsheet to control spreadsheet states

Imperative API is helpful when you want to trigger state changes without having to modify spreadsheet state manually. These APIs can do multiple actions in one function call

How to use

1. Wrap your Spreadsheet in SpreadsheetProvider

2. Use the useSpreadsheetApi hook to access the imperative API

3. Make sure you have handlers for callbacks such as onChange

import React, { useState } from 'react'
// Add css
import "@rowsncolumns/spreadsheet/dist/spreadsheet.min.css";
import { SpreadsheetProvider, CanvasGrid, useSpreadsheetApi } from "@rowsncolumns/spreadsheet"
import { SheetData, CellData } from "@rowsncolumns/spreadsheet-state"

const MySpreadsheet = () => {
  const api = useSpreadsheetApi()
  const [sheetData, setSheetData] =
      useState<SheetData<CellData>>({});
      
  const onChange = (sheetId, cell, value) => {
    setSheetData(prev => {
      // Update your sheet data
    })
  }
  
  return (
    <>
      <button
        onClick={() => {
          // Get the effective value of a cell
          const value = api?.getActiveSheet()
            ?.getRange({ rowIndex: 1, columnIndex: 1 })
            .getEffectiveValue()
          console.log('Cell value:', value)
          
          // Or chain multiple operations
          api?.getActiveSheet()
              ?.getRange({ rowIndex: 2, columnIndex: 2 })
              .setValue("hello world")
              .setFormat("backgroundColor", "#80FF08")
        }}
      >Update a cell</button>
      <CanvasGrid onChange={onChange} />
    </>
  )
}

const App = () => {
  return (
    <SpreadsheetProvider>
      <MySpreadsheet />
    </SpreadsheetProvider>
  )
}

Batch updating Spreadsheet values

For batch update of values, we recommend using setValues API, so only 1 undo/redo history will be added

const api = useSpreadsheetApi()

api?.getActiveSheet()?.getRange({
  startRowIndex: 1,
  endRowIndex: 2,
  startColumnIndex: 2,
  endColumnIndex: 3,
}).setValues([
  ['foo', 'bar'],
  ['hello', 'world']
])

Export range to clipboard

You can export a range to the clipboard:

const api = useSpreadsheetApi()

api?.exportRange?.(
  {
    startRowIndex: 1,
    endRowIndex: 10,
    startColumnIndex: 1,
    endColumnIndex: 5,
    sheetId: activeSheetId,
  },
  undefined,
  "clipboard"
)

Spreadsheet

getSheet(sheetId: number)

setActiveSheet(sheetId: number)

getActiveSheet()

insertSheet()

Sheet

setActiveCell(cell: CellInterface)

setSelections(selections: GridRange)

setRowHeight(rowIndex: number, dimension: number)

setColumnWidth(columnIndex: number, dimension: number)

getActiveCell()

getSelections()

getSheetId()

getRange(range: GridRange | CellInterface)

hideRow(rowIndexes: number[])

hideColumn(columnIndexes: number[])

showRow(rowIndexes: number[])

showColumn(columnIndexes: number[])

deleteRow(rowIndexes: number[])

deleteColumn(columnIndexes: number[])

insertRow(rowIndex: number, numRows = 1)

insertColumn(columnIndex: number, numColumns = 1)

moveRows(rowIndexes: number[], destinationRow: number)

moveColumns(columnIndexes: number[], destinationColumn: number)

onRequestSearch()

sortRange( selections: SelectionArea[], sortOrder: SortOrder )

sortColumn(columnIndex: number, sortOrder: SortOrder)

CellRange

setValue(text: string) - Set value of a single cell

setValues(values: string[][]) - Set values for a range of cells

getEffectiveValue() - Get the computed/formatted value of a cell

clearFormatting() - Clear all formatting from the range

setFormat(type: T, value: FormattingValue) - Set a specific format property (e.g., backgroundColor, numberFormat, indent)

delete() - Delete the content and formatting of the range

Additional API Methods

exportRange(range: SheetRange, format?: string, destination?: "clipboard") - Export a range to clipboard or other destinations

dispatchEvent(event: Event) - Dispatch DOM events to the spreadsheet grid

commit(): boolean - Commit the in-progress cell edit. Submits when the value is dirty, cancels when clean, no-ops when no edit is active. Does not refocus the grid — focus stays where the user moved it. Returns true if there was an edit to flush.

const api = useSpreadsheetApi()

<CanvasGrid onBlur={() => api?.commit()} />

The same method is available on the ref and via useSpreadsheet():

const ref = useRef<CanvasGridMethods>(null)
ref.current?.commit?.()

const { commit } = useSpreadsheet()
commit?.()

You don't need to call it before changing the active sheet — the grid commits the in-progress edit itself when the sheet changes, writing the value back to the sheet it was typed on. (Formula edits are the exception: switching sheets mid-formula is how a cross-sheet range is picked, so the editor stays open.)