ClosedXML / ClosedXML/ClosedXML
DataValidation ranges broken after row insert/delete
- 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
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