ساخت Spreadsheet اپلیکیشن با Vue

Spreadsheet applications مانند Google Sheets و Excel ابزارهای محبوبی برای کار با داده هستند. در این مقاله، یاد می‌گیریم چگونه یک spreadsheet کامل با Vue بسازیم که شامل formula engine، cell editing، selection، copy/paste و export به Excel است.

معماری Spreadsheet

Spreadsheet در ظاهر ساده به نظر می‌رسد اما پیچیدگی‌های زیادی دارد. مدیریت thousands of cells، formulas با references، undo/redo، keyboard navigation، selection ranges، copy/paste و performance. همه این‌ها باید smooth کار کنند.

برای performance بالا، معمولاً از virtual scrolling استفاده می‌شود. فقط cells قابل مشاهده render می‌شوند. برای formulas، یک parser و evaluator نیاز است که expressions را handle کند. برای state، معمولاً از Map یا Object استفاده می‌شود که cells را با key مانند "A1" ذخیره می‌کند.

چندین کتابخانه آماده وجود دارد. x-data-grid و AG Grid حرفه‌ای هستند اما commercial. Handsontable همین‌طور. Luckysheet open source است. x-spreadsheet نیز گزینه خوبی است. برای این آموزش، یک implementation ساده و minimal از صفر می‌سازیم تا مفاهیم را یاد بگیریم.

Data Structure

قبل از هر چیز، باید data structure مناسب داشته باشیم. از Map استفاده می‌کنیم که performance خوبی برای lookup دارد.

// stores/spreadsheet.js
import { defineStore } from "pinia"
import { ref, computed } from "vue"

export const useSpreadsheetStore = defineStore("spreadsheet", () => {
  const cells = ref(new Map())
  const rowCount = ref(100)
  const colCount = ref(26)
  const selection = ref({ start: null, end: null })
  const activeCell = ref("A1")
  const columnWidths = ref(new Map())
  const rowHeights = ref(new Map())
  const history = ref([])
  const historyIndex = ref(-1)

  // Convert A1 notation to row/col
  function parseCell(ref) {
    const match = ref.match(/^([A-Z]+)(\d+)$/)
    if (!match) return null

    const colStr = match[1]
    const row = parseInt(match[2]) - 1

    let col = 0
    for (let i = 0; i < colStr.length; i++) {
      col = col * 26 + colStr.charCodeAt(i) - 64
    }
    col-- // 0-indexed

    return { row, col }
  }

  function cellRef(row, col) {
    let colStr = ""
    col++ // 1-indexed
    while (col > 0) {
      col--
      colStr = String.fromCharCode(65 + (col % 26)) + colStr
      col = Math.floor(col / 26)
    }
    return colStr + (row + 1)
  }

  function getCell(ref) {
    return cells.value.get(ref) || { value: "", formula: null, format: {} }
  }

  function setCell(ref, data) {
    pushHistory()
    const existing = getCell(ref)
    cells.value.set(ref, { ...existing, ...data })
  }

  function deleteCell(ref) {
    cells.value.delete(ref)
  }

  // History management
  function pushHistory() {
    if (historyIndex.value < history.value.length - 1) {
      history.value = history.value.slice(0, historyIndex.value + 1)
    }

    const snapshot = new Map(cells.value)
    history.value.push(snapshot)
    historyIndex.value++

    if (history.value.length > 50) {
      history.value.shift()
      historyIndex.value--
    }
  }

  function undo() {
    if (historyIndex.value > 0) {
      historyIndex.value--
      cells.value = new Map(history.value[historyIndex.value])
    }
  }

  function redo() {
    if (historyIndex.value < history.value.length - 1) {
      historyIndex.value++
      cells.value = new Map(history.value[historyIndex.value])
    }
  }

  // Selection
  function setSelection(start, end = null) {
    selection.value = { start, end: end || start }
  }

  function getSelectionRefs() {
    if (!selection.value.start) return []

    const start = parseCell(selection.value.start)
    const end = parseCell(selection.value.end)

    const refs = []
    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++) {
        refs.push(cellRef(r, c))
      }
    }

    return refs
  }

  function isInSelection(ref) {
    const refs = getSelectionRefs()
    return refs.includes(ref)
  }

  // Column resize
  function setColumnWidth(col, width) {
    columnWidths.value.set(col, Math.max(40, width))
  }

  function getColumnWidth(col) {
    return columnWidths.value.get(col) || 100
  }

  return {
    cells, rowCount, colCount, selection, activeCell,
    columnWidths, rowHeights,
    parseCell, cellRef, getCell, setCell, deleteCell,
    undo, redo, pushHistory,
    setSelection, getSelectionRefs, isInSelection,
    setColumnWidth, getColumnWidth,
    canUndo: computed(() => historyIndex.value > 0),
    canRedo: computed(() => historyIndex.value < history.value.length - 1)
  }
})

