Augustus otu

Posted on Jun 23

Building a Web-Based Excel Editor: A Comprehensive Guide

#webdev #programming #javascript #typescript

By the Toki Space Team

Building a fully-featured Excel editor in the browser is one of the most challenging frontend projects you can undertake. It requires deep understanding of canvas rendering, complex state management, real-time collaboration, and Excel's intricate feature set.

This isn't just another grid component—we're talking about recreating decades of Microsoft Excel's functionality in a browser, complete with formulas, formatting, charts, and real-time collaboration. The complexity rivals that of building a game engine or CAD software.

This tutorial will walk you through building a production-ready collaborative Excel editor using React, TypeScript, and HTML Canvas, sharing the hard-won lessons from building Toki's spreadsheet editor.

Architecture Overview

Core Technologies

Our Excel editor is built with:

React 18 with TypeScript for component architecture
HTML5 Canvas for high-performance grid rendering
WebSocket for real-time collaboration
Custom hooks for state management
Tauri for desktop app capabilities

Key Design Decisions

Canvas over DOM: We chose canvas rendering over DOM-based approaches for performance. Rendering thousands of cells in DOM would be prohibitively slow.
Layered Canvas Architecture: Multiple canvas layers for different concerns:
- Grid and cell content
- Headers (row/column)
- Selection overlay
- Collaboration cursors
Viewport-based Rendering: Only render visible cells plus a buffer zone, similar to virtual scrolling.

// Core constants that define our grid
const CELL_WIDTH = 100;
const CELL_HEIGHT = 32;
const ROW_HEADER_WIDTH = 60;
const COLUMN_HEADER_HEIGHT = 32;
const MAX_COLUMNS = 16384; // Excel's XFD limit
const MAX_ROWS = 1048576; // Excel's row limit
const VIEWPORT_BUFFER = 10; // Extra cells outside viewport

Canvas Rendering System

Understanding Canvas vs DOM for Data Grids

Before diving into implementation, it's crucial to understand why we chose HTML5 Canvas over DOM-based approaches. A typical Excel sheet contains 16,384 columns × 1,048,576 rows = ~17 billion potential cells. Even rendering 1,000 visible cells as DOM elements would create severe performance bottlenecks.

Canvas provides:

Immediate mode rendering: Direct pixel manipulation without DOM overhead
Batch operations: Efficient drawing of thousands of elements in single operations
Memory efficiency: Single canvas element vs thousands of DOM nodes
Pixel-perfect control: Exact positioning and custom rendering logic

The tradeoff is complexity—we must manually implement everything the browser normally handles (hit testing, text input, accessibility).

Multi-Layer Canvas Setup

The foundation of our Excel editor is a multi-canvas system where each layer handles specific rendering concerns. This separation allows us to optimize redraws—when only the selection changes, we don't need to redraw the entire grid.

const canvasContainerRef = useRef<HTMLDivElement>(null);
const gridCanvasRef = useRef<HTMLCanvasElement>(null);           // Cells and grid lines
const headerCanvasRef = useRef<HTMLCanvasElement>(null);         // Row/column headers  
const selectionCanvasRef = useRef<HTMLCanvasElement>(null);      // Selection highlights
const collaborationCanvasRef = useRef<HTMLCanvasElement>(null);  // Other users' cursors

Layer Architecture Explained:

Grid Layer (bottom): Static content that changes infrequently—cell values, borders, backgrounds
Header Layer: Row numbers and column letters, with resize handles
Selection Layer: User's current selection and active cell highlight
Collaboration Layer (top): Real-time cursors and selections from other users

This layering prevents expensive redraws. When a user moves their cursor, only the selection canvas needs updating, not the entire grid.

Viewport Calculation: Virtual Scrolling for Massive Datasets

The viewport system is the heart of our performance optimization. Like video game engines that only render what's visible, we calculate which cells are currently in view and only draw those plus a small buffer.

The Challenge: Excel supports 1,048,576 rows. If each row was 32px tall, the total height would be 33,554,432 pixels—far exceeding browser limits and causing memory issues.

The Solution: Virtual scrolling with dynamic row heights.

