C# EPPlus DeleteRow Error- Source Array Was Not Long Enough

Question

I am having an error when trying to delete thousands of rows from an excel file.

I am using EPPlus in C# to do the modifying of the data.

This is the code that I am running to do the deletion:

rowsToDelete.Reverse();

foreach (var row in rowsToDelete)
{
    try
    {
        worksheet.DeleteRow(row);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

'rowsToDelete' is acquired in a previous section of code that just gets the row numbers that we need to delete based on the data that is in the worksheet.

'rowsToDelete' is a List that is defined as such:

List<int> rowsToDelete = new List<int>();

The error that I am getting is:

Source array was not long enough. Check the source index, length, and the array's lower bounds. (Parameter 'sourceArray')

The number of rows in this Excel file is 65152 and in this circumstance I am trying to delete 65147 rows. Normally I wont delete this many rows but it so happens that this time I need to.

I noticed a pattern in what row was throwing an error. Every 1024th row it catches this same exception.

I thought that maybe it is the way I was deleting every row individually so I swapped it to delete in a large group by using:

worksheet.DeleteRow(rowFrom, rows, true);

But the same error would happen.

The Reverse() function above on rowsToDelete just makes sure we are deleting from the bottom up so it doesn't cause any issues where rows shift and you delete the wrong row.

Any help would be much appreciated!

Answer 1

Through the process of getting more information for the questions I found my answer.

I was using:

List<int> rowsToDelete = new List<int>();

to initialize the List however I started playing around with giving it a capacity on initialization like this:

List<int> rowsToDelete = new List<int>(maxRowNumber);

where I set 'maxRowNumber' equal to 1,000,000.

Making this change stopped the error from being thrown all together.