Number Formatting

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

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: "2702.31" },
    { name: "Number", id: "number", mask: "#,##0.00", example: "2702.31" },
    { name: "Percent", id: "percent", mask: "#,##0.00%", example: "27.0231" },
    { name: "Currency", id: "currency", mask: "$#,##0.00", example: "2702.31" }
];

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

Number format

Related sample:  Basic spreadsheet

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

Custom number formats

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

For example:

var spreadsheet = new dhx.Spreadsheet(document.body, {          
    formats: [
        {
            name: "U.S. Dollar",
            id: "currency",
            mask: "$#,##0.00"
        },
        {
            name: "Euro",
            id: "euro",
            mask: "[$€]#.##0,00",
            example: "1000.50"
        },
        {
            name: "Swiss franc",
            id: "franc",
            mask: "[$CHF ]#.##0,00"
        }
    ]
});

Related sample:  Custom formats

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

Auto detecting cell format

dhtmlxSpreadSheet can automatically identify the format of the content entered in a cell. This feature is controlled by the autoFormat configuration option. By default the format of cells is detected, you can disable this functionality like this:

var spreadsheet = new dhx.Spreadsheet(document.body, {          
    autoFormat:false
});

Related sample:  Disabled auto format

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:

Back to top