npm package: @srmcconomy/spreadsheet
Description: ## What is this?
Latest version: 0.0.11

# Bulk Editor

## What is this?

This is a spreadsheet-like table that allows people to make updates to data in bulk, using custom cell rendering and editing with React components

## Why do we have this?

Alternative libraries aren't very good for this type of thing. We trialed Handsontable for this, but in the end it had a bunch of annoying bugs, it had poor performance, and we had to use a bunch of hacks to get it to work nicely in our codebase and with React. This project should be much more performant and much cleaner to implement

## What are the goals of this project?

- Performance
  - this component should be usable for any number of rows (and a large number of columns) on any computer that our customers might use.
- Stability
  - this component should be easy to cleanly incorporate in our repos, and easy to understand. This will hopefully allow us to avoid major bugs
- Extendability
  - this component was designed with editing products in mind, but it should be easy to use with other data types as well. It should be easy to implement any renderer/editor we want for the data, and modify just about anything we want about the table

## How do you use it?

### With a mouse/trackpad

Clicking on a cell will select it
Double clicking a cell will start editing it
Clicking and dragging will select multiple cells

### With the keyboard

The arrow keys will move the selection around

Holding shift with the arrow keys will change the size of the selection

- moving away from the primary selection will grow the selection
- moving towards the primary selection will shrink the selection

CMD/CTRL+C will copy the selected data. CMD/CTRL+V will paste the copied data. Columns will be separated with `\t` and rows will be separated with `\n`. This means that data can safely be copied to and from Google Sheets

Pressing delete or backspace on a selected (but not edited) cell will clear that cell

Pressing space will start editing the selected cell

Pressing enter will start editing the selected cell. Pressing enter when a cell is already being edited will close the editor and move one cell down

Pressing tab when a cell is being edited will close the editor and move one cell to the right

Pressing any non-function key on a selected cell will clear that cell and then start editing, and the pressed key should be passed to the editor

## Implementating the table in your own work

### Basic example

It is recommended (but not necessary) to memoize all the props passed into the component for performance reasons

```tsx
import { React, useState, useCallback, useMemo } from "react";
import { BulkEditor, IColumnProps, IPaste } from "@faire/web-ui/ui/bulkEditor";

type IRow = {
  key: string;
  data: string[];
};

const updateRow = (row: IRow, colIndex: number, value: string) => {
  const newData = [...row.data];
  newData[colIndex] = value;
  return { ...row, data: newData };
};

const MyBulkEditor = () => {
  const [rows, setRows] = useState<IRow[]>(() =>
    [1, 2, 3, 4, 5].map((rowNum) => ({
      key: `row${rowNum}`,
      data: ["a", "b", "c", "d", "e"].map((col) => `${rowNum}${col}`),
    }))
  );

  const [errors, setErrors] = useState<(string | null)[][]>(
    [1, 2, 3, 4, 5].map(() => ["a", "b", "c", "d", "e"].map(() => null))
  );

  const columnProps = useMemo<IColumnProps<IRow, IRow, string>[]>(
    () =>
      ["a", "b", "c", "d", "e"].map((col, index) => ({
        key: col,
        renderCell: ({ row }) => row.data[index],
        renderEditor: ({ row, onChange, onBlur }) => (
          <input
            autoFocus
            value={row.data[index]}
            onChange={(e) => onChange(updateRow(row, index, e.target.value))}
            onBlur={onBlur}
          />
        ),
        onCopy: (row) => row.data[index],
        onPaste: (row, value) => updateRow(row, index, value),
        onClear: (row) => updateRow(row, index, ""),
      })),
    []
  );

  const handleChange = useCallback(
    (changes: IRow[]) =>
      setRows((oldRows) => {
        const newRows = [...oldRows];
        changes.forEach((change) => {
          const rowIndex = oldRows.findIndex((row) => row.key === change.key);
          newRows[rowIndex] = change;
        });
        return newRows;
      }),
    []
  );

  const applyPastes = useCallback(
    (pastes: IPaste<IRow, IRow>[]) =>
      pastes.map(({ row, changers }) => {
        let newRow = row;
        changers.forEach((changer) => (newRow = changer(row)));
        return newRow;
      }),
    []
  );

  const handleUndo = useCallback(() => {}, []);
  const handleRedo = useCallback(() => {}, []);

  const headers = useMemo(
    () => [
      {
        height: 40,
        row: ["a", "b", "c", "d", "e"].map((col) => ({
          key: col,
          component: <div>{col}</div>,
          columnSpan: 1,
        })),
      },
    ],
    []
  );

  const renderErrorTooltip = useCallback(
    (error: string) => <div>{error}</div>,
    []
  );

  return (
    <BulkEditor
      rows={rows}
      errors={errors}
      columnProps={columnProps}
      onChange={handleChange}
      onUndo={handleUndo}
      onRedo={handleRedo}
      applyPastes={applyPastes}
      headers={headers}
      renderErrorTooltip={renderErrorTooltip}
      rowHeight={40}
      numStickyColumns={1}
      numUnselectableColumns={0}
    />
  );
};
```

### Usage with `useUndoStack`

There are only a few changes you need to make to the above example to get undo/redo working:

