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 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.

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.

import { DEFAULT_TEXT_FORMAT_EXCEL } from '@univerjs/engine-numfmt'

const data = {
  v: '012.0',
  s: {
    n: {
      pattern: DEFAULT_TEXT_FORMAT_EXCEL, // Text format
    },
  },
}

Or use the Facade API to set the cell format:

fRange.setNumberFormat(DEFAULT_TEXT_FORMAT_EXCEL)

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

Facade API

Set Number Format

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

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

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

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()

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.

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.

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

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