// --------------------------------------------------------------------------- // 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 }; }