Conditional Formatting Issue with numbers as Text

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

  • 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
  • Replied 14 July 2022, 6:37 pm EST



    Here an example of what i mean
  • Replied 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
  • Replied 20 July 2022, 1:33 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
  • Replied 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
  • Replied 24 July 2022, 10:47 pm 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.



    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


  • Replied 24 July 2022, 11:10 pm EST

    sampledata.ssjson file:

    sampledata (1).zip
Need extra support?

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

Learn More

Forum Channels