How to add drop down list to a cell in PHPExcel

dropdown-list-in-a-cell-phpexcel.php

<?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"');

?>

you can try this code: your start cell no $i=3 you can change this value and $i<=250 looping area.

for ($i = 3; $i <= 250; $i++)
{

$objValidation2 = $objPHPExcel->getActiveSheet()->getCell('N' . $i)->getDataValidation();
$objValidation2->setType(PHPExcel_Cell_DataValidation::TYPE_LIST);
$objValidation2->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION);
$objValidation2->setAllowBlank(false);
$objValidation2->setShowInputMessage(true);
$objValidation2->setShowDropDown(true);
$objValidation2->setPromptTitle('Pick from list');
$objValidation2->setPrompt('Please pick a value from the drop-down list.');
$objValidation2->setErrorTitle('Input error');
$objValidation2->setError('Value is not in list');
$objValidation2->setFormula1('"male,female"');
}

I've found out, how to add list item with comma in it!
The short solution is, use "," between list items ;)

I store my list items in an array:
$items = array (
'one, two, three',
'four, five, six');

$objValidation = $objPHPExcel->getActiveSheet()->getCell("C$curr_row")->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
// ...
// aaand the Magic code :)
$objValidation->setFormula1('"'.implode('","', $items).'"');
// so the added string is: "one, two, three","four, five, six"

I have dropdown values of around 400 characters. In such case this is not working.

Maximum LEN for $objValidation->setFormula1( 255 characters )
Dropdown Error if strlen formula1 > 255 chrs

@sysmaya can we exceed this length i want to enter more than 255 characters is it possible or not.?

@badi238 if you or anyone else is still interested in this: it is possible to add a cell range (even from a different(hidden) worksheet) like this:
...->setFormula1('=\'Worksheet-Title\'!$A$1:$A$15');

good piece of code from @marajmmc, but i was also expecting the error to show up when the conditions are not met, and for the error message to show up, one needs to also add:
$objValidation2->setShowErrorMessage(true);
However, i was not able to find a way to force the user to select something, even if i setAllowBlank(false).

Hi guys,
I am facing an issue when am trying to generate an excel with multiple columns as drop-downs with this procedure. The excel file is working fine when I generate only a single coloumn of dropdowns in the active sheet, however, if I try to incorporate dropdowns in more than one coloumn in the active sheet, the excel file becomes un-usable, and none of the dropdowns work.

On opening the excel, am getting an error like this :
Removed Feature: Data validation from /xl/worksheets/sheet1.xml part

Please provide a solution to this.

Sample Code for reference

//generating an excel using PHPexcel
public function generateExcel2Action(Request $request)
{
    $responseString = 'Initial Mem. '.(memory_get_peak_usage(true)/1024/1024).' MB <br>';
    $startTime = microtime(true);

    //make a new object
    $objPHPExcel = new PHPExcel();
    //set properties
    $objPHPExcel->getProperties()->setCreator("Ramit Mitra")
        ->setLastModifiedBy("Ramit Mitra")
        ->setTitle("Sample excel doc")
        ->setSubject("Office 2007 XLSX Test Document")
        ->setDescription("Test document for Office 2007 XLSX, generated using PHPExcel.")
        ->setKeywords("office 2007 openxml php")
        ->setCategory("Test result file");

    //Excel headers
    $objPHPExcel->setActiveSheetIndex(0);
    $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Shipment Number');
    $objPHPExcel->getActiveSheet()->setCellValue('B1', 'Country');
    $objPHPExcel->getActiveSheet()->setCellValue('C1', 'Region');
    $objPHPExcel->getActiveSheet()->setCellValue('D1', 'Choose Storage Condition');
    $objPHPExcel->getActiveSheet()->setCellValue('E1', 'Choose Storage Area');
    $objPHPExcel->getActiveSheet()->setCellValue('F1', 'Choose Help Text');
    $objPHPExcel->getActiveSheet()->setCellValue('G1', 'Memory in use');

    //QUERY AND SET DATA
    $em = $this->getDoctrine()->getManager();
    //create all options
    $suSC = ['Cold', 'Hot', 'Normal'];
    $suSA = ['SM', 'LG', 'XS', 'XL'];
    $suHT = ['Text 1', 'Text 2', 'Text 3'];

    $i = 2;
    $allCountries = $em->getRepository('DemoBundle:Country')->findAll();
    foreach($allCountries as $key => $val) {
        //shpno
        $objPHPExcel->getActiveSheet()->setCellValue("A$i", 'SHP'.rand(10000,90000));
        //country
        $objPHPExcel->getActiveSheet()->setCellValue("B$i", $val->getName());
        //region
        $objPHPExcel->getActiveSheet()->setCellValue("C$i", $val->getRegion()->getName());
        //Choose Storage Condition
        $objValidation = $objPHPExcel->getActiveSheet()->getCell("D$i")->getDataValidation();
        $objValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST);
        $objValidation->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION);
        $objValidation->setAllowBlank(false);
        $objValidation->setShowInputMessage(true);
        $objValidation->setShowDropDown(true);
        $objValidation->setPromptTitle('Pick Storage Condition');
        $objValidation->setPrompt('Please pick a value from the drop-down list.');
        $objValidation->setErrorTitle('Input error');
        $objValidation->setError('Value is not in list');
        $objValidation->setFormula1('"'.implode(',', $suSC).'"');
        unset($objValidation);
        //Choose Storage Area
        $objValidation1 = $objPHPExcel->getActiveSheet()->getCell("E$i")->getDataValidation();
        $objValidation1->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST);
        $objValidation1->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION);
        $objValidation1->setAllowBlank(false);
        $objValidation1->setShowInputMessage(true);
        $objValidation1->setShowDropDown(true);
        $objValidation1->setPromptTitle('Pick Storage Area');
        $objValidation1->setPrompt('Please pick a value from the drop-down list.');
        $objValidation1->setErrorTitle('Input error');
        $objValidation1->setError('Value is not in list');
        $objValidation1->setFormula1('"'.implode(',', $suSA).'"');
        unset($objValidation1);
        //Choose Help Text
        $objValidation2 = $objPHPExcel->getActiveSheet()->getCell("F$i")->getDataValidation();
        $objValidation2->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST);
        $objValidation2->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION);
        $objValidation2->setAllowBlank(false);
        $objValidation2->setShowInputMessage(true);
        $objValidation2->setShowDropDown(true);
        $objValidation2->setPromptTitle('Pick Help Text');
        $objValidation2->setPrompt('Please pick a value from the drop-down list.');
        $objValidation2->setErrorTitle('Input error');
        $objValidation2->setError('Value is not in list');
        $objValidation2->setFormula1('"'.implode(',', $suHT).'"');
        unset($objValidation2);
        //mem in use
        $objPHPExcel->getActiveSheet()->setCellValue("G$i", (memory_get_peak_usage(true)/1024/1024).' MB');
        $i++;
    }
    unset($allCountries);
    // create the writer
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $file = $this->get('kernel')->getRootDir() . "\..\web" . $this->getRequest()->getBasePath()."/temp/test2.xlsx";
    $objWriter->save($file);
    unset($objWriter);
    $endTime = microtime(true) - $startTime;
    $responseString .= 'Final Mem. '.(memory_get_peak_usage(true)/1024/1024).' MB <br>'."File generated @ $file <br>Executed in :: $endTime";
    unset($startTime);
    unset($endTime);
    //send response
    return new Response($responseString);
}

Thanks.

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

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

Official doc with examples

https://phpspreadsheet.readthedocs.io/en/develop/topics/recipes/#setting-data-validation-on-a-cell

Does this do the filtering? :)

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?