const calculateViewport = useCallback(() => {
  if (!canvasContainerRef.current) return;

  const container = canvasContainerRef.current;
  const scrollX = container.scrollLeft;
  const scrollY = container.scrollTop;
  const width = container.clientWidth;
  const height = container.clientHeight;

  // Calculate visible rows using cumulative height calculation
  // This is more complex than simple division because rows can have different heights
  let startRow = 0;
  let y = 0;

  // Walk through rows until we reach the scroll position
  while (y < scrollY && startRow < DEFAULT_ROWS) {
    y += rowHeights[startRow] || CELL_HEIGHT;
    startRow++;
  }
  // Include buffer rows before visible area for smooth scrolling
  startRow = Math.max(0, startRow - VIEWPORT_BUFFER);

  // Calculate end row by continuing from start row
  let endRow = startRow;
  y = y - (rowHeights[startRow] || CELL_HEIGHT) * VIEWPORT_BUFFER;
  while (y < scrollY + height && endRow < DEFAULT_ROWS) {
    y += rowHeights[endRow] || CELL_HEIGHT;
    endRow++;
  }
  // Include buffer rows after visible area
  endRow = Math.min(DEFAULT_ROWS - 1, endRow + VIEWPORT_BUFFER);

  // Similar calculation for columns with dynamic widths...

  setViewportState({
    scrollX, scrollY, width, height,
    startRow, endRow, startColumn, endColumn,
  });
}, [columnWidths, rowHeights]);

Key Concepts:

Cumulative Positioning: We can't use simple math (row * height) because rows have variable heights. We must walk through each row, accumulating heights until we reach the scroll position.
Buffer Zones: We render extra rows/columns outside the visible area. This prevents flickering during fast scrolling and provides smooth user experience.
Coordinate System: The viewport maintains two coordinate systems:
- Logical coordinates: Row/column indices (0, 1, 2...)
- Physical coordinates: Pixel positions on screen

Performance Impact: This approach lets us handle massive spreadsheets while only rendering ~100-200 cells at any time, regardless of the total data size.

Grid Rendering

The grid rendering system draws cells, borders, and content efficiently:

const drawGrid = useCallback((ctx: CanvasRenderingContext2D) => {
  ctx.save();
  ctx.strokeStyle = "#e0e0e0";
  ctx.lineWidth = 1;

  const { startRow, endRow, startColumn, endColumn } = viewportState;

  // Draw vertical lines
  let x = ROW_HEADER_WIDTH;
  for (let col = startColumn; col <= endColumn; col++) {
    const width = (columnWidths[col] || CELL_WIDTH) * zoomLevel;
    x += width;
    ctx.beginPath();
    ctx.moveTo(x, 0);
    ctx.lineTo(x, ctx.canvas.height);
    ctx.stroke();
  }

  // Draw horizontal lines
  let y = COLUMN_HEADER_HEIGHT;
  for (let row = startRow; row <= endRow; row++) {
    const height = (rowHeights[row] || CELL_HEIGHT) * zoomLevel;
    y += height;
    ctx.beginPath();
    ctx.moveTo(0, y);
    ctx.lineTo(ctx.canvas.width, y);
    ctx.stroke();
  }

  ctx.restore();
}, [viewportState, columnWidths, rowHeights, zoomLevel]);

Cell Content Rendering

Each cell's content is rendered with proper formatting and styling:

const drawCellContent = useCallback((
  ctx: CanvasRenderingContext2D,
  row: number,
  column: number,
  x: number,
  y: number,
  width: number,
  height: number
) => {
  const address = getCellAddress(row, column);
  const cellData = getCellByAddress(address);

  if (!cellData || (!cellData.value && !cellData.formula)) return;

  ctx.save();

  // Clip to cell bounds
  ctx.beginPath();
  ctx.rect(x, y, width, height);
  ctx.clip();

  // Apply cell formatting
  const style = cellData.style || {};

  // Background
  if (style.backgroundColor) {
    ctx.fillStyle = style.backgroundColor;
    ctx.fillRect(x, y, width, height);
  }

  // Text formatting
  const fontSize = (style.fontSize || 11) * zoomLevel;
  const fontName = style.fontName || "Arial";
  const fontWeight = style.bold ? "bold" : "normal";
  const fontStyle = style.italic ? "italic" : "normal";

  ctx.font = `${fontStyle} ${fontWeight} ${fontSize}px ${fontName}`;
  ctx.fillStyle = style.fontColor || "#000";

  // Text alignment and rendering
  const padding = 4 * zoomLevel;
  let textX = x + padding;
  let textY = y + height / 2;

  ctx.textBaseline = "middle";

  switch (style.horizontalAlignment) {
    case "center":
      ctx.textAlign = "center";
      textX = x + width / 2;
      break;
    case "right":
      ctx.textAlign = "right";
      textX = x + width - padding;
      break;
    default:
      ctx.textAlign = "left";
  }

  // Display formatted value
  const displayValue = cellData.value;
  if (displayValue !== undefined && displayValue !== null) {
    let formattedValue = String(displayValue);

    if (style.numberFormat && typeof displayValue === "number") {
      formattedValue = formatNumber(displayValue, style.numberFormat);
    }

    ctx.fillText(formattedValue, textX, textY);
  }

  ctx.restore();
}, [getCellByAddress, zoomLevel]);

Cell Management and Selection

Cell Addressing System

Excel uses an alphanumeric addressing system (A1, B2, etc.). Here's how we implement it:

