client/src/utils/formulaEngine.ts - develop - kan64/spreadsheet-lab

client/src/utils/formulaEngine.ts
242 строки7 KB
Anton Kravchenkov
first_commit
18 фев 2026, 23:41
18 фев 2026, 23:4142314b2
import { CellData } from '../types/types.ts';
import { parseCellId, cellId } from './cellUtils';

type CellGetter = (id: string) => CellData | undefined;

function resolveCellValue(id: string, getCellData: CellGetter, visited: Set<string>): number {
  if (visited.has(id)) return NaN; // circular reference
  visited.add(id);

  const cell = getCellData(id);
  if (!cell) return 0;

  if (cell.formula) {
    const result = evaluateFormula(cell.formula, getCellData, visited);
    return typeof result === 'number' ? result : parseFloat(result) || 0;
  }

  const num = parseFloat(cell.value);
  return isNaN(num) ? 0 : num;
}

function expandRange(rangeStr: string): string[] {
  const parts = rangeStr.split(':');
  if (parts.length !== 2) return [rangeStr];

  const start = parseCellId(parts[0].trim());
  const end = parseCellId(parts[1].trim());
  if (!start || !end) return [rangeStr];

  const cells: string[] = [];
  const minRow = Math.min(start.row, end.row);
  const maxRow = Math.max(start.row, end.row);
  const minCol = Math.min(start.col, end.col);
  const maxCol = Math.max(start.col, end.col);

  for (let r = minRow; r <= maxRow; r++) {
    for (let c = minCol; c <= maxCol; c++) {
      cells.push(cellId(r, c));
    }
  }
  return cells;
}

function getArgs(argsStr: string, getCellData: CellGetter, visited: Set<string>): number[] {
  const values: number[] = [];
  const parts = splitArgs(argsStr);

  for (const part of parts) {
    const trimmed = part.trim();
    if (trimmed.includes(':')) {
      const cellIds = expandRange(trimmed);
      for (const id of cellIds) {
        values.push(resolveCellValue(id, getCellData, new Set(visited)));
      }
    } else if (parseCellId(trimmed)) {
      values.push(resolveCellValue(trimmed, getCellData, new Set(visited)));
    } else {
      const num = parseFloat(trimmed);
      if (!isNaN(num)) values.push(num);
    }
  }

  return values;
}

function splitArgs(str: string): string[] {
  const args: string[] = [];
  let depth = 0;
  let current = '';

  for (const ch of str) {
    if (ch === '(') depth++;
    else if (ch === ')') depth--;

    if (ch === ',' && depth === 0) {
      args.push(current);
      current = '';
    } else {
      current += ch;
    }
  }
  if (current) args.push(current);
  return args;
}

