Why I can not delete row in Excel?

Question

I use EPPlus to delete only one row with index 10 using this code:

using (var p = new ExcelPackage(fi))
{
   var ws = p.Workbook.Worksheets[1];
   ws.DeleteRow(10, 1);
}

When I save output file I see that row is exist in file:

p.SaveAs(new FileInfo("c:\\output.xlsx"));

Why row is not deleted?

My full code is:

  using(var p = new ExcelPackage(fi)) {

   var ws = p.Workbook.Worksheets[1];
   var start = ws.Dimension.Start;
   var end = ws.Dimension.End;

   for (int row = start.Row + 1; row <= end.Row; row++) {
    for (int col = start.Column; col <= end.Column; col++) {

     var value = ws.Cells[row, 5].Value ? ? string.Empty;
     if (value == null || value == String.Empty || value == "") {

      ws.DeleteRow(row, 1);

     }
    }
   }

   p.SaveAs(new FileInfo("c:\\Projects\\excel\\output.xlsx"));
  }

I try delete all rows where values in column with index 5 is empty

Have you set up a breakpoint at `ws.DeleteRow(row, 1);`? Is it ever hit? — Camilo Terevinto, Apr 03 '19 at 23:28
Why do you even need `for (int col = start.Column; col <= end.Column; col++)`? Seems like your Column criteria is fixed at 5? But to test, set a breakpoint on the Delete line and put a watch with `ws.Dimension` in it. Step over the line and see if the Dimension goes down by one. If it does, the problem is elsewhere, if not something else is up. — Ernie S, Apr 04 '19 at 21:21

score 1 · Accepted Answer · answered Apr 05 '19 at 13:15

I think the problem is that you're looping from the top down. As you delete cells, excel will automatically shift the rest of the rows up, which will cause rows to be skipped when looping from the top down.

Try this for your loop:

 for (int row = end.Row; row >= start.Row; row--)
        {
            var value = ws.Cells[row, 5].Value ?? string.Empty;

            if (value.Equals(String.Empty))
            {
                ws.DeleteRow(row, 1);
            }

        }

Couple of side notes:

No need to loop through the columns if you are evaluating the same column every time. Just check the value of that cell for each row.
value will never be null because of the ?? operator. You can simplify the if statement to just check for String.Empty.
String.Empty and "" are the same value so you only need to evaluate one or the other.
When evaluating strings I would recommend using .Equals(). Using == can cause some bad results. C# difference between == and Equals()

Why I can not delete row in Excel?

1 Answers1