Create Xlsx Files With Date Not In Range Data Validation

| August 23, 2020 | 1,324 views | PhpSpreadsheet

Code the opposite logic of the “Date range data validation“, forbid a user input from a data within a date range, only outside values from a given date range will be accepted. This is another trouble-free way of creating an xlsx file with data validation using PhpSpreadsheet.

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

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

/**
 * Set the date 2 months ago as the default value for C3.
 *
 * The user will only be allowed to input a date before one month
 * ago or a date after one month from now, and for any invalid input,
 * the date 2 months ago (default) will be considered.
 */
$spreadsheet->getActiveSheet()->setCellValue('C3', date('m/d/Y', strtotime('-2 month')));

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

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

/**
 * Set the operator to 'Not Between' to
 * check for any date not in range value.
 */
$validation->setOperator(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::OPERATOR_NOTBETWEEN);

/**
 * Set the date one month ago as the 'Start date'
 */
$validation->setFormula1(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(date('m/d/Y', strtotime('-1 month'))));

/**
 * Set the date one month from now as the 'End date'.
 */
$validation->setFormula2(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(date('m/d/Y', strtotime('+1 month'))));

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

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

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

/**
 * Describe the note.
 */
$validation->setPrompt('Must be not between ' . date('m/d/Y', strtotime('-1 month')) . ' and ' . date('m/d/Y', strtotime('+1 month')) . '.');

/**
 * 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 between ' . date('m/d/Y', strtotime('-1 month')) . ' and ' . date('m/d/Y', strtotime('+1 month')) . '.');

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

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

Test.

Run the following codes.

$ php create-xlsx-files-with-date-not-in-range-data-validation.php

Result.

Open the generated file create-xlsx-files-with-date-not-in-range-data-validation.xlsx.

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

/**
 * Set the date 2 months ago as the default value for C3.
 *
 * The user will only be allowed to input a date before one month
 * ago or a date after one month from now, and for any invalid input,
 * the date 2 months ago (default) will be considered.
 */
$spreadsheet->getActiveSheet()->setCellValue('C3', date('m/d/Y', strtotime('-2 month')));

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

Check the settings.

  • 1. Click on Data.
  • 2. Click on cell C3.
  • 3. Click on ‘Data Validation’.

The ‘Data Validation‘ window should show up.

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

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

/**
 * Set the operator to 'Not Between' to
 * check for any date not in range value.
 */
$validation->setOperator(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::OPERATOR_NOTBETWEEN);

Set the minimum date the user cannot enter (one month ago from the date today).

/**
 * Set the date one month ago as the 'Start date'
 */
$validation->setFormula1(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(date('m/d/Y', strtotime('-1 month'))));

Set the maximum date the user cannot enter (one month in the future from the date today).

/**
 * Set the date one month from now as the 'End date'.
 */
$validation->setFormula2(\PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(date('m/d/Y', strtotime('+1 month'))));

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

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

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

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

/**
 * Describe the note.
 */
$validation->setPrompt('Must be not between ' . date('m/d/Y', strtotime('-1 month')) . ' and ' . date('m/d/Y', strtotime('+1 month')) . '.');

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 on the date range 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 between ' . date('m/d/Y', strtotime('-1 month')) . ' and ' . date('m/d/Y', strtotime('+1 month')) . '.');

References:

0 Comments

Leave a Reply

Your email address will not be published.