Conditional formatting problems

Topics: Developer Forum
Jan 31, 2010 at 12:28 PM

Hi

I'm using PHPExcel 1.7.2 and PHP 5.2.6 on Windows 7 RC 1. I'm having trouble with conditional formatting - specifically, setting a background colour. Here's some code:

require_once('PHPExcel.php');
require_once('PHPExcel/IOFactory.php');

$objPHPExcel = new PHPExcel();

$worksheet = $objPHPExcel->setActiveSheetIndex(0);

$worksheet->setCellValueByColumnAndRow(0, 1, 5);

$condition_style = array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'ff0000'));

$objConditional = new PHPExcel_Style_Conditional();
$objConditional->setConditionType(PHPExcel_Style_Conditional::CONDITION_EXPRESSION);
$objConditional->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_NONE);
$objConditional->setCondition('A1<10');
$objConditional->getStyle()->getFill()->applyFromArray($condition_style);

$conditionalStyles = $objPHPExcel->getActiveSheet()->getStyle('A1')->getConditionalStyles();
array_push($conditionalStyles, $objConditional);
$objPHPExcel->getActiveSheet()->getStyle('A1')->setConditionalStyles($conditionalStyles);

$objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->applyFromArray($condition_style);

// Redirect output to a client's web browser
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="test.xlsx"');
header('Cache-Control: max-age=0');

$xls_writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$xls_writer->save('php://output');

The result: cell A1 is black and it's formatting cannot be changed (perhaps it's formatting is corrupted?); B1, included as a reference, is fine. Any ideas?

A related question: I've been using a template, which has some conditional formatting rules in it. I read in the template, add numbers to it and write it out again (using the Excel 2007 reader/writer). But the output file doesn't contain these rules. Are conditional formatting rules not supported by either the reader or writer (or both)?

Developer
Feb 1, 2010 at 1:01 AM
MarQ wrote:

The result: cell A1 is black and it's formatting cannot be changed (perhaps it's formatting is corrupted?); B1, included as a reference, is fine. Any ideas?

For some reason, with conditional styles, instead of this:

 

$condition_style = array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'startcolor' => array('rgb' => 'ff0000'));

you need this:

$condition_style = array('type' => PHPExcel_Style_Fill::FILL_SOLID, 'endcolor' => array('rgb' => 'ff0000'));

I know that this is the opposite compared to normal styles. Will investigate later why this is the case.

 

MarQ wrote:

A related question: I've been using a template, which has some conditional formatting rules in it. I read in the template, add numbers to it and write it out again (using the Excel 2007 reader/writer). But the output file doesn't contain these rules. Are conditional formatting rules not supported by either the reader or writer (or both)?

This is a bug.

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=11668

Download source code tomorrow:

http://phpexcel.codeplex.com/SourceControl/list/changesets

 

 

Feb 1, 2010 at 9:58 PM

Thanks for your quick reply Koyama. The conditional style now works. However, reading and writing a template with conditional formatting rules is still an issue. I've tested change set 38617 (problem still exists) and 38705 (failed for me - Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 35 bytes) in Cell.php  on line 694).

I tested using the following template and code: http://rapidshare.com/files/344539525/template.xlsx

require_once('PHPExcel.php');
require_once('PHPExcel/IOFactory.php');

$objPHPExcel = new PHPExcel();

$xls_reader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $xls_reader->load('template.xlsx');

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="test.xlsx"');
header('Cache-Control: max-age=0');

$xls_writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$xls_writer->save('php://output');

May 21, 2010 at 2:28 AM
Hi, I am using the template also. The template is a formatted excel worksheet. When i write some data in the sheet and save that sheet, it changes the background color of all cells where formatting is applied Can any one tell suggest me the solution. I am using 1.7.2 Thanks