File size: 16,621 Bytes
e9a4c3e
 
 
 
 
 
 
 
 
 
 
 
b80130d
4b54047
e9a4c3e
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
f5696cb
 
 
 
 
 
 
 
 
e9a4c3e
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
ce4ad7b
 
 
 
 
 
 
 
 
 
b80130d
 
e9a4c3e
ce4ad7b
 
 
e9a4c3e
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
ce4ad7b
 
e9a4c3e
ce4ad7b
 
e9a4c3e
ce4ad7b
 
 
 
 
e9a4c3e
ce4ad7b
 
b80130d
 
ce4ad7b
e9a4c3e
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
b80130d
 
 
 
 
 
 
 
 
 
 
 
 
 
ce4ad7b
e9a4c3e
 
 
 
 
 
 
 
 
 
 
 
ce4ad7b
 
 
e13472e
 
 
ce4ad7b
 
 
 
4b54047
ce4ad7b
 
e13472e
 
 
 
 
 
 
 
 
 
 
 
 
 
 
ce4ad7b
 
 
 
 
 
 
 
 
 
 
 
 
 
 
bdac669
 
 
 
 
 
ce4ad7b
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
b80130d
 
 
ce4ad7b
b80130d
ce4ad7b
 
 
b80130d
ce4ad7b
 
 
 
 
 
 
 
 
 
 
 
 
 
b80130d
 
 
 
ce4ad7b
b80130d
 
 
 
 
 
 
ce4ad7b
b80130d
 
 
 
 
 
 
 
 
 
 
 
ce4ad7b
 
a93637f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
ce4ad7b
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
e9a4c3e
 
 
 
 
 
 
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) };
};