Number formats and cell styles

In Excel, format is a property of the cell, not the column. Two cells in the same column can show $1,234.50 and 123450% from the same stored number. @svgrid/enterprise ships that: an Excel format-string compiler, and a store that keeps per-cell formatting alive through sorting.

import {
  compileNumberFormat, createFormatStore, entryToStyle,
} from '@svgrid/enterprise/sheet'

This is different from the column-level format prop, which is Intl-based and applies to every cell in a column. Use that when a column has one meaning; use this when the user picks per cell.

Formatting a value

const money = compileNumberFormat('$#,##0.00;($#,##0.00)')

money.format(1234.5)    // { text: '$1,234.50' }
money.format(-1234.5)   // { text: '($1,234.50)' }

Compilation is cached per pattern, so a column of ten thousand cells sharing one format parses it once. formatWithPattern(value, pattern) is the one-shot convenience.

The pattern grammar

Sections

A pattern has up to four sections separated by ;:

positive ; negative ; zero ; text

With two, the second covers negatives and zero. With one, it covers everything.

A negative rendered by its own section uses its absolute value, because that section supplies the sign:

formatWithPattern(-1.5, '0.00;(0.00)')   // '(1.50)'  not '(-1.50)'

Digit placeholders

formatWithPattern(5, '000')       // '005'
formatWithPattern(0, '#')         // ''      hides zeros
formatWithPattern(1.5, '0.0#')    // '1.5'   second decimal only when present
formatWithPattern(1.55, '0.0#')   // '1.55'

Separators and scaling

, between placeholders groups thousands. , immediately before the decimal point or at the end of the pattern divides by a thousand per comma:

formatWithPattern(1234567, '#,##0')      // '1,234,567'
formatWithPattern(1500000, '0,,"M"')     // '2M'
formatWithPattern(1500, '0.0,"k"')       // '1.5k'

% multiplies by 100 and prints the sign. E+ switches to scientific, where the placeholders after the E+ size the exponent rather than the mantissa:

formatWithPattern(0.425, '0.00%')        // '42.50%'
formatWithPattern(12345, '0.00E+00')     // '1.23E+04'

Colours and literals

[Red], [Blue], [Green], [Black], [White], [Cyan], [Magenta] and [Yellow] set a colour rather than printing. It comes back on the result:

compileNumberFormat('0.00;[Red](0.00)').format(-5)
// { text: '(5.00)', color: '#ff0000' }

Text in quotes, or after a backslash, is emitted as-is. @ in the fourth section is the text placeholder.

_x (leave the width of x) and *x (fill the cell with x) both render as one space: text has no cell width to leave or fill. That is enough for Excel's accounting patterns to read as $ 1,234.50 with the symbol on the left and the number on the right.

A condition in brackets picks a section by value: the first section whose condition the value passes is used, and a section without one is the fallback. [<=9999999]###-####;(###) ###-#### is Excel's phone number.

formatWithPattern(50, '[<100]"small";"big"')    // 'small'
formatWithPattern(5551234567, '[<=9999999]###-####;(###) ###-####')
// '(555) 123-4567'

Masks

Literal text between integer placeholders turns the integer side into a mask that the digits are laid into from the right. Excel's Special formats are all masks:

formatWithPattern(123456789, '000-00-0000')   // '123-45-6789'
formatWithPattern(1234, '00000')              // '01234'
formatWithPattern(1234, '###-####')           // '-1234'   as Excel gives

SPECIAL_FORMATS holds the four: zip, zip4, phone and ssn.

Dates

A pattern containing date tokens is a date pattern.

m and mm mean minutes after an hour token and months otherwise, so hh:mm gives 15:05 rather than 15:09.

A pattern that names the meridiem puts the hour on a 12-hour clock, as a clock face reads it, and one that does not leaves it on the 24-hour one:

formatWithPattern(46275.625, 'h:mm AM/PM')   // '3:00 PM'
formatWithPattern(46275.625, 'h:mm')         // '15:00'
formatWithPattern(46275.625, 'h:mm A/P')     // '3:00 P'

Times are rounded to the finest unit the pattern shows, which is Excel's rule and the reason 23:59:40 under hh:mm reads 00:00 with the date rolled over. A date-only pattern truncates instead: an afternoon is not tomorrow.

Numbers are read as Excel serial days against the 1899-12-30 epoch. That is correct from 1900-03-01 on; Excel itself is a day out below serial 61 because it counts a 1900-02-29 that never existed, and matching that bug exactly would break real dates.

