PHPExcel Background Color Logic

Question

I have a very confusing issue with PHPExcel. I have 800 students. I'm generated a spreadsheet which lists how much praise (on a daily basis for the current month) that the student has has.

For instance, it may look like this:

+---------------+-----+-----+-----+-----+
| Student Name  | 1st | 2nd | 3rd | 4th | ...
+---------------+-----+-----+-----+-----+
| Test Student  | 2   | 0   | 3   | 7   |
+---------------+-----+-----+-----+-----+

I want to change the background color of the cells which are greater (or equal to) 5. I use a loop to loop over the students, and days. This is my code:

for($d=1; $d<=$daysInCMonth; $d++)
{
    $phpExcel
        ->getSheetByName('Monthly Leaderboard')
        ->setCellValue($alphabetArray[($d+7)] . ($recordCount+5), $record['monthlyReport'][$MonthlyReportKeys[($d-1)]]);

    if($record['monthlyReport'][$MonthlyReportKeys[($d-1)]]>=5)
    {
        $cellId = $alphabetArray[($d+7)] . ($recordCount+5);
        $phpExcel
            ->getSheetByName('Monthly Leaderboard')
            ->getStyle($cellId)
            ->applyFromArray(
                array('fill' => array('type' => PHPExcel_Style_Fill::FILL_SOLID,'color' => array('rgb' => '000000'))
         ));
    }

}

To help understand the code, the initial for loop loops through from 1 up until the number of days in the current month (IE 30 for June). It then sets cells value as the number of points for each given day.

This all works perfectly. Now, the if condition will catch cells which have a value of greater (or equal to) 5.

The code $alphabetArray[($d+7)] . ($recordCount+5) grabs the current cell ID in the iteration. I know this works fine as well, because if I echo it to the screen, the first output is T5 which is a cell greater than 5.

If I implicitly specify T5 as the cell to color, it works fine. However, if I try to use the value of $cellId to dynamically color all cells for my condition, none of the cells are colored.

I know the cell ID is 100% correct, I know the coloring statement is correct (as it does color cells if I refer to them specifically). It just doesn't want to play dynamically.

Any ideas?

Thanks Phil

Answer 1

This is quite an old question now, but I found it after having the same problem. After digging into the code I found something that does work. So thought I would add it in here for any future finder.

For conditional coloring of the background the method of just setting the color of the fill doesn't seem to work. e.g.

'fill' => array(
    'type' => PHPExcel_Style_Fill::FILL_SOLID,
    'color' => array(
        'rgb' => 'FFC7CE'
    ),
)

The above works perfectly well when applied directly to a cell, but when used in a conditional styling. If just does nothing. However if you change it to

'fill' => array(
    'type' => PHPExcel_Style_Fill::FILL_SOLID,
    'startcolor' => array(
        'rgb' => 'FFC7CE'
    ),
    'endcolor' => array(
        'rgb' => 'FFC7CE'
    ),
)

The background colors as expected. It looks like the conditional coloring of a background needs the start and end colors specified.

Answer 2

$headerStyle = array(
                'fill' => array(
                        'type' => PHPExcel_Style_Fill::FILL_SOLID,
                        'color' => array('rgb'=>'00B4F2'),
                ),
                'font' => array(
                        'bold' => true,
                )
        );
        $borderStyle = array('borders' =>
                array('outline' =>
                        array('style' => PHPExcel_Style_Border::BORDER_THICK,
                                'color' => array('argb' => '000000'),   ),),);
        //HEADER COLOR

        $objPHPExcel->getActiveSheet()->getStyle('A1:'.'V1')->applyFromArray($headerStyle);

        //SET ALIGN OF TEXT
        $objPHPExcel->getActiveSheet()->getStyle('A1:V1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle('B2:V'.$row)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_TOP);

        //BORDER TO CELL
        $objPHPExcel->getActiveSheet()->getStyle('A1:'.'V1')->applyFromArray($borderStyle);
        $borderColumn =  (intval($column) -1 );
        $objPHPExcel->getActiveSheet()->getStyle('A1:'.'V'.$borderColumn)->applyFromArray($borderStyle);