Conditional formatting cell ranges behaving strange after adding rows

Posted by: joni.dewinter on 3 October 2021, 9:35 pm EST

  • Posted 3 October 2021, 9:35 pm EST

    In Spread.NET 14, in an ASP.NET MVC 5 environment, we have found strange behavior in a very specific scenario with conditional formatting and adding rows.
    Let's first consider two scenarios that do behave as expected:

    Scenario 1: new spread instance
    Start from a new spread instance.
    Set a conditional formatting rule to a cell using SheetView.SetConditionalFormatting, e.g. row = 1, col = 1, rowcount = 1, colcount = 1.
    Add rows using SheetView.AddRows, e.g. with arguments row = 1, rowcount = 10.
    Result: the earlier assigned conditional formatting rule (as found in SheetView.ConditionalFormatting) gets a new cellrange: row = 11, col = 1, rowcount = 1, colcount = 1.

    Scenario 2: spread instance loaded from an xml
    Start from a new spread instance.
    Set a conditional formatting rule to a cell using SheetView.SetConditionalFormatting, e.g. row = 1, col = 1, rowcount = 1, colcount = 1.
    Save the entire spread instance to xml.
    Start from a new spread instance.
    Load the saved xml into the spread instance.
    Add rows using SheetView.AddRows, e.g. with arguments row = 1, rowcount = 10.
    Result: the earlier assigned conditional formattin rule (as found in SheetView.ConditionalFormatting) gets a new cellrange: row = 11, col = 1, rowcount = 1, colcount = 1.

    The next scenario does behave differently:
    Scenario 3: individual sheetview loaded from xml
    Start from a new spread instance.
    Set a conditional formatting rule to a cell using SheetView.SetConditionalFormatting, e.g. row = 1, col = 1, rowcount = 1, colcount = 1.
    Save the active sheetview to xml.
    Start from a new spread instance.
    Load the saved xml into the active sheetview.
    Add rows using SheetView.AddRows, e.g. with arguments row = 1, rowcount = 10.
    Result: the earlier assigned conditional formattin rule (as found in SheetView.ConditionalFormatting) gets a new cellrange: row = 1, col = 1, rowcount = 10, colcount = 1.

    What is the reason for the 3rd scenario to result in a modified rowcount, instead of row?
    We would need this scenario to behave the same way as 1 and 2.
    Thanks in advance for your help.
  • Replied 5 October 2021, 3:49 pm EST

    Hi Joni,

    Could you please provide a small working sample and some steps by which we could replicate the issue at our end so that we could investigate it further and help you accordingly?

    Regards
    Avinash
  • Replied 5 October 2021, 9:26 pm EST

    Hello Avinash

    While creating the clean solution to reproduce the problem, we found an additional requirement to trigger the incorrect behavior.
    Loading an individual sheet using Open(string fileName) causes the correct behavior, using Open(Stream stream) does not.

    In the solution, we split scenario 3 into 3 and 4.
    Scenario 3 uses Open(string fileName) and works correctly now.
    Scenario 4 uses Open(Stream stream) and does not work correctly.
    SpreadConditionalFormatting.zip

    Please note the files for scenario 2, 3 and 4 are generated in scenario 1.
    You may need to change the file paths in the HomeController class.

    Kind regards
    Joni
  • Replied 6 October 2021, 10:40 pm EST

    Hi Joni,

    Thanks for the sample. This seems like a bug to me hence I have escalated this issue to the concerned team for further investigation. We will update you regarding this as we get any information from the team. The internal ID for this issue will be SPWEB-2560.

    Regards
    Avinash
Need extra support?

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

Learn More

Forum Channels