Iteration and Deletion in Rows Columns and Worksheets

Recommendation

It is not recommended 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.

From Epplus 8.0:

It is recommended to use the DeleteAll<Predicate> method available on Rows, Columns and Worksheets. If you wish to e.g. delete every other row in a range.

Do:

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

Do NOT: Use Foreach.

In earlier versions:

It is recommended to use a For-Loop rather than a foreach and to iterate 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.

Do:

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

Do NOT: Use Foreach.

Problem Example

Example

As an example, here is a simple (faulty) foreach-loop intended to delete every other row:

using (var p = new ExcelPackage())
{
    var wb = p.Workbook;
    var ws = wb.Worksheets.Add("rowSheet");
    var range = ws.Cells["A1:D10"];

    range.Value = "5";
    int iteratedRows = 0;

    var rows = ws.Rows;
    foreach (var row in rows)
    {
        if (row.StartRow % 2 > 0)
        {
            ws.DeleteRow(row.StartRow);
        }
        iteratedRows++;
    }

    var finalRows = iteratedRows;
}

One would expect that finalRows is 10.

However because we delete in the list of rows while we are within it. It is actually 7 (somewhat inexplicably). This is because the row is deleted from the 'rows' variable while iterating through it.

This is confusing and somewhat unexpected behaviour and also partially why .NET classes like List<T> does not allow to change the collection while iterating through it.

Detailed description of example:

In essence: rows 1, 3, 5 and 7 are to be deleted.

Starting with row 1 being deleted. This means rows 2-10 move up one row becoming row 1-9.

We now go to delete row 3 (originally row 4). rows 4-9 now become rows 3-8.

row 5 deleted, rows 6-8 become rows 5-7

Row 7 is deleted. We stop iterating as rows 8-10 no longer exist. Having been moved up each previous deletion.

The iteration ends.

Why does Epplus not throw an Exception?

When trying to delete within e.g. classes like List<T> .NET throws an exception to not delete within the collection you are iterating through. Why doesn't Epplus do the same with rows and columns?

In Epplus we have allowed it since when iterating through e.g. cells[A5:A8] it would be strange to not be allowed to manipulate cells["A2"]. A List<T> only has to respect the items within the list. Cells in Epplus and Excel are directly affected by other cells, worksheets and workbooks that are not within the current range.

Say for example if Row 2 is deleted when iterating cells[A5:A8] the entire range we are iterating through has been shifted upwards. The moment row 2 is deleted we are now iterating through cells[A4:A7].

Simply put, when dealing with ranges the situation is more complex. Therefore Epplus allows users the Freedom of deleting and inserting as they wish with the responsibility of keeping track over how this may affect their cells and collections.

Epplus has choosen to let the user have a fuller freedom with inserts and deletes along with the responsibility of keeping track of the effects those actions do.

However we also see the need for clearer and simpler ways of handling of these situations in the future. And have as such started to add recommended methods like the DeleteAll method.