PHPExcel - clone list and keep its original style

Question

I tried to check all possible similar solutions both here and in the official documentation / forums of PHPExcel, but I did not find a solution for my problem.

Problem

I am trying to clone (or copy, being honest) a sheet for its analysis into another file created using phpexcel , keeping the style of the cloned sheet .

Setup:

sheet.xls <--- File for OPEN and COPY

PHPExcel object <- A file that is created X times in a for loop, where I need to add Y Sheets according to a set of arrays.

What works

Cloning and adding work fine, it takes time due to some weird notifications associated with the phpexcel file:

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

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

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

Note: 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 } 

As for styles, as far as I can tell. <- There are thousands of them, I don’t know where they come from, the files are generated correctly, they just lose their format, as indicated above.

What does not work

The generated LOSES files are in the original format, but retain the formula, so each individual border (and any style) of the original "template" (sheet.xls) is lost.

Relevant piece of code

I only post really relevant code here, mainly because it contains about a thousand lines of code.

A file that will later be saved in the creation (happens in the parent foreach):

 $file = new PHPExcel(); 

Cloning (occurs inside the child foreach after the creation above):

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

Adding (happens N times inside the foreach child for 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 foreach, happens in the same foreach where the PHPExcel object is created:

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

Limitations

I have absolutely no restrictions as to which excel format I should use, I use 2003 because I have some machines that work only with excel 2003, but they will be updated soon to office 2010, so literally any reader and the writer is fine, I use 2003 because I have always used it and have not experienced any problems so far.

I am forced, however, to clone the XLS leaf inside another file, the only possible trick I can do is clone the leaf inside the same file and save it later, preserving the original one, but if there is another chance to “export” the style that I I would really appreciate it.

What I 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 multiple sheets by cloning a template sheet becomes slower with each clone

Workaround for copying style using PHPExcel

EDIT ::

I also tried:

• Open the file and get a sheet instead of cloning the original. The problem remains.
• I tried to use Excel2007 both for reading and writing - the problem persists.
• Tried NOT to use → copy () - The problem persists.
• UPDATED phpexcel to 1.8, now the Notification above appears on line 1079, but refers to the same code fragment - The problem persists.

Answer 1

Ok, I figured out a possible workaround.

Since the problem is related to:

• clone
• PHPExcel Prototype Protocol ->copy()
• PHPExcel Worksheet Link

I thought about this:

• Instead of creating a new instance of the PHPExcel object, simply open the source file.
• Attach the file to other instances of the same file by copying a sheet from the same file.
• Remove the LAST list when done.

So, in short, I changed this:

 $file = new PHPExcel(); 

For this:

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

And this:

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

For 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 have no errors, and everything works as expected, despite the fact that I am sure that there may be another smarter solution.

Answer 2

If you do not change the format of sheet.xls, try A) use .xlsx B) rename * .xlsx to * .zip C) unzip the sheet.zip file and saved files D) copy the .xls / styles.xml file from the 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 php in the loop, but rather by running php in the loop.