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

client/src/utils/cellUtils.ts
134 строки4 KB
Anton Kravchenkov
feat(ui): Add date autofill
11 мар 2026, 22:27
11 мар 2026, 22:277d27916
import { CellPosition, CellRange } from '../types/types.ts';

export function colToLetter(col: number): string {
  let result = '';
  let c = col;
  while (c >= 0) {
    result = String.fromCharCode((c % 26) + 65) + result;
    c = Math.floor(c / 26) - 1;
  }
  return result;
}

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

export function cellId(row: number, col: number): string {
  return `${colToLetter(col)}${row + 1}`;
}

export function parseCellId(id: string): CellPosition | null {
  const match = id.match(/^([A-Z]+)(\d+)$/);
  if (!match) return null;
  return {
    col: letterToCol(match[1]),
    row: parseInt(match[2], 10) - 1,
  };
}

export function normalizeRange(range: CellRange): CellRange {
  return {
    start: {
      row: Math.min(range.start.row, range.end.row),
      col: Math.min(range.start.col, range.end.col),
    },
    end: {
      row: Math.max(range.start.row, range.end.row),
      col: Math.max(range.start.col, range.end.col),
    },
  };
}

export function isCellInRange(pos: CellPosition, range: CellRange | null): boolean {
  if (!range) return false;
  const norm = normalizeRange(range);
  return (
    pos.row >= norm.start.row &&
    pos.row <= norm.end.row &&
    pos.col >= norm.start.col &&
    pos.col <= norm.end.col
  );
}

export function getRangeCells(range: CellRange): CellPosition[] {
  const norm = normalizeRange(range);
  const cells: CellPosition[] = [];
  for (let row = norm.start.row; row <= norm.end.row; row++) {
    for (let col = norm.start.col; col <= norm.end.col; col++) {
      cells.push({ row, col });
    }
  }
  return cells;
}

export function formatCellValue(value: string | number | undefined, format?: string): string {
  if (value === undefined || value === null || value === '') return '';
  const num = typeof value === 'string' ? parseFloat(value) : value;

  if (format && !isNaN(num)) {
    switch (format) {
      case 'number':
        return num.toFixed(2);
      case 'currency':
        return `$${num.toFixed(2)}`;
      case 'percent':
        return `${(num * 100).toFixed(1)}%`;
      default:
        break;
    }
  }

  return String(value);
}

/** Parse cell value as date. Returns Date or null if not parseable. Uses DD.MM.YYYY (European) format. */
export function parseCellDate(value: string | number | undefined): Date | null {
  if (value === undefined || value === null || value === '') return null;
  const s = String(value).trim();
  if (!s) return null;
  let d: Date;
  // DD.MM.YYYY or DD/MM/YYYY — проверяем первым, чтобы не путать с US форматом
  const dmY = s.match(/^(\d{1,2})[./](\d{1,2})[./](\d{4})$/);
  if (dmY) {
    d = new Date(parseInt(dmY[3], 10), parseInt(dmY[2], 10) - 1, parseInt(dmY[1], 10));
    if (!isNaN(d.getTime())) return d;
  }
  // DD.MM.YY (2-digit year) — проверяем до new Date(s), т.к. "01.06.26" иначе парсится как MM.DD.YY
  const dmY2 = s.match(/^(\d{1,2})[./](\d{1,2})[./](\d{2})$/);
  if (dmY2) {
    const y = parseInt(dmY2[3], 10);
    const fullY = y >= 50 ? 1900 + y : 2000 + y;
    d = new Date(fullY, parseInt(dmY2[2], 10) - 1, parseInt(dmY2[1], 10));
    if (!isNaN(d.getTime())) return d;
  }
  // YYYY-MM-DD
  const ymd = s.match(/^(\d{4})-(\d{1,2})-(\d{1,2})$/);
  if (ymd) {
    d = new Date(parseInt(ymd[1], 10), parseInt(ymd[2], 10) - 1, parseInt(ymd[3], 10));
    if (!isNaN(d.getTime())) return d;
  }
  // ISO / built-in (только если не подошли явные форматы)
  d = new Date(s);
  if (!isNaN(d.getTime())) return d;
  // Excel serial (days since 1899-12-30)
  const serial = parseFloat(s);
  if (!isNaN(serial) && serial > 0 && serial < 1000000) {
    d = new Date(1899, 11, 30);
    d.setDate(d.getDate() + Math.floor(serial));
    if (!isNaN(d.getTime())) return d;
  }
  return null;
}

/** Format date for cell display (DD.MM.YYYY) */
export function formatDateForCell(date: Date): string {
  const d = date.getDate().toString().padStart(2, '0');
  const m = (date.getMonth() + 1).toString().padStart(2, '0');
  const y = date.getFullYear();
  return `${d}.${m}.${y}`;
}