استفاده از Map به‌جای Object برای cells بسیار مهم است. Map performance بهتری برای frequent updates دارد. همچنین از Set برای selection استفاده می‌کنیم که lookup O(1) است.

Formula Engine

قلب هر spreadsheet، formula engine است. باید expressions را parse کند، cell references را resolve کند و نتیجه را محاسبه کند.

// utils/formulaEngine.js
export class FormulaEngine {
  constructor(getCellValue) {
    this.getCellValue = getCellValue
    this.functions = {
      SUM: (args) => args.reduce((a, b) => a + Number(b || 0), 0),
      AVERAGE: (args) => args.length ? args.reduce((a, b) => a + Number(b || 0), 0) / args.length : 0,
      MIN: (args) => Math.min(...args.map(Number)),
      MAX: (args) => Math.max(...args.map(Number)),
      COUNT: (args) => args.filter(a => !isNaN(Number(a))).length,
      IF: (args) => args[0] ? args[1] : args[2],
      CONCAT: (args) => args.join(""),
      UPPER: (args) => String(args[0]).toUpperCase(),
      LOWER: (args) => String(args[0]).toLowerCase(),
      LEN: (args) => String(args[0]).length,
      ROUND: (args) => Math.round(Number(args[0]) * Math.pow(10, args[1] || 0)) / Math.pow(10, args[1] || 0),
      NOW: () => new Date().toLocaleString("fa-IR"),
      TODAY: () => new Date().toLocaleDateString("fa-IR")
    }
  }

  evaluate(formula, visited = new Set()) {
    if (!formula.startsWith("=")) return formula

    try {
      const expression = formula.slice(1)
      return this.evaluateExpression(expression, visited)
    } catch (err) {
      return "#ERROR!"
    }
  }

  evaluateExpression(expr, visited) {
    // Resolve cell references مانند A1, B2
    expr = expr.replace(/[A-Z]+\d+/g, (match) => {
      if (visited.has(match)) {
        throw new Error("Circular reference")
      }
      visited.add(match)

      const cellValue = this.getCellValue(match)
      if (typeof cellValue === "string" && cellValue.startsWith("=")) {
        return this.evaluate(cellValue, visited)
      }
      return isNaN(Number(cellValue)) ? JSON.stringify(cellValue) : cellValue
    })

    // Resolve ranges مانند A1:A10
    expr = expr.replace(/[A-Z]+\d+:[A-Z]+\d+/g, (match) => {
      const [start, end] = match.split(":")
      const values = this.expandRange(start, end).map(ref => {
        const val = this.getCellValue(ref)
        return typeof val === "string" && val.startsWith("=")
          ? this.evaluate(val, visited)
          : val
      })
      return JSON.stringify(values)
    })

    // Resolve functions
    expr = this.resolveFunctions(expr)

    // Evaluate final expression
    return eval(expr)
  }

