Formatting Number using Calx

Formatting Number using Calx2017-05-17T19:56:54+00:00

Instead of plain number, Calx can be used to format any number to be more readable format. You can define the format inside data-format attribute, when data-format is not present, 0

[.]00 will be used as the default format.

Defining Language Format

By default, Calx will use comma [,] as thousands delimiter, dot [.] as decimal delimiter, and dollar sign [$] as currency symbol. But, You can change it to any other format by calling Calx language method and define it’s configuration

After new language was registered, you can call it when initializing the Calx:

 

Formatting Number

Since Calx use Numeral.JS in it’s core, it is use format syntax from Numeral.JS . Documentation below was borrowed from Numeral.JS documentation. Use dollar sign [$] to display currency symbol, comma[,] as thousand separator, and dot [.] as decimal separator, regardless of currency symbol, thousands separator, and decimal separator in language configuration, Calx will replace it automatically.

Thanks to Adam Drapper for creating Numeral.JS https://github.com/adamwdraper/Numeral-js

Numbers

NumberFormatString
10000‘0,0.0000’10,000.0000
10000.23‘0,0’10,000
-10000‘0,0.0’-10,000.0
10000.1234‘0.000’10000.123
10000.1234‘0[.]00000’10000.12340
-10000‘(0,0.0000)’(10,000.0000)
-0.23‘.00’-.23
-0.23‘(.00)’(.23)
0.23‘0.00000’0.23000
0.23‘0.0[0000]’0.23
1230974‘0.0a’1.2m
1460‘0 a’1 k
-104000‘0a’-104k
1‘0o’1st
52‘0o’52nd
23‘0o’23rd
100‘0o’100th

Currency

NumberFormatString
1000.234‘$0,0.00’$1,000.23
1000.2‘0,0[.]00 $’1,000.20 $
1001‘$ 0,0[.]00’$ 1,001
-1000.234‘($0,0)’($1,000)
-1000.234‘$0.00’-$1000.23
1230974‘($ 0.00 a)’$ 1.23 m

Bytes

NumberFormatString
100‘0b’100B
2048‘0 b’2 KB
7884486213‘0.0b’7.3GB
3467479682787‘0.000 b’3.154 TB

Percentages

NumberFormatString
1‘0%’100%
0.974878234‘0.000%’97.488%
-0.43‘0 %’-43 %
0.43‘(0.000 %)’43.000 %

Time

NumberFormatString
25’00:00:00′0:00:25
238’00:00:00′0:03:58
63846’00:00:00′17:44:06

12 Comments

  1. rukim May 11, 2016 at 7:44 am - Reply

    Saya makai ini saja yg bisa jalan mas,

    kalau kopi langsuung dari web sampean ini gak mau jalan, padahal pingin banget format desimal jadi tanda koma bukan tanda titik. Tadinya makai punya Perancis agar tanda desimal jadi koma, tapi tetap saja harus pakai tanda titik. Sy masih makai calx 2.2.0

    Mohon pencerahannya 😀 Maturnuwun

    • rukim May 11, 2016 at 8:12 am - Reply

      Walah malah bubrah kopian tadi maaf.

  2. Didier February 12, 2016 at 10:23 pm - Reply

    I am having trouble with percentages and different behaviour depending on how cells are created.

    s is the spreadsheet. doing

    ex 1: s.getCell(‘A1’).setFormat(“0%”).setValue(0.35)
    or
    Ex 2: s.getCell(‘A1’).setValue(0.35).setFormat(‘0%’)

    Does not lead to the same result.

    case 1 the value set is 0.00349999 and the display is 0%
    case 2 the value set is 0.35 and the display is 35%

    To me case 2 is right (and corresponds to what excel does)
    In any event modifying the value passed is wrong I believe.

    Note that when creating the spreadsheet for the first time it is using the case 1.

    As a short term workaround, once the spreadsheet is created I loop over all cells with a percent; wipe the format, update the value by the value * 100 and reapply the format. This works but is a bit time consuming 🙂

    Any idea ? is it a bug or am I doing something stupid ?

    Didier
    PS This is the 3rd time I try to post this commet, it has been lost twice ?

  3. Lee October 15, 2015 at 3:14 pm - Reply

    Hi. I have a field that shows a calculated number. However sometimes it shows a negative number, so how can I set the field to show 0 (zero) if the number is less than 0 (zero)?

    Thanks!

    • ikhsan October 15, 2015 at 6:48 pm - Reply

      Hi Lee,

      you can simply use IF formula to do that, IF(A1<0, 0, A1)

      regards,
      ikhsan

  4. Gabriel September 7, 2015 at 4:24 am - Reply

    Help!

    I created a dynamic form by adding value (mysql search), discount and per product, and adding fields per line, and input with the full amount, however when I click the input to search the mysql data it wipes out the value of the autocomplete !

    • ikhsan September 7, 2015 at 7:18 am - Reply

      Hi Gabriel,

      so the input form value is setted up by autocomplete plugin? if so, make sure to use $('#the_input').trigger('change') or set cell value using $('#form').calx('setValue', 'cell_addr', 'the value')

  5. Gary April 21, 2015 at 7:37 pm - Reply

    Hi there, loving this script to help me build an invoice form but just cannot locate where I am to change the currency from $ to £, is it possible at all?

    Thank you

    • ikhsan April 22, 2015 at 6:28 am - Reply

      Hi Gary,

      Which version of calx do you use? 1x or 2.x?

  6. Igor August 8, 2014 at 2:38 pm - Reply

    Thanks for this plugin.
    How I change time format to this type: 3 days 14 hours 42 minutes

    • ikhsan August 8, 2014 at 2:48 pm - Reply

      Hi Igor,

      I am sorry, but that format is not supported, the formatting function is highly depend on numeral.js

      but you can try using onupdate callback to reformat the value

      regards
      Ikhsan

  7. isaac April 17, 2014 at 12:11 pm - Reply

    Is there a way to change the css of the symbol. For instance, I am using this data-format=”0[.]00 %” and need to adjust the % symbol to be formatted using a <sup></sup> html tag. Is that possible?

Leave A Comment