const columnToLetter = (column: number): string => {
  let result = "";
  while (column >= 0) {
    result = String.fromCharCode((column % 26) + 65) + result;
    column = Math.floor(column / 26) - 1;
  }
  return result;
};

const letterToColumn = (letter: string): number => {
  let result = 0;
  for (let i = 0; i < letter.length; i++) {
    result = result * 26 + letter.charCodeAt(i) - 64;
  }
  return result - 1;
};

const getCellAddress = (row: number, column: number): string => {
  return `${columnToLetter(column)}${row + 1}`;
};

Selection System

The selection system handles single cells and ranges:

interface CellPosition {
  row: number;
  column: number;
  worksheetId: string;
}

interface CellSelection {
  start: CellPosition;
  end: CellPosition;
}

// State management
const [activeCell, setActiveCell] = useState<CellPosition | null>(null);
const [selectedRange, setSelectedRange] = useState<CellSelection | null>(null);
const [isSelecting, setIsSelecting] = useState(false);

Mouse Event Handling: Coordinate System Transformations

Converting mouse coordinates to cell positions is more complex than it appears. We're dealing with multiple coordinate systems that must be reconciled:

Screen coordinates: Mouse position relative to viewport
Canvas coordinates: Position relative to canvas element
Grid coordinates: Position accounting for scroll offset
Cell coordinates: Final row/column indices

The Mathematical Challenge: Unlike a simple grid where we could use Math.floor(x / cellWidth), Excel has variable row heights and column widths, requiring iterative calculation.

const getCellAtCoordinates = useCallback((x: number, y: number): CellPosition | null => {
  if (!currentWorksheet) return null;

  // Step 1: Transform from canvas coordinates to grid coordinates
  // Account for header areas (row numbers, column letters)
  x -= ROW_HEADER_WIDTH;
  y -= COLUMN_HEADER_HEIGHT;

  // Step 2: Validate coordinates are within grid bounds
  if (x < 0 || y < 0) return null;

  // Step 3: Find column using cumulative width calculation
  // We can't use simple division because columns have variable widths
  let column = 0;
  let accX = 0;
  while (accX < x && column < DEFAULT_COLUMNS) {
    // Account for zoom level in all calculations
    accX += (columnWidths[column] || CELL_WIDTH) * zoomLevel;
    column++;
  }
  // Adjust for off-by-one from the while loop
  column = Math.max(0, column - 1);

  // Step 4: Find row using cumulative height calculation  
  let row = 0;
  let accY = 0;
  while (accY < y && row < DEFAULT_ROWS) {
    accY += (rowHeights[row] || CELL_HEIGHT) * zoomLevel;
    row++;
  }
  row = Math.max(0, row - 1);

  return { row, column, worksheetId: currentWorksheet.id };
}, [currentWorksheet, columnWidths, rowHeights, zoomLevel]);

Why This Approach?

Variable Sizing: Excel allows different widths/heights per column/row
Zoom Support: All calculations must account for zoom level
Precision: We need exact cell boundaries, not approximate regions
Performance: This O(n) search is faster than binary search for typical viewport sizes

Edge Cases Handled:

Clicks on headers (return null)
Clicks outside grid boundaries
Zoom level transformations
Fractional pixel positions

Editing System

In-Cell Editing

The editing system provides seamless inline editing with formula support:

const [editingCell, setEditingCell] = useState<CellPosition | null>(null);
const [editingValue, setEditingValue] = useState("");
const [isEditingFormula, setIsEditingFormula] = useState(false);

// Render editing input overlay
const renderCellInput = () => {
  if (!editingCell || !currentWorksheet) return null;

  const { x, y } = getCellPosition(editingCell.row, editingCell.column);
  const { width, height } = getCellDimensions(editingCell.row, editingCell.column);

  return (
    <Input
      className="absolute z-[60] border-2 border-blue-500 px-1 bg-white"
      style={{
        left: `${x - viewportState.scrollX}px`,
        top: `${y - viewportState.scrollY}px`,
        width: `${Math.max(width, 200)}px`,
        height: `${height}px`,
        fontSize: `${11 * zoomLevel}px`,
      }}
      value={editingValue}
      onChange={(e) => handleFormulaInput(e.target.value)}
      onKeyDown={handleEditKeyDown}
      onBlur={() => handleCellEdit(editingValue)}
      autoFocus
    />
  );
};

Formula Bar Integration

The formula bar provides Excel-like formula editing:

const [formulaBarValue, setFormulaBarValue] = useState("");
const [showFunctionHelper, setShowFunctionHelper] = useState(false);
const [functionSuggestions, setFunctionSuggestions] = useState<string[]>([]);

