Can't add data validation rule back after removing it

[craigbrown]

EPPlus usage

Commercial use (I have a commercial license)

Environment

Windows

Epplus version

8.2.1

Spreadsheet application

Excel

Description

Hi there!

We have a spreadsheet with a data validation rule in one of the cells. When we want to extend the range that the data validation rule applies to, we remove the rule and re-add it with the new range. However, in some cases this throws an exception:

System.InvalidOperationException: A DataValidation already exists at A1:A10 If using ClearDataValidation this may be because the sheet you're reading has multiple dataValidations on one cell.

The exception message says that there is already a DataValidation rule, even though it was removed.

Here's a unit test which demonstrates the problem. This was passing up to v6.1.3.

Interestingly, if I remove the sheet1.DeleteRow(2, 9) line, the test passes.

[TestMethod]
public void RemoveAndReAddDataValidationAfterDeletingRows()
{
    using (var pck = new ExcelPackage())
    {
        // Add a worksheet
        var sheet1 = pck.Workbook.Worksheets.Add("Sheet1");

        // Next, add a data validation list to the sheet
        var dv = sheet1.Cells["A1:A10"].DataValidation.AddListDataValidation();
        dv.Formula.Values.Add("Option A");
        dv.Formula.Values.Add("Option B");

        // Delete all except the first row
        sheet1.DeleteRow(2, 9);

        // Remove the data validation
        sheet1.DataValidations.Remove(dv);
        Assert.AreEqual(0, sheet1.DataValidations.Count);

        // Now re-add the data validation
        // THIS SHOULDN'T THROW AN EXCEPTION
        sheet1.Cells["A1:A10"].DataValidation.AddListDataValidation();
    }
}

Thanks!

[OssianEPPlus]

We've verified the problem and yes this is a bug.

The reason for this:

Interestingly, if I remove the sheet1.DeleteRow(2, 9) line, the test passes.

Is related. Essentially we cannot allow multiple dataValidation within the same range. But if a user adds a validation to the exact same range in its entirety we assume the user wishes to overwrite what was there.

To deal with that sort of disrepancy and to optimize we have a lookup system. Essentially DeleteRow(2,9) deletes the range 3->9 but did not delete the A2 in the lookup. Leaving A1->A2. It DOES remove it appropriately from the dataValidation itself. Which causes a miss-match when deleting the dataValidation as it removes its address "A1" but not the faultily remaining one.