Lite Excel Sheet

Problem Statement

Build a small spreadsheet that starts as a 2 by 2 grid of editable cells, with buttons to add and remove rows and columns and a simple sum formula.

Functional Requirements

Editable cells: Every cell is a text input, and typing updates that cell in the grid.
Add row and column: "Add Row" appends an empty row. "Add Column" appends an empty cell to every row. Existing values stay.
Delete row and column: "Delete Row" removes the last row. "Delete Column" removes the last column.
Sum formula: When a cell that starts with = loses focus, its value is replaced with the sum of the + separated numbers, so =1+2+3 becomes 6. Parts that are not numbers count as 0.

Technical Constraints

Keep the grid as a 2D array in state, plus row and col counts.
Never mutate the current grid. Copy the rows before changing a cell.

Overview
Component Structure
Core Functions Deep Dive
UI Components
Code Flow
Improvements

Overview

import React, { useState } from "react";

A lightweight Excel-like application built using React that provides:

Dynamic grid management
Formula calculation
Row/column manipulation
Real-time cell updates

Component Structure

State Management

const [row, setRow] = useState(2);
const [col, setCol] = useState(2);
const [grid, setGrid] = useState(createGrid(2, 2));

Core Functions Deep Dive

1. Array Methods Explained

Slice vs Splice

// Splice: Modifies original array
const fruits = ["apple", "banana", "orange"];
fruits.splice(1, 1); // Removes 1 element at index 1
console.log(fruits); // ['apple', 'orange']

// Slice: Creates new array
const numbers = [1, 2, 3, 4];
const newNumbers = numbers.slice(1, 3);
console.log(newNumbers); // [2, 3]
console.log(numbers); // Original: [1, 2, 3, 4]

Map Function

// Basic Map
const numbers = [1, 2, 3];
const doubled = numbers.map((num) => num * 2);
console.log(doubled); // [2, 4, 6]

// Grid Creation using Map
const grid = new Array(2).fill("").map(() => new Array(2).fill(""));
/* Result:
[
  ["", ""],
  ["", ""]
]
*/

2. Grid Creation Function

function createGrid(rows, cols) {
  // Method 1: Traditional Loops
  const data = [];
  for (let i = 0; i < rows; i++) {
    const row = [];
    for (let j = 0; j < cols; j++) {
      row.push("");
    }
    data.push(row);
  }
  return data;

  // Method 2: Modern Array Methods
  return new Array(rows).fill("").map(() => new Array(cols).fill(""));
}

3. Grid Manipulation Functions

// Add Row Function: append an empty row, keep existing values
const addRows = () => {
  setRow(row + 1);
  setGrid([...grid, new Array(col).fill("")]);
};

// Delete Row Function (the button deletes the last row)
const deleteRow = (rowIndex) => {
  if (row === 0) return;
  const newGrid = [...grid];
  newGrid.splice(rowIndex, 1);
  setRow(row - 1);
  setGrid(newGrid);
};

// Add Column Function: append an empty cell to every row
const addCols = () => {
  setCol(col + 1);
  setGrid(grid.map((rowData) => [...rowData, ""]));
};

// Delete Column Function (the button deletes the last column)
const deleteCol = (colIndex) => {
  if (col === 0) return;
  const newGrid = grid.map((rowData) =>
    rowData.filter((_, index) => index !== colIndex)
  );
  setCol(col - 1);
  setGrid(newGrid);
};

4. Cell Value Management

const handleCallValueChange = (rowIndex, colIndex, value) => {
  // Copy every row so the old state is not mutated
  const newGrid = grid.map((rowData) => [...rowData]);
  // Update specific cell
  newGrid[rowIndex][colIndex] = value;
  // Update state
  setGrid(newGrid);
};

// Example Usage:
// handleCallValueChange(0, 0, "Hello")
// Changes cell [0,0] to "Hello"

5. Formula Calculation

const calculateForumula = (formula) => {
  // Step 1: Remove '=' prefix
  const withoutEquals = formula.substring(1); // "1+2+3"

  // Step 2: Split into numbers
  const numberStrings = withoutEquals.split("+"); // ["1", "2", "3"]

  // Step 3: Convert to numbers and handle invalid inputs
  const numbers = numberStrings.map((num) => parseFloat(num.trim()) || 0);

  // Step 4: Sum all numbers
  return numbers.reduce((sum, num) => sum + num, 0);
};

// Example:
// calculateForumula("=1+2+3") returns 6
// calculateForumula("=1.5+2.5+invalid") returns 4

UI Components

Table Structure

<table>
  <tbody>
    {grid.map((row, rowIndex) => (
      <tr key={rowIndex}>
        {row.map((cell, cellIndex) => (
          <td key={cellIndex}>
            <input
              type="text"
              value={cell}
              onChange={(e) =>
                handleCallValueChange(rowIndex, cellIndex, e.target.value)
              }
              onBlur={(e) => {
                if (e.target.value.startsWith("=")) {
                  const result = calculateForumula(e.target.value);
                  handleCallValueChange(rowIndex, cellIndex, result);
                }
              }}
            />
          </td>
        ))}
      </tr>
    ))}
  </tbody>
</table>

Code Flow

Initialization
- Component mounts
- Initial 2x2 grid created
- State variables set

User Interactions

// Cell Update Flow
User types → onChange → handleCallValueChange → Grid Update → Rerender

// Formula Flow
User enters "=1+2" → onBlur → calculateFormula → handleCallValueChange → Grid Update

// Grid Manipulation Flow
Click Add Row → addRows → Append Empty Row → Update State → Rerender

Improvements

1. Enhanced Formula Calculation

const enhancedCalculateFormula = (formula) => {
  const operators = {
    "+": (a, b) => a + b,
    "-": (a, b) => a - b,
    "*": (a, b) => a * b,
    "/": (a, b) => (b !== 0 ? a / b : 0),
  };
  // Implementation for multiple operations
};

2. Cell References

const processCellReference = (ref) => {
  const col = ref.charAt(0).toUpperCase().charCodeAt(0) - 65;
  const row = parseInt(ref.substring(1)) - 1;
  return grid[row][col];
};

3. Undo/Redo

const [history, setHistory] = useState([]);
const [currentStep, setCurrentStep] = useState(0);

const undo = () => {
  if (currentStep > 0) {
    setCurrentStep(currentStep - 1);
    setGrid(history[currentStep - 1]);
  }
};

4. Cell Formatting

const formatCell = (value, format) => {
  switch (format) {
    case "currency":
      return new Intl.NumberFormat("en-US", {
        style: "currency",
        currency: "USD",
      }).format(value);
    case "percentage":
      return `${(value * 100).toFixed(2)}%`;
    default:
      return value;
  }
};

5. Performance Optimization

const Cell = React.memo(({ value, onChange }) => (
  <input value={value} onChange={onChange} />
));

6. Error Handling

const handleError = (error, cellPosition) => {
  return {
    hasError: true,
    errorMessage: error.message,
    cellPosition,
  };
};

7. Data Validation

const validateInput = (value, type) => {
  switch (type) {
    case "number":
      return !isNaN(value);
    case "date":
      return !isNaN(Date.parse(value));
    default:
      return true;
  }
};