Iteration and Deletion in Rows Columns and Worksheets

New Functionality DeleteAll(<Predicate>)

Epplus 8.0.0 introduces new delete functionality for Worksheets, Columns and Rows

The recommended way to delete e.g. rows based on some predicate is the DeleteAll method.

If you wish to e.g. delete every other row in a range, you can now do this:

var ws = wb.Worksheets.Add("rowSheet");
var range = ws.Cells["A1:D10"];

//Some value must be set for row to exist
range.Value = "5";

ws.Rows.DeleteAll(row => row.Row % 2 > 0);

In earlier versions:

It is recommended to use a For-Loop and to go from the last item to the first whenever possible. To avoid the indicies getting mixed up as e.g. rows are moved up in the order when a row above them is deleted.

var ws = wb.Worksheets.Add("rowSheet");
var range = ws.Cells["A1:D10"];

//Some value must be set for row to exist
range.Value = "5";

var toBeDeleted = ws.Rows.Where(row => row.StartRow % 2 > 0).ToList();

for (int i = toBeDeleted.Count() -1; i >= 0; i--)
{
    ws.DeleteRow(toBeDeleted[i].StartRow);
}

Iteration is not recommended for delete and insert

It is not recommended to use Foreach or other Enumeration to alter a collection via deletion or insertion when iterating through it in Epplus as this may lead to unexpected behaviour. Nor to delete or insert in cells around the iterated range in a way that would affect the iterated cells.

Much like in e.g. List<T> altering a collection while iterating through it can cause issues.