  resolveFunctions(expr) {
    const funcRegex = /([A-Z]+)\(([^()]*)\)/g

    while (funcRegex.test(expr)) {
      expr = expr.replace(funcRegex, (match, funcName, argsStr) => {
        const func = this.functions[funcName]
        if (!func) throw new Error("Unknown function: " + funcName)

        let args = []
        if (argsStr.trim()) {
          args = argsStr.split(",").map(a => {
            const trimmed = a.trim()
            try {
              return JSON.parse(trimmed)
            } catch {
              return eval(trimmed)
            }
          })

          // Flatten arrays (for ranges)
          args = args.flat()
        }

        return func(args)
      })
    }

    return expr
  }

  expandRange(start, end) {
    const parseRef = (ref) => {
      const match = ref.match(/^([A-Z]+)(\d+)$/)
      let col = 0
      const colStr = match[1]
      for (let i = 0; i < colStr.length; i++) {
        col = col * 26 + colStr.charCodeAt(i) - 64
      }
      return { col: col - 1, row: parseInt(match[2]) - 1 }
    }

    const toRef = (row, col) => {
      let colStr = ""
      col++
      while (col > 0) {
        col--
        colStr = String.fromCharCode(65 + (col % 26)) + colStr
        col = Math.floor(col / 26)
      }
      return colStr + (row + 1)
    }

    const s = parseRef(start)
    const e = parseRef(end)
    const refs = []

    for (let r = Math.min(s.row, e.row); r <= Math.max(s.row, e.row); r++) {
      for (let c = Math.min(s.col, e.col); c <= Math.max(s.col, e.col); c++) {
        refs.push(toRef(r, c))
      }
    }

    return refs
  }
}

کامپوننت Grid

کامپوننت اصلی که grid را render می‌کند با virtual scrolling برای performance.

<!-- components/Spreadsheet.vue -->
<script setup>
import { ref, computed, onMounted } from "vue"
import { useSpreadsheetStore } from "@/stores/spreadsheet"
import { storeToRefs } from "pinia"
import { FormulaEngine } from "@/utils/formulaEngine"
import Cell from "./Cell.vue"

const store = useSpreadsheetStore()
const { cells, rowCount, colCount, activeCell } = storeToRefs(store)

const scrollContainer = ref(null)
const scrollTop = ref(0)
const scrollLeft = ref(0)
const viewportHeight = ref(600)
const viewportWidth = ref(1000)

const CELL_HEIGHT = 28
const HEADER_HEIGHT = 30
const ROW_HEADER_WIDTH = 50

// Formula engine
const engine = new FormulaEngine((ref) => store.getCell(ref).value)

function getDisplayValue(ref) {
  const cell = store.getCell(ref)
  if (!cell.formula) return cell.value
  return engine.evaluate(cell.formula)
}

// Visible range calculation
const visibleRange = computed(() => {
  const startRow = Math.max(0, Math.floor(scrollTop.value / CELL_HEIGHT) - 2)
  const endRow = Math.min(
    rowCount.value,
    Math.ceil((scrollTop.value + viewportHeight.value) / CELL_HEIGHT) + 2
  )

  let startCol = 0
  let endCol = 0
  let cumWidth = 0

  for (let c = 0; c < colCount.value; c++) {
    const width = store.getColumnWidth(c)
    if (cumWidth + width < scrollLeft.value) {
      startCol = c + 1
    }
    cumWidth += width
    if (cumWidth < scrollLeft.value + viewportWidth.value) {
      endCol = c + 1
    }
  }

  return { startRow, endRow, startCol, endCol: Math.min(colCount.value, endCol + 2) }
})

// Total size
const totalWidth = computed(() => {
  let sum = 0
  for (let c = 0; c < colCount.value; c++) {
    sum += store.getColumnWidth(c)
  }
  return sum
})

const totalHeight = computed(() => rowCount.value * CELL_HEIGHT)

function handleScroll(e) {
  scrollTop.value = e.target.scrollTop
  scrollLeft.value = e.target.scrollLeft
}

// Column letter from index
function colLetter(col) {
  let str = ""
  col++
  while (col > 0) {
    col--
    str = String.fromCharCode(65 + (col % 26)) + str
    col = Math.floor(col / 26)
  }
  return str
}

