Repository navigation
Description
Is your feature request related to a problem? Please describe.
I'd like the ability to extend the range that a data validation rule applies to.
A very common scenario for us is this: we have a table with a header row and a single data row, and in that single data row there is a data validation list on some of the columns. We insert n rows into the table using ExcelWorksheet.InsertRow. But the data validation is now only in the last row. We have to remove the data validation and re-add it with the new extended range (which involves us doing some complicated parsing of the old address).
It would be great if EPPlus could do this for us.
I would note that this is already possible if the data validation list applies to more than one row and you insert the new rows in the middle - e.g. if you have a data validation list on A1:A2 and you insert a new row at row 2, the data validation list now applies to A1:A3.
I just want a way to do the same when the data validation list starts off as applying only to A1, and I insert a row above it.
Actually, this is possible already if we use ExcelTable.AddRow instead, but this method is not safe for us to use. It inserts cells only below the table area, rather than inserting an entire row to the sheet. This causes an exception to be thrown if a second, wider table is present further down the sheet.
Describe the solution you'd like
I don't mind, as long as it's relatively simple.
Making the Address property settable would partially solve it, but this would still require us to extend the address string manually, so it is not a full solution.
Perhaps something like:
ExcelWorksheet.InsertRow(rowFrom: 1, rows: 2, extendStylesAndDataValidationAndConditionalFormattingFromRowBelow: true);Or, since there is already a parameter called copyStylesFromRow:
ExcelWorksheet.InsertRow(rowFrom: 1, rows: 2, copyStylesFromRow: 1);...perhaps this could also copy data validation/conditional formatting along with the styles, extending them instead of copying them when rowFrom == copyStylesFromRow.
Either way, it would be ideal if this would extend existing DV objects where possible rather than just adding a new one.
And I have the same request for conditional formatting, if that's possible!
Additional context
Here are some unit tests in case it helps:
// THIS FAILS:
[TestMethod]
public void InsertRow_AtFirstTableDataRow_ExtendDataValidation()
{
using (var pck = new ExcelPackage())
{
// GIVEN a table with a header row and one data row, where the first column of the data row has a list data validation
var wks = pck.Workbook.Worksheets.Add("Sheet1");
wks.Cells["A1"].Value = "Col1";
wks.Cells["B1"].Value = "Col2";
wks.Tables.Add(wks.Cells["A1:B2"], "Table1");
var dv = wks.DataValidations.AddListValidation("A2");
dv.Formula.Values.Add("Yes");
dv.Formula.Values.Add("No");
// WHEN we insert 3 rows at the first data row, so the original data row becomes the last row of the table
wks.InsertRow(2, 3, 2);
// THEN there is still a single data validation, and it covers every data row in the table
Assert.AreEqual(1, wks.DataValidations.Count);
Assert.AreEqual("A2:A5", wks.DataValidations[0].Address.Address);
}
}
// THIS PASSES:
[TestMethod]
public void InsertRow_BetweenTableDataRows_ExtendDataValidation()
{
using (var pck = new ExcelPackage())
{
// GIVEN a table with a header row and two data rows, where the first column of both data rows has a list data validation
var wks = pck.Workbook.Worksheets.Add("Sheet1");
wks.Cells["A1"].Value = "Col1";
wks.Cells["B1"].Value = "Col2";
wks.Tables.Add(wks.Cells["A1:B3"], "Table1");
var dv = wks.DataValidations.AddListValidation("A2:A3");
dv.Formula.Values.Add("Yes");
dv.Formula.Values.Add("No");
// WHEN we insert 3 rows at the second data row, so the new rows are between the two original data rows
wks.InsertRow(3, 3, 3);
// THEN there is still a single data validation, and it has been extended to cover every data row in the table
Assert.AreEqual(1, wks.DataValidations.Count);
Assert.AreEqual("A2:A6", wks.DataValidations[0].Address.Address);
}
}Metadata
Metadata
Assignees
Labels
Type
Projects
- StatusShow more project fieldsBacklog
Activity
OssianEPPlus commentedon Oct 16, 2026
We're looking into this. If we add this It's a bit counter to how the same operation works in Excel currently. Which is a minor hurdle but we could add an alternate insert method or some such potentially.
IIRC there IS a workaround that should already work for this but I was unable to formulate it. Essentially the reason the operation is different between the two tests is because one is considered a range and the other a single-cell address. There should be some way to make EPPlus consider a single-cell address a range specifically for operations like this but it doesn't appear to work in this particular case.
We'll get back to this issue beginning of next week but it may be a decent feature suggestion
OssianEPPlus commentedon Oct 19, 2026
We've looked into to this and found an additional minor bug #2586 which we've fixed internally.
When it comes to copying data validations when inserting rows on/above the given row we will not be doing so by default as Excel does not function that way by default.
However your suggestion of an additional overload or input parameter similar or related to
int copyStylesFromRowis a good suggestion and after internal discussion we're considering how to implement something like it that also allows for changes of other row-properties in the future. For details and further development see #2588 . We're not going to rush it out but we like the idea and will see what we can do.To help you as of right now however, while the
Addressproperty is not directly settable theAddress.Addressproperty IS publicly settable and has listeners that should update things accordingly. So while not optimal you Can extend the address string that way.craigbrown commentedon Oct 27, 2026
OK sounds good, thanks for looking into this. We'll try working with the
Address.Addressproperty for now.