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
<input type="text" id="A1" data-format="$ 0,0[.]00" />
<span id="D5" data-format="0[.]00 %"></span>
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
$().calx('language',{ id : 'id', config: { delimiters: { thousands: '.', decimal: ',' }, abbreviations: { thousand: 'rb', million: 'jt', billion: 'M', trillion: 'T' }, ordinal : function (number) { return ''; }, currency: { symbol: 'Rp.' } } });
After new language was registered, you can call it when initializing the Calx:
$('#form_element').calx({ language : 'id' });
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
Number | Format | String |
---|---|---|
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
Number | Format | String |
---|---|---|
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
Number | Format | String |
---|---|---|
100 | ‘0b’ | 100B |
2048 | ‘0 b’ | 2 KB |
7884486213 | ‘0.0b’ | 7.3GB |
3467479682787 | ‘0.000 b’ | 3.154 TB |
Percentages
Number | Format | String |
---|---|---|
1 | ‘0%’ | 100% |
0.974878234 | ‘0.000%’ | 97.488% |
-0.43 | ‘0 %’ | -43 % |
0.43 | ‘(0.000 %)’ | 43.000 % |
Time
Number | Format | String |
---|---|---|
25 | ’00:00:00′ | 0:00:25 |
238 | ’00:00:00′ | 0:03:58 |
63846 | ’00:00:00′ | 17:44:06 |
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
Walah malah bubrah kopian tadi maaf.
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 ?
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!
Hi Lee,
you can simply use IF formula to do that,
IF(A1<0, 0, A1)
regards,
ikhsan
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 !
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')
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
Hi Gary,
Which version of calx do you use? 1x or 2.x?
Thanks for this plugin.
How I change time format to this type: 3 days 14 hours 42 minutes
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
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?