@srmcconomy/spreadsheet v0.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
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:
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.
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.