const handleFormulaInput = useCallback((value: string) => {
  setFormulaBarValue(value);
  setEditingValue(value);

  if (value.startsWith("=")) {
    setIsEditingFormula(true);

    // Extract the current function being typed
    const lastFunctionMatch = value.match(/([A-Z]+)$/);
    if (lastFunctionMatch) {
      const partialFunction = lastFunctionMatch[1];
      const suggestions = EXCEL_FUNCTIONS.filter(
        (func) => func.startsWith(partialFunction) && func !== partialFunction
      ).slice(0, 10);

      setFunctionSuggestions(suggestions);
      setShowFunctionHelper(suggestions.length > 0);
    } else {
      setShowFunctionHelper(false);
    }
  } else {
    setIsEditingFormula(false);
    setShowFunctionHelper(false);
  }
}, []);

Cell Value Updates

Handling cell updates with proper formula vs value distinction:

const handleCellEdit = useCallback(async (value: string) => {
  if (!editingCell || !currentWorksheet || !documentId) return;

  const address = getCellAddress(editingCell.row, editingCell.column);

  try {
    const isFormula = value.startsWith("=");

    if (isFormula) {
      // Use setCellFormula for formula-specific handling
      await setCellFormula(documentId, {
        worksheetId: currentWorksheet.id,
        cellAddress: address,
        formula: value,
      });
    } else {
      // Use updateCell for regular values
      await updateCell(documentId, {
        worksheetId: currentWorksheet.id,
        cellAddress: address,
        value: value,
      });
    }

    // Send edit to collaborators
    if (excelState.editSession) {
      websocketService.sendExcelEdit(
        documentId,
        currentWorksheet.id,
        excelState.editSession.id,
        isFormula ? "formula_update" : "cell_update",
        address,
        {
          value: isFormula ? undefined : value,
          formula: isFormula ? value : undefined,
        }
      );
    }

    // Clear editing state
    setEditingCell(null);
    setEditingValue("");
    setIsEditingFormula(false);
    setShowFunctionHelper(false);

    // Refresh data
    await loadWorksheetData(documentId, currentWorksheet.id);
    render();
  } catch (error) {
    console.error("Failed to update cell:", error);
    toast.error("Failed to update cell");
  }
}, [editingCell, currentWorksheet, documentId, setCellFormula, updateCell]);

Formatting and Styling

Cell Style System

Excel cells support rich formatting options:

interface CellFormat {
  bold?: boolean;
  italic?: boolean;
  underline?: boolean;
  fontSize?: number;
  fontFamily?: string;
  textColor?: string;
  backgroundColor?: string;
  borderColor?: string;
  borderStyle?: string;
  alignment?: "left" | "center" | "right";
  verticalAlignment?: "top" | "middle" | "bottom";
  numberFormat?: string;
  wrapText?: boolean;
}

Format Application

Applying formatting to selected cells:

const handleToggleBold = useCallback(async () => {
  if (!activeCell || !currentWorksheet || !documentId) return;

  try {
    const address = getCellAddress(activeCell.row, activeCell.column);
    const cellData = getCellByAddress(address);
    const currentStyle = cellData?.style || {};

    await setCellStyle(documentId, {
      worksheetId: currentWorksheet.id,
      range: address,
      style: {
        ...currentStyle,
        bold: !currentStyle.bold,
      },
    });

    await loadWorksheetData(documentId, currentWorksheet.id);
    render();
  } catch (error) {
    console.error("Failed to toggle bold:", error);
    toast.error("Failed to apply formatting");
  }
}, [activeCell, currentWorksheet, documentId, getCellByAddress, setCellStyle]);

Number Formatting

Supporting various number formats like Excel:

const formatNumber = useCallback((value: number, format: string): string => {
  switch (format) {
    case "currency":
      return new Intl.NumberFormat("en-US", {
        style: "currency",
        currency: "USD",
      }).format(value);
    case "percent":
      return new Intl.NumberFormat("en-US", {
        style: "percent",
        minimumFractionDigits: 0,
        maximumFractionDigits: 2,
      }).format(value);
    case "thousands":
      return new Intl.NumberFormat("en-US").format(value);
    case "decimal2":
      return value.toFixed(2);
    default:
      return String(value);
  }
}, []);

Real-time Collaboration

The Challenge of Collaborative Editing

Real-time collaborative editing is one of computer science's most complex distributed systems problems. Consider this scenario:

User A types "Hello" in cell A1
User B simultaneously types "World" in the same cell
Both operations occur at nearly the same time

Questions that arise:

Which edit wins?
How do we maintain consistency across all clients?
How do we handle network delays and out-of-order messages?
How do we resolve conflicts without losing data?

Operational Transformation Theory

Excel-style collaboration requires Operational Transformation (OT), a mathematical framework for handling concurrent edits. The core principle:

Given operations O1 and O2 that started from the same document state, we can transform them into O1' and O2' such that applying O1' after O2 produces the same result as applying O2' after O1.

Mathematical Properties (Consistency Conditions):

