Using removeRow Leaves Blank Rows, How do I Delete Those?

Oct 10, 2011 at 7:56 PM

I am using the removeRow function to go through and trim up some unneeded rows in an Excel 2005 Spreadsheet (.xls). The only issue is that when a row is removed, it's contents are emptied, but the row is still counted as an existing row (easily demonstrated with file size). All of the cells remain initialized to blank values. This is also reflected with the getHighestRow function which still returns the original highest row of the spreadsheet, not the altered one, indicating all rows that were "removed" are still counted and stored.

This is the code that deletes it:

$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(TRUE);
$objReader->setLoadAllSheets();
$objPHPExcel = $objReader->load($dest);

$ranges_formatted = format_ranges($rows_to_delete);
foreach ($ranges_formatted as $start_spot => $range)
{
	$objPHPExcel->getActiveSheet()->removeRow($start_spot, $range['range']);
}
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter->save($dest);

 

$dest is a filepath and format_ranges is a function that returns an array of ranges and their start spots to delete, defined as so:

function format_ranges($ranges)
{
	$prior = NULL;
	$all_ranges = array();
	$start_spot = NULL;
	$this_range = 1;
	foreach ($ranges as $range)
	{
		if ($prior === NULL)
		{
			$prior = $range;
			$start_spot = $range;
		}
		else {
			if (($prior + 1) == $range)
			{
				$this_range += 1;
				$prior = $range;
				if (!isset($start_spot))
				{
					$start_spot = $prior;
				}
			}
			else {
				$all_ranges[$start_spot]['range'] = $this_range;
				$this_range = 1;
				$prior = $range;
				unset($start_spot);
			}
		}
	}
	
	if (isset($start_spot))
	{
		$all_ranges[$start_spot]['range'] = $this_range;
	}
	
	return $all_ranges;
}


As is, format_ranges seems to be working just fine and the correct rows are removed, they just remain blank, meaning the file is still rather large. As you can also see, I am also just reading the data. I did try this with reading everything, not just the data, and the end result was an even bigger file than just reading data.
So, my question is: how do I delete these blank rows? Can I modify my code so removeRow does this?
Thanks in advance, please tell me if I can provide more information.