Create Xlsx Files With Drop Down List Data Validation

Code to write down selections where users can pick an option from, not allowing any data not within the itemize selection to be inputed in a particular cell. This kind of data validation, specifically can be referred as a drop down list validation, can straightforwardly achieve with PhpSpreadsheet when creating an xlsx file.

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 "Select from the drop down options:"
 * string value, will serve as the 'Select Option Title'.
 */
$sheet->setCellValue('B3', 'Select from the drop down options:');

/**
 * Set the 'drop down list' validation on C3.
 */
$validation = $sheet->getCell('C3')->getDataValidation();

/**
 * Since the validation is for a 'drop down list',
 * set the validation type to 'List'.
 */
$validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);

/**
 * List drop down options.
 */
$validation->setFormula1('"A, B, C, D"');

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

/**
 * Show drop down.
 */
$validation->setShowDropDown(true);

/**
 * Display a cell 'note' about the
 * 'drop down list' validation.
 */
$validation->setShowInputMessage(true);

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

/**
 * Describe the note.
 */
$validation->setPrompt('Must select one from the drop down options.');

/**
 * 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 option');

/**
 * Set the error message.
 */
$validation->setError('Select one from the drop down list.');

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

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

create-xlsx-files-with-drop-down-list-data-validation.php

Test.

Run the following codes.

$ php create-xlsx-files-with-drop-down-list-data-validation.php

command line

Result.

Open the generated file create-xlsx-files-with-drop-down-list-data-validation.xlsx.

/**
 * Set cell B3 with the "Select from the drop down options:"
 * string value, will serve as the 'Select Option Title'.
 */
$sheet->setCellValue('B3', 'Select from the drop down options:');

/**
 * Set the 'drop down list' validation on C3.
 */
$validation = $sheet->getCell('C3')->getDataValidation();

/p>

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 'drop down list',
 * set the validation type to 'List'.
 */
$validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);

Important:

Separate each option with a comma, and enclosed all options with the double quotations, ex. (‘”A, B, C, D”‘).

/**
 * List drop down options.
 */
$validation->setFormula1('"A, B, C, D"');

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

/**
 * Show drop down.
 */
$validation->setShowDropDown(true);

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

/**
 * Display a cell 'note' about the
 * 'drop down list' validation.
 */
$validation->setShowInputMessage(true);

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

/**
 * Describe the note.
 */
$validation->setPrompt('Must select one from the drop down options.');

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

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

If the data entered is not on the drop down options, 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 option');

/**
 * Set the error message.
 */
$validation->setError('Select one from the drop down list.');