Building an in-browser Excel formula grader with Univer and Astro

#astro #javascript #frontend #webdev

Most coding judges run code in a backend sandbox. For Excel practice, that model is a bad fit: you want real spreadsheet feel, instant feedback, and zero compute cost per submit.

On SheetDo, we grade formulas entirely in the browser: Astro SSG for SEO-friendly pages, Univer for the grid + formula engine, and a small React island that runs multi-case checks locally.

Why Astro + Univer

Three goals drove the stack:

SEO + fast first paint — practice pages need crawlable HTML and structured data.
Real Excel interaction — selection, formula bar, cell refs, Canvas grid.
Zero backend compute — every recalculation stays on the client.

We also skipped writing our own formula parser. Real Excel needs ranges, absolute refs, dynamic arrays, and messy type coercion. Univer’s @univerjs/engine-formula already does that.

Astro static shell (SEO, copy, JSON-LD, skeleton)
        │  client:only="react"
        ▼
ExerciseRunner (React island)
        │
        ├── SheetEditor (Univer grid + formula engine)
        └── Multi-case grader (inject data → recalc → compare)

Pitfall 1: Univer and SSR don’t mix

Univer needs window, document, and Canvas. Astro SSR will crash it. We mount the runner with client:only="react" and ship a matching Astro skeleton as slot="fallback" so the layout doesn’t jump while the island boots.

<ExerciseRunner client:only="react" exercise={exercise} locale={locale}>
  <ExerciseRunnerSkeleton slot="fallback" exercise={exercise} locale={locale} />
</ExerciseRunner>

Vite also needs help. Univer’s package graph is deep; stale optimizeDeps caches caused blank islands in dev. We pin the full import set in astro.config.ts and mark @univerjs/* as ssr.noExternal.

Pitfall 2: lock the sheet, but still mutate for grading

Learners see a full spreadsheet. Without locks, they can edit source data and “pass” by cheating the inputs.

We cancel edits outside the answer cells with Univer events (BeforeSheetEditStart, BeforeCommandExecute), then open a short programmatic escape hatch for grading:

async function withEditableSheet(mutate) {
  allowProgrammaticMutateRef.current = true;
  try {
    return await mutate(sheet);
  } finally {
    allowProgrammaticMutateRef.current = false;
  }
}

Users can’t touch locked cells. The grader still can rewrite cases and apply formulas.

The grader: multi-case, anti-hardcoding

A single expected value isn’t enough. =1500 would pass a sum exercise. On Submit we:

Require a formula starting with =.
Optionally require a function keyword (XLOOKUP, SUM, …).
For each test case: inject cells → apply the user’s formula → wait for calculation → compare.
Restore the visible case and show pass/fail per case.

for (const tc of testCases) {
  await editor.updateCells({ ...baseCells, ...tc.cells });
  const value = await editor.applyFormula(target, formula);
  evaluations.push({ actualValue: value, expectedValue: tc.expectedValue });
}

The subtle bit: Univer calculates asynchronously. After setFormula, wait for onCalculationResultApplied before reading the cell — otherwise you grade a stale value.

Comparison also tolerates floats, currency symbols, and commas (valuesEqual with a 1e-9 epsilon).

Headless CI for the exercise bank

Every JSON exercise has official solutions. We boot a UI-less Univer in Vitest (core + sheets + formula plugins only), run each solution against every case, and fail CI if an answer drifts. No Playwright, no browser — seconds for the whole bank.

What we got

	Backend sandbox	Client grader (Astro + Univer)
Latency	hundreds of ms+	tens of ms
Compute cost	scales with submits	~CDN only
Offline	weak	works after first load
SEO	SPA friction	static HTML

Takeaways: keep heavy spreadsheet UI out of SSR; grade with injected cases, not string matching; always wait for the formula engine before reading results.

Live at sheetdo.com.

DEV Community