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

Answer 1

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