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 } from '@univerjs/engine-numfmt';
{
v: '012.0',
s: {
n: {
pattern: DEFAULT_TEXT_FORMAT // Text format
}
}
}
Set the cell format through univerAPI
:
range.setNumberFormat(DEFAULT_TEXT_FORMAT);
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,
});