Epplus delete all rows from specific row

Question

It is possible to somehow delete all following rows from specific (empty) row ? I tried for cyclus

            for (int rowNum = 1; rowNum <= worksheet.Dimension.End.Row; rowNum++)
            {
                var rowCells = from cell in worksheet.Cells
                               where (cell.Start.Row == rowNum)
                               select cell;

                if (!rowCells.Any(cell => cell.Value != null))
                {
                    worksheet.DeleteRow(rowNum);
                }

            }

but it takes minutes if in excel are millions of empty rows.

Epplus offer this method worksheet.DeleteRow(int rowFrom, int rows) but i do not know the count of all additional empty rows.

In following example i need to delete all rows 12+ but the problem is that i do not know the specific row, where the empty rows begin.

The alternative aproach can be finding last non empty row and delete everything with the range, which will be faster, but there is another issue with empty row inside the table.

ws.DeleteRow(lastFilledTableRow, workSheet.Dimension.End.Row - tableRowsCount,true);

In this example the problem is the red row but maybe i will tell the users that this kind of excel format is invalid and circumvent the problem.

Answer 1

I know that it is old but I could not find any solution so made one my by own. It is checking the last row if it is empty and if yes it deletes it and doing this until finds non-empty row. (non-empty means here: all columns in this row have some value)

worksheet.TrimLastEmptyRows();

public static void TrimLastEmptyRows(this ExcelWorksheet worksheet)
    {
        while (worksheet.IsLastRowEmpty())
            worksheet.DeleteRow(worksheet.Dimension.End.Row);
    }

public static bool IsLastRowEmpty(this ExcelWorksheet worksheet)
    {
        var empties = new List<bool>();

        for (int i = 1; i <= worksheet.Dimension.End.Column; i++)
        {
            var rowEmpty = worksheet.Cells[worksheet.Dimension.End.Row, i].Value == null ? true : false;
            empties.Add(rowEmpty);
        }

        return empties.All(e => e);
    }