C# ExcelPackage (EPPlus) DeleteRow does not change sheet dimension?

Question

I am trying to build a data import tool that accepts an EXCEL file from the user and parses the data from the file to import data into my application.

I am running across a strange issue with DeleteRow that I cannot seem to find any information online, although it seems like someone would have come across this issue before. If this is a duplicate question, I apologize, however I could not find anything related to my issue after searching the web, except for this one which still isn't solving my problem.

So the issue:

I use the following code to attempt to "remove" any row that has blank data through ExcelPackage.

                for (int rowNum = 1; rowNum <= ws.Dimension.End.Row; rowNum++)
                {
                    var rowCells = from cell in ws.Cells
                                        where (cell.Start.Row == rowNum)
                                        select cell;
                    if (rowCells.Any(cell => cell.Value != null))
                    {
                        nonEmptyRowsInFile += 1;
                        continue;
                    }
                    else ws.DeleteRow(rowNum);
                    //Update: ws.DeleteRow(rowNum, 1, true) also does not affect dimension
                }

Stepping through that code, I can see that the DeleteRow is indeed getting called for the proper row numbers, but the issue is when I go to set the "total rows in file" count on the returned result object:

parseResult.RowsFoundInFile = (ws.Dimension.End.Row);

ws.Dimension.End.Row will still return the original row count even after the calls to DeleteRow.

My question is...do I have to "save" the worksheet or call something in order for the worksheet to realize that those rows have been removed? What is the point of calling "DeleteRow" if the row still "exists"? Any insight on this would be greatly appreciated...

Thanks

Elmaelmajian

Have you tried saving? Besides, Excel isn't affected by how many rows or columns are actually stored in a sheet. If you specify a larger range for calculations or for filling, it will work just fine. There are no removed rows, there are only empty rows – Gold

Saving the file in excel? Of course I saved the file in excel...but when I upload the file with the last 5 "blank rows", and execute the above code to delete those 5 rows using ExcelPackage (my intention is to remove those rows, similar to how one would "delete and shift rows up" in excel, the EPPlus method even gives that option as a parameter), the Dimenion of the file still shows those 5 rows as being in the document, even after a call to DeleteRow with those 5 row ids. Is there a way I am supposed to "save" the worksheet (ws) in EPPlus before calling the dimension? – Elmaelmajian

The point is - it shouldn't matter! Once you save the sheet in your code it should remove redundant rows. But it still wouldn't matter because it's perfectly valid to address cells, rows that are empty. What if your data started at row 10? Would you consider 1-9 as deleted? – Gold

Why this question? Is there a different problem you are trying to solve? Did you encounter some other issue and think that deleting rows is the answer? – Gold

I think you are misunderstanding...if rows 1-9 were blank, (as in there are no cells with non null data in that row), it will call DeleteRow on that row number. I have finally figured out the issue. It is a closure problem inside my for loop. I still have to figure out how I'm going to approach this, since it appears that as soon as you "delete" a row, you have to call: ws = excelPackage.Workbook.Worksheets.First(); again to grab a new reference to the worksheet. If you call dimension on that, it will return the correct row count (with the removed rows). – Elmaelmajian

Answer 1

I think I figured out the problem. This is yet again another closure issue in C#. The problem is that the reference to "ws" is still the same reference from before the DeleteRow call.

In order to get the "updated" dimension, you have to redeclare the worksheet, for example:

 ws = excelPackage.Workbook.Worksheets.First();

Once you get a new reference to the worksheet, it will have the updated dimensions, including any removed/added rows/columns.

Hopefully this helps someone.

Elmaelmajian

Just a note to mention thanks for posting the answer to your own (somewhat obscure) question. Doing this probably saved me many hours. I owe you a beer! – Mountford