Delete rows with PHPExcel

Question

I want to delete all rows that start with "//", from Excel sheet, with PHPExcel. My code:

require '../Classes/PHPExcel.php';
require_once '../Classes/PHPExcel/IOFactory.php';

error_reporting(E_ALL & ~E_STRICT & ~E_NOTICE);


$path = "del_head.xls";

$objPHPExcel = PHPExcel_IOFactory::load($path);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);


for($row=1; $row < $highestRow; ++$row){
   $value = $objPHPExcel->getActiveSheet()->getCell('A'.$row)->getValue();

   if (substr($value,0,2) == "//") {
      $objPHPExcel->getActiveSheet()->removeRow($row, $row);
      }
}

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
$objWriter->save($path);

But, the code not delete all rows that start with "//". I think the problem is with function 'removeRow'.

Thank you very much in advance.

Answer 1

Replace this line

$objPHPExcel->getActiveSheet()->removeRow($row, $row);

with

$objPHPExcel->getActiveSheet()->removeRow($row);

Answer 2

There's one obvious problem with your call to removeRow(): The first argument is the starting row, the second is the number of rows to remove; so if you find a // at row 5, then you're telling PHPExcel to remove 5 rows starting from row 5 (i.e. rows 5 to 9).

You're also reading a file with a .xls extension (assuming that it's a BIFF file from the extension), but saving with the same .xls filename as an OfficeOpenXML file (which is normally a .xlsx file).

So what format actually is your file? What does a call to the IOFactory's identify() method recognise it as? If it's actually a comma- or tab-separated value file (most likely to have a leading //), then you'd be better using PHP's fgetcsv() and fputcsv() functions to process it to remove those lines.

Answer 3

Try this code, Its worked me

$filename     = 'path/example.xls';
$objReader    = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel  = $objReader->load($filename);
$objWorksheet = $objPHPExcel->getActiveSheet();

$row_id  = 1; // deleted row id
$number_rows = 2; // number of rows count 
if ($objWorksheet != NULL) {
    if ($objWorksheet->removeRow($row_id, $number_rows)) {
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save($filename);
    }
}