help Required in populating the drop down in a xls sheet using phpexcel

Topics: Developer Forum
Jun 17, 2013 at 12:55 PM
Edited Jun 17, 2013 at 12:56 PM
Hi ,
I am using php-excel library for multipurpose use .
I am using php version -5.3
I am able to generate a drop down in an excel sheet by using the setformula funciton on a specific column.
note: all the data to be shown in the drop down are present in the excel sheet.
I want to create a drop down whose values should be dynamic and not to be shown in excel sheet in some other cells like in the first case where i have some data in A1 to A3 then I applied the formaul on these cells to create a drop down in B2.
The code is :
$objPHPExcel->getActiveSheet()->setCellValue("A2", "item1");
$objPHPExcel->getActiveSheet()->setCellValue("A3", "item2");
$objPHPExcel->getActiveSheet()->setCellValue("A4", "item3");
$objPHPExcel->getActiveSheet()->setCellValue("B2", "Select Country");
$objValidation = $objPHPExcel->getActiveSheet()->getCell("B2")->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('Sheet!$A$1:$A$3');
Rather than using the cell number in the ablove setformaula1 function can I use variables which hols data to create a drop down?
something like below:
$objValidation->setFormula1('Sheet!100,200,300');

Please help me on this issue.
Thanks in advance
Jun 17, 2013 at 4:43 PM
Edited Jun 19, 2013 at 5:42 AM
I have not tested with PHPExcel, but Excel accepts a list of strings instead of a range reference: My Item 1, My Item 2, My Item 3.
Edit : You can find a example in documentation of PHPExcel, 4.6.27
$objValidation->setFormula1('"Item A,Item B,Item C"');
The maximum string length is 255 characters.

For information: you can override the restriction requiring the presence of cells in the same worksheet by using a named range.