TP1: O1 ∘ transform(O2, O1) = O2 ∘ transform(O1, O2)
TP2: transform(O3, O1 ∘ O2) = transform(transform(O3, O1), O2)

These equations ensure that all clients converge to the same final state regardless of operation order.

User Presence System

Beyond document operations, we track user presence for seamless collaboration:

interface UserPresence {
  userId: string;
  userName: string;
  userColor: string;          // Deterministic color from userId hash
  cursor?: CellPosition;      // Current cell cursor position
  selection?: CellSelection;  // Selected range (for copy/paste, etc.)
  isTyping: boolean;         // Shows typing indicator
  lastActivity: number;      // For connection timeout detection
}

const [userPresences, setUserPresences] = useState<Map<string, UserPresence>>(new Map());
const [isConnected, setIsConnected] = useState(false);

Presence Design Decisions:

Deterministic Colors: User colors are generated from userId hashes, ensuring consistency across sessions
Granular State: Separate cursor vs selection allows for different interaction modes
Activity Tracking: Detect and clean up stale connections
Lightweight Updates: Presence changes are sent separately from document operations to avoid conflicts

WebSocket Integration

Setting up real-time communication:

const setupWebSocketCollaboration = useCallback(() => {
  if (!documentId || !excelState.editSession || !currentWorksheet) return;

  websocketService
    .on("onDocumentEdit", (type, data) => {
      if (type === "excel" && data.documentId === documentId) {
        handleRemoteEdit(data);
      }
    })
    .on("onDocumentCursor", (type, data) => {
      if (type === "excel" && data.documentId === documentId) {
        handleRemoteCursor(data);
      }
    })
    .on("onDocumentSelection", (type, data) => {
      if (type === "excel" && data.documentId === documentId) {
        handleRemoteSelection(data);
      }
    });

  // Join the document
  websocketService.joinExcelDocument(
    documentId,
    currentWorksheet.id,
    excelState.editSession.id,
    currentUser.userName
  );

  setIsConnected(true);
}, [documentId, excelState.editSession, currentWorksheet, currentUser]);

Collaborative Cursor Rendering

Drawing other users' cursors and selections:

const drawCollaborationCursors = useCallback((ctx: CanvasRenderingContext2D) => {
  ctx.clearRect(0, 0, ctx.canvas.width, ctx.canvas.height);

  userPresences.forEach((presence) => {
    if (presence.cursor && presence.cursor.worksheetId === currentWorksheet?.id) {
      const { x, y } = getCellPosition(presence.cursor.row, presence.cursor.column);
      const { width, height } = getCellDimensions(presence.cursor.row, presence.cursor.column);

      ctx.save();

      // Draw cursor outline
      ctx.strokeStyle = presence.userColor;
      ctx.lineWidth = 2;
      ctx.strokeRect(
        x - viewportState.scrollX,
        y - viewportState.scrollY,
        width,
        height
      );

      // Draw user label
      ctx.fillStyle = presence.userColor;
      ctx.fillRect(
        x - viewportState.scrollX,
        y - viewportState.scrollY - 20,
        100,
        20
      );
      ctx.fillStyle = "#fff";
      ctx.font = "12px sans-serif";
      ctx.fillText(
        presence.userName,
        x - viewportState.scrollX + 4,
        y - viewportState.scrollY - 6
      );

      ctx.restore();
    }
  });
}, [userPresences, currentWorksheet, getCellPosition, getCellDimensions]);

Advanced Features

Row and Column Operations

Supporting Excel's row/column manipulation:

const handleInsertRowAbove = useCallback(async () => {
  if (!activeCell || !currentWorksheet || !documentId) return;

  try {
    await insertRow(documentId, {
      worksheetId: currentWorksheet.id,
      rowIndex: activeCell.row,
      count: 1,
    });

    await loadWorksheetData(documentId, currentWorksheet.id);
    render();
    toast.success("Row inserted");
  } catch (error) {
    console.error("Failed to insert row:", error);
    toast.error("Failed to insert row");
  }
}, [activeCell, currentWorksheet, documentId, insertRow]);

Cell Merging

Implementing Excel's cell merge functionality:

const handleToggleMergeCells = useCallback(async () => {
  if (!selectedRange || !currentWorksheet || !documentId) return;

  try {
    const startAddress = getCellAddress(
      Math.min(selectedRange.start.row, selectedRange.end.row),
      Math.min(selectedRange.start.column, selectedRange.end.column)
    );
    const endAddress = getCellAddress(
      Math.max(selectedRange.start.row, selectedRange.end.row),
      Math.max(selectedRange.start.column, selectedRange.end.column)
    );

    const isMerged = false; // Check actual merge status

    if (isMerged) {
      await unmergeCells(documentId, {
        worksheetId: currentWorksheet.id,
        range: `${startAddress}:${endAddress}`,
      });
    } else {
      await mergeCells(documentId, {
        worksheetId: currentWorksheet.id,
        range: `${startAddress}:${endAddress}`,
      });
    }

    await loadWorksheetData(documentId, currentWorksheet.id);
    render();
  } catch (error) {
    console.error("Failed to merge/unmerge cells:", error);
  }
}, [selectedRange, currentWorksheet, documentId, mergeCells, unmergeCells]);

