ClosedXML / ClosedXML/ClosedXML

DataValidation ranges broken after row insert/delete

Open
#2,198 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
C#
Stars
5.7k
Forks
933
Avg merge
12h 14m
Merged PRs (30d)
1

Description

Under certain conditions data validation ranges are broken when rows are inserted or deleted. I have narrowed down the issue to two test cases that clearly show the problem:

Here a range is selected that is in use by a validation after insert. (Tanken from here https://github.com/ClosedXML/ClosedXML/blob/e911cf49b47ccc0f354de292696c6e3d3a0a2500/ClosedXML.Tests/Excel/DataValidations/DataValidationShiftTests.cs#L36)
```cs
[Test]
public void DataValidationShiftedOnRowInsert()
{
using (var wb = new XLWorkbook())
{
var ws = wb.AddWorksheet("DataValidationShift");
ws.SelectedRanges.Add(ws.Range("G6:G7")); // <---- A range is selected that is used in a validation after insert
ws.Range("A1:A1").CreateDataValidation().WholeNumber.Between(0, 1);
ws.Range("B1:B2").CreateDataValidation().WholeNumber.Between(0, 1);
ws.Range("C1:C3").CreateDataValidation().WholeNumber.Between(0, 1);
ws.Range("D2:F2").CreateDataValidation().WholeNumber.Between(0, 1);
ws.Range("G4:G5").CreateDataValidation().WholeNumber.Between(0, 1);
ws.Cells("A1:G5").Value = 1;

ws.Row(2).InsertRowsBelow(2);
var dv = ws.DataValidations.ToArray();

Assert.AreEqual(5, dv.Length);
Assert.AreEqual("A1:A1", dv[0].Ranges.Single().RangeAddress.ToString());
Assert.AreEqual("B1:B4", dv[1].Ranges.Single().RangeAddress.ToString());
Assert.AreEqual("C1:C5", dv[2].Ranges.Single().RangeAddress.ToString());
Assert.AreEqual("D2:F4", dv[3].Ranges.Single().RangeAddress.ToString());
Assert.AreEqual("G6:G7", dv[4].Ranges.Single().RangeAddress.ToString());
}
}
```
The same can be done in the respective test for row deletion `DataValidationShiftedOnRowDelete`.

I believe the issue lies somewhere in the `NotifyRangeShiftedRows` function. Here the `_rangeRepository` is enumerated and the ranges are modified. Could it be if a range is already in the repository, e.g., selected as above, this range is shifted twice?

```cs
public void NotifyRangeShiftedRows(XLRange range, Int32 rowsShifted)
{
var rangesToShift = _rangeRepository
.Where(r => r.RangeAddress.IsValid)
.OrderBy(r => r.RangeAddress.FirstAddress.RowNumber * -Math.Sign(rowsShifted))
.ToList();

WorksheetRangeShiftedRows(range, rowsShifted);

foreach (var storedRange in rangesToShift)
{
if (storedRange.IsEntireColumn())
continue;

if (ReferenceEquals(range, storedRange))
continue;

storedRange.WorksheetRangeShiftedRows(range, rowsShifted);
}
range.WorksheetRangeShiftedRows(range, rowsShifted);
}
```

Contributor guide

Open the contributing guide

Research direction

Start with DataValidationShiftTests.cs, especially DataValidationShiftedOnRowInsert and DataValidationShiftedOnRowDelete, then inspect NotifyRangeShiftedRows and its _rangeRepository handling. Run the two reproductions and verify that each validation range shifts exactly once, matching the asserted addresses after insertion and deletion.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp
Domain
backend
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.