loopable / web /src /ui /numberFormat.ts
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
658edb3 verified
Raw History Blame Contribute Delete
34.1 kB
// ---------------------------------------------------------------------------
// ui/numberFormat.ts - W51-T27, wave 51 lane F, contract C7.
//
// THE Excel number-format-code engine. `formatWithCode(value, code)` renders a
// value the way an Excel format code says it must read; `parseFormatCode` says
// whether a code can be honoured at all, and `formatCodeRefusal` says why not,
// in a sentence.
//
// WHY IT EXISTS (wave 51 R1): 30,748 of the owner's treasury workbook's 58,083
// cells (52.9%) carry a format code our `CellFormat` cannot express, and 30,690
// of those are the accounting code. An .xlsx importer that lands the workbook
// and renders it wrong has not landed it.
//
// ZERO IMPORTS, ON PURPOSE. This is a leaf library: verify_number_format.py
// compiles it and runs it under node, and a module with no imports also keeps
// tsc's inferred rootDir stable (verify_overlay.py::emitted explains what a
// moving import graph does to a compiled-artifact gate).
//
// ===========================================================================
// THE GRAMMAR, AS NUMBERED RULES.
//
// The fixture corpus in _test/numberFormat.fixtures.json is DERIVED FROM THESE
// RULES BY HAND and never from this code's output; every row cites the rules it
// applied. If a row and this code disagree, name the rule that was misapplied
// BEFORE touching either one. (Two scars: a published formula corpus where 20
// of 101 rows expected 120.0 and got 0.0, and a gate whose coverage leg rebound
// its own ruler so it could never go red.)
//
// R1 SECTIONS. A code splits on `;` at top level: not inside a "quoted
// literal", not after a backslash. At most 4 sections; a 5th refuses.
// R2 SELECTION. 1 section serves every value. 2 sections are
// [positive-or-zero, negative]. 3 are [positive, negative, zero]. 4 add a
// TEXT section. index = value<0 ? (n>=2?1:0) : value===0 ? (n>=3?2:0) : 0.
// R3 THE AUTOMATIC MINUS. A negative value that rendered through section 0
// (because the code has no negative section) is prefixed with ONE ASCII
// "-" placed before that section's fully rendered output, padding and
// literals included. A negative rendered through a negative section carries
// no sign at all, which is why `(1,234)` has none.
// R4 TONE names the section that rendered: index 1 -> "negative", index 2 ->
// "zero", and nothing for the positive or the text section. It is NOT a
// colour: a `[Red]` tag lands on `section.color` (R12) instead.
// R5 A ZERO INTEGER PART CONTRIBUTES NO DIGITS. The integer placeholders then
// show their fillers, which is how `#,##0` prints "0", `??` prints two
// spaces and `#.##` prints no leading zero.
// R6 FILLERS, for a placeholder with no digit to show: `0` -> "0",
// `?` -> one space, `#` -> nothing.
// R7 TRAILING FRACTION DIGITS that are insignificant (every digit from there
// rightward is 0) take R6's filler; every other position takes its digit.
// So `0.??` renders 1234.50 as "1234.5 " and 0 as "0. ".
// R8 COMMAS. Between digit placeholders a comma is the thousands separator;
// after the LAST digit placeholder each comma divides the value by 1000;
// with no digit placeholder anywhere before it a comma is a literal (which
// is what `dddd, mmmm d, yyyy` depends on). A thousands separator is
// emitted only where the character to its left is a digit, so a padded
// `?,???,??0` shows blanks and not stray commas.
// R9 PERCENT. Each `%` multiplies the value by 100 and emits a literal "%".
// R10 EXPONENTS. `E+` or `E-` starts one; the digit placeholders after it are
// the exponent's own width. The exponent is a multiple of the section's
// INTEGER placeholder count, which makes `0.00E+00` scientific and
// `##0.0E+0` engineering out of one rule. `E+` always signs the exponent,
// `E-` signs only a negative one. If rounding the mantissa overflows the
// integer placeholders (999.99 under `##0.0E+0`) the exponent steps once
// more and the mantissa is recomputed.
// R11 LITERALS. `\x` emits x. `"..."` emits its content. `_x` consumes x and
// emits ONE space. `*x` consumes x and emits NOTHING (a fill character has
// no width we can know). Every other unrecognised character, `$ - + / ( )`
// and a space included, emits itself.
// R12 COLOUR. A `[Red]`-class tag (the eight names plus `color1`..`color56`) is
// swallowed onto `section.color` and never reaches the text. Any other
// bracketed content refuses: `[h]` is elapsed time (R24) and `[>100]` is a
// condition, and neither is supported yet.
// R13 `@` IN A NUMERIC RENDER. A section holding an `@` and NO digit
// placeholder renders its literals with the General text (R21) at the `@`,
// and takes no automatic minus because General already carries the sign.
// That is why built-in 49 (`@`) prints "1234.5". An `@` in a section that
// DOES hold digit placeholders emits nothing.
// R14 TEXT ROUTING. A text value uses section 3 when the code has four
// sections; otherwise section 0 if that section holds an `@` and no digit
// placeholder; otherwise the text passes through unchanged, because a
// numeric format applied to text must not invent digits.
// R15 SERIAL TO DATE, and the Lotus 1900 bug in full. s < 60 is
// 1900-01-01 + (s-1) days. s === 60 is the PHANTOM 1900-02-29, a date that
// never existed and which Excel serialises anyway. s > 60 is
// 1899-12-30 + s days. s === 0 is Excel's "January 0, 1900". Every read is
// getUTC*, and the month and weekday names are hardcoded English, so a
// rendering is identical on every box (no toLocaleDateString, no
// Intl.DateTimeFormat, no local-time getter anywhere in this file).
// R16 WEEKDAY comes from the SERIAL, not from the constructed date:
// WEEKDAYS[floor(s) % 7] with the table starting at Saturday. Two
// independent anchors fix it with no branch at all: s = 0 is Saturday and
// s >= 61 is the real calendar (45000 -> Wednesday, 61 -> Thursday). The
// consequence is deliberate: because Excel's calendar contains the phantom
// of R15, its weekday cycle is one day behind the real calendar for every
// date before 1900-03-01, so s = 1 prints Sunday where 1900-01-01 was a
// Monday. We print what Excel prints.
// R17 `mm` IS AMBIGUOUS and resolves by neighbour: MINUTES when the nearest
// date token before it is `h`/`hh` or the nearest one after it is `s`/`ss`,
// and the MONTH otherwise. Get this wrong and built-ins 20, 21, 22 and 45
// all lie.
// R18 12-HOUR CLOCK. An `AM/PM` (or `A/P`) token anywhere in the section makes
// `h`/`hh` 12-hour, with hour 0 shown as 12.
// R19 A NEGATIVE VALUE IS NOT A DATE. A section carrying date or time tokens
// renders the General text (R21) for a value below zero, so an out-of-range
// cell shows its number instead of painted garbage.
// R20 ONE NUMBER FIELD PER SECTION: the FIRST run of digit placeholders. Any
// LATER run in the same section is padding only (R6 fillers) and a second
// decimal point is a literal. The sole reason this rule exists is that
// openpyxl's built-in 44 is malformed (see the header of the fixture
// corpus); every other code in the corpus has at most one run per section,
// so nothing else can shift.
// R21 GENERAL. At most 11 significant digits, trailing zeros trimmed, no
// grouping; zero is "0".
// R22 ROUNDING is half away from zero, applied to the ABSOLUTE value through
// toFixed, whose tie rule is "pick the larger n".
// R23 `formatWithCode` NEVER THROWS. A refused code renders the General text.
// R24 REFUSALS, each with a sentence from `formatCodeRefusal`: more than four
// sections, an unterminated quote, a code over 64 characters (contract C2's
// cap), an empty code, a bracketed condition, and the three families that
// are not supported yet - `fraction` (`# ?/?`), `elapsed` (`[h]:mm:ss`) and
// `subsecond` (`mmss.0`). A refused code is refused whole: no section of it
// renders, so nothing half-formatted reaches a screen.
// ---------------------------------------------------------------------------
/** Which section rendered (R4). Never a colour. */
export type FormatTone = "negative" | "zero";
/** C7's result. `tone` is absent for the positive and the text section. */
export interface FormatResult {
text: string;
tone?: FormatTone;
}
export type Placeholder = "0" | "#" | "?";
export type FormatToken =
| { k: "lit"; s: string }
| { k: "int"; p: Placeholder }
| { k: "frac"; p: Placeholder }
| { k: "pad"; p: Placeholder }
| { k: "point" }
| { k: "padpoint" }
| { k: "pct" }
| { k: "exp" }
| { k: "date"; t: string; minutes?: boolean }
| { k: "at" };
export interface FormatSection {
/** The section's own source text, colour tag included. */
raw: string;
/** R12. Lowercased tag name, or null. */
color: string | null;
/** The section is the word `General` (R21). */
general: boolean;
tokens: FormatToken[];
/** R20's first run, before the decimal point. */
intPh: Placeholder[];
/** R20's first run, after the decimal point. */
fracPh: Placeholder[];
grouping: boolean;
/** R8. How many times to divide by 1000. */
scale: number;
/** R9. How many `%` the section carries. */
percent: number;
exp: { plus: boolean; digits: number } | null;
hasDigits: boolean;
hasDate: boolean;
hasAmPm: boolean;
hasAt: boolean;
}
/** Contract C2: a format code is at most 64 characters. */
export const MAX_CODE_LENGTH = 64;
const COLOR_NAMES = [
"black", "blue", "cyan", "green", "magenta", "red", "white", "yellow",
];
const MONTHS = [
"January", "February", "March", "April", "May", "June",
"July", "August", "September", "October", "November", "December",
];
// R15: hardcoded English, so a rendering cannot depend on the box's locale.
const MONTHS_SHORT = [
"Jan", "Feb", "Mar", "Apr", "May", "Jun",
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec",
];
// R16: indexed by serial % 7, so index 0 is Saturday.
const WEEKDAYS = [
"Saturday", "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday",
];
const WEEKDAYS_SHORT = ["Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri"];
const DAY_MS = 86400000;
// ---------------------------------------------------------------------------
// refusals (R24)
// ---------------------------------------------------------------------------
type Refusal = { why: string; family: string | null };
const refuse = (why: string, family: string | null): Refusal => ({ why, family });
const UNSUPPORTED_SENTENCE: { [family: string]: string } = {
fraction:
"Fraction formats such as '# ?/?' are not supported yet, so this code cannot be applied.",
elapsed:
"Elapsed time formats such as '[h]:mm:ss' are not supported yet, so this code cannot be applied.",
subsecond:
"Sub second time formats such as 'mmss.0' are not supported yet, so this code cannot be applied.",
};
// ---------------------------------------------------------------------------
// R1: split a code into sections
// ---------------------------------------------------------------------------
const splitSections = (code: string): string[] | null => {
const out: string[] = [];
let cur = "";
let quoted = false;
for (let i = 0; i < code.length; i += 1) {
const c = code.charAt(i);
if (c === "\\") {
cur += c + code.charAt(i + 1);
i += 1;
continue;
}
if (c === '"') {
quoted = !quoted;
cur += c;
continue;
}
if (c === ";" && !quoted) {
out.push(cur);
cur = "";
continue;
}
cur += c;
}
if (quoted) return null;
out.push(cur);
return out;
};
// ---------------------------------------------------------------------------
// the tokenizer (R8, R11, R12, and the date letters)
// ---------------------------------------------------------------------------
type RawToken =
| { k: "lit"; s: string }
| { k: "digit"; p: Placeholder }
| { k: "point" }
| { k: "comma" }
| { k: "pct" }
| { k: "exp"; plus: boolean; digits: number }
| { k: "date"; t: string }
| { k: "at" };
const isPlaceholder = (c: string): boolean => c === "0" || c === "#" || c === "?";
const DATE_LETTERS = "ymdhs";
const tokenizeSection = (
src: string,
): { toks: RawToken[]; color: string | null } | Refusal => {
const toks: RawToken[] = [];
let color: string | null = null;
let i = 0;
while (i < src.length) {
const c = src.charAt(i);
if (c === "[") {
const end = src.indexOf("]", i);
if (end < 0) {
return refuse(
"A format code has an unclosed bracket. Close it with ']' or remove it.",
null,
);
}
const inner = src.slice(i + 1, end);
const low = inner.toLowerCase();
if (COLOR_NAMES.indexOf(low) >= 0 || /^color([1-9]|[1-4][0-9]|5[0-6])$/.test(low)) {
color = low;
} else if (/^[hms]+$/.test(low)) {
return refuse(UNSUPPORTED_SENTENCE.elapsed, "elapsed");
} else {
return refuse(
"A bracketed condition or locale tag is not supported in a format code, "
+ "so this code cannot be applied.",
null,
);
}
i = end + 1;
continue;
}
if (c === '"') {
const end = src.indexOf('"', i + 1);
if (end < 0) {
return refuse(
"A format code has an unterminated quoted literal. Close the double quote.",
null,
);
}
toks.push({ k: "lit", s: src.slice(i + 1, end) });
i = end + 1;
continue;
}
if (c === "\\") {
// R11: the escape emits the next character verbatim.
if (i + 1 < src.length) toks.push({ k: "lit", s: src.charAt(i + 1) });
i += 2;
continue;
}
if (c === "_") {
// R11: padding is ONE space, whatever character it was told to match.
toks.push({ k: "lit", s: " " });
i += 2;
continue;
}
if (c === "*") {
// R11: a fill character repeats to the column width, which a pure module
// cannot know, so it contributes nothing.
i += 2;
continue;
}
if (isPlaceholder(c)) {
toks.push({ k: "digit", p: c as Placeholder });
i += 1;
continue;
}
if (c === ".") {
toks.push({ k: "point" });
i += 1;
continue;
}
if (c === ",") {
toks.push({ k: "comma" });
i += 1;
continue;
}
if (c === "%") {
toks.push({ k: "pct" });
i += 1;
continue;
}
if ((c === "e" || c === "E") && (src.charAt(i + 1) === "+" || src.charAt(i + 1) === "-")) {
const plus = src.charAt(i + 1) === "+";
i += 2;
let digits = 0;
while (i < src.length && isPlaceholder(src.charAt(i))) {
digits += 1;
i += 1;
}
toks.push({ k: "exp", plus, digits: digits > 0 ? digits : 1 });
continue;
}
if (c === "@") {
toks.push({ k: "at" });
i += 1;
continue;
}
if (c === "a" || c === "A") {
const ahead = src.slice(i).toUpperCase();
if (ahead.indexOf("AM/PM") === 0) {
toks.push({ k: "date", t: "ampm" });
i += 5;
continue;
}
if (ahead.indexOf("A/P") === 0) {
toks.push({ k: "date", t: "ampm" });
i += 3;
continue;
}
}
const low = c.toLowerCase();
if (DATE_LETTERS.indexOf(low) >= 0) {
let run = 0;
while (i + run < src.length && src.charAt(i + run).toLowerCase() === low) run += 1;
toks.push({ k: "date", t: low.repeat(run > 4 ? 4 : run) });
i += run;
continue;
}
toks.push({ k: "lit", s: c });
i += 1;
}
return { toks, color };
};
// ---------------------------------------------------------------------------
// raw tokens -> a render-ready section (R8, R17, R20, R24)
// ---------------------------------------------------------------------------
const nearestDate = (toks: RawToken[], from: number, step: number): string | null => {
for (let i = from + step; i >= 0 && i < toks.length; i += step) {
const t = toks[i];
if (t.k === "date") return t.t;
}
return null;
};
const buildSection = (raw: string): FormatSection | Refusal => {
const trimmed = raw.trim();
const lit = tokenizeSection(raw);
if ("why" in lit) return lit;
const bare = trimmed.replace(/^\[[^\]]*\]/, "").trim();
if (/^general$/i.test(bare)) {
return {
raw, color: lit.color, general: true, tokens: [], intPh: [], fracPh: [],
grouping: false, scale: 0, percent: 0, exp: null, hasDigits: false,
hasDate: false, hasAmPm: false, hasAt: false,
};
}
const toks = lit.toks;
let lastDigit = -1;
let firstDigit = -1;
let hasDate = false;
let hasAmPm = false;
let hasAt = false;
for (let i = 0; i < toks.length; i += 1) {
const t = toks[i];
if (t.k === "digit") {
if (firstDigit < 0) firstDigit = i;
lastDigit = i;
} else if (t.k === "date") {
hasDate = true;
if (t.t === "ampm") hasAmPm = true;
} else if (t.k === "at") {
hasAt = true;
}
}
// R24: `# ?/?` is a fraction, which we refuse rather than mis-render.
for (let i = 0; i < toks.length; i += 1) {
const t = toks[i];
if (t.k === "lit" && t.s === "/" && firstDigit >= 0 && firstDigit < i && lastDigit > i) {
return refuse(UNSUPPORTED_SENTENCE.fraction, "fraction");
}
}
// R24: `mmss.0` asks for sub-second precision; nothing else mixes a date
// token with a digit placeholder, so the mix itself is the signal.
if (hasDate && lastDigit >= 0) {
return refuse(UNSUPPORTED_SENTENCE.subsecond, "subsecond");
}
const tokens: FormatToken[] = [];
const intPh: Placeholder[] = [];
const fracPh: Placeholder[] = [];
let sectionGrouping = false;
let scale = 0;
let percent = 0;
let exp: { plus: boolean; digits: number } | null = null;
let runIndex = 0;
let runOpen = false;
let pointTaken = false;
const closeRun = (): void => {
if (runOpen) {
runIndex += 1;
runOpen = false;
}
};
for (let i = 0; i < toks.length; i += 1) {
const t = toks[i];
if (t.k === "digit") {
runOpen = true;
// R20: only the first run is the number field.
if (runIndex > 0) {
tokens.push({ k: "pad", p: t.p });
} else if (pointTaken) {
fracPh.push(t.p);
tokens.push({ k: "frac", p: t.p });
} else {
intPh.push(t.p);
tokens.push({ k: "int", p: t.p });
}
continue;
}
if (t.k === "point") {
if (runIndex === 0 && !pointTaken) {
pointTaken = true;
runOpen = true;
tokens.push({ k: "point" });
} else {
tokens.push({ k: "padpoint" });
}
continue;
}
if (t.k === "comma") {
// R8: grouping, scaling, or a literal, decided here and never in the
// renderer.
if (lastDigit < 0 || firstDigit > i) {
tokens.push({ k: "lit", s: "," });
} else if (i > lastDigit) {
scale += 1;
} else {
sectionGrouping = true;
}
continue;
}
closeRun();
if (t.k === "pct") {
percent += 1;
tokens.push({ k: "pct" });
} else if (t.k === "exp") {
exp = { plus: t.plus, digits: t.digits };
tokens.push({ k: "exp" });
} else if (t.k === "date") {
// R17, decided once, here.
let minutes = false;
if (t.t === "m" || t.t === "mm") {
const before = nearestDate(toks, i, -1);
const after = nearestDate(toks, i, 1);
minutes = before === "h" || before === "hh" || after === "s" || after === "ss";
}
tokens.push({ k: "date", t: t.t, minutes });
} else if (t.k === "at") {
tokens.push({ k: "at" });
} else {
tokens.push({ k: "lit", s: t.s });
}
}
return {
raw,
color: lit.color,
general: false,
tokens,
intPh,
fracPh,
grouping: sectionGrouping,
scale,
percent,
exp,
hasDigits: lastDigit >= 0,
hasDate,
hasAmPm,
hasAt,
};
};
// ---------------------------------------------------------------------------
// C7: parseFormatCode / formatCodeRefusal
// ---------------------------------------------------------------------------
const compile = (code: string): { sections: FormatSection[] } | Refusal => {
if (typeof code !== "string" || code.length === 0) {
return refuse("A format code cannot be empty.", null);
}
if (code.length > MAX_CODE_LENGTH) {
return refuse(
"A format code is at most " + MAX_CODE_LENGTH + " characters. This one is "
+ code.length + ".",
null,
);
}
const parts = splitSections(code);
if (parts === null) {
return refuse(
"A format code has an unterminated quoted literal. Close the double quote.",
null,
);
}
if (parts.length > 4) {
return refuse(
"A format code has at most 4 sections separated by ';'. This one has "
+ parts.length + ".",
null,
);
}
const sections: FormatSection[] = [];
for (let i = 0; i < parts.length; i += 1) {
const built = buildSection(parts[i]);
if ("why" in built) return built;
sections.push(built);
}
return { sections };
};
/**
* C7. The parsed sections, or null when the code is refused.
*
* The refusal SENTENCE is a separate export (`formatCodeRefusal`) on purpose:
* lane E consumes this name and this return shape at the combine, so widening
* it into a discriminated union would break the contract it is named in.
*/
export function parseFormatCode(code: string): FormatSection[] | null {
const out = compile(code);
return "why" in out ? null : out.sections;
}
/** The sentence for a refused code (R24), or null when the code is accepted. */
export function formatCodeRefusal(code: string): string | null {
const out = compile(code);
return "why" in out ? out.why : null;
}
/** The family a refused code belongs to, when it is one we mean to add later. */
export function formatCodeUnsupportedFamily(code: string): string | null {
const out = compile(code);
return "why" in out ? out.family : null;
}
// ---------------------------------------------------------------------------
// numbers
// ---------------------------------------------------------------------------
/** R21. */
export function generalText(value: number): string {
if (typeof value !== "number" || !isFinite(value)) return String(value);
if (value === 0) return "0";
return String(Number(value.toPrecision(11)));
}
const filler = (p: Placeholder): string => (p === "0" ? "0" : p === "?" ? " " : "");
/** R2. NC-(a) and NC-(b) mutate the two returns below, one line each. */
const selectSection = (count: number, value: number): number => {
if (value < 0) return count >= 2 ? 1 : 0;
if (value === 0) return count >= 3 ? 2 : 0;
return 0;
};
const separators = (locale?: string): { group: string; point: string } => {
// The corpus is ASCII by contract; a caller that passes a locale gets that
// locale's separators as a GROUPING HINT only, and a bad tag falls back
// rather than throwing (R23).
if (!locale) return { group: ",", point: "." };
try {
const parts = new Intl.NumberFormat(locale).formatToParts(11111.1);
let group = ",";
let point = ".";
for (let i = 0; i < parts.length; i += 1) {
if (parts[i].type === "group") group = parts[i].value;
if (parts[i].type === "decimal") point = parts[i].value;
}
return { group, point };
} catch (err) {
return { group: ",", point: "." };
}
};
/** R5, R6, R8. The integer half of the number field. */
const renderInteger = (
digits: string,
ph: Placeholder[],
grouping: boolean,
group: string,
): string => {
const chars: string[] = [];
if (digits.length >= ph.length) {
for (let i = 0; i < digits.length; i += 1) chars.push(digits.charAt(i));
} else {
for (let i = 0; i < ph.length - digits.length; i += 1) chars.push(filler(ph[i]));
for (let i = 0; i < digits.length; i += 1) chars.push(digits.charAt(i));
}
let out = "";
for (let i = 0; i < chars.length; i += 1) {
const c = chars[i];
out += c;
const remaining = chars.length - i - 1;
// R8: a separator only ever follows a digit.
if (grouping && remaining > 0 && remaining % 3 === 0 && c >= "0" && c <= "9") {
out += group;
}
}
return out;
};
/** R7. The fraction half of the number field. */
const renderFraction = (digits: string, ph: Placeholder[]): string => {
let lastSignificant = -1;
for (let i = 0; i < digits.length; i += 1) {
if (digits.charAt(i) !== "0") lastSignificant = i;
}
let out = "";
for (let i = 0; i < ph.length; i += 1) {
if (i <= lastSignificant || ph[i] === "0") out += digits.charAt(i);
else out += filler(ph[i]);
}
return out;
};
/** R10. The decimal exponent of a value, read off toExponential so no log10
* rounding can put it one decade out. */
const decimalExponent = (x: number): number => {
if (x === 0) return 0;
const s = x.toExponential();
return Number(s.slice(s.indexOf("e") + 1));
};
const renderNumericSection = (
section: FormatSection,
value: number,
locale?: string,
): string => {
const seps = separators(locale);
let x = Math.abs(value);
for (let i = 0; i < section.scale; i += 1) x /= 1000;
for (let i = 0; i < section.percent; i += 1) x *= 100;
let expValue = 0;
if (section.exp) {
// R10.
const width = section.intPh.length > 0 ? section.intPh.length : 1;
expValue = Math.floor(decimalExponent(x) / width) * width;
let mantissa = expValue === 0 ? x : x / Math.pow(10, expValue);
if (Number(mantissa.toFixed(section.fracPh.length)) >= Math.pow(10, width)) {
expValue += width;
mantissa = x / Math.pow(10, expValue);
}
x = mantissa;
}
const fixed = x.toFixed(section.fracPh.length); // R22
const dot = fixed.indexOf(".");
const intRaw = dot < 0 ? fixed : fixed.slice(0, dot);
const fracRaw = dot < 0 ? "" : fixed.slice(dot + 1);
// R5: a zero integer part contributes no digits at all.
const intDigits = intRaw === "0" ? "" : intRaw;
const intStr = renderInteger(intDigits, section.intPh, section.grouping, seps.group);
const fracStr = renderFraction(fracRaw, section.fracPh);
let out = "";
let intDone = false;
let fracDone = false;
for (let i = 0; i < section.tokens.length; i += 1) {
const t = section.tokens[i];
if (t.k === "int") {
if (!intDone) {
out += intStr;
intDone = true;
}
} else if (t.k === "frac") {
if (!fracDone) {
out += fracStr;
fracDone = true;
}
} else if (t.k === "point") {
out += seps.point;
} else if (t.k === "padpoint") {
out += seps.point;
} else if (t.k === "pad") {
out += filler(t.p); // R20
} else if (t.k === "pct") {
out += "%";
} else if (t.k === "exp" && section.exp) {
const sign = expValue < 0 ? "-" : section.exp.plus ? "+" : "";
const width = section.exp.digits;
out += "E" + sign + String(Math.abs(expValue)).padStart(width, "0");
} else if (t.k === "lit") {
out += t.s;
}
// an `@` in a section that holds digits emits nothing (R13); a date token
// cannot reach here, because a date/digit mix is refused (R24).
}
return out;
};
// ---------------------------------------------------------------------------
// dates (R15, R16, R17, R18)
// ---------------------------------------------------------------------------
export interface SerialParts {
y: number;
mo: number;
d: number;
/** serial % 7, indexing WEEKDAYS (R16). */
wd: number;
h: number;
mi: number;
s: number;
}
/** R15 and R16. Returns null for a serial that cannot be a date. */
export function serialToParts(serial: number): SerialParts | null {
if (typeof serial !== "number" || !isFinite(serial) || serial < 0) return null;
let days = Math.floor(serial);
let secs = Math.round((serial - days) * 86400);
if (secs >= 86400) {
days += 1;
secs -= 86400;
}
const wd = days % 7;
let y = 1900;
let mo = 1;
let d = 0;
if (days === 0) {
// Excel's "January 0, 1900": TEXT(0,"mm-dd-yy") is "01-00-00".
y = 1900;
mo = 1;
d = 0;
} else if (days === 60) {
// The Lotus phantom. 1900 was not a leap year; Excel says otherwise.
y = 1900;
mo = 2;
d = 29;
} else {
const base = days < 60 ? Date.UTC(1899, 11, 31) : Date.UTC(1899, 11, 30);
const at = new Date(base + days * DAY_MS);
y = at.getUTCFullYear();
mo = at.getUTCMonth() + 1;
d = at.getUTCDate();
}
return { y, mo, d, wd, h: Math.floor(secs / 3600), mi: Math.floor(secs / 60) % 60, s: secs % 60 };
}
const pad2 = (n: number): string => (n < 10 ? "0" + String(n) : String(n));
const renderDateSection = (section: FormatSection, value: number): string | null => {
const parts = serialToParts(value);
if (!parts) return null; // R19
const hour12 = parts.h % 12 === 0 ? 12 : parts.h % 12;
let out = "";
for (let i = 0; i < section.tokens.length; i += 1) {
const t = section.tokens[i];
if (t.k === "lit") {
out += t.s;
continue;
}
if (t.k === "point" || t.k === "padpoint") {
out += ".";
continue;
}
if (t.k !== "date") continue;
const showHour = section.hasAmPm ? hour12 : parts.h; // R18
switch (t.t) {
case "yyyy":
case "yyy":
out += String(parts.y);
break;
case "yy":
case "y":
out += pad2(parts.y % 100);
break;
case "mmmm":
out += MONTHS[parts.mo - 1];
break;
case "mmm":
out += MONTHS_SHORT[parts.mo - 1];
break;
case "mm":
out += t.minutes ? pad2(parts.mi) : pad2(parts.mo); // R17
break;
case "m":
out += t.minutes ? String(parts.mi) : String(parts.mo);
break;
case "dddd":
out += WEEKDAYS[parts.wd];
break;
case "ddd":
out += WEEKDAYS_SHORT[parts.wd];
break;
case "dd":
out += pad2(parts.d);
break;
case "d":
out += String(parts.d);
break;
case "hh":
out += pad2(showHour);
break;
case "h":
out += String(showHour);
break;
case "ss":
out += pad2(parts.s);
break;
case "s":
out += String(parts.s);
break;
case "ampm":
out += parts.h < 12 ? "AM" : "PM";
break;
default:
break;
}
}
return out;
};
// ---------------------------------------------------------------------------
// C7: formatWithCode
// ---------------------------------------------------------------------------
const asNumber = (value: unknown): number | null => {
if (typeof value === "number") return isFinite(value) ? value : null;
if (typeof value !== "string") return null;
const trimmed = value.replace(/[,\s]/g, "");
if (trimmed === "") return null;
const n = Number(trimmed);
return isFinite(n) ? n : null;
};
/** R13, R14. Literals plus one substitution. */
const renderTextSection = (section: FormatSection, text: string): string => {
let out = "";
for (let i = 0; i < section.tokens.length; i += 1) {
const t = section.tokens[i];
if (t.k === "lit") out += t.s;
else if (t.k === "at") out += text;
}
return out;
};
const atOnly = (section: FormatSection): boolean =>
section.hasAt && !section.hasDigits && !section.hasDate;
/**
* C7. Render `value` through the Excel format code `code`.
*
* Never throws (R23): a refused code renders the General text, so a cell whose
* code we cannot honour shows its number and not a painted lie.
*/
export function formatWithCode(
value: number | string | null | undefined,
code: string,
locale?: string,
): FormatResult {
const numeric = asNumber(value);
const raw = value === null || value === undefined ? "" : String(value);
const compiled = compile(code);
if ("why" in compiled) {
return { text: numeric === null ? raw : generalText(numeric) };
}
const sections = compiled.sections;
if (numeric === null) {
// Hotfix 2026-09-24 (owner: rows 6 and 7 must read as dates). A date the sheet computed or
// typed is ISO text, the form every formula and binding speaks. Under a DATE code it is that
// day, painted by the date section, not a string that falls through to the text section.
const iso = /^(\d{4})-(\d{2})-(\d{2})$/.exec(raw.trim());
if (iso && sections[0]?.hasDate) {
const at = Date.UTC(Number(iso[1]), Number(iso[2]) - 1, Number(iso[3]));
const painted = new Date(at).getUTCDate() === Number(iso[3])
? renderDateSection(sections[0], Math.round((at - Date.UTC(1899, 11, 30)) / DAY_MS)) : null;
if (painted !== null) return { text: painted };
}
// R14.
if (sections.length === 4) return { text: renderTextSection(sections[3], raw) };
if (sections.length >= 1 && atOnly(sections[0]) && !sections[0].general) {
return { text: renderTextSection(sections[0], raw) };
}
return { text: raw };
}
const idx = selectSection(sections.length, numeric);
const section = sections[idx];
const tone: FormatTone | null = idx === 1 ? "negative" : idx === 2 ? "zero" : null; // R4
let text: string;
if (section.general) {
text = generalText(numeric);
} else if (atOnly(section)) {
// R13: General already carries its own sign, so no automatic minus here.
text = renderTextSection(section, generalText(numeric));
} else if (section.hasDate) {
const painted = renderDateSection(section, numeric);
text = painted === null ? generalText(numeric) : painted; // R19
} else {
// R3: the automatic minus prefixes the section's fully rendered output.
const sign = numeric < 0 && idx === 0 ? "-" : "";
text = sign + renderNumericSection(section, numeric, locale);
}
return tone === null ? { text } : { text, tone };
}