Show numbers and dates with Excel-style format codes and an explicit locale.On this page
Loading documentation…
A column or a cell can carry an Excel-style numberFormat code, and a column can carry a BCP 47 numberLocale, so numbers and dates paint the same way on every host.
The code General takes a cell out of its column's format.
XLSX import keeps each cell's own format, so a column that mixes formats round-trips cell by cell.
The grid actions setNumberFormat(code: string | null) and stepDecimals(direction: 1 | -1) change the format of the selection. setNumberFormat(null) returns the cells to the column's format. stepDecimals shows one more (1) or one fewer (-1) decimal place than the focus cell does.
Format codes
formatNumber(value, code?, locale?) is exported from @sheetwrite/core. It is a cached renderer for the supported Excel-style families:
These examples use "en-US", the formatter's default locale. Set numberLocale on a column or pass a locale as the third argument to change separators.
Code
Example output
(none) or General
1234.5 → 1,234.5 (locale default)
0
1234.5 → 1235
0.00
1234.5 → 1234.50
#,##0.00
1234.5 → 1,234.50
0%
0.5 → 50%
$#,##0.00
1234.5 → $1,234.50
0.00E+00
12345 → 1.23E+04
yyyy-mm-dd
serial 45351 → 2024-02-29
mmm d, yyyy h:mm:ss AM/PM
UTC serial 45477.62924768519 (2024-07-04 15:06:07 UTC) → Jul 4, 2024 3:06:07 PM
0.00;[Red](0.00);"none";"value: "@
Positive 2.5 → 2.50; negative -2.5 → (2.50); zero 0 → none; text "draft" → value: draft
Numeric codes
Numeric formats support fixed decimals, optional grouping, percent scaling, scientific exponents with an explicit width, quoted and escaped literals, and currency prefixes and suffixes.
Up to four semicolon sections select the positive, negative, zero and text output. Sheetwrite accepts color directives such as [Red] as formatting metadata, but they do not change the canvas text color.
Date and time codes
Date and time formats support numeric and named month and day fields, 12-hour and 24-hour time, minutes, seconds, and AM/PM. Neighboring time tokens decide whether m means month or minute.
UTC fields make date rendering the same in every host timezone. Locale-specific separators come from numberLocale, not from the browser's locale.
Edge cases and limits
Fractions, elapsed-time bracket tokens and fractional seconds are not supported.
Formula errors are explicit values such as #DIV/0! and #CYCLE!. They are not non-finite numbers that the formatter hides. See Formulas.
Native boolean values use the normal cell style model and render as uppercase TRUE or FALSE. They are centered unless CellStyle.align overrides it.
interface WorkbookinterfaceWorkbookinterfaceWorkbookLive workbook schema containing ordered sheets and the active sheet ID.API reference →const columns: Column[]constcolumns:Column[]constcolumns:Column[]Exact column runs represented by every returned row.API reference →Column.key: stringinterfaceColumn {key:string;}interfaceColumn {key:string;}Non-empty key, unique within the sheet, used to map input and datasource values.API reference →Column.header: stringinterfaceColumn {header:string;}interfaceColumn {header:string;}Schema label used by table exports and data-grid presentation headers.API reference →Column.width: numberinterfaceColumn {width:number;}interfaceColumn {width:number;}Unzoomed column width in CSS pixels.API reference →Column.type: CellFormatinterfaceColumn {type:CellFormat;}interfaceColumn {type:CellFormat;}Controls cell input parsing and default value formatting for this column.API reference →Column.numberFormat?: string | undefinedinterfaceColumn {numberFormat?:string|undefined;}interfaceColumn {numberFormat?:string|undefined;}Excel number-format code, e.g. "#,##0.00"API reference →Column.numberLocale?: string | undefinedinterfaceColumn {numberLocale?:string|undefined;}interfaceColumn {numberLocale?:string|undefined;}Explicit BCP 47 locale for separators; omitted keeps the deterministic default.API reference →const grid: Gridconstgrid:Gridconstgrid:GridThe imperative core grid this controller owns.API reference →Grid.styleRange(range: Range, style: Partial<CellStyle> | null): voidGrid.styleRange(range: Range, style: Partial<CellStyle>|null): voidGrid.styleRange(range: Range, style: Partial<CellStyle>|null): voidMerge style into every cell of range (null clears cell styles) as one undoable transaction. Styles land in the store and paint in the canvas — unlike highlightCells, which draws a translucent overlay above it.API reference →Range.sheet: stringinterfaceRange {sheet:string;}interfaceRange {sheet:string;}Sheet identity used by the simple data-first adapter. Defaults to sheet1.API reference →Range.start: {
row: number;
col: number;
}interfaceRange {start: {row:number;col:number;};}interfaceRange {start: {row:number;col:number;};}Zero-based workbook column index of the first key.API reference →row: numberlet row:numberlet row:number0-based row of the cell that holds the top-left corner.API reference →col: numberlet col:numberlet col:number0-based column of the cell that holds the top-left corner.API reference →Range.end: {
row: number;
col: number;
}interfaceRange {end: {row:number;col:number;};}interfaceRange {end: {row:number;col:number;};}Exclusive workbook column index after the last key.API reference →numberFormat?: string | undefinednumberFormat?: string |undefinednumberFormat?: string |undefinedExcel number-format code for this cell, e.g. "0.00%". Overrides the column's numberFormat; at most 255 characters.API reference →function createGrid(host: HTMLElement, opts: GridOptions): GridfunctioncreateGrid(host:HTMLElement,opts:GridOptions,):GridfunctioncreateGrid(host:HTMLElement,opts:GridOptions,):GridCreates and mounts an imperative Grid in the supplied host element.Exampleimport { createGrid, initSheetwrite } from"@sheetwrite/core";awaitinitSheetwrite();consthost= document.createElement("div");host.style.height ="400px";document.body.append(host);constgrid=createGrid(host, {workbook: {activeSheet: "sales",sheets: [{ id: "sales", name: "Sales", rowCount: 2,columns: [{ key: "amount", header: "Amount", type: "number", width: 120 }] }],},data: { rowCount: 2, columns: { amount: [12, 18] } },});grid.scrollToCell({ sheet: "sales", row: 1, col: 0 });// Call when the host view is removed.grid.destroy();See alsoGrid *See alsoGridOptionsAPI reference →function formatNumber(value: number | string, format?: string, locale?: string): stringfunctionformatNumber(value:number|string,format?:string,locale?:string,):stringfunctionformatNumber(value:number|string,format?:string,locale?:string,):stringDeterministic Excel-style number/date formatter. Supports explicit locale separators, percent/scientific notation, UTC date/time tokens, four-section positive/negative/zero/text codes, quoted literals, and backslash escapes. General formats exactly like an omitted code.API reference →function initSheetwrite(source?: BufferSource | URL | string | Request | WebAssembly.Module, engine?: SheetwriteEngine): Promise<void>functioninitSheetwrite(source?:BufferSource|URL|string|Request|WebAssembly.Module,engine?:SheetwriteEngine,):Promise<void>functioninitSheetwrite(source?:|BufferSource|URL|string|Request|WebAssembly.Module,engine?:SheetwriteEngine,):Promise<void>Load one WASM engine before createGrid. The default is @sheetwrite/wasm. Pass the @sheetwrite/formulas module as the second argument to select the full engine.Exampleimport { initSheetwrite } from"@sheetwrite/core";import*as formulas from"@sheetwrite/formulas";awaitinitSheetwrite(undefined, formulas);See also/docs/start/choose-an-engine/See alsocreateGridAPI reference →interface ColumnarDatainterfaceColumnarDatainterfaceColumnarDataEager column-oriented values used to initialize a sheet.API reference →const host: HTMLElement | nullconsthost:HTMLElement|nullconsthost:HTMLElement|nullvar document: Documentvar document:Documentvar document:Documentwindow.document returns a reference to the document contained in the window. MDN ReferenceAPI reference →ParentNode.querySelector<HTMLElement>(selectors: string): HTMLElement | null (+4 overloads)ParentNode.querySelector<HTMLElement>(selectors: string): HTMLElement |null (+4 overloads)ParentNode.querySelector<HTMLElement>(selectors: string): HTMLElement |null (+4 overloads)Returns the first element that is a descendant of node that matches selectors. MDN Referenceinterface HTMLElementinterfaceHTMLElementinterfaceHTMLElementThe HTMLElement interface represents any HTML element. MDN Referencevar Error: ErrorConstructor
new (message?: string, options?: ErrorOptions) => Error (+2 overloads)var Error:ErrorConstructornew (message?:string, options?:ErrorOptions) =>Error (+2 overloads)var Error:ErrorConstructornew (message?:string, options?:ErrorOptions) =>Error (+2 overloads)const host: HTMLElementconsthost:HTMLElementconsthost:HTMLElementElementCSSInlineStyle.style: CSSStyleDeclarationinterfaceElementCSSInlineStyle {style:CSSStyleDeclaration;}interfaceElementCSSInlineStyle {style:CSSStyleDeclaration;}MDN ReferenceAPI reference →CSSStyleDeclaration.height: stringinterfaceCSSStyleDeclaration {height:string;}interfaceCSSStyleDeclaration {height:string;}MDN ReferenceAPI reference →const workbook: Workbookconstworkbook:Workbookconstworkbook:WorkbookLive workbook schema adopted by the store and updated by document operations.API reference →Workbook.activeSheet: stringinterfaceWorkbook {activeSheet:string;}interfaceWorkbook {activeSheet:string;}Active sheet ID and initial tab presented when the grid is created.API reference →Workbook.sheets: Sheet[]interfaceWorkbook {sheets:Sheet[];}interfaceWorkbook {sheets:Sheet[];}Sheets in display/tab order.API reference →Sheet.id: stringinterfaceSheet {id:string;}interfaceSheet {id:string;}Stable identifier, unique within the workbook and used by every cell address.API reference →Sheet.name: stringinterfaceSheet {name:string;}interfaceSheet {name:string;}User-facing sheet name shown in tabs and workbook exports.API reference →Sheet.rowCount: numberinterfaceSheet {rowCount:number;}interfaceSheet {rowCount:number;}Row count for both in-memory and datasource-backed sheets.API reference →Sheet.columns: Column[]interfaceSheet {columns:Column[];}interfaceSheet {columns:Column[];}Ordered schema; array positions are the zero-based column coordinates.API reference →const data: ColumnarDataconstdata:ColumnarDataconstdata:ColumnarDataEager column-major values loaded into workbook.activeSheet; use instead of datasource.API reference →ColumnarData.rowCount: numberinterfaceColumnarData {rowCount:number;}interfaceColumnarData {rowCount:number;}Row count for both in-memory and datasource-backed sheets.API reference →ColumnarData.columns: Record<string, ArrayLike<CellScalar | CellValue>>interfaceColumnarData {columns:Record<string, ArrayLike<CellScalar|CellValue>>;}interfaceColumnarData {columns:Record<string,ArrayLike<CellScalar|CellValue>>;}Exact column runs represented by every returned row.API reference →amount: Float64Array<ArrayBuffer>let amount:Float64Array<ArrayBuffer>let amount:Float64Array<ArrayBuffer>var Float64Array: Float64ArrayConstructor
new (elements: Iterable<number>) => Float64Array<ArrayBuffer> (+6 overloads)var Float64Array:Float64ArrayConstructornew (elements:Iterable<number>) =>Float64Array<ArrayBuffer> (+6 overloads)var Float64Array:Float64ArrayConstructornew (elements:Iterable<number>) =>Float64Array<ArrayBuffer> (+6 overloads)day: Float64Array<ArrayBuffer>let day:Float64Array<ArrayBuffer>let day:Float64Array<ArrayBuffer>share: Float64Array<ArrayBuffer>let share:Float64Array<ArrayBuffer>let share:Float64Array<ArrayBuffer>GridOptions.workbook: WorkbookinterfaceGridOptions {workbook:Workbook;}interfaceGridOptions {workbook:Workbook;}Live workbook schema adopted by the store and updated by document operations.API reference →GridOptions.data?: ColumnarData | undefinedinterfaceGridOptions {data?:ColumnarData|undefined;}interfaceGridOptions {data?:ColumnarData|undefined;}Eager column-major values loaded into workbook.activeSheet; use instead of datasource.API reference →var console: Consolevar console:Consolevar console:ConsoleConsole.log(...data: any[]): void (+1 overload)Console.log(...data: any[]): void (+1 overload)Console.log(...data: any[]): void (+1 overload)The console.log() static method outputs a message to the console. MDN Reference