CODEWITHSUNDEEP
phpphpexcel
Rendi Chs
Rendi Chs

Reputation: 55

PHPExcel : How to alternating rows color

I want to create alternating rows of color on PHPExcel. Example like odd=no, even=color.

enter image description here

my code is :

$row = 4;
$excel->getActiveSheet()->getStyle('A4:I'.($row-1))->applyFromArray(
 array(
    'fill' => array(
        'type' => PHPExcel_Style_Fill::FILL_SOLID,
        'color' => array('rgb' => 'ccff99')
    )
 )
);

I'm confused about looping if even=color else no color.

Upvotes: 2

Views: 2546

Answers (2)

synthetic
synthetic

Reputation: 21

You can use conditional formatting for this issue. Official documentation: https://phpspreadsheet.readthedocs.io/en/latest/topics/conditional-formatting/#formulae

$cellRange = 'A1:Z999';
$conditionalStyles = [];
$wizardFactory = new Wizard($cellRange);
/** @var Wizard\Expression $cellWizard */
$cellWizard = $wizardFactory->newRule(Wizard::EXPRESSION);
$style = $cellWizard->getStyle();
$style->getFill()->setFillType(Fill::FILL_SOLID)
    ->getStartColor()->setARGB('D9D9D9');

$cellWizard->formula('MOD(ROW(),2)')
    ->setStyle($style);
$conditionalStyles[] = $cellWizard->getConditional();

$spreadSheet->getActiveSheet()
    ->getStyle($cellWizard->getCellRange())
    ->setConditionalStyles($conditionalStyles);

OR this approach without wizard

        $conditional = new Conditional();
    $conditional->setConditionType(Conditional::CONDITION_EXPRESSION)
        ->setOperatorType(Conditional::OPERATOR_NONE)
        ->addCondition('MOD(ROW(),2)')
        ->getStyle()
        ->getFill()
        ->setFillType(Fill::FILL_SOLID)
        ->getStartColor()
        ->setRGB(self::EVEN_COLOR);
    $sheet->getStyle([1, $firstRow, $dimension, $lastRow])->setConditionalStyles([$conditional]);

Upvotes: 2

Yann Chabot
Yann Chabot

Reputation: 4879

Here was my solution:

So you set your data into the Excel:

$excel->getActiveSheet()->fromArray($datas);

First, for the table header, I apply this fill and color (note that you must change the A1:I1 so it fits your needs).

$excel->getActiveSheet()->getStyle('A1:I1')->applyFromArray(
    array(
        'fill' => array(
            'type' => \PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('argb' => 'FFCCFFCC')
        ),
        'borders' => array(
            'bottom' => array('style' => \PHPExcel_Style_Border::BORDER_THIN),
            'right' => array('style' => \PHPExcel_Style_Border::BORDER_MEDIUM)
        )
    )
);

Then, you do a simple loop to color on row after another. You, of course, start at the 2nd row this time and end when you reach your data's count.

for ($i = 2; $i < count($sub); $i++) {
    if ($i % 2 == 0) {
        $excel->getActiveSheet()->getStyle('A' . $i . ':I' . $i)->applyFromArray(
            array(
                'fill' => array(
                    'type' => \PHPExcel_Style_Fill::FILL_SOLID,
                    'color' => array('argb' => 'FFF3F3F3')
                ),
            )
        );
    }
}

Upvotes: 4

Related Questions