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.

so you have empty row, than following rows have some data, then again empty and again some data and so on and so on? but there are some rows in the beginning that you want to keep? (or perhaps you confused column with row in your question somewhere?). if you could draw ascii or show picture that could help. — Vanity Slug - codidact.com, Jun 23 '17 at 16:03
@alex i updated question, hope it will be more understandable. I know that this empty rows can be deleted in the database, but i have problem importing this excel because of its size due to empty rows, therefore i need to delete it first. I am also thinking about some rows limit for the users, for example million of rows and automatically delete everything else. — Muflix, Jun 23 '17 at 19:43
fyi friend. reproduced your problem, but could not find solution yet. — Vanity Slug - codidact.com, Jun 26 '17 at 13:11

score 20 · Accepted Answer · answered Mar 12 '18 at 10:01

20

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

answered Mar 12 '18 at 10:01

Tomasz Kowalczyk

1,873
2
23
33

I like your solution, but it would be nice to have the option to consider empty-rows differently. From my point of view, a row is empty only if all of its cell values are empty. – MMalke Sep 12 '18 at 15:48
Brilliant! Saved my day! Thanks a ton :) – Vinit Divekar Sep 28 '18 at 04:11
To check for empty row in one line `return Enumerable.Range(1, worksheet.Dimension.End.Column).Select(i => worksheet.Cells[worksheet.Dimension.End.Row, i].Value == null).All(x => x);`. – Bamdad Jul 04 '23 at 07:07

user1488242 · Answer 2 · 2019-09-12T07:48:06.893

Above solution is to delete last empty rows in the file. This will not work if file has empty rows in the middle of the rows list somewhere.

Below is the solution to identify the empty rows in the middle of the rows list.

I used combination of both above and mine to delete empty rows at the end of the rows list and empty rows in the middle of the rows list

 private void TrimEmptyRows(ExcelWorksheet worksheet)
    {
        //loop all rows in a file
        for (int i = worksheet.Dimension.Start.Row; i <= 
       worksheet.Dimension.End.Row; i++)
        {
            bool isRowEmpty = true;
            //loop all columns in a row
            for (int j = worksheet.Dimension.Start.Column; j <= worksheet.Dimension.End.Column; j++)
            {
                if (worksheet.Cells[i, j].Value != null)
                {
                    isRowEmpty = false;
                    break;
                }
            }
            if (isRowEmpty)
            {
                worksheet.DeleteRow(i);
            }
        }
    }

If there is an empty row directly beneath the deleted one it will get the same row number and will therefore be skipped when the row number is increased. This doesn't occur if the rows are scanned bottom up, `for (int i = worksheet.Dimension.End.Row; i >= worksheet.Dimension.Start.Row; i--)`. — ivvi, May 02 '20 at 00:18

Epplus delete all rows from specific row

2 Answers2

Linked