I need to add a dropdown list to few columns to allow user to select from range, instead of typing text. for this, I did the below
$objPHPExcel = new PHPExcel();
$currentWorkingSheet = $objPHPExcel->getActiveSheet();
$cellRange = 'A2:A100';
$cellDataValidation = new PHPExcel_Cell_DataValidation();
$validData = "";
$dataRows = array("One","Two","Three","Four","Five and more");
foreach($dataRows as $unitData)
$validData .= '"'.$unitData.'" ';
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
However the new line is not working properly. After the excel sheet is saved and i reopen and make change, save and reopen, then the dropdowns are getting lost.
If you are saving via LibreOffice, this is a known issue with LibreOffice, but with OpenOffice.org, Excel, etc. should work fine.
See this bug that I reported a few months ago to LibO that is still status "new":
So if you use LibO and can confirm this bug over there, that may help get LibO to fix it.
I faced the issue with OpenOffice. But, I got this fixed by using NamedRange for the list, instead of the above approach
However, one issue remains. The Validation List (in Open Office) disappears if we change something the spreadsheet, save it and reopen it.. Looks like the checkbox "Show Selection List" in Data->validity->criteria tab is not retaining the selection.