Skip to main content

Number formatting

DHTMLX Spreadsheet supports number formatting that you can apply for numeric values in cells.

note

There is a User Guide provided to make work with Spreadsheet easy for your end users.

Default number formats

A number format is an object that includes a set of properties:

  • id - the id of a format that is used to set format to a cell via the setFormat() method
  • mask - a mask for a number format. Check the list of characters available in a mask below
  • name - the name of a format displayed in the toolbar and menu drop-down lists
  • example - an example that shows how a formatted number looks like. The number 2702.31 is used as a default value for format examples

The default number formats are the following:

defaultFormats = [
{ name: "Common", id: "common", mask: "", example: "1500.31" },
{ name: "Number", id: "number", mask: "#,##0.00", example: "1,500.31" },
{ name: "Percent", id: "percent", mask: "#,##0.00%", example: "1,500.31%" },
{ name: "Currency", id: "currency", mask: "$#,##0.00", example: "$1,500.31" },
{ name: "Date", id: "date", mask: "mm-dd-yy", example: "28/12/2021" },
{ name: "Text", id: "text", mask: "@", example: "'1500.31'" }
];

This is how a spreadsheet with data in various number formats looks like:

Date format

You can define the format the dates to be displayed in the spreadsheet via the dateFormat property. The default format is "%d/%m/%Y".

const spreadsheet = new dhx.Spreadsheet("spreadsheet", {
dateFormat: "%D/%M/%Y"
});

Check the full list of available characters used to make formats.

Formats customization

You are not limited by default number formats only. There are two options of formats customization available:

  • changing the settings of default number formats
  • adding custom number formats into spreadsheet

All such modifications can be implemented via the formats configuration option. It represents an array of format objects each of which contains a set of properties:

  • id - (string) mandatory, the id of a format that is used to set format to a cell via the setFormat() method
  • mask - (string) mandatory, a mask for a number format. Check the list of characters available in a mask below
  • name - (string) optional, the name of a format that will be displayed in the toolbar and menu drop-down lists
  • example - (string) optional, an example that shows how a formatted number will look like

The structure of a mask

A mask may contain a set of common syntax characters that include digit placeholders, separators, percent and currency signs:

  • 0 - a digit in the number. Used to display insignificant zeros, if a number has fewer digits than there are zeros in the format. For example, to display 2 as 2.0, use the format 0.0.
  • # - a digit in the number. Used to display only significant numbers (insignificant zeros will be ommitted, if a number has fewer digits than there are # symbols in the format).
  • $ - formats numbers as a dollar value. To use a different currency sign, you need to define it in a mask as [$ your_currency_sign]#,##0.00 ,e.g. [$ €]#,##0.00.
    note

    Note that all characters between [$ and ] will be interpreted as a currency sign.

  • .(period) - applies a decimal point to numbers.
  • ,(comma) - applies a thousands separator to numbers.

Setting format

In order to apply the necessary format to a numeric value, make use of the setFormat() method. It takes two parameters:

  • cell - (string) the id of a cell the value of which should be formatted
  • format - (string) the name of the default number format to apply to the cell value

For example:

// applies the currency format to the cell A1
spreadsheet.setFormat("A1","percent");

Getting format

You can get the number format applied to the value of a cell with the help of the getFormat() method. The method takes the id of a cell as a parameter.

var format = spreadsheet.getFormat("A1"); 
// ->"percent"

Events

There is a pair of events you can use to control the process of cell's format changing. They are: