delete rows fast and like in excel

Topics: Developer Forum
Feb 14, 2013 at 7:47 AM
I try to delete 1000 rows with something like
$curWorksheet->removeRow(12,1000)
but this takes 1 minute
and the formatting below is also removed (e.g. a formatting of a whole column)
If I delete a complete sheet and it’s done as it were directly.

is there a function in phpExcel which deletes a bunch of rows like you can do in excel
by selecting rows and choose 'delete rows'?
All rows below the deleted rows will move up and it’s done in less than one second.
This delete function don’t need any checks of formulas or other things cause all cells depending on each other will also be deleted.
Coordinator
Feb 14, 2013 at 8:12 AM
is there a function in phpExcel which deletes a bunch of rows like you can do in excel
by selecting rows and choose 'delete rows'?
All rows below the deleted rows will move up and it’s done in less than one second.
I really don't understand what you're asking for. the removeRows() method allows you to select a starting row, and a number of rows to delete. All rows below the deleted rows move up.... that's pretty much what happens if you do it in Excel, though Excel does a few other things with formula references that you don't mention.... and PHPExcel also does those. And it certainly doesn't remove column formatting.

Are you just asking us to make it faster so that it will run in less than a second?
There's some changes I'm testing at the moment that will make inserting/deleting faster, but it still won't be instantaneous without any processing required at all.
This delete function don’t need any checks of formulas or other things cause all cells depending on each other will also be deleted.
I just don't follow this logic.... so you want all cells that contain formula referencing the deleted rows to be automagically deleted as well? Excel's delete does formula checks and makes adjustments across the spreadsheet whenever you delete a row or column, PHPExcel does the same... you're saying you want what Excel does, but don't want it to work the same way?
Feb 14, 2013 at 12:48 PM
Edited Feb 14, 2013 at 12:49 PM
I really don't understand what you're asking for. the removeRows() method allows you to select a starting row, and a number of rows to delete. All rows below the deleted rows move up.... that's pretty much what happens if you do it in Excel, though Excel does a few other things with formula references that you don't mention.... and PHPExcel also does those. And it certainly doesn't remove column formatting.
ok I deleted the last rows in sheet so only the formatting was removed and I thought the rows wouldn't move up.

Here now a more exactly description:

if you run this
$curWorksheet->getStyle("B:D")->getFill()
->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
->getStartColor()->setARGB("FFFFFFA3");

for($row=1;$row<6;$row++)
{
    for($col=65;$col<85;$col++)
    {
        $curWorksheet->getCell(chr($col).$row)->setValue($row);
    }
}
you get somethin like this as result:
Image

now you run this:
$curWorksheet->removeRow(4,2);
and the excelsheet looks like this:
Image

if you delete rows in the middle
$curWorksheet->removeRow(2,2);
you will get this:
Image

so theres in the rows 4 and 5 no formatting anymore.
Are you just asking us to make it faster so that it will run in less than a second?
There's some changes I'm testing at the moment that will make inserting/deleting faster, but it still won't be instantaneous without any processing required at all.
That's what i meant. I supposed that there are some checks, which take some time, before the code will delete the row. I don't know how the cells where stored internally. Also thought if they are stored in an array, why dont use something like array_splice. But could be that I'm thinking to easy at this point. Therefore I don't know the code good enough.
This delete function don’t need any checks of formulas or other things cause all cells depending on each other will also be deleted.
I just don't follow this logic.... so you want all cells that contain formula referencing the deleted rows to be automagically deleted as well? Excel's delete does formula checks and makes adjustments across the spreadsheet whenever you delete a row or column, PHPExcel does the same... you're saying you want what Excel does, but don't want it to work the same way?
I didn't mean it like that. I tried just to explain, with my bad English knowledge, that I need a function which hasn't any need for checks and is just fast.