Spaces:
Running on CPU Upgrade
Running on CPU Upgrade
File size: 16,621 Bytes
f530297 c1fc133 82cdb59 f530297 82cdb59 f530297 c1fc133 f530297 c1fc133 f530297 c1fc133 f530297 82cdb59 f530297 82cdb59 f530297 82cdb59 f530297 c1fc133 f530297 c1fc133 f530297 c1fc133 f530297 c1fc133 f530297 c1fc133 f530297 c1fc133 f530297 c1fc133 f530297 2eae9f0 f530297 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 | // 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) };
};
|