Matching the formatting from the cell of one .xls file in the current xls file.

Topics: Developer Forum
Jul 17, 2012 at 9:38 PM
Edited Jul 17, 2012 at 9:42 PM

I have a template that is currently used in a vb.net program that populates this template with data keeping the formatting of the template and outputs a new file with the data formatted all pretty.

I want to use this templates with PHPExcel.  I can load the template, and add it and the new data to a new spreadsheet using this code :

 

$sheetData = $TEMPLATE_objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
foreach($sheetData as $row=>$value) {
	foreach ($value as $key=>$cellvalue) {
		$out_objPHPExcel->getActiveSheet()->SetCellValue($key.$row, $cellvalue);
	}
}

foreach($output_data as $row=>$value) {
	foreach ($value as $key=>$cellvalue) {
		$cur_row=$row+$step;		
		$out_objPHPExcel->getActiveSheet()->SetCellValue($key.$cur_row, $cellvalue);
	}
}

 

This chuck of code works.  It loads the template, adds it to the output file.  Inserts the output data, but when I load up the final file its like a plain spreadsheet that someone entered the data in and didn't do any formatting.

Because the second half is on the fly, and the first half is from the template, my question only applies to the first foreach.

How do I set cell $key.$row 's formatting to the matching row from the template?
[edit] to refine my question, is there an easy one line function I could use.  I have read in the documentation how to set the formatting of a cell, but it seems like I would have to do a lot of work to match the template. [/edit]

By formatting I mean font size/color/alignment/ect..

Any help would be much appreciated!  I just discovered PHPexcel the other day and am ecstatic!

Thanks

Nick

Coordinator
Jul 17, 2012 at 10:29 PM
Edited Jul 17, 2012 at 10:31 PM

You can use the worksheet's getStyle() method to read the style for any given cell, and duplicateStyle() to copy it to a range of cells:

$style = $out_objPHPExcel->getActiveSheet()->getStyle('A1');
$out_objPHPExcel->getActiveSheet()->duplicateStyle($style,'A2:A99');

It's always better to apply styles to a range where you can rather than to each individual cell: the latter is slow and memory-intensive.

Jul 18, 2012 at 12:26 AM

Ah, I know I'm getting closer!  Many thanks.

 

Using your code, I came up with this :

$TEMPLATE_objPHPExcel = PHPExcel_IOFactory::load($TEMPLATE_FileName);

$out_objPHPExcel = new PHPExcel();
$out_objPHPExcel->setActiveSheetIndex(0);

$TEMPLATE_objPHPExcel->setActiveSheetIndex(0);

$sheetData = $TEMPLATE_objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
foreach($sheetData as $row=>$value) {
	foreach ($value as $key=>$cellvalue) {
		$style = $TEMPLATE_objPHPExcel->getActiveSheet()->getStyle($key.$row);
		$out_objPHPExcel->getActiveSheet()->duplicateStyle($style,$key.$row);

		$out_objPHPExcel->getActiveSheet()->SetCellValue($key.$row, $cellvalue);	

	}
}

but it doesn't make a difference?  

I have a feeling there is one more step that I am missing!

Thanks again for the help.  When I get home from band practice I'll be able to take another look and really hit google again.

Nick

Coordinator
Jul 18, 2012 at 7:44 AM

duplicate style doesn't copy from one workbook to another.

Either clone the active sheet from your template and insert it in your $out_objPHPExcel using addExternalSheet() and work with that; or add your data to the template and then change its name when you save.