Skip to content

Instantly share code, notes, and snippets.

@rotexdegba
Created November 21, 2013 18:25
Show Gist options
  • Star 7 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
  • Save rotexdegba/7586827 to your computer and use it in GitHub Desktop.
Save rotexdegba/7586827 to your computer and use it in GitHub Desktop.
How to add drop down list to a cell in PHPExcel
<?php
$objValidation = $objPHPExcel->getActiveSheet()->getCell("C$curr_row")->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Value is not in list.');
$objValidation->setPromptTitle('Pick from list');
$objValidation->setPrompt('Please pick a value from the drop-down list.');
$objValidation->setFormula1('"Item A,Item B,Item C"');
?>
@eddysapata
Copy link

Successfully used that and working as well in Libra Office but giving error in Microsoft excel..!
What to do ?
Giving error

Removed Feature: Data validation from /xl/worksheets/sheet1.xml part

@cheinle
Copy link

cheinle commented Nov 22, 2017

I found a workaround where you use the same formula you would if you are getting the list values from a column in another worksheet

$objValidation = $objPHPExcel->getActiveSheet()->getCell('A1')->getDataValidation();     
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );     
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );     
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Please choose from dropdown list');
$objValidation->setPromptTitle('Allowed input');
$objValidation->setFormula1('\'Sheet2\'!$A$1:$A$11'); //sheet name and column/row of dropdown values on other sheet (or same sheet)

I no longer received the 'Removed Feature: Data validation from /xl/worksheets/sheet1.xml part' error

@metalrex100
Copy link

@kgrosvenor
Copy link

Does this do the filtering? :)

@bertoost
Copy link

I am generating template excel files and some columns should have a pre-filled list with posiible values. Super that it is possible!
But I only generate the header row (row 1) and when someone else wants to fill the data-rows (row 2 and above) the dropdown should appear. Is it possible to define a dropdown for unlimited rows in a certain column, without specifying an end row number?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment