Copy cell style - reading then writing

Topics: Developer Forum, User Forum
Jan 19, 2012 at 12:08 PM
Edited Jan 19, 2012 at 8:42 PM

Im having trouble coping the styles from reading one xls modifying then writing a new file. The duplicate styles doesnt seem to work across different files.  Any suggestions?

 

 

<?php
 include 'Phpexcel/PHPExcel.php';
 include 'Phpexcel/PHPExcel/Writer/Excel2007.php';

$objPHPExcel = new PHPExcel();
$new_sheet =  $objPHPExcel->getActiveSheet();
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader = $objReader->load("Projection_Template.xlsx");
$read_sheet = $objReader->getActiveSheet();

foreach ($read_sheet->getRowIterator() as $row) {
   $cellIterator = $row->getCellIterator();
   foreach ($cellIterator as $cell) {
      if (($cell->getValue() != "")) {
         $coord = $cell->getCoordinate();
         echo "Cell:$coord - " . $cell->getValue() ."<BR>";
         $new_sheet->SetCellValue($coord,$cell->getValue());
         $new_sheet->duplicateStyle($read_sheet->getStyle($coord), $coord ); //<--------
      }
   }
}

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save("1_modified.xlsx");
?>
Coordinator
Jan 19, 2012 at 9:34 PM

This is where the addExternalSheet() is useful: it allows you to copy a worksheet from one workbook to another, including merging the styles correctly into the new worksheet.

Jan 20, 2012 at 5:14 AM
Edited Jan 20, 2012 at 8:46 AM

Thanks heaps. That worked like a charm! Although I think I found a bug using addExternalSheet.  After the following code copied over the sheet into the new workbook (styles and all) it now appears that if you try and edit one of the cells the background of the cell changes to black instead of the normal thinning of the line. My code is as follows:

Edit: I fixed the black cell problem by commenting out a line in PHPExcel/Reader/Excel2007.php as found by quacki in this post: http://phpexcel.codeplex.com/discussions/213643

//Comment line 1584  $docStyle->getFill()->getStartColor()->setARGB('FF000000');

My new code:

<?php
 include 'Phpexcel/PHPExcel.php';
 include 'Phpexcel/PHPExcel/Writer/Excel2007.php';


$objPHPExcel = new PHPExcel();
$new_sheet =  $objPHPExcel->getActiveSheet();

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader = $objReader->load("1_test.xlsx");
$template = $objReader->getActiveSheet();

$objPHPExcel->addExternalSheet($template); //our template is now in the same workbook
$objPHPExcel->setActiveSheetIndex(1); //set it to the newly added sheet
$template = $objPHPExcel->getActiveSheet(); 

foreach ($template->getRowIterator() as $row) {
   $cellIterator = $row->getCellIterator();
   foreach ($cellIterator as $cell) {
      if (($cell->getValue() != "")) {
         $coord = $cell->getCoordinate();
         echo "Cell:$coord - " . $cell->getValue() ."<BR>";
         $new_sheet->SetCellValue($coord,$cell->getValue());
         $new_sheet->duplicateStyle($template->getStyle($coord), $coord );
      }
   }
}

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save("1_modified.xlsx");
?>