Create Xlsx Files With Cell Conditional Formatting Settings

| August 7, 2020 | 1,823 views | PhpSpreadsheet

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"
    }
}

Step 2.

Install phpspreadsheet.

$ composer install

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');

Test.

Run the following codes.

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

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.

References:

0 Comments

Leave a Reply

Your email address will not be published.