phpexcelexcel-formulaphpspreadsheetphpoffice-phpspreadsheet

Fill cell with text if empty in PHPSpreadSheet


I want to make something like a placeholder for an empty cell, so I decided to use conditional formatting, example here says about OPERATOR_LESSTHAN, but nothing much about other conditions. I want to fill cell with colored text, which says something like 'enter text here'. If user enters a text, the cell has to change it's color to default. I tried the following, but it does not work, the cell stays empty:

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();

$conditional1 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
$conditional1->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
$conditional1->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_EQUAL);
$conditional1->addCondition('');
$conditional1->setText("Enter text here");
$conditional1->getStyle()->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);

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

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

UPD: the following code makes the cell gray, I can set the cell's content by $spreadsheet->getActiveSheet()->getCell("C3")->setValue("text");, but it does not change the color if user inputs something to the cell.

<?php

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();

$conditional1 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
$conditional1->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_NOTCONTAINSTEXT);
$conditional1->getStyle()->getFont()->getColor()->setARGB('969696');

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

$writer = new Xlsx($spreadsheet);
$writer->save('hello world.xlsx');

Solution

  • Finally I figured out how to do this. This is the code:

    <?php
    
    require 'vendor/autoload.php';
    
    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
    
    $spreadsheet = new Spreadsheet();
    
    $cellToUse = 'C3';
    
    $conditional1 = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
    $conditional1->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_CONTAINSTEXT);
    $conditional1->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_CONTAINSTEXT);
    $conditional1->setText('Add text here...');
    $conditional1->getStyle()->getFont()->getColor()->setARGB('969696');
    
    if ($spreadsheet->getActiveSheet()->getCell($cellToUse)->getValue() === null) {
        $spreadsheet->getActiveSheet()->getCell($cellToUse)->setValue('Add text here...');
    }
    
    $conditionalStyles = $spreadsheet->getActiveSheet()->getStyle($cellToUse)->getConditionalStyles();
    $conditionalStyles[] = $conditional1;
    $spreadsheet->getActiveSheet()->getStyle($cellToUse)->setConditionalStyles($conditionalStyles);
    
    
    $writer = new Xlsx($spreadsheet);
    $writer->save('hello world.xlsx');
    
    

    This code sets C3 text color to gray if is contains placeholder text, otherwise the color remains default