Line break in Data Validation List of a Cell

Topics: User Forum
Jan 13, 2012 at 7:07 AM

Hello,

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();
$cellDataValidation->setAllowBlank(false);
$cellDataValidation
->setType(PHPExcel_Cell_DataValidation::TYPE_LIST);
$cellDataValidation->setShowDropDown(true);
$validData = "";
$dataRows = array("One","Two","Three","Four","Five and more");
foreach($dataRows as $unitData)
{
$validData .= '"'.$unitData.'" ';
}
$cellDataValidation->setFormula1($validData);
$currentWorkingSheet->setDataValidation($cellRange, $cellDataValidation);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');

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.

Jan 16, 2012 at 8:52 PM

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": https://bugs.freedesktop.org/show_bug.cgi?id=41425

So if you use LibO and can confirm this bug over there, that may help get LibO to fix it.

Jan 23, 2012 at 8:35 AM

Hi Scott,

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.


Thanks
Ajitha