// Column position
function getColumnLeft(col) {
  let left = 0
  for (let c = 0; c < col; c++) {
    left += store.getColumnWidth(c)
  }
  return left
}

// Keyboard navigation
function handleKeyDown(e) {
  const { row, col } = store.parseCell(activeCell.value)

  let newRow = row
  let newCol = col

  switch (e.key) {
    case "ArrowUp": newRow = Math.max(0, row - 1); break
    case "ArrowDown": newRow = Math.min(rowCount.value - 1, row + 1); break
    case "ArrowLeft": newCol = Math.max(0, col - 1); break
    case "ArrowRight": newCol = Math.min(colCount.value - 1, col + 1); break
    case "Enter": newRow = Math.min(rowCount.value - 1, row + 1); break
    case "Tab":
      e.preventDefault()
      newCol = e.shiftKey ? Math.max(0, col - 1) : Math.min(colCount.value - 1, col + 1)
      break
    case "Delete":
    case "Backspace":
      store.getSelectionRefs().forEach(ref => store.deleteCell(ref))
      return
  }

  if (newRow !== row || newCol !== col) {
    e.preventDefault()
    const newRef = store.cellRef(newRow, newCol)
    activeCell.value = newRef
    store.setSelection(newRef)
  }

  // Undo/Redo
  if ((e.ctrlKey || e.metaKey) && e.key === "z") {
    e.preventDefault()
    store.undo()
  }
  if ((e.ctrlKey || e.metaKey) && e.key === "y") {
    e.preventDefault()
    store.redo()
  }
}

onMounted(() => {
  window.addEventListener("keydown", handleKeyDown)
})
</script>

<template>
  <div class="spreadsheet" tabindex="0">
    <!-- Formula bar -->
    <div class="formula-bar">
      <span class="active-cell">{{ activeCell }}</span>
      <input
        :value="store.getCell(activeCell).formula || store.getCell(activeCell).value"
        @input="store.setCell(activeCell, {
          [$event.target.value.startsWith(\"=\") ? \"formula\" : \"value\"]: $event.target.value
        })"
        placeholder="formula یا مقدار..."
      />
    </div>

    <!-- Grid container -->
    <div
      ref="scrollContainer"
      class="grid-container"
      @scroll="handleScroll"
    >
      <!-- Fake content for scroll sizing -->
      <div
        class="grid-content"
        :style="{ width: totalWidth + \"px\", height: totalHeight + \"px\" }"
      >
        <!-- Column headers -->
        <div class="col-headers">
          <div
            v-for="col in Array.from({length: visibleRange.endCol - visibleRange.startCol}, (_, i) => i + visibleRange.startCol)"
            :key="col"
            class="col-header"
            :style="{
              left: getColumnLeft(col) + \"px\",
              width: store.getColumnWidth(col) + \"px\"
            }"
          >
            {{ colLetter(col) }}
          </div>
        </div>

        <!-- Cells -->
        <div
          v-for="row in Array.from({length: visibleRange.endRow - visibleRange.startRow}, (_, i) => i + visibleRange.startRow)"
          :key="row"
          class="row"
          :style="{ top: row * CELL_HEIGHT + \"px\" }"
        >
          <div class="row-header">{{ row + 1 }}</div>

          <Cell
            v-for="col in Array.from({length: visibleRange.endCol - visibleRange.startCol}, (_, i) => i + visibleRange.startCol)"
            :key="col"
            :ref-id="store.cellRef(row, col)"
            :row="row"
            :col="col"
            :value="getDisplayValue(store.cellRef(row, col))"
            :raw-value="store.getCell(store.cellRef(row, col)).formula || store.getCell(store.cellRef(row, col)).value"
            :is-active="activeCell === store.cellRef(row, col)"
            :is-selected="store.isInSelection(store.cellRef(row, col))"
            :style="{
              left: getColumnLeft(col) + \"px\",
              width: store.getColumnWidth(col) + \"px\",
              height: CELL_HEIGHT + \"px\"
            }"
          />
        </div>
      </div>
    </div>
  </div>
</template>

