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);

This can also be done with columns and worksheets.

For example you can now easily delete a selection of worksheets based on what's in their names:

//Following example is from a simple console application
using (var p = new ExcelPackage())
{
    var wb = p.Workbook;
    p.Workbook.Worksheets.Add("Sheet1");
    p.Workbook.Worksheets.Add("Sheet2");
    p.Workbook.Worksheets.Add("SomeOtherws");
    p.Workbook.Worksheets.Add("Sheet3");
    p.Workbook.Worksheets.Add("YetAnother");

    Console.WriteLine("Worksheets Expected Before: 5");
    Console.WriteLine($"Actual: {p.Workbook.Worksheets.Count}");

    p.Workbook.Worksheets.DeleteAll(ws => ws.Name.Contains("Sheet"));

    Console.WriteLine("Worksheets Expected After: 2");
    Console.WriteLine($"Actual: {p.Workbook.Worksheets.Count}");
}

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.