Number Formatting

While working with number values in Webix SpreadSheet, you have two options of setting format for them:

  • make use of the provided default formats with the possibilities to adjust them to your needs
  • create a custom format that will meet your requirements

There is User Guide to help your end users apply number formatting in Webix SpreadSheet.

Default number formats

The following default formats for numbers are available:

  • Common - no formatting applied
  • Currency - a format to display a number as currency ($)
  • Number - a format to display a number with delimited tens, hundreds and thousands
  • Percent - a format to display a number as percentage (%)

Default Number Formats

The default formats can be customized via the dedicated interface:

Default Number Formats Settings

Related sample:  Number Format

Custom number format

It is also possible to apply a custom format of displaying a numeric value to a cell or a selected range of cells.

There is a handy interface for setting a custom number format:

Custom Format Interface

Related sample:  Number Format

Custom format structure

A custom format notation presents a string which consists of several code sections, separated by semicolons.

Each code section may include:

  • [condition] in square brackets - e.g. [>1000], to compare the cell value with
  • [color] in square brackets - e.g. [blue]
  • format as a set of zeros (e.g. 0.0;) which:
    • are separated by decimal or thousand separators
    • have additional 0 after the separator to display insignificant zeros or # to ignore them
  • text - e.g. Text, that should be displayed in this cell

If there are both the condition and the color sections, the condition must go first.

For example:

"[>1000][red];[>100][green]0.0;[blue] Small"

Applying custom format

For setting a custom number format for a cell value, make use of the setFormat method. It takes three parameters:

  • rowId - (number) the row id
  • columnId - (number) the column id
  • format - (string) a string with conditions for formatting the cell content. Each separated by semi-colons
$$("ss1").setFormat(2,2,"[>1000]>0.0;[>100]Check 0,000.0#;[=0]Nope");

Specifying conditions

The condition should be enclosed in square brackets and consist of a comparison operator and a value. For example, the following format colors numbers that are less than or equal to 150 in green and numbers that are greater than 150 in orange.

"[<=150][green];[>150][orange]"

You can also apply conditional formats to cells (for example, highlighting a cell depending on its value).

Specifying colors

To specify the color for a section of the format, type the name of one of the available colors (green, red, blue, orange, black, violet or magenta) enclosed in square brackets in the section. The color code must be the second item in the section (after the condition).

Specifying significant digits, decimal and thousands separators

  • 0 (zero) - the digit placeholder to display insignificant zeros, if a number has fewer digits than there are zeros in the format. For example, to display 4 as 4.0, use the format 0.0
  • # - the digit placeholder to display only significant numbers (not to display extra zeros when the number has fewer digits than there are # symbols in the format)
  • ? - works the same as 0 (zero), but adds a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column
  • . (period) - the digit placeholder to display the decimal point in a number
  • , (comma) - the digit placeholder to display the thousands separator in a number

Styling number formatting

You can redefine the styling of default number formats via the corresponding CSS classes. There are three of them:

  • webix_ssheet_format_int - for the Number format
  • webix_ssheet_format_price - for the Currency format
  • webix_ssheet_format_percent - for the Percent format

For example, the style of the Number format can be set as in:

<style>
    .webix_ssheet_format_int{
        color: blue;
        font-style: italic; 
    }
</style>

You will see the result in the number format dialog:

Custom Style for Default Number Format

  • webix_ssheet_format_{color} - can be used to specify styles for custom cell format

You can replace the last part of the class name, {color} with the name of the color which you want to use for formatting (green, red, blue, orange, black, violet or magenta) and apply the same word in the [color] section of the custom format.

For example, the default CSS style for the green color is the following:

<style>
    .webix_ssheet_format_green{
        color: green !important;
    }
</style>

And it is used in the custom format as follows:

"[>100][green]0.0"
Back to top