Elapsed time

[h], [m] and [s] (and [hh], [mm], [ss]) give the whole duration in that unit rather than the clock's reading of it, which is what a timesheet adds up:

formatWithPattern(1.5, '[h]:mm')       // '36:00'   a day and a half
formatWithPattern(1.5, 'h:mm')         // '12:00'   the clock, for comparison
formatWithPattern(0.5, '[mm]:ss')      // '720:00'

Fractions

# ?/? and its relatives show the closest fraction that fits the placeholders: one ? allows a denominator up to 9, two up to 99. A literal denominator is used as it stands, unreduced, which is how eighths and sixteenths are written.

formatWithPattern(1.25, '# ?/?')       // '1 1/4'
formatWithPattern(5.0625, '# ??/??')   // '5  1/16'
formatWithPattern(1.25, '# ?/8')       // '1 2/8'
formatWithPattern(1.25, '?/?')         // '5/4'     no integer part

Text

@ is the text placeholder, and it stands for the value whatever the value is: a number in a cell formatted as Text reads as the number.

formatWithPattern('note', '@')         // 'note'
formatWithPattern(5, '@')              // '5'
formatWithPattern('note', '"x"@')      // 'xnote'
formatWithPattern(5, ';;;')            // ''        Excel's hide-the-cell
formatWithPattern('note', ';;;')       // ''        text as well
formatWithPattern('note', '0.00')      // 'note'    no text section, so as it is

Scientific and engineering notation

The integer placeholders set the step the exponent moves in. One of them is the everyday scientific form; three make it engineering notation, where the exponent is a multiple of three and the mantissa carries up to three integer digits, which is how an engineer reads a datasheet.

formatWithPattern(12345, '0.00E+00')   // '1.23E+04'
formatWithPattern(12345, '##0.0E+0')   // '12.3E+3'
formatWithPattern(0.000123, '##0.0E+0')// '123.0E-6'

Presets

FORMAT_PRESETS holds what Ctrl+Shift+1 through 6 apply: number, time, date, currency, percent, scientific, plus general and accounting, which the ribbon's $ button applies, as Excel's does. percent is 0%, Excel's Percent Style: Ctrl+Shift+% and the % button show 0.425 as 43%. The ribbon's Number Format combo is the one place that differs, as it does in Excel: its Percentage entry applies 0.00%, from FORMAT_CATEGORY_PATTERNS.

accountingPattern(symbol, decimals) spells an accounting pattern for any symbol (an empty one is Excel's "None"), and accountingParts reads one back, which is how Increase Decimal works on an accounting cell:

accountingPattern('$', 2)
// '_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)'
formatWithPattern(1234.5, accountingPattern('$', 2))   // ' $ 1,234.50 '
formatWithPattern(-1234.5, accountingPattern('$', 2))  // ' $ (1,234.50)'

The per-cell store

const store = createFormatStore()

const lookup = {
  rowIdAt: (i) => rows[i]?.id ?? null,
  columnIdAt: (i) => FIELDS[i] ?? null,
}

store.set([[0, 2, 4, 3]], { numFmt: '$#,##0.00' }, lookup)
store.toggle([[0, 0, 0, 4]], 'bold', lookup)
store.get('r1', 'price')     // { numFmt: '$#,##0.00' }

Ranges are [minRow, minCol, maxRow, maxCol] in display coordinates, the same as cmd.ranges. The store converts them to row and column ids on the way in, which is what makes formatting survive a sort: keying by display index means sorting leaves the bold on whatever row now sits at that position.

entryToStyle(entry) renders one to inline CSS for a cell renderer.

Wiring the shortcuts

Ctrl+B, Ctrl+Shift+4 and the rest write through whatever store you attach. Until you attach one they decline, so the key falls through to the grid rather than looking broken:

import { enableSheet, setFormatTarget } from '@svgrid/enterprise'

enableSheet()
setFormatTarget({ store, lookup, onChange: () => (version += 1) })

Ctrl+1 calls setFormatDialogHandler(fn) if you registered one. The shortcut layer ships no dialog: what a Format Cells dialog should look like is a design decision, not a keyboard one.

Exporting

Per-cell formats round-trip to xlsx through the exporter's existing cellVisual hook, so a sheet formatted in the browser opens formatted in Excel. See export.

See also

• Excel keyboard shortcuts
• Spreadsheet formulas