Comments System

Adding collaborative comments to cells:

const handleAddComment = useCallback(() => {
  if (!activeCell || !currentWorksheet) return;

  const cellAddress = getCellAddress(activeCell.row, activeCell.column);
  setCommentTargetCell(cellAddress);
  setCurrentComment("");
  setShowCommentDialog(true);
}, [activeCell, currentWorksheet]);

const saveComment = useCallback(async () => {
  if (!commentTargetCell || !currentComment.trim() || !documentId || !currentWorksheet) {
    return;
  }

  try {
    await addComment(documentId, {
      worksheetId: currentWorksheet.id,
      cellAddress: commentTargetCell,
      text: currentComment,
    });

    // Send comment via WebSocket for real-time collaboration
    if (excelState.editSession) {
      websocketService.sendExcelEdit(
        documentId,
        currentWorksheet.id,
        excelState.editSession.id,
        "comment_add",
        commentTargetCell,
        {
          text: currentComment,
          author: currentUser.userName,
          userId: currentUser.userId,
          timestamp: Date.now(),
        }
      );
    }

    setCurrentComment("");
    setShowCommentDialog(false);
    await loadWorksheetData(documentId, currentWorksheet.id);
    render();
  } catch (error) {
    console.error("Failed to save comment:", error);
  }
}, [commentTargetCell, currentComment, documentId, currentWorksheet, addComment]);

Find and Replace

Implementing Excel's find/replace functionality:

const [showFindReplace, setShowFindReplace] = useState(false);
const [searchQuery, setSearchQuery] = useState("");
const [replaceText, setReplaceText] = useState("");

// Find and replace implementation
const handleFindAndReplace = useCallback(async () => {
  if (!documentId || !currentWorksheet || !searchQuery) return;

  try {
    await replaceInWorksheet(documentId, {
      worksheetId: currentWorksheet.id,
      find: searchQuery,
      replace: replaceText,
      options: { replaceAll: true },
    });

    await loadWorksheetData(documentId, currentWorksheet.id);
    render();
    toast.success("Replace completed");
  } catch (error) {
    console.error("Failed to replace:", error);
  }
}, [documentId, currentWorksheet, searchQuery, replaceText]);

Performance Optimization

Viewport-Based Rendering

Only render visible cells to maintain performance:

// Only render cells within viewport plus buffer
const drawCells = useCallback((ctx: CanvasRenderingContext2D) => {
  const { startRow, endRow, startColumn, endColumn } = viewportState;

  let y = COLUMN_HEADER_HEIGHT;
  for (let row = startRow; row <= endRow; row++) {
    let x = ROW_HEADER_WIDTH;
    const height = (rowHeights[row] || CELL_HEIGHT) * zoomLevel;

    for (let col = startColumn; col <= endColumn; col++) {
      const width = (columnWidths[col] || CELL_WIDTH) * zoomLevel;
      drawCellContent(ctx, row, col, x, y, width, height);
      x += width;
    }
    y += height;
  }
}, [viewportState, columnWidths, rowHeights, zoomLevel, drawCellContent]);

Efficient Re-rendering: React Optimization Strategies

React's rendering can become a bottleneck when dealing with complex canvas operations. Here's how we optimize:

// Memoize expensive calculations
const activeCellData = useMemo(() => {
  if (!activeCell || !currentWorksheet) return null;
  const address = getCellAddress(activeCell.row, activeCell.column);
  return getCellByAddress(address);
}, [activeCell, currentWorksheet, getCellByAddress]);

// Debounce frequent operations to maintain 60fps
const debouncedRender = useMemo(
  () => debounce(render, 16), // 16ms = 60fps target
  [render]
);

// Separate hot and cold paths
const fastRender = useCallback(() => {
  // Only redraw selection layer for cursor movements
  const selectionCtx = getCanvasContext(selectionCanvasRef.current);
  if (selectionCtx) {
    drawSelection(selectionCtx);
  }
}, [drawSelection, getCanvasContext]);

const fullRender = useCallback(() => {
  // Full redraw for data changes
  render();
}, [render]);

Performance Principles Applied:

Differential Rendering: Only redraw changed layers
Frame Budget: Target 16ms per frame for 60fps
Memoization: Cache expensive calculations
Event Coalescing: Batch rapid updates
Lazy Evaluation: Defer non-critical rendering

Memory Management

Clean up resources and prevent memory leaks:

