Create an xlsx file using PhpSpreadsheet and code a data validation requiring a user to input a date value within a certain date range, consequently setting a minimum date limit and maximum date boundary at the same time.
Requirements:
- Composer
- PHP 7.2 or newer
Step 1.
Setup dependencies.
{
"require": {
"phpoffice/phpspreadsheet": "^1.3"
}
}composer.json
Step 2.
Install phpspreadsheet.
$ composer installcommand 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 date between a month ago and a month ahead:"
* string value, will serve as the 'Input Title'.
*/
$sheet->setCellValue('B3', 'Enter a date between a month ago and a month ahead:');
/**
* Set the date today as the default value for C3.
*
* The user will only be allowed to input a date between one month
* ago and a date one month from now, and for any invalid input,
* the date tiday (default) will be considered.
*/
$spreadsheet->getActiveSheet()->setCellValue('C3', date("m/d/Y"));
/**
* Set the 'date range' validation on C3.
*/
$validation = $sheet->getCell('C3')->getDataValidation();
/**
* Since the validation is for a 'date range',
* set the validation type to 'Date'.
*/
$validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_DATE);
/**
* Set the operator to 'Between' to
* check for any date range value.
*/
$validation->setOperator(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::OPERATOR_BETWEEN);
/**
* 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 range' validation.
*/
$validation->setShowInputMessage(true);
/**
* Set the 'note' title.
*/
$validation->setPromptTitle('Note');
/**
* Describe the note.
*/
$validation->setPrompt('Must be 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 not 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-range-data-validation.xlsx');
create-xlsx-files-with-date-range-data-validation.php
Test.
Run the following codes.
$ php create-xlsx-files-with-date-range-data-validation.phpcommand line
Result.
Open the generated file create-xlsx-files-with-date-range-data-validation.xlsx.
/**
* Set cell B3 with the "Enter a date between a month ago and a month ahead:"
* string value, will serve as the 'Input Title'.
*/
$sheet->setCellValue('B3', 'Enter a date between a month ago and a month ahead:');
/**
* Set the date today as the default value for C3.
*
* The user will only be allowed to input a date between one month
* ago and a date one month from now, and for any invalid input,
* the date tiday (default) will be considered.
*/
$spreadsheet->getActiveSheet()->setCellValue('C3', date("m/d/Y"));
/**
* Set the 'date range' 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 'date range',
* set the validation type to 'Date'.
*/
$validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_DATE);
/**
* Set the operator to 'Between' to
* check for any date range value.
*/
$validation->setOperator(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::OPERATOR_BETWEEN);
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 minimum date the user can 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 can 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 range' validation.
*/
$validation->setShowInputMessage(true);
/**
* Set the 'note' title.
*/
$validation->setPromptTitle('Note');
/**
* Describe the note.
*/
$validation->setPrompt('Must be 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 not between ' . date('m/d/Y', strtotime('-1 month')) . ' and ' . date('m/d/Y', strtotime('+1 month')) . '.');
Leave a Reply