PhpSpreadsheet cell validation rules to disallow invalid entry

Question

I am using PhpSpreadsheet within a Laravel app to output a spreadsheet that needs fairly strict controls around it in order to allow that same spreadsheet to be input back into the app after data has been entered.

I can control the data types and even have a nice pop-up message when a user enters an incorrect type such as this:

However, once the user clicks OK or Cancel, the incorrectly formatted text is allowed to remain in the cell. Is there a way to not only flag it for the user, but disallow the incorrect entry altogether?

Current example code:

if($sub['type'] === 'date') {
    $ws->getStyle('C' . $row)->getNumberFormat()->setFormatCode(
        \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DMYSLASH
    );
    $objValidation = $ws->getCell('C' . $row)->getDataValidation();
    $objValidation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_DATE);
    $objValidation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION);
    $objValidation->setAllowBlank(true);
    $objValidation->setShowInputMessage(true);
    $objValidation->setShowErrorMessage(true);
    $objValidation->setShowDropDown(true);
    $objValidation->setErrorTitle('Input error');
    $objValidation->setError('Please enter a date in the format d/m/yy only.');
}

I'm guessing I've missed something in the PhpSpreadsheet manual? I've looked into the base code on some of the methods like setAllowBlank, but I'm likely not looking in the right place.

score 2 · Accepted Answer · answered Jul 14 '19 at 17:36

I still couldn't find this in the docs (though I'm sure it is there somewhere). But once I was able to figure out where to look in the PhpSpreadsheet code, I found it. Very elegant solution from PhpSpreadsheet... I just couldn't see it in the area of base code I was looking into!

Easy fix, change one word:

$objValidation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION);

Becomes:

$objValidation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);

The big '!' on the popup gave me a clue to look at their style code rather than commands code. This brings up the same box, but with an 'X' and prevents changing the cell if not valid, which is exactly what I wanted.

Hopefully helps someone else in future.

score 1 · Answer 2 · answered Jul 14 '19 at 16:49

1

Why not blanking the cell after validation error there is an isValid function. The code can look like:

if(! PhpOffice\PhpSpreadsheet\Cell\DataValidator::isValid($yourCell)){
    $yourCell->setValue("");
}

answered Jul 14 '19 at 16:49

Collie-IT Anne K. Frey

825
7
19

Many thanks for your help. I'm upvoting this, as it looks like a valid solution, but not for me - too many pieces to fix, as I want to to display previous. After poking around the whole PhpSpreadsheet code, I found a little better solution (for me). Thanks again! – Watercayman Jul 14 '19 at 17:33

PhpSpreadsheet cell validation rules to disallow invalid entry

2 Answers2

Linked