addExternalSheet is removing original sheet

Topics: Developer Forum
Feb 28, 2013 at 3:27 PM
Hello,
I started using your library (great work!), and I think I have found a bug when copying worksheets from one workbook to another. Below I present the code of the script.
$template = new excel_reader("a.xlsx");
$logic_spec = new excel_writer();

for($sheet = 1; $sheet <= $template->get_sheet_count(); $sheet++)
{
  $template->debug();

  $sheet_to_clone_1 = $template->clone_sheet("OnOff");
  $logic_spec->add_external_sheet($sheet_to_clone_1, 'siallal'.$sheet);

  $template->debug();
}

$logic_spec->save('logic_spec');
The output I get is:
Array
(
    [0] => AI
    [1] => AO
    [2] => DI
    [3] => DO
    [4] => OnOff
)
Array
(
    [0] => AO
    [1] => DI
    [2] => DO
    [3] => OnOff
)
Array
(
    [0] => AO
    [1] => DI
    [2] => DO
    [3] => OnOff
)
Array
(
    [0] => DI
    [1] => DO
    [2] => OnOff
)
Array
(
    [0] => DI
    [1] => DO
    [2] => OnOff
)
Array
(
    [0] => DO
    [1] => OnOff
)
Array
(
    [0] => DO
    [1] => OnOff
)
Array
(
    [0] => OnOff
)
Array
(
    [0] => OnOff
)
Array
(
)
When I run a script with modified line responsible for adding cloned sheet:
$template = new excel_reader("a.xlsx");
$logic_spec = new excel_writer();

for($sheet = 1; $sheet <= $template->get_sheet_count(); $sheet++)
{
  $template->debug();

  $sheet_to_clone_1 = $template->clone_sheet("OnOff");
  //  $logic_spec->add_external_sheet($sheet_to_clone_1, 'siallal'.$sheet); // <----- modified

  $template->debug();
}

$logic_spec->save('logic_spec');
I got correct, not modified output:
Array
(
    [0] => AI
    [1] => AO
    [2] => DI
    [3] => DO
    [4] => OnOff
)
Array
(
    [0] => AI
    [1] => AO
    [2] => DI
    [3] => DO
    [4] => OnOff
)
Array
(
    [0] => AI
    [1] => AO
    [2] => DI
    [3] => DO
    [4] => OnOff
)
Array
(
    [0] => AI
    [1] => AO
    [2] => DI
    [3] => DO
    [4] => OnOff
)
Array
(
    [0] => AI
    [1] => AO
    [2] => DI
    [3] => DO
    [4] => OnOff
)
Array
(
    [0] => AI
    [1] => AO
    [2] => DI
    [3] => DO
    [4] => OnOff
)
Array
(
    [0] => AI
    [1] => AO
    [2] => DI
    [3] => DO
    [4] => OnOff
)
Array
(
    [0] => AI
    [1] => AO
    [2] => DI
    [3] => DO
    [4] => OnOff
)
Array
(
    [0] => AI
    [1] => AO
    [2] => DI
    [3] => DO
    [4] => OnOff
)
Array
(
    [0] => AI
    [1] => AO
    [2] => DI
    [3] => DO
    [4] => OnOff
)
sources of the methods "clone_sheet" and "add_external_sheet" are below:
class excel_reader
{
  protected $excel_object;
  protected $excel_sheet_count;
  protected $excel_sheet_active;
  protected $excel_sheet_names;

  public function __construct($file_path)
  {
    if(file_exists($file_path))
    {
      try
      {
        $this->excel_object = PHPExcel_IOFactory::load($file_path);
        //      $objReader = PHPExcel_IOFactory::createReader('Excel2007');
        //      $objReader->setReadDataOnly(true);
        //      $this->excel_object = $objReader->load($file_path);;
      }

      catch(Exception $exception)
      {
        exit('error loading file: '.$exception->getMessage()."\n");
      }

      $this->excel_sheet_active = 0;
      $this->excel_object->getSheet($this->excel_sheet_active);
      $this->excel_object->setActiveSheetIndex($this->excel_sheet_active);
      $this->excel_sheet_count = $this->excel_object->getSheetCount();
      $this->excel_sheet_names = $this->excel_object->getSheetNames();
    }
    else
    {
      exit('file '.$file_path.' does not exist!'."\n");
    }
  }

  public function __destruct()
  {
    $this->excel_object->disconnectWorksheets();
    unset($this->excel_object);
  }

  public function next_sheet()
  {
    $next_sheet = $this->excel_sheet_active + 1;

    if($next_sheet < $this->excel_sheet_count)
    {
      $this->excel_sheet_active = $next_sheet;
      $this->excel_object->getSheet($this->excel_sheet_active);
    }
    else
    {
      $this->excel_sheet_active = 0;
      $this->excel_object->getSheet(0);
    }

    $this->excel_object->setActiveSheetIndex($this->excel_sheet_active);
  }

  public function clone_sheet($sheet_name) // <-------- here
  {
    return clone $this->excel_object->getSheetByName($sheet_name);
  }

  public function get_sheet_count()
  {
    return $this->excel_sheet_count;
  }

  public function get_active_sheet_name()
  {
    return $this->excel_sheet_names[$this->excel_sheet_active];
  }

  public function debug()
  {
    $sheet_names = $this->excel_object->getSheetNames();
    print_r($sheet_names);
  }
}

//----------------------------------------------------------------------
// class responsible for creating and writing excel file

class excel_writer
{
  protected $excel_object;

  public function __construct()
  {
    $this->excel_object = new PHPExcel();
  }

  public function __destruct()
  {
    $this->excel_object->disconnectWorksheets();
    unset($this->excel_object);
  }

  public function add_external_sheet($cloned_sheet, $new_sheet_name) // <-------- here
  {
    $cloned_sheet->setTitle($new_sheet_name);
    $this->excel_object->addExternalSheet($cloned_sheet);
  }

  public function save($file_name)
  {
    $excel_object_writer = PHPExcel_IOFactory::createWriter($this->excel_object, 'Excel2007');
    $excel_object_writer->save($file_name.'.xlsx');
  }
}
As you see, it the first example every iteration I am loosing a original worksheet from the workbook. Am I doing something wrong? I am using:
PHP 5.3.3 (cli) (built: Jun 28 2012 11:20:32)
Copyright (c) 1997-2010 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2010 Zend Technologies

and PHPExcel 1.7.8
P.S I also created a new issue in issue tracker, if you think it is not useful please remove it.