Conditional formatting cell ranges behaving strange after adding rows

Conditional formatting cell ranges behaving strange after adding rows

Posted by: joni.dewinter on 4 October 2021, 6:35 am EST

• 

  joni.dewinter

  • Post Options:
  • Link
    Copy

  Posted 4 October 2021, 6:35 am 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.

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 6 October 2021, 12:49 am 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

• 

  joni.dewinter

  • Post Options:
  • Link
    Copy

  Posted 6 October 2021, 6:26 am 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

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 7 October 2021, 7:40 am 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