@wish-org/sheet-number-formatter

Format numbers and dates using the same format string syntax as Excel and Google Sheets.

const formatter = new SheetNumberFormatter();

const fmt = formatter.compile("#,##0.00");
fmt.formatter.format(1234567.8, enUS); // → "1,234,567.80"

Installation

npm install @wish-org/sheet-number-formatter

Quick start

import { SheetNumberFormatter, enUS } from "@wish-org/sheet-number-formatter";

const formatter = new SheetNumberFormatter();

// Compile once, reuse many times
const result = formatter.compile("#,##0.00");

if (result.isSuccess) {
  result.formatter.format(1234.5,  enUS); // "1,234.50"
  result.formatter.format(-42,     enUS); // "-42.00"
  result.formatter.format(0,       enUS); // "0.00"
}

API

SheetNumberFormatter

compile(formatString: string): CompileResult

Parses a format string and returns a compiled formatter. Compiling is the expensive step — do it once and reuse the result.

type CompileResult =
  | { isSuccess: true;  formatter: CompiledFormatter }
  | { isSuccess: false; errors: ParseError[] }

CompiledFormatter.format(value, locale)

Formats a number, bigint, or Date using the compiled format and the given locale.

formatter.format(value: number | bigint | Date, locale: SheetLocale): string

Locale

A SheetLocale controls decimal/group/date separators, month names, day names, and AM/PM labels.

Built-in locale

import { enUS } from "@wish-org/sheet-number-formatter";

Build from a BCP-47 tag

import { localeFromIntl } from "@wish-org/sheet-number-formatter";

const frFR = localeFromIntl("fr-FR");
const deCH = localeFromIntl("de-CH");

localeFromIntl uses the Intl API — available in Node.js 13+ and all modern browsers.

Custom locale

import type { SheetLocale } from "@wish-org/sheet-number-formatter";

const myLocale: SheetLocale = {
  decimalSeparator: ",",
  groupSeparator: ".",
  dateSeparator: ".",
  monthNames: ["Januar", /* … */ "Dezember"],
  shortMonthNames: ["Jan", /* … */ "Dez"],
  singleLetterMonthNames: ["J", /* … */ "D"],
  dayNames: ["Sonntag", /* … */ "Samstag"],
  shortDayNames: ["So", /* … */ "Sa"],
  amLabel: "AM",
  pmLabel: "PM",
};

Format string syntax

Format strings follow the Excel / Google Sheets convention. Multiple sections can be separated by ; to apply different formats to positive, negative, zero, and text values.

Numbers

| Format | Input | Output | |--------|-------|--------| | 0 | 3.7 | 4 | | 0.00 | 3.7 | 3.70 | | #,##0 | 1234567 | 1,234,567 | | #,##0.00 | 1234.5 | 1,234.50 | | 0% | 0.175 | 18% | | 0.00% | 0.175 | 17.50% | | 0.00E+00 | 12345 | 1.23E+04 | | # ??/?? | 1.375 | 1 3/8 | | # ?/16 | 1.5 | 1 8/16 | | 0,, | 1500000 | 2 (scale by millions) |

Digit placeholders

| Char | Meaning | |------|---------| | 0 | Always show digit; pad with 0 if absent | | # | Show digit only if significant | | ? | Like # but pads with a space for alignment |

Dates & Times

| Format | Input | Output | |--------|-------|--------| | yyyy-mm-dd | new Date(2024, 2, 5) | 2024-03-05 | | dd/mm/yyyy | new Date(2024, 2, 5) | 05/03/2024 | | d mmmm yyyy | new Date(2024, 2, 5) | 5 March 2024 | | ddd, mmm d | new Date(2024, 2, 5) | Tue, Mar 5 | | h:mm AM/PM | new Date(2024,0,1,14,5) | 2:05 PM | | hh:mm:ss | new Date(2024,0,1,9,7,3) | 09:07:03 | | hh:mm:ss.000 | new Date(2024,0,1,9,7,3,45) | 09:07:03.045 |

Date tokens

| Token | Output | |-------|--------| | yyyy / yy | 4-digit / 2-digit year | | mmmm / mmm / mm / m | Full month / short month / 2-digit month / month number | | dddd / ddd / dd / d | Full weekday / short weekday / 2-digit day / day number | | hh / h | Hours (padded / unpadded); 12h when AM/PM present | | ss / s | Seconds (padded / unpadded) | | AM/PM | AM/PM label from locale | | .000 | Fractional seconds (milliseconds) |

m / mm are interpreted as minutes when immediately following an hour token.

Elapsed time

Use square brackets to format a duration rather than a clock time. The input Date's .getTime() value is treated as milliseconds.

| Format | Input (ms) | Output | |--------|-----------|--------| | [h]:mm:ss | 3661000 | 1:01:01 | | [mm]:ss | 3661000 | 61:01 | | [ss] | 3661000 | 3661 |

Multiple sections

Sections are separated by ;:

positive ; negative ; zero ; text

formatter.compile("#,##0.00 ; (#,##0.00) ; -").formatter.format(-1234.5, enUS);
// → "(1,234.50)"

Conditional sections use comparison operators:

formatter.compile("[>=1000]#,##0 ; 0").formatter.format(500, enUS);  // → "500"
formatter.compile("[>=1000]#,##0 ; 0").formatter.format(1500, enUS); // → "1,500"

Error handling

compile never throws — invalid format strings are returned as ParseError objects:

const result = formatter.compile("##invalid!!");

if (!result.isSuccess) {
  for (const err of result.errors) {
    console.error(err.message);
  }
}

TypeScript

All types are exported:

import type {
  SheetLocale,
  CompiledFormatter,
  CompileResult,
} from "@wish-org/sheet-number-formatter";

import { ParseError } from "@wish-org/sheet-number-formatter";