# 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.

{% tabs %}
{% tab title="yarn" %}

```sh
yarn add @rowsncolumns/spreadsheet-state
```

{% endtab %}

{% tab title="npm" %}

```sh
npm install @rowsncolumns/spreadsheet-state
```

{% endtab %}
{% endtabs %}

## 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].

{% code overflow="wrap" %}

```tsx
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>
  )
}
```

{% endcode %}

#### Creating initial row data from primitives

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

```tsx
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.

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

### Using cellXfs in useSpreadsheetState

Pass `cellXfs` and `onChangeCellXfs` to the `useSpreadsheetState` hook:

```tsx
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:

```tsx
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

```tsx
<CanvasGrid getEffectiveFormat={getEffectiveFormat} />
```