Number Format

The number format feature in spreadsheets is used to control how numbers are displayed. It allows users to customize the display format of numbers, including decimal places, thousands separators, percentages, currency symbols, and more. By setting the number format, users can make data easier to read and understand.

The currency toolbar action uses the Univer region to select its currency symbol and pattern. region follows locale unless explicitly configured:

TypeScript

const univer = new Univer({  locale: LocaleType.EN_US,  region: LocaleType.DE_DE,})

Caution

The DBNum syntax is not supported.

Usage

In the UI interface, you can set the number format of the cell through the menu bar. Or set the s.n.pattern field in the cell data ICellData object.

TypeScript

const data = {  v: 123456.789,  s: {    n: {      pattern: '#,##0.00',    },  },}

By default, text starting with 0 and numbers that can be converted to date formats are automatically converted to the corresponding number format.

If you do not want this automatic conversion, you can choose one of the following methods:

Set the cell number format to text format.

TypeScript

import { DEFAULT_TEXT_FORMAT_EXCEL } from '@univerjs/core'const data = {  v: '012.0',  s: {    n: {      pattern: DEFAULT_TEXT_FORMAT_EXCEL, // Text format    },  },}

Or use the Facade API to set the cell format:

TypeScript

fRange.setNumberFormat(DEFAULT_TEXT_FORMAT_EXCEL)

Add a single quote ' before entering the number (this is known as forcing text).

Facade API

Importing

Plugin mode note

Only plugin mode requires manually importing the Facade package. Preset mode already includes the corresponding Facade package, so no extra import is needed.

TypeScript

import '@univerjs/sheets-numfmt/facade'

Set Number Format

For specific parameters, please refer to: https://support.microsoft.com/en-us/office/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()// Set the number format of the A1 cell to '#,##0.00'.const fRange = fWorksheet.getRange('A1')fRange.setValue(1234.567).setNumberFormat('#,##0.00')fRange.getDisplayValue() // 1,234.57

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()// Set the number formats of the A1:B2 range.const fRange = fWorksheet.getRange('A1:B2')fRange.setValues([  [1234.567, 0.1234],  [45658, 0.9876],]).setNumberFormats([  ['#,##0.00', '0.00%'],  ['yyyy-MM-DD', ''],])fRange.getDisplayValues() // [['1,234.57', '12.34%'], ['2025-01-01', 0.9876]]

Get Number Format

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()// Get the number format of the top-left cell of the A1:B2 range.const fRange = fWorksheet.getRange('A1:B2')fRange.getNumberFormat()

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()// Get the number formats of the A1:B2 range.const fRange = fWorksheet.getRange('A1:B2')fRange.getNumberFormats()

Set the locale for number formatting

You can use the FWorkbook.setNumfmtLocal(local) method to set the locale for number formatting.

TypeScript

const fWorkbook = univerAPI.getActiveWorkbook()const fWorksheet = fWorkbook.getActiveSheet()const fRange = fWorksheet.getRange('A1')fRange.setValue(1234.567).setNumberFormat('#,##0.00')// Set the locale en_US for number formatting.fWorkbook.setNumfmtLocal('en_US')fRange.getDisplayValue() // 1,234.57// Set the locale de_DE for number formatting.fWorkbook.setNumfmtLocal('de_DE')fRange.getDisplayValue() // 1.234,57

Disable Text Format Alert and Mark

The UniverSheetsNumfmtPlugin plugin provides disableTextFormatAlert and disableTextFormatMark two configuration items to disable the alert and mark of the cell text format.

TypeScript

// presets configurationconst { univerAPI } = createUniver({  // ...  presets: [    UniverSheetsCorePreset({      disableTextFormatAlert: true,      disableTextFormatMark: true,    }),  ],})

TypeScript

// Plugin configurationuniver.registerPlugin(UniverSheetsNumfmtPlugin, {  disableTextFormatAlert: true,  disableTextFormatMark: true,})

How is this guide?