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