PHPExcel - Clone sheet and keep its original style

Question

I've tried to check every possible similar solution both here and in the PHPExcel official documentation / forums, but I didn't find any solution to my issue.

The problem

I'm trying to clone (or copy, being honest) a sheet to parse it into another file created through phpexcel by keeping the style of the cloned sheet.

The setup is:

sheet.xls <--- File to OPEN & COPY

PHPExcel object <-- File that gets created X times in a for loop, where I need to append Y Sheets according to a set of arrays.

What works

The cloning & appending works beautifully, takes time because of some strange notices related to a phpexcel file:

Notice: Undefined offset: 1 in \serverpath\PHPExcel\Classes\PHPExcel.php on line 729

Notice: Undefined offset: 2 in \serverpath\PHPExcel\Classes\PHPExcel.php on line 729

Notice: Undefined offset: 3 in \serverpath\PHPExcel\Classes\PHPExcel.php on line 729

Notice: Undefined offset: 4 in \serverpath\PHPExcel\Classes\PHPExcel.php on line 729

EDIT ::

Line 729 refers to this:

foreach ($sheet->getCellCollection(false) as $cellID) {
    $cell = $sheet->getCell($cellID);
    ++$countReferencesCellXf[$cell->getXfIndex()]; // line 729
}

Which is about styles as far as I can tell. <-- There are thousand of these, no idea where they are coming from though, the files are getting generated correctly, they just lose their format as said above.

What doesn't work

The generated files LOSES the original format but keeps the formula, hence every single border (and any style) of the original "template" (sheet.xls) is lost.

The relevant part of the code

I'm only posting the really relevant code here, mostly because it's about a thousand lines of code.

File that will later be saved creation (happens in parent foreach) :

$file       =   new PHPExcel();

Cloning (happens inside a child foreach after the creation above) :

$sd = $objReader->load("sheet.xls");
$sc =   $sd ->getActiveSheet()->copy();
$clonedSheet = clone $sc;

Appending (happens N times inside a child foreach of the cloning above) :

$ficheName = "not relevant tbh and less than 31 characters";
$temporarySheet = clone $clonedSheet;
$temporarySheet->setTitle($ficheName);
$file->addSheet($temporarySheet,0);
$file->setActiveSheetIndex($file->getIndex($temporarySheet));
unset($temporarySheet);

// some actions are done here

Saving (outside of the foreach, happens in the same foreach where the PHPExcel object gets created:

$objWriter = PHPExcel_IOFactory::createWriter($file, 'Excel5');
$objWriter->save($filename);

Restrictions

I have absolutely no restrictions about what kind of excel format I'm supposed to use, I'm using 2003 because I have some machines that only works with excel 2003, but they will soon be upgrading to office 2010, so literally any reader and writer is okay, I'm using 2003 because I've always used it and had no problem so far.

I am forced, though, to clone the XLS sheet inside another file, the only possible trick I can do is clone the sheet inside the same file and save it later by keeping the original one, but if there is any other chance to "export" the style I would really appreciate it.

What I have already checked:

PHPExcel clone .xlsm with macros

http://www.mindfiresolutions.com/Cloning-a-XLS-worksheet-in-PHP--Mindfire-Solutions-933.php

PHPExcel 1.8.0 - Creating many sheets by cloning a template sheet gets slower with each clone

Workaround for copying style with PHPExcel

EDIT ::

I've also tried to:

• Open the file and get the sheet instead of cloning the original one - Problem persists.
• Tried to use Excel2007 both for reading and writing - Problem persist.
• Tried NOT to use ->copy() - Problem persists.
• UPDATED phpexcel to 1.8, now the Notice above appears on line 1079, but refers to the same exact piece of code - Problem persists.

Answer 1

Okay, I've figured out a possible workaround.

Because the problem seems to be with:

• clone
• PHPExcel Worksheet ->copy() prototype
• Referencing PHPExcel Worksheet

I've thought about that:

• Instead of creating a new PHPExcel object instance, just OPEN the original file.
• Append the file with other instances of the same file, by copying the sheet still from the same file.
• Remove the LAST sheet when finished.

So, in a nutshell, I've changed this:

$file       =   new PHPExcel();

To this:

$file       =   $objReader->load("sheet.xlsx"); // decided to work with excel2007

And this:

$objWriter = PHPExcel_IOFactory::createWriter($file, 'Excel5');
$objWriter->save($filename);

To this:

$sheetCount = $file->getSheetCount();
$file->removeSheetByIndex($sheetCount - 1);

$objWriter = PHPExcel_IOFactory::createWriter($file, 'Excel2007'); // same story, excel 2007 instead of 2003
$objWriter->save($filename);

Now I don't have any error and everything is working as expected, despite I'm sure that there may be another cleverer solution.

Answer 2

If you don't change the format of sheet.xls then try to A) use .xlsx B) rename *.xlsx to *.zip C) unzip sheet.zip, and the files you haved saved D) copy the .xls/styles.xml from sheet to the saved files E) repack and rename *.zip to *.xlsx and your format is back. You can minimize the problem a bit by not generating in a loop in php but rather run the php in a loop.