Adding rows/columns automatically after pasting data

[Anderbone]

I use react router v7 here. once Ctrl V the UI will update to show the full table from clipboard. Then I click save to save it to DB.

import { useState, useEffect, type Dispatch, type SetStateAction } from "react";
import {
  ReactGrid,
  useReactGridAPI,
  type CellsLookup,
  type NumericalRange,
  type ReactGridAPI,
} from "@silevis/reactgrid";
import {
  Cell,
  Column,
  NonEditableCell,
  TextCell,
  NumberCell,
} from "@silevis/reactgrid";

const ReactGridExample = ({
  tableJson,
  onChange,
}: {
  tableJson: { rowIndex: number; colIndex: number; text: string }[];
  onChange?: (
    updated: { rowIndex: number; colIndex: number; text: string }[]
  ) => void;
}) => {
  const [tableData, setTableData] = useState(tableJson);
  const cells: Cell[] = tableData.map((item, index) => ({
    rowIndex: item.rowIndex,
    colIndex: item.colIndex,
    Template: TextCell,
    props: {
      text: item.text,
      onTextChanged: (newText: string) => {
        // console.log(newText);
        setTableData((prev) => {
          const updatedTable = [...prev];
          updatedTable[index].text = newText;
          return updatedTable;
        });
      },
    },
  }));

  const columnsData: Column[] = cells.map((cell) => ({
    colIndex: cell.colIndex,
    width: 100,
    // resizable: true,
  }));

  const rows = cells.map((cell) => ({
    rowIndex: cell.rowIndex,
    height: 35,
  }));

  const [columns, setColumns] = useState(columnsData);
  useEffect(() => {
    onChange?.(tableData);
  }, [tableData, onChange]);

  const gridStyles = {
    grid: {
      gap: {
        color: "#f0f0f0",
      },
    },
    gridWrapper: {
      fontSize: "14px",
      color: "#000",
      fontWeight: "300",
      fontFamily: "Arial",
    },
    paneContainer: {
      top: {
        background: "#fff",
        boxShadow: "0px 2px 10px 0px rgba(0, 0, 0, 0.3)",
      },
      left: {
        background: "#fff",
        boxShadow: "5px 0 3px -2px rgba(0, 0, 0, .06)",
      },
      right: {
        boxShadow: "-5px 0 3px -2px rgba(0, 0, 0, .06)",
      },
    },
    cellContainer: {
      background: "#fafbfc",
    },
    focusIndicator: {
      border: {
        color: "#32a852",
        width: "2px",
        style: "solid",
      },
    },
    selectionIndicator: {
      background: "rgba(144, 238, 144, 0.1)",
      border: {
        color: "#81df9b",
        style: "solid",
      },
    },
    fillHandle: {
      background: "transparent",
      border: {
        color: "#32a852",
        style: "dashed",
      },
    },
  };
  const handleResizeColumn = (
    newWidth: number,
    columnIndexes: number[],
    setColumns: Dispatch<SetStateAction<Column[]>>
  ) => {
    setColumns((prevColumns) => {
      // If resizing spans multiple columns (e.g., header cells with colSpan > 1), divide the new width by the number of columns
      const newWidthPerColumn =
        columnIndexes.length > 1 ? newWidth / columnIndexes.length : newWidth;

      return prevColumns.map((column, idx) => {
        if (columnIndexes.includes(idx)) {
          return { ...column, width: newWidthPerColumn };
        }

        return column;
      });
    });
  };
  const gridAPI = useReactGridAPI("cut-copy-paste-example");

  const expandTableIfNeeded = (
    currentData: typeof tableJson,
    pastedRows: number,
    pastedCols: number,
    startRowIdx: number,
    startColIdx: number
  ) => {
    let newData = [...currentData];
    const currentMaxRow = Math.max(...currentData.map((cell) => cell.rowIndex));
    const currentMaxCol = Math.max(...currentData.map((cell) => cell.colIndex));

    // Calculate required dimensions
    const requiredRows = Math.max(currentMaxRow + 1, startRowIdx + pastedRows);
    const requiredCols = Math.max(currentMaxCol + 1, startColIdx + pastedCols);

    // Add new rows if needed
    for (
      let rowIndex = currentMaxRow + 1;
      rowIndex < requiredRows;
      rowIndex++
    ) {
      for (let colIndex = 0; colIndex <= currentMaxCol; colIndex++) {
        newData.push({ rowIndex, colIndex, text: "" });
      }
    }

    // Add new columns if needed
    for (let rowIndex = 0; rowIndex < requiredRows; rowIndex++) {
      for (
        let colIndex = currentMaxCol + 1;
        colIndex < requiredCols;
        colIndex++
      ) {
        newData.push({ rowIndex, colIndex, text: "" });
      }
    }

    return newData;
  };

  const handlePaste = (
    event: React.ClipboardEvent<HTMLDivElement>,
    cellsRange: NumericalRange,
    cellsLookup: CellsLookup,
    gridAPI?: ReactGridAPI
  ) => {
    const html = event.clipboardData.getData("text/html");
    const parser = new DOMParser();
    const doc = parser.parseFromString(html, "text/html");
    const rows = doc.querySelectorAll("tr");

    if (rows.length === 0) return true;

    // Get dimensions of pasted data
    const pastedRows = rows.length;
    const pastedCols = Math.max(
      ...Array.from(rows).map((row) => row.querySelectorAll("td").length)
    );

    // Expand table if needed
    const expandedTable = expandTableIfNeeded(
      tableData,
      pastedRows,
      pastedCols,
      cellsRange.startRowIdx,
      cellsRange.startColIdx
    );

    // Create a new table state
    let newTableState = [...expandedTable];

    // Update cells with pasted data
    rows.forEach((row, rowIndex) => {
      const cells = row.querySelectorAll("td");
      cells.forEach((cell, colIndex) => {
        const targetRowIdx = cellsRange.startRowIdx + rowIndex;
        const targetColIdx = cellsRange.startColIdx + colIndex;
        const value = cell.textContent || "";

        // Find and update the cell in our table state
        const cellIndex = newTableState.findIndex(
          (cell) =>
            cell.rowIndex === targetRowIdx && cell.colIndex === targetColIdx
        );
        if (cellIndex !== -1) {
          newTableState[cellIndex] = {
            ...newTableState[cellIndex],
            text: value,
          };
        }
      });
    });

    // Update state and notify parent
    setTableData(newTableState);
    onChange?.(newTableState);

    if (gridAPI) {
      gridAPI.setSelectedArea({
        startRowIdx: cellsRange.startRowIdx,
        endRowIdx: cellsRange.startRowIdx + pastedRows,
        startColIdx: cellsRange.startColIdx,
        endColIdx: cellsRange.startColIdx + pastedCols,
      });
    }

    return false;
  };

  return (
    <ReactGrid
      cells={cells}
      onResizeColumn={(width, columnIdx) =>
        handleResizeColumn(width, columnIdx, setColumns)
      }
      styles={gridStyles}
      columns={columns}
      rows={rows}
      onPaste={handlePaste}
    />
  );
};