export function evaluateFormula(
  formula: string,
  getCellData: CellGetter,
  visited: Set<string> = new Set()
): string | number {
  try {
    const trimmed = formula.trim();

    const funcMatch = trimmed.match(/^([A-Z]+)\((.+)\)$/i);
    if (funcMatch) {
      const funcName = funcMatch[1].toUpperCase();
      const argsStr = funcMatch[2];

      switch (funcName) {
        case 'SUM': {
          const vals = getArgs(argsStr, getCellData, visited);
          return vals.reduce((a, b) => a + b, 0);
        }
        case 'AVERAGE':
        case 'AVG': {
          const vals = getArgs(argsStr, getCellData, visited);
          if (vals.length === 0) return 0;
          return vals.reduce((a, b) => a + b, 0) / vals.length;
        }
        case 'MIN': {
          const vals = getArgs(argsStr, getCellData, visited);
          if (vals.length === 0) return 0;
          return Math.min(...vals);
        }
        case 'MAX': {
          const vals = getArgs(argsStr, getCellData, visited);
          if (vals.length === 0) return 0;
          return Math.max(...vals);
        }
        case 'COUNT': {
          const vals = getArgs(argsStr, getCellData, visited);
          return vals.filter((v) => !isNaN(v)).length;
        }
        case 'IF': {
          const parts = splitArgs(argsStr);
          if (parts.length < 3) return '#ERROR!';
          const condition = evaluateExpression(parts[0].trim(), getCellData, visited);
          const condNum = typeof condition === 'number' ? condition : parseFloat(String(condition));
          if (condNum && condNum !== 0) {
            return evaluateExpression(parts[1].trim(), getCellData, visited);
          } else {
            return evaluateExpression(parts[2].trim(), getCellData, visited);
          }
        }
        case 'ABS': {
          const vals = getArgs(argsStr, getCellData, visited);
          return vals.length > 0 ? Math.abs(vals[0]) : 0;
        }
        case 'ROUND': {
          const parts = splitArgs(argsStr);
          const val = evaluateExpression(parts[0].trim(), getCellData, visited);
          const digits = parts.length > 1 ? Number(evaluateExpression(parts[1].trim(), getCellData, visited)) : 0;
          const numVal = typeof val === 'number' ? val : parseFloat(String(val));
          return Number(numVal.toFixed(digits));
        }
        case 'CONCAT':
        case 'CONCATENATE': {
          const parts = splitArgs(argsStr);
          return parts
            .map((p) => {
              const t = p.trim();
              if (t.startsWith('"') && t.endsWith('"')) return t.slice(1, -1);
              const cellPos = parseCellId(t);
              if (cellPos) {
                const cell = getCellData(t);
                return cell?.computedValue ?? cell?.value ?? '';
              }
              return t;
            })
            .join('');
        }
        default:
          return '#NAME?';
      }
    }

    return evaluateExpression(trimmed, getCellData, visited);
  } catch {
    return '#ERROR!';
  }
}

function evaluateExpression(
  expr: string,
  getCellData: CellGetter,
  visited: Set<string>
): string | number {
  const trimmed = expr.trim();

  if (trimmed.startsWith('"') && trimmed.endsWith('"')) {
    return trimmed.slice(1, -1);
  }

  const funcMatch = trimmed.match(/^([A-Z]+)\((.+)\)$/i);
  if (funcMatch) {
    return evaluateFormula(trimmed, getCellData, visited);
  }

  // Replace cell references with their values
  let processed = trimmed.replace(/[A-Z]+\d+/g, (match) => {
    const val = resolveCellValue(match, getCellData, new Set(visited));
    return String(val);
  });

  // Simple arithmetic: support +, -, *, /, comparison
  processed = processed.replace(/\s+/g, '');

  // Comparison operators
  for (const op of ['>=', '<=', '<>', '!=', '>', '<', '=']) {
    const idx = processed.indexOf(op);
    if (idx > 0 && idx < processed.length - op.length) {
      const left = parseFloat(processed.slice(0, idx));
      const right = parseFloat(processed.slice(idx + op.length));
      if (!isNaN(left) && !isNaN(right)) {
        switch (op) {
          case '>': return left > right ? 1 : 0;
          case '<': return left < right ? 1 : 0;
          case '>=': return left >= right ? 1 : 0;
          case '<=': return left <= right ? 1 : 0;
          case '=': return left === right ? 1 : 0;
          case '<>':
          case '!=': return left !== right ? 1 : 0;
        }
      }
    }
  }

  // Try to evaluate simple arithmetic
  try {
    if (/^[\d+\-*/().]+$/.test(processed)) {
      const result = Function(`"use strict"; return (${processed})`)();
      if (typeof result === 'number' && isFinite(result)) return result;
    }
  } catch {
    // fall through
  }

  const num = parseFloat(processed);
  if (!isNaN(num)) return num;

  return processed;
}

export function computeCell(
  cellData: CellData,
  getCellData: CellGetter
): string | number {
  if (cellData.formula) {
    return evaluateFormula(cellData.formula, getCellData);
  }
  return cellData.value;
}