Create Xlsx Files With Cell Conditional Formatting Settings

Create an xlsx file using PhpSpreadsheet with different cell conditional settings, for instance, coding an arrangement on a cell to check if a value is less than, equal to, or greater than a certain amount, then setting a specific cell style format for each conditions made to quickly identify one from another.

The following tutorials are also covered here, it would be better to check and understand them first.

Requirements:

Composer
PHP 7.2 or newer

Step 1.

Setup dependencies.

{
    "require": {
        "phpoffice/phpspreadsheet": "^1.3"
    }
}

composer.json

Step 2.

Install phpspreadsheet.

$ composer install

command line

Step 3.

Create a new PHP file, and start coding.

<?php

// Autoload dependencies
require 'vendor/autoload.php';

// Import the core class of PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;

// Import the Xlsx writer class
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Create a new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Retrieve the current active worksheet
$sheet = $spreadsheet->getActiveSheet();

/**
 * Randomly fill cell A1 to A10 with
 * values ranging from -10 to 10.
 */
for ($i=1; $i<11; $i++) {
    $sheet->setCellValue('A' . $i, rand(-10, 10));
}

/**
 * Set up a condition to check if a cell
 * value is less than zero (0), the following
 * cell styles will auto apply.
 * 
 * - font color = red
 * - font weight = bold
 * - background fill type = linear gradient
 * - background fill color = from light black to white
 */
$conditionalLessThan = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
$conditionalLessThan->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
$conditionalLessThan->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_LESSTHAN);
$conditionalLessThan->addCondition('0');
$styleArrayLessThan = [
    'font' => [
        'color' => [
            'argb' => 'FFFF0000',
        ],
        'bold' => true,
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
        'startColor' => [
            'argb' => 'FF777777',
        ],
        'endColor' => [
            'argb' => 'FFFFFFFF',
        ],
    ],
];
$conditionalLessThan->getStyle()->applyFromArray($styleArrayLessThan);

/**
 * Set up a condition to check if a cell
 * value is equal to zero (0), the following
 * cell styles will auto apply.
 * 
 * - font style = underlined
 */
$conditionalEqual = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
$conditionalEqual->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
$conditionalEqual->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_EQUAL);
$conditionalEqual->addCondition('0');
$styleArrayEqual = [
    'font' => [
        'underline' => true,
    ],
];
$conditionalEqual->getStyle()->applyFromArray($styleArrayEqual);

/**
 * Set up a condition to check if a cell
 * value is greater than zero (0), the following
 * cell styles will auto apply.
 * 
 * - font color = light green
 */
$conditionalGreaterThan = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
$conditionalGreaterThan->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
$conditionalGreaterThan->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_GREATERTHAN);
$conditionalGreaterThan->addCondition('0');
$styleArrayGreaterThan = [
    'font' => [
        'color' => [
            'argb' => 'FF227447',
        ]
    ],
];
$conditionalGreaterThan->getStyle()->applyFromArray($styleArrayGreaterThan);

/**
 * Gather all conditions and their
 * specific style settings in an array.
 */
$conditionalStyles = $sheet->getStyle('A1:A' . $i)->getConditionalStyles();
$conditionalStyles[] = $conditionalLessThan;
$conditionalStyles[] = $conditionalEqual;
$conditionalStyles[] = $conditionalGreaterThan;

/**
 * Apply all the gathered conditions
 * and styles from A1 to A10.
 */
$sheet->getStyle('A1:A' . $i)->setConditionalStyles($conditionalStyles);

// Write a new .xlsx file
$writer = new Xlsx($spreadsheet);

// Save the new .xlsx file
$writer->save('create-xlsx-files-with-cell-conditional-formatting-settings.xlsx');

create-xlsx-files-with-cell-conditional-formatting-settings.php

Test.

Run the following codes.

$ php create-xlsx-files-with-cell-conditional-formatting-settings.php

command line

Result.

Open the generated file create-xlsx-files-with-cell-conditional-formatting-settings.xlsx.

/**
 * Set up a condition to check if a cell
 * value is less than zero (0), the following
 * cell styles will auto apply.
 * 
 * - font color = red
 * - font weight = bold
 * - background fill type = linear gradient
 * - background fill color = from light black to white
 */
$conditionalLessThan = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
$conditionalLessThan->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
$conditionalLessThan->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_LESSTHAN);
$conditionalLessThan->addCondition('0');
$styleArrayLessThan = [
    'font' => [
        'color' => [
            'argb' => 'FFFF0000',
        ],
        'bold' => true,
    ],
    'fill' => [
        'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
        'startColor' => [
            'argb' => 'FF777777',
        ],
        'endColor' => [
            'argb' => 'FFFFFFFF',
        ],
    ],
];
$conditionalLessThan->getStyle()->applyFromArray($styleArrayLessThan);

/**
 * Set up a condition to check if a cell
 * value is equal to zero (0), the following
 * cell styles will auto apply.
 * 
 * - font style = underlined
 */
$conditionalEqual = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
$conditionalEqual->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
$conditionalEqual->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_EQUAL);
$conditionalEqual->addCondition('0');
$styleArrayEqual = [
    'font' => [
        'underline' => true,
    ],
];
$conditionalEqual->getStyle()->applyFromArray($styleArrayEqual);

/**
 * Set up a condition to check if a cell
 * value is greater than zero (0), the following
 * cell styles will auto apply.
 * 
 * - font color = light green
 */
$conditionalGreaterThan = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
$conditionalGreaterThan->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
$conditionalGreaterThan->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_GREATERTHAN);
$conditionalGreaterThan->addCondition('0');
$styleArrayGreaterThan = [
    'font' => [
        'color' => [
            'argb' => 'FF227447',
        ]
    ],
];
$conditionalGreaterThan->getStyle()->applyFromArray($styleArrayGreaterThan);

Test Again.

Manually change the value of A3.

Current value is -7.

Change -7 to 0.

Result.

The php coded cell format conditions will auto apply for any manual update because it is programmed before the xlsx file is generated.