Conditional Formatting Issue with numbers as Text

Conditional Formatting Issue with numbers as Text

Posted by: m.holland-moritz on 14 July 2022, 6:34 pm EST

• 

  m.holland-moritz

  • Post Options:
  • Link
    Copy

  Posted 14 July 2022, 6:34 pm EST

  Hello,

  while testing some tables of our customers we encountered a problem with conditional formatting.
  
  Our customers work with article data and of course the article number plays a big role. They may only occur once. So our application sets a conditional formatting on the column with the article numbers, searching for duplicates. This column is always marked as text, because they can consist of numbers as well as letters or certain characters.

  So we encountered the problem that some of our customers use article numbers like 123456 and a variation of this article is 123456.0, which is marked afterwards according to the set conditional formatting, but this is and error since they are both unique. I know that they simply use the integers and so it is technically the same.

  Is there a way to work around this issue?

  Regards
  
  Maik

• 

  m.holland-moritz

  • Post Options:
  • Link
    Copy

  Posted 14 July 2022, 6:37 pm EST - Updated 3 October 2022, 12:10 am EST

  

  1318×755 49.3 KB

  Here an example of what i mean

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 17 July 2022, 10:50 pm EST

  Hi Maik,

  Currently, the requested feature is not supported in SpreadJS. As I can verify, Microsoft Excel doesn’t support this and we match the Excel’s policy. Currently, there is no workaround to achieve the requested functionality.

  Regards
  
  Ankit

• 

  m.holland-moritz

  • Post Options:
  • Link
    Copy

  Posted 20 July 2022, 1:33 am EST - Updated 3 October 2022, 12:11 am EST

  Hey Ankit,

  Yes you are right, Excel doesn’t provide it that way, but Excel looks up for the Culture using.
  
  Since we use german culture and i implemented that via GC.Spread.Common.CultureManager.addCultureInfo() normaly .0 ending numbers should not be interepreted as number anymore?

  

  Regards
  
  Maik

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 20 July 2022, 7:32 pm EST

  Hi Maik,

  I was able to see the difference in the Excel behavior and SpreadJS behavior when applying the german culture. I have escalated this to the dev team for further investigation. The internal tracking id is: SJS-13973. I will let you know when I have an update on this.

  Regards
  
  Ankit

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 24 July 2022, 10:47 pm EST - Updated 3 October 2022, 12:11 am EST

  Hi Maik,

  The devs have informed that the behavior is by design. The value “looks like” a number value, but actually, they are not. Using the same value with the same conditional format in Microsoft Excel, we will get the same result.

  

  794×746 72.1 KB

  Sample: https://jscodemine.grapecity.com/share/jHdfiL54wUmwHWMDWJWMbw/

  Steps I have followed:

  1. Import the attached “sampledata.ssjson” .

  2. Notice that In Sheet1, the “Text” formatter is applied on the Column “C”.

  3. Select the column “C”. Go to Styles → ConditionalFormat → ManageRules.

  4. Create “New Rule” by selecting rule type as “Format only unique or duplicate values”. Select the format as “Bold Italic”.

  5. Apply the Rule.

  You can also refer to “Steps.gif” that describes the steps I have followed.

  Steps.gif:

  

  Regards

  Ankit

• 

  ankit.kumar

  

  • Post Options:
  • Link
    Copy

  Posted 24 July 2022, 11:10 pm EST

  sampledata.ssjson file:

  sampledata (1).zip