PHPExcel - duplicateStyle() seems not to work / does nothing

Question

Im making an export for my company which takes soma data, given through PHP, and outputs them to a excel spreadsheet.

Everything works well, except for one little thing. Im conditionally formatting some cells to have a specific color. Neither I get an error, nor i get the right background color for the other cells, they just stay white.

I'm using PHPExcel to create the output, the following is my Code:

//just for information:
// $spreadsheet = $objPHPExcel->getActiveSheet();

//normal
$conditionalStyleNormal = new PHPExcel_Style_Conditional();
$conditionalStyleNormal->setConditionType(PHPExcel_Style_Conditional::CONDITION_CONTAINSTEXT)
    ->setOperatorType(PHPExcel_Style_Conditional::OPERATOR_CONTAINSTEXT)
    ->setText('Normal (Mittagspause)')
    ->getStyle()->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getEndColor()
    ->setARGB(PHPExcel_Style_Color::COLOR_LIGHTYELLOW);

//apply style
$conditionalStyles = $spreadsheet->getStyle('A5:A50')->getConditionalStyles();
array_push($conditionalStyles, $conditionalStyleNormal);
$spreadsheet->getStyle('A5:I50')->setConditionalStyles($conditionalStyles);

//copy style to other cells (does not work)
$spreadsheet->duplicateStyle($spreadsheet->getStyle('A5'), 'C5:I5');

The last line has no effect on the file.

The documentation of PHPExcel says:

If you want to copy the ruleset to other cells, you can duplicate the style object: $objPHPExcel->getActiveSheet()->duplicateStyle($objPHPExcel->getActiveSheet()->getStyle('B2'), 'B3:B7');

Am i overlooking something? Or is it just a bug? If so, is there a good workaround?

Couldn't find a solution, just found out that the function had a misbehaviour in a earlier version. I'm working with PHPExcel 1.8.0.

Thanks in advance!

score 0 · Accepted Answer · answered Jul 02 '15 at 12:02

I've now made a own little workaround. Instead of adding conditions to the cells, I now have the whole thing in my PHP script. I check the cell, which had the condition on it, if it has a specific value and if so, I color the other cells next to them.

for ($row=1; $row <= 50; $row++) {
    $cellvalue = $spreadsheet->getCell('A'.$row)->getValue();
    switch ($cellvalue) {
        // a case for each value where the row should be colored,
        // will not list the others, so it uses not as much space
        case 'Frühschicht':
                for ($col="A"; $col <= "I"; $col++) {
                    $spreadsheet->getStyle($col.$row)->getFill()
                        ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                        ->getStartColor()->setARGB($lightblue);
                }
            break;

        default:
            break;
    }
}

May not be the nicest solution, but for a little project like this one, it should be fine and could be much worse i think xD

score 0 · Answer 2 · answered Dec 08 '16 at 06:48

I was suffering from the same problem this morning and figured out the work-around that is not so miserable to myself. The key point is: after calling duplicateStyle(), you have to re-set the values for cells you have performed this method on. For example:



    $sheet = $phpExcel->getActiveSheet();
    key step 1: $style= $sheet->getStyle('D1');
    key step 2: $sheet->duplicateStyle($style,'A5');
    key step 3: $sheet->setCellValue('A5','NIDONGDE');

The above solution works well for me.

PHPExcel - duplicateStyle() seems not to work / does nothing

2 Answers2