useEffect(() => {
  // Cleanup on unmount
  return () => {
    if (autoSaveTimeoutRef.current) {
      clearTimeout(autoSaveTimeoutRef.current);
    }
    if (collaborationTimeoutRef.current) {
      clearTimeout(collaborationTimeoutRef.current);
    }
    cleanupCollaboration();
  };
}, []);

Key Learnings

1. Canvas vs DOM Trade-offs

Canvas Advantages:

High performance with thousands of cells
Pixel-perfect control over rendering
Efficient updates and animations
Lower memory footprint

Canvas Challenges:

No native accessibility support
Complex event handling
Manual text input management
Limited browser dev tools support

2. State Management Complexity

Excel editors require sophisticated state management spanning multiple domains and consistency requirements:

State Architecture Layers:

// 1. Document State (Persistent)
interface DocumentState {
  worksheets: Worksheet[];
  metadata: DocumentMetadata;
  sharedSettings: WorkbookSettings;
  version: number;
}

// 2. Worksheet State (Per-sheet)
interface WorksheetState {
  cells: Map<string, CellData>;      // Sparse matrix of cell data
  rowHeights: Map<number, number>;   // Custom row heights
  columnWidths: Map<number, number>; // Custom column widths
  mergedCells: MergedRegion[];       // Cell merge information
  charts: Chart[];                   // Embedded charts
  images: Image[];                   // Embedded images
  formatting: ConditionalFormat[];   // Conditional formatting rules
  dataValidation: ValidationRule[];  // Data validation rules
}

// 3. UI State (Session-specific)
interface UIState {
  activeCell: CellPosition | null;
  selectedRange: CellSelection | null;
  editingCell: CellPosition | null;
  viewport: ViewportState;
  zoomLevel: number;
  showGridlines: boolean;
  showHeaders: boolean;
}

// 4. Collaboration State (Real-time)
interface CollaborationState {
  users: Map<string, UserPresence>;
  operations: Operation[];           // Pending operations
  vectorClock: VectorClock;         // For ordering operations
  conflicts: ConflictResolution[];   // Unresolved conflicts
}

// 5. Undo/Redo State (History)
interface HistoryState {
  undoStack: Operation[];
  redoStack: Operation[];
  maxHistorySize: number;
  currentVersion: number;
}

State Synchronization Challenges:

Cross-Layer Dependencies: UI state depends on document state, but changes must propagate both ways
Consistency Guarantees: Collaborative edits must maintain referential integrity
Performance Isolation: Heavy document operations shouldn't block UI updates
Memory Management: Large spreadsheets require careful state pruning

Solutions Implemented:

Normalized State: Separate storage for cells, formatting, and metadata
Immutable Updates: Using Immer for safe state mutations
Computed Properties: Derived state using selectors and memoization
Event Sourcing: All changes as operations for undo/collaboration

3. Real-time Collaboration Challenges

Conflict Resolution:

Operational Transformation for concurrent edits
Last-write-wins for simple properties
User presence and cursor tracking

Performance Considerations:

Debounce frequent updates
Batch small changes
Optimize WebSocket message size

4. Excel Feature Completeness

Building a complete Excel editor requires implementing:

Core Features: Cell editing, formulas, formatting
Layout Features: Row/column operations, merging, freezing
Data Features: Sorting, filtering, validation
Visual Features: Charts, images, conditional formatting
Collaboration: Comments, sharing, version history

5. Browser Limitations

Memory Constraints:

Large spreadsheets can exhaust browser memory
Implement virtual scrolling and lazy loading
Use Web Workers for heavy calculations

Performance Considerations:

Canvas rendering can be CPU-intensive
Implement frame-rate limiting
Use requestAnimationFrame for smooth updates

Conclusion

Building a web-based Excel editor is a complex undertaking that pushes the boundaries of web technology. The key to success lies in:

Smart Architecture: Layered canvas system with efficient rendering
Performance Focus: Viewport-based rendering and memory management
Real-time Collaboration: WebSocket integration with conflict resolution
Feature Completeness: Supporting Excel's extensive feature set
User Experience: Smooth interactions and responsive design

The end result should feel native and performant, rivaling desktop spreadsheet applications while leveraging the web's collaborative advantages.

Learning Resources

Books

Canvas and Graphics Programming:

"HTML5 Canvas" by Steve Fulton & Jeff Fulton - Comprehensive guide to 2D canvas programming
"Real-Time Rendering" by Tomas Akenine-Möller - Advanced graphics concepts applicable to canvas optimization
"Game Programming Patterns" by Robert Nystrom - Design patterns for performance-critical applications

Data Structures and Algorithms:

"Introduction to Algorithms" by Cormen, Leiserson, Rivest & Stein - Essential for viewport calculations and spatial indexing
"The Algorithm Design Manual" by Steven Skiena - Practical approaches to complex algorithmic problems

Collaborative Systems:

