时间戳

首页 > 解决方案 > PHPSpreadsheet - 条件格式 - 单元格上的多个条件

php - PHPSpreadsheet - 条件格式 - 单元格上的多个条件

问题描述

我正在尝试研究如何在 PHPSpreadsheet 的条件单元格格式中设置多个可能的条件，但似乎无法使其正常工作。

以下面的表格为例：

A1 | B1 | C1
A2 | B2 | C2

如果值为：A1、B1 或 C1，我想格式化单元格（例如为红色）。

我尝试添加多个条件，如下所示：

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

$cells = ['A1', 'A2', 'B1', 'B2', 'C1', 'C2'];

foreach ($cells as $cell) {

    $sheet->setCellValue($cell, $cell);

    $conditional1 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
    $conditional1->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
    $conditional1->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_EQUAL);
    $conditional1->addCondition('A1');
    $conditional1->addCondition('B1');
    $conditional1->addCondition('C1');
    $conditional1->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
    $conditional1->getStyle()->getFont()->setBold(true);

    $conditionalStyles = $spreadsheet->getActiveSheet()->getStyle('B2')->getConditionalStyles();
    $conditionalStyles[] = $conditional1;

    $spreadsheet->getActiveSheet()->getStyle($cell)->setConditionalStyles($conditionalStyles);

}

但它只满足第一个条件。所以在上面的例子中，只有单元格 A1 会被涂成红色，因为只有第一条规则被插入到这些单元格的 excel 文件中。

几个小时以来，我一直在尝试查找有关此的文档或示例，但似乎找不到任何内容。有人可以指出我正确的方向吗？

谢谢。

标签： phpexcelphpexcelphpspreadsheet

解决方案

https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#formatting-cells

 $conditional1 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
        $conditional1->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
        $conditional1->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_LESSTHAN);
        $conditional1->addCondition('0');
        $conditional1->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);
        $conditional1->getStyle()->getFont()->setBold(true);
        
        $conditional2 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
        $conditional2->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
        $conditional2->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_GREATERTHANOREQUAL);
        $conditional2->addCondition('0');
        $conditional2->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_GREEN);
        $conditional2->getStyle()->getFont()->setBold(true);
        
        $conditionalStyles = $spreadsheet->getActiveSheet()->getStyle('B2')->getConditionalStyles();
        $conditionalStyles[] = $conditional1;
        $conditionalStyles[] = $conditional2;
        
        $spreadsheet->getActiveSheet()->getStyle('B2')->setConditionalStyles($conditionalStyles);
    
    /* If you want to copy the ruleset to other cells, you can duplicate the style object:*/
    $spreadsheet->getActiveSheet()
        ->duplicateStyle(
            $spreadsheet->getActiveSheet()->getStyle('B2'),
            'B3:B7'
        );

推荐阅读