<style scoped>
.spreadsheet {
  display: flex;
  flex-direction: column;
  height: 100vh;
  font-family: sans-serif;
}

.formula-bar {
  display: flex;
  gap: 10px;
  padding: 8px;
  background: #f5f5f5;
  border-bottom: 1px solid #ddd;
}

.active-cell {
  min-width: 60px;
  padding: 4px 8px;
  background: white;
  border: 1px solid #ccc;
  font-family: monospace;
  font-weight: bold;
}

.formula-bar input {
  flex: 1;
  padding: 4px 8px;
  border: 1px solid #ccc;
}

.grid-container {
  flex: 1;
  overflow: auto;
  position: relative;
}

.col-headers {
  position: sticky;
  top: 0;
  z-index: 2;
  height: 30px;
  background: #e0e0e0;
}

.col-header {
  position: absolute;
  height: 100%;
  display: flex;
  align-items: center;
  justify-content: center;
  border-right: 1px solid #ccc;
  background: #e0e0e0;
  font-weight: bold;
  font-size: 12px;
}

.row-header {
  position: absolute;
  left: 0;
  width: 50px;
  height: 100%;
  display: flex;
  align-items: center;
  justify-content: center;
  background: #e0e0e0;
  border-right: 1px solid #ccc;
  border-bottom: 1px solid #ccc;
  font-size: 12px;
}

.row {
  position: absolute;
  left: 0;
  right: 0;
}
</style>

کامپوننت Cell

هر cell یک component مستقل است که edit mode، display و keyboard events را handle می‌کند.

<!-- components/Cell.vue -->
<script setup>
import { ref, watch, nextTick } from "vue"
import { useSpreadsheetStore } from "@/stores/spreadsheet"

const props = defineProps({
  refId: String,
  row: Number,
  col: Number,
  value: [String, Number],
  rawValue: String,
  isActive: Boolean,
  isSelected: Boolean
})

const store = useSpreadsheetStore()
const editing = ref(false)
const editValue = ref("")
const inputRef = ref(null)

function handleClick() {
  store.setSelection(props.refId)
  store.activeCell = props.refId
}

async function handleDoubleClick() {
  editing.value = true
  editValue.value = props.rawValue || ""
  await nextTick()
  inputRef.value?.focus()
  inputRef.value?.select()
}

function handleEditEnd(save = true) {
  if (save) {
    const data = editValue.value.startsWith("=")
      ? { formula: editValue.value, value: "" }
      : { value: editValue.value, formula: null }

    store.setCell(props.refId, data)
  }
  editing.value = false
}

function handleKeyDown(e) {
  if (e.key === "Enter") {
    handleEditEnd(true)
  } else if (e.key === "Escape") {
    handleEditEnd(false)
  }
}

watch(() => props.isActive, (active) => {
  if (active && !editing.value) {
    // Listen to typing to start editing
  }
})
</script>

<template>
  <div
    class="cell"
    :class="{
      active: isActive,
      selected: isSelected,
      editing
    }"
    @click="handleClick"
    @dblclick="handleDoubleClick"
  >
    <input
      v-if="editing"
      ref="inputRef"
      v-model="editValue"
      @blur="handleEditEnd(true)"
      @keydown="handleKeyDown"
    />
    <span v-else class="value">{{ value }}</span>
  </div>
</template>

<style scoped>
.cell {
  position: absolute;
  border-right: 1px solid #ddd;
  border-bottom: 1px solid #ddd;
  background: white;
  overflow: hidden;
  padding: 4px 6px;
  font-size: 13px;
  cursor: cell;
  display: flex;
  align-items: center;
  margin-top: 30px; /* for header */
  margin-left: 50px; /* for row header */
}

.cell.active {
  outline: 2px solid #660000;
  z-index: 1;
}

.cell.selected {
  background: rgba(102, 0, 0, 0.1);
}

.cell.editing {
  padding: 0;
  z-index: 2;
}

.cell input {
  width: 100%;
  height: 100%;
  padding: 4px 6px;
  border: 2px solid #660000;
  font-size: 13px;
  outline: none;
}

