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

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,
  CellXfs
} from "@rowsncolumns/spreadsheet-state"
import { functionDescriptions, functions } from "@rowsncolumns/functions";
import {
  SpreadsheetProvider,
  CanvasGrid,
  CellData,
  Sheet,
  EmbeddedChart,
  EmbeddedObject,
  TableView,
  PivotTable
} from "@rowsncolumns/spreadsheet"

const MySpreadSheet = () => {
  const [sheets, onChangeSheets] = useState<Sheet[]>([]);
  const [sheetData, onChangeSheetData] = useState<SheetData<CellData>>({});
  const [cellXfs, onChangeCellXfs] = useState<CellXfs | null | undefined>(new Map());
  const [scale, onChangeScale] = useState(1);
  const [charts, onChangeCharts] = useState<EmbeddedChart[]>([]);
  const [embeds, onChangeEmbeds] = useState<EmbeddedObject[]>([]);
  const [tables, onChangeTables] = useState<TableView[]>([]);
  const [pivotTables, onChangePivotTables] = useState<PivotTable[]>([]);
  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,
    cellXfsRegistry,
    getEffectiveFormat
  } = useSpreadsheetState({
     sheets,
     sheetData,
     tables,
     pivotTables,
     functions,
     namedRanges,
     theme,
     colorMode,
     conditionalFormats,
     cellXfs,
     onChangeSheets,
     onChangeSheetData,
     onChangeEmbeds,
     onChangeCharts,
     onChangeTables,
     onChangePivotTables,
     onChangeNamedRanges,
     onChangeTheme,
     onChangeCellXfs,
     onChangeHistory(patches) {
      onBroadcastPatch(patches);
     },
     onChangeProtectedRanges,
     onChangeConditionalFormats,
  })

  return (
    <CanvasGrid
      {...spreadsheetColors}
      stickyEditor={true}
      scale={scale}
      getEffectiveFormat={getEffectiveFormat}
      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
  ),
});

Using CellXfs for Shared Cell Formatting

cellXfs (Cell Extended Formatting Styles) is an optional feature that allows you to define reusable cell formats that can be shared across multiple cells. This is particularly useful for:

• Reducing memory footprint by sharing format definitions

• Maintaining consistent styling across cells

• Importing/exporting Excel files with shared styles

Setting up cellXfs

cellXfs is a Map where keys are style IDs and values are format objects containing properties like backgroundColor, textFormat, borders, etc.

const [cellXfs, onChangeCellXfs] = useState<CellXfs | null | undefined>(
  new Map([
    [
      "123",
      {
        backgroundColor: "green",
      },
    ],
  ])
);

Using cellXfs in useSpreadsheetState

Pass cellXfs and onChangeCellXfs to the useSpreadsheetState hook:

const {
  cellXfsRegistry,
  // ... other returned values
} = useSpreadsheetState({
  cellXfs,
  onChangeCellXfs,
  // ... other configuration
});

Applying cellXfs to cells

To apply a cellXfs style to a cell, set the sid (style ID) property in the cell's ef (effective format) object:

const cellData = {
  ef: { sid: "123" },  // References the cellXfs Map key
  fv: "Hello world",
  ev: { sv: "hello world" },
};

The cellXfsRegistry returned from the hook provides access to the shared styles and can be used for programmatic style management.

Use getEffectiveFormat to pick up cellXfs format

<CanvasGrid getEffectiveFormat={getEffectiveFormat} />