export default ReactGridExample;

my table page route:

import {
  MetaFunction,
  useLoaderData,
  Form,
  redirect,
  useActionData,
  useSubmit,
  redirectDocument,
} from "react-router";
import { Nav } from "~/components/Nav";
import {
  Suspense,
  lazy,
  useEffect,
  useState,
  useRef,
  useCallback,
} from "react";
import type { ActionFunctionArgs, LoaderFunctionArgs } from "react-router";
import { prisma } from "~/db/client";

const ReactGridExample = lazy(() => import("~/components/table/test"));

export const meta: MetaFunction = () => {
  return [{ title: "Edit Table" }];
};

export async function loader({ params }: LoaderFunctionArgs) {
  const tableId = Number(params.id);
  const lookuptableData = await prisma.lookuptable.findUnique({
    where: { id: tableId },
  });

  if (!lookuptableData) {
    throw new Response("Not Found", { status: 404 });
  }

  return {
    tableData: lookuptableData.lookuptable,
    tableId: tableId,
  };
}

export async function action({ request, params }: ActionFunctionArgs) {
  const formData = await request.formData();
  const actionType = formData.get("actionType");
  const tableId = Number(params.id);
  const tableData = JSON.parse(formData.get("tableData") as string);

  // Only handle save operation in the backend
  if (actionType === "save") {
    await prisma.lookuptable.update({
      where: { id: tableId },
      data: {
        lookuptable: tableData,
      },
    });
    return { success: true, tableData };
  }
  return { success: false };
}

