Formulas in conditional format rules are not shifted properly

[Pankraty]

Do you want to request a feature or report a bug?

• Bug
• Feature

Version of ClosedXML
Current develop

What is the current behavior?

Formulas specified in conditional formats are not shifted properly when the associated range is moved (e.g. rows or columns are inserted or deleted).

What is the expected behavior or new feature?

Ranges and formulas of conditional format rules should follow the range shifts.
After this is fixed the same approach may be applied to DataValidation rules (see #680)

Did this work in previous versions of our tool? Which versions?

Never tested it before

Code to reproduce problem:

public void Main()
{
            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("ConditionalFormat");
            var range = ws.Range("C3:E5");
            var condFormat = range.AddConditionalFormat();
            condFormat.Values.Add(new XLFormula("=IF(C3>A1,TRUE,FALSE)"));
            condFormat.Style.Fill.BackgroundColor = XLColor.AirForceBlue;

            ws.Row(2).InsertRowsAbove(1);

            condFormat = ws.ConditionalFormats.First();

            Console.WriteLine("Range. Expected: {0}, actual: {1}", range.RangeAddress, condFormat.Range.RangeAddress);
            Console.WriteLine("Formula. Expected: {0}, actual: {1}", "=IF(C4>A1,TRUE,FALSE)", condFormat.Values[1].Value);

            Console.ReadKey();
}

• I attached a sample spreadsheet.
  CFShift.xlsx

[igitur]

Do you have an existing PR that fixes this? If so, reference this issue, please.

[Pankraty]

No, I did not dive into it yet. And I suspect this can be quite a large task.

[MikeInSwitzerland]

Note that this "fix" actually introduces a new bug.
If your Conditional Formatting range includes a blank Excel file, it's Value will be "null", and it'll throw an exception on line 338:

            foreach (var v in format.Values.Where(v => v.Value.IsFormula).ToList())

https://github.com/ClosedXML/ClosedXML.Report/blob/develop/ClosedXML.Report/Excel/XlExtensions.cs#L333