Download web/src/sheet/a1.ts from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 16.6 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/web/src/sheet/a1.ts
- Command line
-
hf download hf://spaces/fsanyoto/loopable/web/src/sheet/a1.ts
-
curl -L -o a1.ts https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/web/src/sheet/a1.ts
16.6 kB
| // A1 addressing on the client (wave 49, W49-T03, ruling R5). | |
| // | |
| // This file owns ONE job: what a formula's text becomes when it is dragged to another cell. The | |
| // EVALUATION of that formula is the server's (`aios-web/api/formula_runtime.py`, `AUTHORITY = | |
| // "server"`), and nothing here computes a value. | |
| // | |
| // β THE RULE, WHICH IS EXCEL'S AND IS NOT NEGOTIABLE: a `$` PINS the part it precedes. | |
| // `B4` both parts shift `$B4` column pinned, row shifts | |
| // `B$4` row pinned, column shifts `$B$4` neither shifts | |
| // A sheet prefix (`Sheet2!C3`) never shifts; it names a tab, not a position. | |
| /** `A` -> 1, `Z` -> 26, `AA` -> 27. Spreadsheet base-26, which has no zero digit. */ | |
| import { ERROR_REF, transformSheetFormula } from "./formulaEval"; | |
| export const colToIndex = (letters: string): number => { | |
| let n = 0; | |
| for (const ch of letters.toUpperCase()) n = n * 26 + (ch.charCodeAt(0) - 64); | |
| return n; | |
| }; | |
| /** The inverse of {@link colToIndex}. */ | |
| export const indexToCol = (index: number): string => { | |
| let n = Math.floor(index); | |
| if (n < 1) return ""; | |
| let out = ""; | |
| while (n > 0) { | |
| const rem = (n - 1) % 26; | |
| out = String.fromCharCode(65 + rem) + out; | |
| n = Math.floor((n - 1) / 26); | |
| } | |
| return out; | |
| }; | |
| /** W57-T01: a whole column from its 1-based index, e.g. 1 -> "A:A". */ | |
| export const columnRangeA1 = (col: number): string => { | |
| const column = indexToCol(col); | |
| return `${column}:${column}`; | |
| }; | |
| /** W57-T01: a whole row from its 1-based index, e.g. 1 -> "1:1". */ | |
| export const rowRangeA1 = (row: number): string => `${row}:${row}`; | |
| export interface CellRef { | |
| sheet: string; | |
| colAbs: boolean; | |
| col: string; | |
| rowAbs: boolean; | |
| row: number; | |
| } | |
| const REF_SOURCE = | |
| "(?:(?:'([^']{1,64})'|([A-Za-z_][A-Za-z0-9_]{0,63}))!)?(\\$?)([A-Za-z]{1,3})(\\$?)([1-9][0-9]{0,6})"; | |
| /** Parse one reference, or null. Exported so a caller can validate without shifting. */ | |
| export const parseRef = (text: string): CellRef | null => { | |
| const m = new RegExp(`^${REF_SOURCE}$`).exec(String(text || "").trim()); | |
| if (!m) return null; | |
| return { | |
| sheet: m[1] || m[2] || "", | |
| colAbs: m[3] === "$", | |
| col: m[4].toUpperCase(), | |
| rowAbs: m[5] === "$", | |
| row: Number(m[6]), | |
| }; | |
| }; | |
| /** | |
| * A bare sheet name the grammar accepts unquoted. Anything else has to wear quotes. | |
| * | |
| * β THIS WAS A BUG BEFORE W50-T20 AND IT ONLY SHOWED UP ON A SHEET WITH A SPACE IN ITS NAME. | |
| * `formatRef` printed the name raw, so a fill or a re-anchor turned `'Q1 plan'!A1` into | |
| * `Q1 plan!A1`, which `REF_SOURCE` cannot parse: the reference stopped being a reference and the | |
| * server answered `bad_formula` for a formula the person never touched. | |
| */ | |
| const BARE_SHEET = /^[A-Za-z_][A-Za-z0-9_]{0,63}$/; | |
| export const formatRef = (ref: CellRef | string): string => { | |
| if (typeof ref === "string") return ref; | |
| const body = `${ref.colAbs ? "$" : ""}${ref.col}${ref.rowAbs ? "$" : ""}${ref.row}`; | |
| if (!ref.sheet) return body; | |
| const name = BARE_SHEET.test(ref.sheet) ? ref.sheet : `'${ref.sheet.replace(/'/g, "")}'`; | |
| return `${name}!${body}`; | |
| }; | |
| /** | |
| * Shift ONE reference by a row and column delta, honouring `$`. | |
| * | |
| * β A shift that would leave the grid is CLAMPED to row 1 / column A rather than wrapping or | |
| * going negative. Excel reports an error there; clamping keeps the text a valid address so the | |
| * server answers with an ordinary `unknown_ref` instead of a parse failure, which is the better | |
| * of the two for a person dragging a fill handle by accident. | |
| */ | |
| export const shiftRef = (ref: CellRef, dRow: number, dCol: number): CellRef => ({ | |
| ...ref, | |
| col: ref.colAbs ? ref.col : indexToCol(Math.max(1, colToIndex(ref.col) + dCol)), | |
| row: ref.rowAbs ? ref.row : Math.max(1, ref.row + dRow), | |
| }); | |
| /** | |
| * ββ THE ONE SCANNER (W50-T20). Walk a formula's TEXT and hand every REFERENCE to `rewrite`, | |
| * copying everything else through byte for byte. | |
| * | |
| * β QUOTED TEXT IS NEVER TOUCHED. `="B4 is the total"` must survive unchanged, so the scan tracks | |
| * string state exactly as the server's `_rewrite` does. A regex over the whole string would | |
| * quietly edit prose, and the damage would only surface as a wrong label on someone's report. | |
| * | |
| * ββ IT IS EXTRACTED RATHER THAN COPIED, AND THAT IS THE POINT OF THIS FUNCTION EXISTING. Drag | |
| * fill and structural re-anchoring are two DIFFERENT rules over the SAME text, and a second | |
| * hand written scanner would be a second opinion about which of `LOG10(`, `'Q1 plan'!A1` and | |
| * `"B4 is the total"` is a reference. One scan, two callers. | |
| */ | |
| export const rewriteRefs = ( | |
| formula: string, | |
| rewrite: (ref: CellRef) => CellRef | string, | |
| rewriteRange?: (from: CellRef, to: CellRef, separator: string) => string, | |
| ): string => { | |
| const src = String(formula || ""); | |
| const scan = new RegExp(REF_SOURCE, "gy"); | |
| let out = ""; | |
| let i = 0; | |
| let quote: string | null = null; | |
| while (i < src.length) { | |
| const c = src[i]; | |
| if (quote) { | |
| out += c; | |
| if (c === quote) quote = null; | |
| i += 1; | |
| continue; | |
| } | |
| if (c === '"' || c === "'") { | |
| // A leading quote could open a STRING or a quoted sheet name. Only a sheet name is followed | |
| // by a closing quote and then `!`, so look ahead before deciding. | |
| const close = src.indexOf(c, i + 1); | |
| const isSheet = c === "'" && close > i && src[close + 1] === "!"; | |
| if (!isSheet) { | |
| quote = c; | |
| out += c; | |
| i += 1; | |
| continue; | |
| } | |
| } | |
| const prev = i ? src[i - 1] : ""; | |
| const boundary = !/[A-Za-z0-9_$]/.test(prev); | |
| if (boundary && /[A-Za-z$']/.test(c)) { | |
| scan.lastIndex = i; | |
| const m = scan.exec(src); | |
| if (m && m.index === i) { | |
| const after = src.slice(m.index + m[0].length); | |
| // The same collision the server guards: `LOG10(` has the shape of a reference. A `(` after | |
| // the token means it is a function name, so it is copied through untouched. | |
| if (!/^\s*\(/.test(after)) { | |
| const ref = parseRef(m[0]); | |
| if (ref) { | |
| // Only the optional range caller consumes a pair. Other callers keep their scan. | |
| const separator = rewriteRange && /^\s*:\s*/.exec(after)?.[0]; | |
| if (rewriteRange && separator) { | |
| scan.lastIndex = m.index + m[0].length + separator.length; | |
| const end = scan.exec(src); | |
| if (end && !/^\s*\(/.test(src.slice(end.index + end[0].length))) { | |
| const last = parseRef(end[0]); | |
| if (last) { | |
| out += rewriteRange(ref, last, separator); | |
| i = end.index + end[0].length; | |
| continue; | |
| } | |
| } | |
| } | |
| out += formatRef(rewrite(ref)); | |
| i = m.index + m[0].length; | |
| continue; | |
| } | |
| } | |
| } | |
| } | |
| out += c; | |
| i += 1; | |
| } | |
| return out; | |
| }; | |
| /** | |
| * Drag-fill: rewrite every reference in a formula's TEXT by the given delta. | |
| * | |
| * A fill copies the formula to another cell, so every unpinned part shifts by the same amount | |
| * wherever it points. That is what makes it different from a structural insert, which shifts by | |
| * POSITION. | |
| */ | |
| export const fillFormula = (formula: string, dRow: number, dCol: number): string => { | |
| const src = String(formula || ""); | |
| if (!dRow && !dCol) return src; | |
| return transformSheetFormula(src, dRow, dCol); | |
| }; | |
| export type RefMove = "copy" | "cut"; | |
| /** | |
| * Copy or cut a formula to another cell, using the same reference scan as drag-fill. | |
| * | |
| * A COPY walks every unpinned reference by the block delta. A CUT is a move, so its formula | |
| * arrives verbatim: shifting it would change which cells the moved formula reads. | |
| */ | |
| export const moveFormula = ( | |
| formula: string, | |
| dRow: number, | |
| dCol: number, | |
| mode: RefMove, | |
| ): string => mode === "cut" ? String(formula || "") : fillFormula(formula, dRow, dCol); | |
| // βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | |
| // ββ W50-T20 β STRUCTURAL RE-ANCHORING, AND WHY IT IS NOT A FILL. | |
| // | |
| // ββ THE TRAP, SAID FIRST, BECAUSE REUSING `shiftRef` HERE LOOKS OBVIOUSLY RIGHT AND IS WRONG. | |
| // A FILL shifts EVERY reference in the formula by the SAME delta, because the formula itself | |
| // moved. A structural INSERT moves nothing; it moves the CELLS a reference points at, and only | |
| // the ones at or below the insertion point. So the shift is decided PER REFERENCE, by comparing | |
| // that reference's own position against the insertion point: | |
| // | |
| // insert one row at row 4 `=B2` stays `=B2` (above the insert: nothing moved) | |
| // `=B4` becomes `=B5` (at or below: the value slid down) | |
| // | |
| // Handing this to `fillFormula(formula, 1, 0)` would turn `=B2` into `=B3` and point it at a cell | |
| // nobody touched. That is a wrong number with no error, on a sheet somebody trusts. | |
| // | |
| // SDLC 61 R3 (2026-09-15) REVERSED THE EARLIER DECISION TO HOLD `$` AGAINST STRUCTURE. | |
| // The owner's Sales dashboard broke when one inserted column left every `$` reference pointing | |
| // at the wrong column. `$` now shifts on a structural insert or delete, matching Excel: the | |
| // referenced cells moved, so every reference must follow them. `$` still pins against a FILL | |
| // and a COPY, which is the only thing it ever meant in Excel. This replaces the earlier | |
| // deliberate divergence so an anchored reference cannot silently read a different cell. | |
| // [[two-gates-can-assert-opposite-things]] | |
| // | |
| // β CROSS TAB IS THE SAME SWEEP, KEYED ON THE NAME. A reference with no sheet prefix means the | |
| // tab the formula lives on; one with a prefix means that tab wherever it lives. So the rewrite | |
| // runs over EVERY tab's formulas and each reference is matched against the tab that actually | |
| // changed. Comparison is case insensitive, as Excel's is. | |
| // βββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββββ | |
| /** One structural change: `delta` rows or columns inserted (positive) or deleted (negative). */ | |
| export interface StructuralShift { | |
| /** The NAME of the tab whose rows or columns moved. */ | |
| sheet: string; | |
| axis: "row" | "col"; | |
| /** The 1-based row number or column index the band starts at. */ | |
| at: number; | |
| /** How many were inserted (> 0) or deleted (< 0). */ | |
| delta: number; | |
| } | |
| const sameName = (a: string, b: string): boolean => | |
| String(a || "").trim().toLowerCase() === String(b || "").trim().toLowerCase(); | |
| /** | |
| * Where one 1-based index lands after `delta` were inserted or deleted at `at`. | |
| * | |
| * R49 retires the deleted-reference clamp: both grammars already parse `#REF!` as an error | |
| * token, so it cannot cause the feared `bad_formula`. A deleted index returns null; only a | |
| * range with surviving cells may clamp its deleted endpoint and shrink. | |
| */ | |
| const shiftIndex = (value: number, at: number, delta: number): number | null => { | |
| if (value < at) return value; | |
| if (delta > 0) return value + delta; | |
| const past = at - delta; // the first index BELOW the deleted band | |
| return value < past ? null : value + delta; | |
| }; | |
| /** | |
| * Re-anchor one formula for a structural insert or delete. | |
| * | |
| * @param ownSheet the NAME of the tab this formula lives on, so a bare reference knows what it | |
| * is bare ABOUT. Passing the wrong one silently re-anchors the wrong tab's references. | |
| */ | |
| export const shiftForStructure = ( | |
| formula: string, | |
| shift: StructuralShift, | |
| ownSheet: string, | |
| ): string => { | |
| if (!shift.delta) return String(formula || ""); | |
| const index = (ref: CellRef): number => shift.axis === "row" ? ref.row : colToIndex(ref.col); | |
| const atIndex = (ref: CellRef, value: number): CellRef => shift.axis === "row" | |
| ? { ...ref, row: value } : { ...ref, col: indexToCol(value) }; | |
| const rewrite = (ref: CellRef): CellRef | string => { | |
| if (!sameName(ref.sheet || ownSheet, shift.sheet)) return ref; | |
| const moved = shiftIndex(index(ref), shift.at, shift.delta); | |
| return moved === null ? ERROR_REF : atIndex(ref, moved); | |
| }; | |
| const rewriteRange = (from: CellRef, to: CellRef, separator: string): string => { | |
| const sheet = from.sheet || ownSheet; | |
| if (to.sheet && !sameName(sheet, to.sheet)) { | |
| return formatRef(rewrite(from)) + separator + formatRef(rewrite(to)); | |
| } | |
| // A bare right endpoint inherits the left endpoint's sheet, not the formula's tab. | |
| if (!sameName(sheet, shift.sheet)) return formatRef(from) + separator + formatRef(to); | |
| const first = index(from), last = index(to); | |
| const low = Math.min(first, last), high = Math.max(first, last); | |
| if (low >= shift.at && high < shift.at - shift.delta) return ERROR_REF; | |
| const move = (value: number): number => shiftIndex(value, shift.at, shift.delta) | |
| ?? (value === low ? shift.at : shift.at - 1); | |
| return formatRef(atIndex(from, move(first))) + separator + formatRef(atIndex(to, move(last))); | |
| }; | |
| // Insertions retain the existing per-reference behavior, as does every scanner caller | |
| // that supplies no range callback (including reference rewrites used for copying). | |
| return rewriteRefs(String(formula || ""), rewrite, shift.delta < 0 ? rewriteRange : undefined); | |
| }; | |
| /** | |
| * ββ S67-T09 (QI5, D-3285) β A CUT CELL TAKES EVERY REFERENCE TO IT ALONG, AS EXCEL DOES. | |
| * | |
| * A move relocates the block `col0:col1 x row0:row1` (1-based, inclusive) on `sheet` by `dRow` / | |
| * `dCol`. A reference that points INTO the block follows it, `$` or not (Excel re-points absolute | |
| * references on a cut too); a range follows only when BOTH ends lie inside the block, so a range | |
| * the block merely overlaps is left as it was. Any other reference is untouched. | |
| */ | |
| export interface CellMove { | |
| /** The NAME of the tab the block moved on. */ | |
| sheet: string; | |
| col0: number; | |
| row0: number; | |
| col1: number; | |
| row1: number; | |
| dRow: number; | |
| dCol: number; | |
| } | |
| export const shiftForMove = (formula: string, move: CellMove, ownSheet: string): string => { | |
| const src = String(formula || ""); | |
| if (!move.dRow && !move.dCol) return src; | |
| const inside = (ref: CellRef, sheet: string): boolean => { | |
| if (!sameName(ref.sheet || sheet, move.sheet)) return false; | |
| const col = colToIndex(ref.col); | |
| return ref.row >= move.row0 && ref.row <= move.row1 && col >= move.col0 && col <= move.col1; | |
| }; | |
| const moved = (ref: CellRef): CellRef => ({ | |
| ...ref, row: ref.row + move.dRow, col: indexToCol(colToIndex(ref.col) + move.dCol), | |
| }); | |
| const rewrite = (ref: CellRef): CellRef => (inside(ref, ownSheet) ? moved(ref) : ref); | |
| const rewriteRange = (from: CellRef, to: CellRef, separator: string): string => { | |
| const sheet = from.sheet || ownSheet; | |
| // A bare right endpoint inherits the left endpoint's sheet, not the formula's tab. | |
| if (inside(from, ownSheet) && inside(to, to.sheet ? ownSheet : sheet)) { | |
| return formatRef(moved(from)) + separator + formatRef(moved(to)); | |
| } | |
| return formatRef(from) + separator + formatRef(to); | |
| }; | |
| return rewriteRefs(src, rewrite, rewriteRange); | |
| }; | |
| /** | |
| * R5's other half: a cross-tab reference FOLLOWS a rename. | |
| * | |
| * β IT IS A SECOND, SEPARATE SWEEP AND NOT A SIDE EFFECT OF THE FIRST. A rename changes WHICH | |
| * NAME a reference wears and nothing about where it points; a structural change is the reverse. | |
| * Folding them together would mean a rename could never happen without an insert, and the day | |
| * somebody needs one alone the other would fire too. | |
| * | |
| * β A BARE REFERENCE IS LEFT BARE. On the renamed tab itself a bare `B4` still means "this tab", | |
| * which is still true after the rename, so writing the new name in would only add noise. | |
| */ | |
| export const renameSheetRefs = (formula: string, from: string, to: string): string => { | |
| if (!from || !to || sameName(from, to)) return String(formula || ""); | |
| return rewriteRefs(String(formula || ""), (ref) => | |
| ref.sheet && sameName(ref.sheet, from) ? { ...ref, sheet: to } : ref); | |
| }; | |
| /** The delta between two addresses, for a fill driven by dragging from one cell to another. */ | |
| export const deltaBetween = (from: string, to: string): { dRow: number; dCol: number } => { | |
| const a = parseRef(from); | |
| const b = parseRef(to); | |
| if (!a || !b) return { dRow: 0, dCol: 0 }; | |
| return { dRow: b.row - a.row, dCol: colToIndex(b.col) - colToIndex(a.col) }; | |
| }; | |