jqgrid currency formatter

Question

In Jqgrid for currency formatter there is only thousandsSeparator is available but i want lakhsSeparator

colModel: [
            {name: 'Code', index: 'Code', width: 55, editable: true, sortable: true },
        { name: 'Ammount', index: 'Ammount', width: 100, editable: true, sortable: false, formatter: 'currency', formatoptions: { prefix: '($', suffix: ')', thousandsSeparator: ','} },
          ],

here in place of thousandsSeparator i want lakhsSeparator.

Spillway

Answer 1

I find the question very interesting. I suggest don't implement the Globalize plugin. Here and here you can find additional information about it.

The usage will be simple. One should define custom formatter which uses Globalize.format and unformatter which uses Globalize.parseFloat functions. For example

formatter: function (v) {
    // uses "c" for currency formatter and "n" for numbers
    return Globalize.format(Number(v), "c");
},
unformat: function (v) {
    return Globalize.parseFloat(v);
}

For more comfort I would recommend to define numberTemplate and currencyTemplate for example like

var numberTemplate = {align: 'right', sorttype: 'number', editable: true,
        searchoptions: { sopt: ['eq', 'ne', 'lt', 'le', 'gt', 'ge', 'nu', 'nn', 'in', 'ni']},
        formatter: function (v) {
            return Globalize.format(Number(v), "n");
        },
        unformat: function (v) {
            return Globalize.parseFloat(v);
        }},
    currencyTemplate = {align: 'right', sorttype: 'number', editable: true,
        searchoptions: { sopt: ['eq', 'ne', 'lt', 'le', 'gt', 'ge', 'nu', 'nn', 'in', 'ni']},
        formatter: function (v) {
            return Globalize.format(Number(v), "c");
        },
        unformat: function (v) {
            return Globalize.parseFloat(v);
        }};

and use there in colModel like

{ name: 'amount', index: 'amount', width: 150, template: currencyTemplate },
{ name: 'age', index: 'age', width: 52, template: numberTemplate },

The demo uses "en-IN" locale and display results like on the picture below

Spurlock

thanks for the great answer, but again one more info, in this case how would i provide negative currency format like "-$1.00" – Spillway

@Meraj: The file already contains the information about negative format of currency. For example "Rs. -2,10,98,76,54,321.00". If you find another format for negative currency better you can either modify in your copy of culture.en-IN.js pattern: ["$ -n","$ n"] to another value or you can use Globalize.format(Number(v), "n") instead of Globalize.format(Number(v), "c") in custom formatter for currency. You can add "-" or other additional character like you as want. – Spurlock

@Meraj: Another thing is interesting for me: how the number "100,000,000,000,000,000" will be written in your country: as "1,000,00,00,000,00,00,000" or as "1,00,00,00,00,00,00,00,000"? See the discussion under the answer from Tomasz Pęczek (tpeczek). – Spurlock

Oleg: we show 10 crore in indian numbering system as 10,00,00,000,00,00,000, tpeczek solution was spot on in my case. – Spillway

@Meraj: Thanks! I'll post the corresponding bug report to the developer of Globalization plugin. – Spurlock

Answer 2

You can add this functionality to currency the formatter. First you would need to modify the built in NumberFormat function. To do this you can run below script after loading jqGrid script files:

$.fmatter.util.NumberFormat = function(nData,opts) {
    if(!$.fmatter.isNumber(nData)) {
        nData *= 1;
    }
    if($.fmatter.isNumber(nData)) {
        var bNegative = (nData < 0);
        var sOutput = nData + "";
        var sDecimalSeparator = (opts.decimalSeparator) ? opts.decimalSeparator : ".";
        var nDotIndex;
        if($.fmatter.isNumber(opts.decimalPlaces)) {
            var nDecimalPlaces = opts.decimalPlaces;
            var nDecimal = Math.pow(10, nDecimalPlaces);
            sOutput = Math.round(nData*nDecimal)/nDecimal + "";
            nDotIndex = sOutput.lastIndexOf(".");
            if(nDecimalPlaces > 0) {
                if(nDotIndex < 0) {
                    sOutput += sDecimalSeparator;
                    nDotIndex = sOutput.length-1;
                }
                else if(sDecimalSeparator !== "."){
                    sOutput = sOutput.replace(".",sDecimalSeparator);
                }
                while((sOutput.length - 1 - nDotIndex) < nDecimalPlaces) {
                    sOutput += "0";
                }
            }
        }
        if(opts.thousandsSeparator) {
            var sThousandsSeparator = opts.thousandsSeparator;
            nDotIndex = sOutput.lastIndexOf(sDecimalSeparator);
            nDotIndex = (nDotIndex > -1) ? nDotIndex : sOutput.length;
            var sNewOutput = sOutput.substring(nDotIndex);
            var nCount = -1;
            for (var i=nDotIndex; i>0; i--) {
                nCount++;
                if ((nCount%3 === 0) && (i !== nDotIndex) && (!bNegative || (i > 1))) {
                    sNewOutput = sThousandsSeparator + sNewOutput;
                }
                sNewOutput = sOutput.charAt(i-1) + sNewOutput;
            }
            sOutput = sNewOutput;
        }
        else if(opts.lakhsSeparator) {
            var sLakhsSeparator = opts.lakhsSeparator;
            nDotIndex = sOutput.lastIndexOf(sDecimalSeparator);
            nDotIndex = (nDotIndex > -1) ? nDotIndex : sOutput.length;
            var sNewOutput = sOutput.substring(nDotIndex);
            var nCount = -1;
            var i = nDotIndex;
            while (i > 0) {
                for (var nCount = 0; nCount < 7 && i > 0; nCount++) {
                    sNewOutput = sOutput.charAt(i-1) + sNewOutput;
                    if (((nCount === 2) || (nCount === 4) || (nCount == 6)) && (!bNegative || (i > 1))) {
                        sNewOutput = sLakhsSeparator + sNewOutput;
                    }
                    i--;
                }
            }
            sOutput = sNewOutput;
        }
        sOutput = (opts.prefix) ? opts.prefix + sOutput : sOutput;
        sOutput = (opts.suffix) ? sOutput + opts.suffix : sOutput;
        return sOutput;

    } else {
        return nData;
    }
};

Now you can define your formatting options like this:

colModel: [
        { name: 'Code', index: 'Code', width: 55, editable: true, sortable: true },
        { name: 'Ammount', index: 'Ammount', width: 100, editable: true, sortable: false, formatter: 'currency', formatoptions: { prefix: '($', suffix: ')', thousandsSeparator: null, lakhsSeparator: ',' } },
        ...
],

That should give you the required result.

Ebberta

thanks alot its works, with minor change, but if column is editable then we need to append code to unformat. – Spillway

@Spillway Yes in case of editing $.unformat should be modified as well – Ebberta

@tpeczek: How I could understand from wikipedia the Lakh separator should work together with Thousands separator. Additionally not only nCount < 7 is supported. If I understand correct the number 3,210,987,654,321 will be written in India as 32,10,98,76,54,321. It corresponds groupSizes: [3,2] in globalize.culture.en-IN.js instead of default groupSizes: [3] setting in en-US (default setting). – Spurlock

@tpeczek: I find the easiest way to use Globalize plugin to solve such problems. See my answer for details. – Spurlock

@Spurlock My first approach was similar to yours but then I have read South Asian numbering system article which introduces Lakh and Crore. According to this article number 3,210,987,654,321 should be written in India as 3,21,098,76,45,321. I think we need an Indian specialist here. – Ebberta

@tpeczek: It seems that it's one more bug in Globalize plugin. I find funny the usage of mix separators after 2 and 3 position: "1,000,00,00,000" (one thousand crore / one lakh lakh) and "1,000,00,00,000,00,00,000". It seems, one should post one more bug report. – Spurlock

@Spurlock The real question still is what they actually use in India. This subject seems similar to dd.mm.[yy]yy dates in Polish that we once talked about. The standard might be one and the life might be something different. – Ebberta

@tpeczek: In the article one can find: "1 billion (100 crore) is written as 1,00,00,00,000", but later in the table one find "100,00,00,000". So I am not really understand whether both forms are correct or only one from the table. – Spurlock

@tpeczek: By the way, could you post pull request about Polish dates (). My pull request to DE and EN in merged here. lukasz-schab don't answered till now on my comments here. – Spurlock

@tpeczek: Thanks! I think that you can make better choice between different date formats currently used Poland as me. :-) – Spurlock