Number Format

Facade API	Has Paid Plan	Univer Server	Univer on Node.js	Preset
✅	-	-	✅	UniverSheetsCorePreset

The number format plugin provides the formatting function of numbers in cells, including the number of decimal places, thousands separator, currency symbol, etc.

🚨

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.


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

Facade API 0.5.2+

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');
console.log(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', '']
]);
console.log(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');
console.log(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');
console.log(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');
console.log(fRange.getDisplayValue()); // 1,234.57
 
// Set the locale de_DE for number formatting.
fWorkbook.setNumfmtLocal('de_DE');
console.log(fRange.getDisplayValue()); // 1.234,57

FAQ

I don’t want the cell to be converted by default when entering a number starting with 0 or a number that can be converted to a date format

You can set the cell number format to text format, or add a single quote ' (force text) before entering the number.

Set s.n.pattern in the ICellData object:


import { DEFAULT_TEXT_FORMAT_EXCEL } from '@univerjs/engine-numfmt';
 
{
  v: '012.0',
  s: {
    n: {
      pattern: DEFAULT_TEXT_FORMAT_EXCEL // Text format
    }
  }
}

Set the cell format through univerAPI:


range.setNumberFormat(DEFAULT_TEXT_FORMAT_EXCEL);

Disable Text Format Alert and Mark 0.6.7+

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,
});