set a specified thousand separator in a formatter

Posted by: loukalem on 3 May 2022, 12:03 am EST

  • Posted 3 May 2022, 12:03 am EST

    Dear all,

    I want to specify for a number a custom formatter that specifies which thousand separator is used for this number (different than the one defined in the culture)

    For example, I want to display the following numbers this way

    123 -> 123
    1234 -> 1'234
    1234567 -> 1'234'457

    If I specify a custom format with
    ###'###'##0
    then I will have this result
    123 -> ''123 -> not OK
    1234 -> '1'234 -> not OK
    1234567 -> 1'234'567 OK

    I know that can I create a formatter like this
    ###'###'##0[>999999];###'##0[>999];##0[<=999]

    that will work but I was wondering if there was not something more generic to allow to continue to use the comma as the thousand separator when describing the number but to simply specify the character to use as this thousand separator

    ###,###,##0[thousand=']

  • Replied 3 May 2022, 3:50 pm EST

    Hi,

    You can define a custom culture and set the numberGroupSeparator to the character you want to use. For example, you can refer to the following sample that I have created for you: https://jscodemine.grapecity.com/share/DAbVVMufF02lhAL6RZifqg/

    NumberFormat Field: https://www.grapecity.com/spreadjs/docs/latest/online/SpreadJS~GC.Spread.Common.CultureInfo~NumberFormat.html

    Create Custom Culture: https://www.grapecity.com/spreadjs/docs/latest/online/culturecustom.html

    Globalization Demo: https://www.grapecity.com/spreadjs/demos/features/culture/globalization/purejs

    Please let us know if you need further assistance on your query? We would be happy to help you.

    Regards
    Ankit
  • Replied 6 May 2022, 12:12 am EST

    Thank you for this information, but my goal is to specifically set a culture for a particular formatter. I know that I can define the culture to use for a formatter by adding in the format [$-xxx] when xxx is the hexa decimal value of the desired code page.

    So I could be able to switch the culture for this cell to fr-CH by using this formatter

    [$-100C]###,###,##0

    and get 123'456'789 but I got 123 456 789, with a space and not a quote?

    I know that you handle well the swiss culture because in your sample, if I select fr-CH in the list, numbers are well formatted with quotes.

    https://www.grapecity.com/spreadjs/demos/features/culture/globalization/purejs

    Do I miss something?

    Thank you
  • Replied 8 May 2022, 8:57 pm EST

    Hi,

    [$-100C]###,###,##0

    and get 123'456'789 but I got 123 456 789, with a space and not a quote?

    Could, you please let me know what is steps for this observation. in which culture you have applied this formatter. Please provide this information so that we could investigate it further and help you accordingly.

    Regards
  • Replied 8 May 2022, 11:45 pm EST

    Hello Avinash, thanks for your answer.

    I had a look at the file gc.spread.sheets.designer.resource.en.15.0.5.js and see that some formatters were expressed using a syntax beginning with [$-409] or [$-804], like date or accounting formats.

    409 and 804 are respectively hexadecimal code page for US and China, and a formatter like "[$-409]ddd mmm yy" will express the day and month always in english, even with a different culture.

    So my guess was that I can force the culture to any supported codepage by adding [$-xxx] where xxx is the hexa value for the code page.

    But it seems that it works only for a limited number of codepages, US, China, Japan and Korean, and that the french-swiss code page fr-CH (100C in hexa) is not supported.

    So I have no way to tell that a number must be formatted using decimal separator and thousand separator of a specific code page, out of the few supported codepages.
  • Replied 10 May 2022, 9:25 pm EST

    Hi,

    Currently, SpreadJS provides users with 18+ language packages in order to support globalization and localization for CalcEngine function names, table function names, special function logic, CalcError names and boolean value resource. You could also use Custom Language Package and modify the existing language package.

    Language Packages: https://www.grapecity.com/spreadjs/docs/latest/online/AvailableCalcEngineLanguagePackagesForSpreadJS.html

    Your query: So I have no way to tell that a number must be formatted using decimal separator and thousand separator of a specific code page, out of the few supported codepages.

    ---> I couldn't understand your use case. Could you please explain your use case with an example/demo with the steps so that we can have a better understanding of what you are trying to achieve and we could assist you accordingly?

    Regards
    Ankit
  • Replied 17 May 2022, 1:24 am EST

    I want to have a number in a cell formated using Swiss thousand separator which is a quote

    I want to specify this formatter #,###,##0 and display numbers like

    123
    1'234
    1'234'567

    This is possible if you specify in your culture that your thousand separator is a quote BUT I don't want my document to be dependant of the current culture, I want that my Swiss invoice displays numbers in the specified swiss format with the quote separator whatever the current culture is.

    So I need a way to either tell this cell to use the Swiss Culture (or to use at least a specified thousand separator)
  • Replied 17 May 2022, 1:27 am EST

    I can do it using this trick

    ###'###'##0[>999999];###'##0[>999];##0[<=999]


    but then I can't have a specific formatter for zero or negative values because it's limited to three expressions
  • Replied 17 May 2022, 11:14 pm EST

    Hi,

    The thousand separator depends on the culture being applied on the SpreadSheet. Currently, to set the thousand separator independent of the culture is not supported.

    However, you can use the Custom Formatter if that meets your requirement. Please refer to the sample that I have created for you: https://jscodemine.grapecity.com/share/iq2Q78AM20ulxvQs3pp9kQ/

    Custom Formatter: https://www.grapecity.com/spreadjs/demos/features/cells/formatter/custom-formatter/purejs

    Regards

    Ankit

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels