Set a specified thousand separator in a formatter

Set a specified thousand separator in a formatter

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

• 

  loukalem

  • Post Options:
  • Link
    Copy

  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

  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=']

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 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

• 

  loukalem

  • Post Options:
  • Link
    Copy

  Posted 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

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 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

• 

  loukalem

  • Post Options:
  • Link
    Copy

  Posted 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.

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 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

• 

  loukalem

  • Post Options:
  • Link
    Copy

  Posted 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)

• 

  loukalem

  • Post Options:
  • Link
    Copy

  Posted 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

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 17 May 2022, 11:14 pm EST - Updated 3 October 2022, 12:16 am 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