.value {
  white-space: nowrap;
  overflow: hidden;
  text-overflow: ellipsis;
  width: 100%;
}
</style>

Copy, Paste و Export

یکی از قابلیت‌های حیاتی spreadsheet، کار با clipboard و export به Excel است.

// Copy to clipboard
function copySelection() {
  const refs = store.getSelectionRefs()
  const rows = {}

  refs.forEach(ref => {
    const { row, col } = store.parseCell(ref)
    if (!rows[row]) rows[row] = {}
    rows[row][col] = store.getCell(ref).value
  })

  const text = Object.keys(rows)
    .sort((a, b) => a - b)
    .map(row => {
      const cols = Object.keys(rows[row]).sort((a, b) => a - b)
      return cols.map(col => rows[row][col]).join("\t")
    })
    .join("\n")

  navigator.clipboard.writeText(text)
}

// Paste from clipboard
async function pasteSelection() {
  const text = await navigator.clipboard.readText()
  const lines = text.split("\n")

  const start = store.parseCell(store.activeCell)

  lines.forEach((line, rowOffset) => {
    const cells = line.split("\t")
    cells.forEach((value, colOffset) => {
      const ref = store.cellRef(start.row + rowOffset, start.col + colOffset)
      store.setCell(ref, {
        value,
        formula: value.startsWith("=") ? value : null
      })
    })
  })
}

// Export to CSV
function exportCSV() {
  const rows = []
  for (let r = 0; r < store.rowCount; r++) {
    const row = []
    for (let c = 0; c < store.colCount; c++) {
      const ref = store.cellRef(r, c)
      const value = String(store.getCell(ref).value || "")
      row.push("\"" + value.replace(/"/g, "\"\"") + "\"")
    }
    if (row.some(v => v !== "\"\"")) {
      rows.push(row.join(","))
    }
  }

  const csv = "\uFEFF" + rows.join("\n")
  const blob = new Blob([csv], { type: "text/csv;charset=utf-8" })
  const url = URL.createObjectURL(blob)

  const link = document.createElement("a")
  link.href = url
  link.download = "spreadsheet.csv"
  link.click()
  URL.revokeObjectURL(url)
}

// Export to Excel با SheetJS
import * as XLSX from "xlsx"

function exportExcel() {
  const data = []
  for (let r = 0; r < store.rowCount; r++) {
    const row = []
    for (let c = 0; c < store.colCount; c++) {
      const ref = store.cellRef(r, c)
      row.push(store.getCell(ref).value || "")
    }
    if (row.some(v => v !== "")) data.push(row)
  }

  const ws = XLSX.utils.aoa_to_sheet(data)
  const wb = XLSX.utils.book_new()
  XLSX.utils.book_append_sheet(wb, ws, "Sheet1")
  XLSX.writeFile(wb, "spreadsheet.xlsx")
}

بهترین شیوه‌ها

برای ساخت spreadsheet performant، این نکات را رعایت کنید. اول، virtual scrolling ضروری است. render کردن 10000 cell بدون آن، browser را freeze می‌کند. دوم، lazy formula evaluation. فقط visible cells را calculate کنید.

سوم، keyboard shortcuts کامل. کاربران spreadsheet عادت دارند. چهارم، copy/paste با tab و newline delimiters. پنجم، undo/redo با snapshot یا command pattern.

ششم، column/row resize با drag. هفتم، freeze panes برای headers. هشتم، multiple sheets. نهم، formulas پیشرفته (VLOOKUP، INDEX، MATCH). دهم، charts از selection. یازدهم، collaborative editing با CRDT.

Spreadsheet application یکی از پیچیده‌ترین پروژه‌های frontend است اما بسیار آموزنده. مفاهیم زیادی را یاد می‌گیرید: virtual scrolling، formula parsing، state management بهینه و keyboard handling. اگر می‌خواهید production-ready باشد، از کتابخانه‌های موجود مانند Handsontable استفاده کنید.