PHPExcel - Formatting gets lost when editing from code

Question

Hi all,

I'm playing around with an PHP - MS Excel integration these days. My task is to open an existing spreadsheet, add some data and save the filled spreadsheet as a new file. Basically sort of a template filling engine, eventhough xlsx files are used as templates.

I looked into PHPExcel which seems to be fairly nice framework. In order to implement a proof of concept I did the following (reduced to the minimum required to illustrate what I need to do):

$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objReader->setReadDataOnly(true); 
$objPHPExcel = $objReader->load("myTemplateToFill.xlsx");

    //Here comes the actual filling
$objWorksheet = $objPHPExcel->createSheet();
$objWorksheet->setTitle('Apple') ;
$objWorksheet->setCellValue('A1', 'Banana'); 
$objPHPExcel->setActiveSheetIndex(0);

$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save('myFilledTemplate.xlsx');

Running this I found that a new file is created, my daat is inserted but sadly with all of the existing formatting is lost.

So the question is, is there a way to make PHPExcel keep those formattigs in the new file? Or to go even further: is it possible to keep charts etc. in a template file and fill those the way I tried?

Thanks in advance for all of your experience shared!

K

Answer 1

Hum, you are creating an new workSheet, wich has no format as default. I think you have to add your values to the existing sheet ?

$objPHPExcel->setActiveSheetIndex(0); // index of sheet
$workSheet = $objPHPExcel->getActiveSheet();
$workSheet->setTitle('Pflaume');

Answer 2

Formatting is lost because of this line $objReader->setReadDataOnly(true); which tells the reader to read only the data from your template workbook, not the formatting.

You also need to modify the data in the existing worksheets. Creating a new worksheet will create an unformatted worksheet. Alternatively, you can use the PHPExcel_WorkSheet copy() method