Spreadsheet state

Use hooks to manage spreadsheet state

Spreadsheet 2 is stateless and controlled React component. Developers can create their own data management model, using React state/Immer or any other state management library.

For quick start we recommend using useSpreadsheetState hook to manage the spreadsheet state. It has all built in features such as undo/redo, internal data models for Sheet, Sheet Data etc.

useSpreadsheetState hook

useSpreadsheetState is a hook that can be installed as a separate package to render a completely stateful Spreadsheet 2 component.

yarn

yarn add @rowsncolumns/spreadsheet-state

npm

npm install @rowsncolumns/spreadsheet-state

Adding the hook

Always use SpreadsheetProvider to wrap your component beforing using the hook.

SheetData is organized as an array of rows, where each row holds an array of cells. To find a specific cell within the sheet, you'd access it via its column and row indexes.

For instance, if you're looking for the cell at column 1 and row 1 (A1) in sheet 999, you'd find its CellData at sheetData[999][1].values[1].

SheetData is set up as a sparse array, meaning it only includes indexes that actually contain CellData. Empty indexes are either undefined/null or represented by empty objects.

Remember, the first row (index 0) and the first column (index 0) of SheetData are reserved for headers and should not be used to store data. So, please refrain from saving any information in sheetData[999][0] or sheetData[999][0].values[0].

import React, { useState } from "react"
import { useSpreadsheetState, SheeData } from "@rowsncolumns/spreadsheet-state"
import { functionDescriptions, functions } from "@rowsncolumns/functions";
import { 
  SpreadsheetProvider,
  CanvasGrid, 
  CellData, 
  Sheet, 
  EmbeddedChart, 
  EmbeddedObject, 
  TableView 
} from "@rowsncolumns/spreadsheet"