```tsx
const MyBulkEditor = () => {
  const [rows, setRows] = useState<IRow[]>(() =>
    [1, 2, 3, 4, 5].map((rowNum) => ({
      key: `row${rowNum}`,
      data: ["a", "b", "c", "d", "e"].map((col) => `${rowNum}${col}`),
    }))
  );

  const { handleUndo, handleRedo, handleChange } = useUndoStack(
    rows,
    getKey: (row) => row.key,
    onChange: setRows,
  );

  // All the other stuff from above, except for defining handleChange, handleUndo, and handleRedo

  return (
    <BulkEditor
      rows={rows}
      errors={errors}
      columnProps={columnProps}
      onChange={handleChange}
      onUndo={handleUndo}
      onRedo={handleRedo}
      applyPastes={applyPastes}
      headers={headers}
      renderErrorTooltip={renderErrorTooltip}
      rowHeight={40}
      numStickyColumns={1}
      numUnselectableColumns={0}
    />
  );
```

### Usage with `useSubRowProps`

"Sub rows" are a useful concept for us when editing products, since each product can have multiple options. We want to display all products and their options in the same table, which we can do with sub rows.

```tsx
import {
  useSubRows,
  ISubRowColumnProps,
} from "@faire/web-ui/ui/bulkEditor/hooks";

type IMyOption = {
  key: string;
  data: string[];
};
type IMyProduct = {
  key: string;
  options: IMyOption[];
};

const updateProduct = (
  product: IMyProduct,
  option: IMyOption,
  colIndex: number,
  value: string
) => {
  const optionIndex = product.options.findIndex((o) => o.key === option.key);
  const newData = [...product.options[optionIndex].data];
  newData[colIndex] = value;
  const newOptions = [...product.options];
  newOptions[optionIndex] = {
    ...product.options[optionIndex],
    data: newData,
  };
  return { ...product, options: newOptions };
};

const MyBulkEditor = () => {
  const [products, setProducts] = useState<IMyProduct[]>(() =>
    [1, 2, 3, 4, 5].map((productNum) => ({
      key: `p_${productNum}`,
      options: [1, 2, 3].map((optionNum) => ({
        key: `o_${optionNum}`,
        data: ["a", "b", "c", "d", "e"].map(
          (col) => `${productNum}.${optionNum}${col}`
        ),
      })),
    }))
  );

  const createSubRows = useCallback(
    (product: IMyProduct) => [...product.options],
    []
  );
  const areRowsEqual = useCallback(
    (a: IMyProduct, b: IMyProduct) => a.key === b.key,
    []
  );
  const getKey = useCallback((product: IMyProduct) => product.key, []);
  const columnProps = useMemo<
    ISubRowColumnProps<IMyProduct, IMyOption, string>[]
  >(
    () =>
      ["a", "b", "c", "d", "e"].map((col, colIndex) => ({
        key: col,
        renderCell: ({ subRow }) => <div>{subRow[colIndex]}</div>,
        renderEditor: ({ element, subRow, onChange, onBlur }) => (
          <input
            autoFocus
            value={subRow.data[colIndex]}
            onChange={(e) =>
              onChange(updateProduct(element, subRow, colIndex, e.target.value))
            }
            onBlur={onBlur}
          />
        ),
        onCopy: (_element, subRow) => subRow.data[colIndex],
        onPaste: (element, subRow, value) =>
          updateProduct(element, subRow, colIndex, value),
        onClear: (element, subRow) =>
          updateProduct(element, subRow, colIndex, ""),
      })),
    []
  );

  const tableProps = useSubRowProps({
    elements: products,
    createSubRows,
    areRowsEqual,
    getKey,
    columnProps,
    onChange: handleChange, // Get it from useUndoStack or make your own
    onUndo: handleUndo, // Get it from useUndoStack or make your own
    onRedo: handleRedo, // Get it from useUndoStack or make your own
    headers, // Same as above example
    renderErrorTooltip, // Same as above example
    rowHeight: 40,
    numStickyColumns: 1,
    numUnselectableColumns: 0,
  });

  return <BulkEditor {...tableProps} />;
};
```

## How it was implemented

### React Context

This project makes heavy use of React contexts, since there is a lot of state that needs to be shared throughout the component tree. Since these states are independent, they are stored in different contexts. This leads to the unfortunate side effect that the `BulkEdit` component is very deeply nested, but it makes things elsewhere much cleaner.

### Memoization

A lot of the decisions in this project were made with performance in mind. We are trying to rerender the cells only when something actually changes, so there is heavy usage of `useMemo` and `useRef`. One way to easily accomplish this is to make sure that state from a `useState` is only used in actual render functions themselves. When used only in a callback of a component, `useState` is not necessary or desired, since it would cause unnecessary rerenders. Instead, we can store the state in a ref for use in callbacks. This is what `StateContext.tsx` accomplishes. It gives us a setter and a ref that never change that can be used safely in callbacks, as well as the state that causes rerenders that can be used in render functions

### Virtualization

`Rows.tsx` and `Row.tsx` make use of "virtualization" or "infinite scrolling" ideas to efficiently render any number of columns. It does this by only rendering as many rows as fit on the screen. For further performance, these rows are reused when scrolling. Basically, when a row is scrolled so that it goes off the top of the screen, that row is moved to the bottom of the screen, and then reused for the row that belongs at the bottom. These rows are always in the same order in the DOM, they only move around on the screen using css transforms. This makes sure that React only rerenders them when they scroll off the screen, not when a different element scrolls off screen. By using transforms and absolute positions, we also limit the number of DOM layout events, since the rows can't cause other elements to move.