type TableCell = {
  text: string;
  colIndex: number;
  rowIndex: number;
};

export default function TableEdit() {
  const { tableData, tableId } = useLoaderData<typeof loader>();
  const [isClient, setIsClient] = useState(false);
  const [showSaved, setShowSaved] = useState(false);
  const formRef = useRef<HTMLFormElement>(null);
  const actionData = useActionData<typeof action>();
  const [currentTable, setCurrentTable] = useState<TableCell[]>(
    Array.isArray(tableData) ? (tableData as TableCell[]) : []
  );

  const handleTableChange = useCallback((newData: TableCell[]) => {
    setCurrentTable(newData);
  }, []);

  const handleOperation = useCallback((actionType: string) => {
    setCurrentTable((prevTable) => {
      const maxRowIndex = Math.max(...prevTable.map((cell) => cell.rowIndex));
      const maxColIndex = Math.max(...prevTable.map((cell) => cell.colIndex));

      switch (actionType) {
        case "addRow":
          const newRow = Array.from(
            { length: maxColIndex + 1 },
            (_, colIndex): TableCell => ({
              text: "",
              colIndex,
              rowIndex: maxRowIndex + 1,
            })
          );
          return [...prevTable, ...newRow];

        case "deleteRow":
          return maxRowIndex > 0
            ? prevTable.filter((cell) => cell.rowIndex !== maxRowIndex)
            : prevTable;

        case "addColumn":
          const newColumn = Array.from(
            { length: maxRowIndex + 1 },
            (_, rowIndex): TableCell => ({
              text: "",
              colIndex: maxColIndex + 1,
              rowIndex,
            })
          );
          return [...prevTable, ...newColumn];

        case "deleteColumn":
          return maxColIndex > 0
            ? prevTable.filter((cell) => cell.colIndex !== maxColIndex)
            : prevTable;

        default:
          return prevTable;
      }
    });
  }, []);

  useEffect(() => {
    setIsClient(true);
  }, []);

  useEffect(() => {
    if (actionData?.success) {
      setCurrentTable(actionData.tableData);
      setShowSaved(true);
      const timer = setTimeout(() => {
        setShowSaved(false);
      }, 3000);
      return () => clearTimeout(timer);
    }
  }, [actionData]);

  return (
    <div>
      <Nav />
      <div className="p-4">
        <div className="flex items-center gap-4 mb-4">
          <h1 className="text-2xl font-bold">Edit Table {tableId}</h1>
          <div className="flex gap-2">
            {["addRow", "deleteRow", "addColumn", "deleteColumn"].map(
              (action) => (
                <button
                  key={action}
                  type="button"
                  onClick={() => handleOperation(action)}
                  className={`${
                    action.startsWith("add") ? "bg-green-500" : "bg-red-500"
                  } text-white px-3 py-1 rounded hover:opacity-90`}
                >
                  {action
                    .replace(/([A-Z])/g, " $1")
                    .split(" ")
                    .map((word) => word.charAt(0).toUpperCase() + word.slice(1))
                    .join(" ")}
                </button>
              )
            )}
          </div>
        </div>
        <div className="border rounded-lg p-4">
          {isClient ? (
            <Suspense fallback={<div>Loading...</div>}>
              <ReactGridExample
                key={currentTable.length} // Force re-render when table structure changes
                tableJson={currentTable}
                onChange={handleTableChange}
              />
            </Suspense>
          ) : null}
          <Form
            ref={formRef}
            method="post"
            className="mt-4 flex items-center gap-4"
          >
            <input
              type="hidden"
              name="tableData"
              value={JSON.stringify(currentTable)}
            />
            <input type="hidden" name="actionType" value="save" />
            <button
              type="submit"
              className="bg-primary text-primary-foreground hover:bg-primary/90 px-4 py-2 rounded"
            >
              Save Table
            </button>
            {showSaved && (
              <span className="text-green-600 animate-fade-out">
                Table saved
              </span>
            )}
          </Form>
        </div>
      </div>
    </div>
  );
}