const MySpreadSheet = () => {
  const [sheets, onChangeSheets] = useState<Sheet[]>([]);
  const [sheetData, onChangeSheetData] = useState<SheetData<CellData>>({});
  const [scale, onChangeScale] = useState(1);
  const [charts, onChangeCharts] = useState<EmbeddedChart[]>([]);
  const [embeds, onChangeEmbeds] = useState<EmbeddedObject[]>([]);
  const [tables, onChangeTables] = useState<TableView[]>([]);
  const [conditionalFormats, onChangeConditionalFormats] = useState<
    ConditionalFormatRule[]
  >([]);
  const [protectedRanges, onChangeProtectedRanges] = useState<ProtectedRange[]>(
    []
  );
  
  const {
    activeCell,
    activeSheetId,
    selections,
    rowCount,
    columnCount,
    frozenColumnCount,
    frozenRowCount,
    spreadsheetColors,
    spreadsheetTheme,
    rowMetadata,
    columnMetadata,
    merges,
    canRedo,
    redo,
    canUndo,
    undo,
    getCellData,
    getSheetName,
    getSheetId,
    getUserEnteredFormat,
    onRequestResultPreview,
    onChangeActiveCell,
    onChangeActiveSheet,
    onSelectNextSheet,
    onSelectPreviousSheet,
    onChangeSelections,
    onChange,
    onDelete,
    onChangeFormatting,
    onClearFormatting,
    onUnMergeCells,
    onMergeCells,
    onResize,
    onChangeBorder,
    onChangeDecimals,
    onChangeSheetTabColor,
    onRenameSheet,
    onDeleteSheet,
    onShowSheet,
    onHideSheet,
    onProtectSheet,
    onUnProtectSheet,
    onMoveSheet,
    onCreateNewSheet,
    onDuplicateSheet,
    onHideColumn,
    onShowColumn,
    onHideRow,
    onShowRow,
    onFill,
    onFillRange,
    onMoveChart,
    onResizeChart,
    onMoveEmbed,
    onResizeEmbed,
    onDeleteRow,
    onDeleteColumn,
    onDeleteCellsShiftUp,
    onDeleteCellsShiftLeft,
    onInsertCellsShiftRight,
    onInsertCellsShiftDown,
    onInsertRow,
    onInsertColumn,
    onMoveColumns,
    onMoveRows,
    onMoveSelection,
    onSortColumn,
    onSortRange,
    onFilterRange,
    onCopy,
    onPaste,
  } = useSpreadsheetState({
     sheets,
     sheetData,
     tables,
     functions,
     namedRanges,
     theme,
     colorMode,
     conditionalFormats,
     onChangeSheets,
     onChangeSheetData,
     onChangeEmbeds,
     onChangeCharts,
     onChangeTables,
     onChangeNamedRanges,
     onChangeTheme,
     onChangeHistory(patches) {
      onBroadcastPatch(patches);
     },
     onChangeProtectedRanges,
     onChangeConditionalFormats,
  })

  return (
    <CanvasGrid
      {...spreadsheetColors}
      stickyEditor={true}
      scale={scale}
      conditionalFormats={conditionalFormats}
      sheetId={activeSheetId}
      rowCount={rowCount}
      columnCount={columnCount}
      frozenColumnCount={frozenColumnCount}
      frozenRowCount={frozenRowCount}
      rowMetadata={rowMetadata}
      columnMetadata={columnMetadata}
      activeCell={activeCell}
      selections={selections}
      theme={spreadsheetTheme}
      merges={merges}
      charts={charts}
      embeds={embeds}
      tables={tables}
      protectedRanges={protectedRanges}
      onChangeActiveCell={onChangeActiveCell}
      onChangeSelections={onChangeSelections}
      onChangeActiveSheet={onChangeActiveSheet}
      onRequestResultPreview={onRequestResultPreview}
      onSelectNextSheet={onSelectNextSheet}
      onSelectPreviousSheet={onSelectPreviousSheet}
      onChangeFormatting={onChangeFormatting}
      onHideColumn={onHideColumn}
      onShowColumn={onShowColumn}
      onHideRow={onHideRow}
      onShowRow={onShowRow}
      onDelete={onDelete}
      onClearContents={onDelete}
      onFill={onFill}
      onFillRange={onFillRange}
      onResize={onResize}
      onMoveChart={onMoveChart}
      onMoveEmbed={onMoveEmbed}
      onResizeChart={onResizeChart}
      onResizeEmbed={onResizeEmbed}
      onDeleteRow={onDeleteRow}
      onDeleteColumn={onDeleteColumn}
      onDeleteCellsShiftUp={onDeleteCellsShiftUp}
      onDeleteCellsShiftLeft={onDeleteCellsShiftLeft}
      onInsertCellsShiftRight={onInsertCellsShiftRight}
      onInsertCellsShiftDown={onInsertCellsShiftDown}
      onInsertRow={onInsertRow}
      onInsertColumn={onInsertColumn}
      onMoveColumns={onMoveColumns}
      onMoveRows={onMoveRows}
      onMoveSelection={onMoveSelection}
      onCreateNewSheet={onCreateNewSheet}
      functionDescriptions={functionDescriptions}
      getSheetName={getSheetName}
      getSheetId={getSheetId}
      getCellData={getCellData}
      onChange={onChange}
      onUndo={undo}
      onRedo={redo}
      onSortColumn={onSortColumn}
      onSortRange={onSortRange}
      onFilterRange={onFilterRange}
      onClearFormatting={onClearFormatting}
      onCopy={onCopy}
      onPaste={onPaste}
    />
  )
      
}

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

Creating initial row data from primitives

To create initial sheet data you can use the helper function createRowDataFromArray

import { createRowDataFromArray } from "@rowsncolumns/spreadsheet-state";

const [sheetData, onChangeSheetData] = useState<SheetData<CellData>>({
  1: createRowDataFromArray(
    [[], [null, "helo world", null, "=SUM(4,4)"]],
    1
  ),
});