"Designing Data-Intensive Applications" by Martin Kleppmann - Modern approaches to distributed systems and consistency
"Building Microservices" by Sam Newman - Architecture patterns for scalable collaborative systems

JavaScript Performance:

"High Performance JavaScript" by Nicholas Zakas - Browser optimization techniques
"You Don't Know JS" series by Kyle Simpson - Deep JavaScript understanding for complex applications

Research Papers

Operational Transformation:

"Operational Transformation in Real-Time Group Editors" (1998) - Foundational OT paper
"Consistency Maintenance in Real-Time Collaborative Graphics Editing Systems" (2000)
"Real-Time Collaborative Editing Systems" by Chengzheng Sun (2002)

Collaborative Editing:

"High-Responsiveness for Group Editing CRDTs" (2016) - Modern alternatives to OT
"Conflict-free Replicated Data Types" by Marc Shapiro - CRDT fundamentals

User Interface Research:

"The Information Visualizer: An Information Workspace" (1991) - Spatial information organization
"Spreadsheet Parsing and Formula Understanding" - Research on formula parsing systems

Documentation and Specifications

Web Standards:

HTML5 Canvas Specification - Official canvas API documentation
WebSocket Protocol RFC 6455 - WebSocket specification for real-time communication
Excel File Format Documentation - Understanding Excel's binary formats

Browser APIs:

MDN Canvas API - Comprehensive canvas tutorials and examples
Web Workers API - For offloading computation
Intersection Observer API - Efficient viewport tracking

Open Source Projects

Spreadsheet Implementations:

Luckysheet - Full-featured online spreadsheet
EtherCalc - Web-based collaborative spreadsheet
OnlyOffice - Office suite with collaborative editing

Canvas Libraries:

Fabric.js - Canvas manipulation library with object model
Konva.js - High-performance 2D canvas library
Paper.js - Vector graphics scripting framework

Collaborative Editing:

ShareJS - Operational Transformation implementation
Yjs - CRDT-based collaborative editing framework
Fluid Framework - Microsoft's collaborative development platform

Online Courses and Tutorials

Canvas Programming:

MDN Canvas Tutorial - Step-by-step canvas introduction
HTML5 Canvas Deep Dive - Advanced canvas techniques

Performance Optimization:

Web.dev Performance - Google's performance optimization guides
JavaScript Performance Tips - Browser-specific optimization

Collaborative Systems:

Operational Transformation Explained - Interactive OT examples
CRDT Tutorial - Comprehensive CRDT learning resource

Mathematical Foundations

Linear Algebra:

Understanding coordinate transformations and matrix operations
Vector math for selection rectangles and spatial calculations

Graph Theory:

Dependency graphs for formula calculations
Shortest path algorithms for cell reference resolution

Distributed Systems Theory:

CAP Theorem implications for collaborative editing
Consistency models and their tradeoffs
Vector clocks and logical time

Tools and Development Environment

Performance Profiling:

Chrome DevTools Performance tab for canvas optimization
React DevTools Profiler - Component performance analysis
Web Vitals - User experience metrics

Testing Frameworks:

Jest with Canvas mock for unit testing
Playwright for end-to-end testing with canvas interactions
Testing Library for accessibility testing

Build Tools:

Vite for fast development builds
Rollup for optimized production bundles
TypeScript for type safety in complex applications

With love from the Toki Space team

Find us on tokispace dot com.

This tutorial represents the collective knowledge gained from building Toki's collaborative Excel editor. The resources above will help you dive deeper into any aspect that interests you. For questions or contributions, reach out to our engineering team. [email protected]

Table of Contents

Architecture Overview

Core Technologies

Key Design Decisions

Canvas Rendering System

Understanding Canvas vs DOM for Data Grids

Multi-Layer Canvas Setup

Viewport Calculation: Virtual Scrolling for Massive Datasets

Grid Rendering

Cell Content Rendering

Cell Management and Selection

Cell Addressing System

Selection System

Mouse Event Handling: Coordinate System Transformations

Editing System

In-Cell Editing

Formula Bar Integration

Cell Value Updates

Formatting and Styling

Cell Style System

Format Application

Number Formatting

Real-time Collaboration

The Challenge of Collaborative Editing

Operational Transformation Theory

User Presence System

WebSocket Integration

Collaborative Cursor Rendering

Advanced Features

Row and Column Operations

Cell Merging

Comments System

Find and Replace

Performance Optimization

Viewport-Based Rendering

Efficient Re-rendering: React Optimization Strategies

Memory Management

Key Learnings

1. Canvas vs DOM Trade-offs

2. State Management Complexity

3. Real-time Collaboration Challenges

4. Excel Feature Completeness

5. Browser Limitations

Conclusion

Learning Resources

Books

Research Papers

Documentation and Specifications

Open Source Projects

Online Courses and Tutorials

Mathematical Foundations

Tools and Development Environment