Create Xlsx Files With Future Date Data Validation

Using PhpSpreadsheet, restrict user entered values to a future date by coding a data validation on a specific cell which will have settings that will check if a given value is greater than the current date. Add some notes as a reminder to what exact data are only allowed, and also set the error message once an invalid value was entered.

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

/**
 * Set cell B3 with the "Enter a future date:"
 * string value, will serve as the 'Input Title'.
 */
$sheet->setCellValue('B3', 'Enter a future date:');

/**
 * Set the date tomorrow as the default value for C3.
 *
 * The user will only be allowed to input date from
 * tomorrow to any date in the future, and for any
 * invalid input, the date tomorrow (default) will
 * be considered.
 */
$spreadsheet->getActiveSheet()->setCellValue('C3', date("m/d/Y", strtotime('tomorrow')));

/**
 * Set the 'future date' validation on C3.
 */
$validation = $sheet->getCell('C3')->getDataValidation();

/**
 * Since the validation is for a 'future date',
 * set the validation type to 'Date'.
 */
$validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_DATE);

/**
 * Set the operator to 'Greater than' to
 * check for any future date value.
 */
$validation->setOperator(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::OPERATOR_GREATERTHAN);

/**
 * Set the current date as the 'Start date' for which
 * the 'future validation' will be based from.
 */
$validation->setFormula1(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(date('m/d/Y')));

/**
 * Do not allow empty value.
 */
$validation->setAllowBlank(false);

/**
 * Display a cell 'note' about the
 * 'future date' validation.
 */
$validation->setShowInputMessage(true);

/**
 * Set the 'note' title.
 */
$validation->setPromptTitle('Note');

/**
 * Describe the note.
 */
$validation->setPrompt('Must be greater than the date today.');

/**
 * Show error message if the data entered is invalid.
 */
$validation->setShowErrorMessage(true);

/**
 * Do not allow any other data to be entered
 * by setting the style to 'Stop'.
 */
$validation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);

/**
 * Set descriptive error title.
 */
$validation->setErrorTitle('Invalid date.');

/**
 * Set the error message.
 */
$validation->setError('The given date is not greater than the date today.');

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

// Save the new .xlsx file
$writer->save('create-xlsx-files-with-future-date-data-validation.xlsx');

create-xlsx-files-with-future-date-data-validation.php

Test.

Run the following codes.

$ php create-xlsx-files-with-future-date-data-validation.php

command line

Result.

Open the generated file create-xlsx-files-with-future-date-data-validation.xlsx.

/**
 * Set cell B3 with the "Enter a future date:"
 * string value, will serve as the 'Input Title'.
 */
$sheet->setCellValue('B3', 'Enter a future date:');

/**
 * Set the date tomorrow as the default value for C3.
 *
 * The user will only be allowed to input date from
 * tomorrow to any date in the future, and for any
 * invalid input, the date tomorrow (default) will
 * be considered.
 */
$spreadsheet->getActiveSheet()->setCellValue('C3', date("m/d/Y", strtotime('tomorrow')));

/**
 * Set the 'future date' validation on C3.
 */
$validation = $sheet->getCell('C3')->getDataValidation();

Check the settings.

Click on Data.

Click on cell C3.

Click on ‘Data Validation’.

The ‘Data ValidationData Validation‘ window should show up.

Under the Settings Tab of the ‘Data Validation‘ window.

/**
 * Since the validation is for a 'future date',
 * set the validation type to 'Date'.
 */
$validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_DATE);

/**
 * Set the operator to 'Greater than' to
 * check for any future date value.
 */
$validation->setOperator(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::OPERATOR_GREATERTHAN);

Important:

The formula value must be set appropriately, otherwise the data validation will not work. And on this case, it must be converted to excel time format using the \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel method, this will ensure the data is properly formatted.

/**
 * Set the current date as the 'Start date' for which
 * the 'future validation' will be based from.
 */
$validation->setFormula1(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(date('m/d/Y')));

/**
 * Do not allow empty value.
 */
$validation->setAllowBlank(false);

Under the Input Message Tab of the ‘Data Validation‘ window.

/**
 * Display a cell 'note' about the
 * 'future date' validation.
 */
$validation->setShowInputMessage(true);

/**
 * Set the 'note' title.
 */
$validation->setPromptTitle('Note');

/**
 * Describe the note.
 */
$validation->setPrompt('Must be greater than the date today.');

Under the Error Alert Tab of the ‘Data Validation‘ window.

/**
 * Show error message if the data entered is invalid.
 */
$validation->setShowErrorMessage(true);

If the date entered is not a future data, the ‘Error Window‘ should appear.

/**
 * Do not allow any other data to be entered
 * by setting the style to 'Stop'.
 */
$validation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);

/**
 * Set descriptive error title.
 */
$validation->setErrorTitle('Invalid date.');

/**
 * Set the error message.
 */
$validation->setError('The given date is not greater than the date today.');