Delete single row

Editor
Feb 2, 2010 at 5:13 PM
Edited Feb 2, 2010 at 5:13 PM

I know apparently epplus has a problem with delete row when using shared formula, but it isn´t my case, so i did a small piece of code to delete a single row, i´ll post here, maybe can help someone. =)

        public void DeleteRow(int row, bool shiftOtherRowsUp)
        {
            //throw (new Exception("Insert and delete of rows has been removed for now."));

            //We have a problem here with shared formulas update to be fixed
            var cpy = new SortedDictionary<ulong, ExcelCell>();            
            
            foreach (ExcelCell cell in _cells.Values)
            {
                if (cell.Row != row)
                {
                    if (cell.Row > row && shiftOtherRowsUp)
                    {
                        cell.Row -= 1;
                        if (shiftOtherRowsUp && cell.Formula != "")
                        {
                            cell.Formula = ExcelCell.UpdateFormulaReferences(cell.Formula, -1, 0, row, 0);
                        }
                    }

                    cpy.Add(cell.CellID, cell);
                }  
            }
            _cells = cpy;
        }

 

Editor
Feb 2, 2010 at 5:21 PM
Edited Feb 3, 2010 at 3:34 PM

Remember if you´ll be calling this method in a loop, everytime that you delete a row, all your worksheet will be "update", so you´ll lost your index reference. If you want to test a bunch of rows with a clasule, you need something like this:

        private void removeLines(ExcelWorksheet ws, int column, int rowStart, int rowEnd, List<string> allowedValues)
        {            
            int i = rowStart;
            int t = rowEnd - rowStart;
            do
            {
                if (!allowedValues.Exists(a => a.Equals(ws.Cells[i, column].Value)))
                {
                    ws.DeleteRow(i, true);                                        
                }
                else
                {
                    i++;
                }

                t--;

            }while( t >= 0 );            
        }

It isnt a beautifull code, but works....I need to study more about epplus architeture =)

maybe Jan can do something better.

 

sorry for my english

 

Coordinator
Feb 3, 2010 at 7:06 PM

Nice with some code here :)

The main reason i removed the insert and delete methods was that I wasn't to happy with the design. Cells are stored in a SortedDictionary and accessed by the CellID (a composite of SheetID, column and row).  When inserting or deleting, the CellID changes for all rows/columns after. Since the SortedDictionary-class don't allow the key(the cellID) to be changed the only way to do this is to create a new dictionary and reload it or delete and reinsert the cells, and that is not so good for the performance.

I'll guess the best way to fix this is to create your own dictionary-class where you can change the key. I'll try to have a look at it when I have time and see what I can do. The shared formula method also need to be fixed and properbly some other things as well, so if you come up with something, please send me a message or post it here.

 

Editor
Feb 3, 2010 at 7:32 PM
Edited Feb 3, 2010 at 11:10 PM

 

i need to go home now, i´ll think more about our problem =) 

 

Editor
Feb 4, 2010 at 12:46 PM

Hi Jan,

Why are u using SortedDictionary instead Dictionary ? I´ve made some benchmarkings using dictionary and results was always better in my scenarios. For example, I´ve inserted 10000 rows with numbers ( 1-10000) and in the next step I delete all even numbers, when I was using SortedDictionarty I obtain 30s and with Dictionary 16s elapdsed.

btw, I only change cell´s dictionary, row and columns still using sorteddictionary.

Coordinator
Feb 4, 2010 at 1:49 PM

The cells/rows/columns must be in order when saving the sheet. If you use a Dictionary the ID's are added in the order you access the cells. So it will work if you add all cells from A1-, but if you change the order the sheet will properbly be unreadable.

I am currently working on my own implementation of a SortedDictionary where you can change the keys. I hope I can check it in tonight or tomorow, then you can have a look at it.

Coordinator
Feb 5, 2010 at 7:47 AM

Ok, so I have checked in the new code. Have a look at it if you have time, and try your code. I hope things go faster now (at least inserts and deletes), so i didn't mess things up : )

Still some work to do on this code though (fix formulas, merged cells and hyperlinks).

Editor
Feb 5, 2010 at 12:01 PM

I´ve updated in my project and seems everything is fine, I will do more tests. Nice work Jan =)