Blank dropdown with Excel2005 writer

Topics: Developer Forum, User Forum
Jul 29, 2014 at 6:08 PM
The hosted site I am on does not support Excel2007 writer for some options I want to do so I am using Excel5 but when I try to add a dropdown from a named range or cell range I just get a blank dropdown. and I am not sure what options I have since there are more than 265 characters in the list.

I am using PHPExcel v. 1.8.0 with PHP version 5.2.9.

I am using the following code and $dept_list is an array. I can verify that the data gets written correctly to the "DATA" sheet.

Thanks.

$objWorksheet3 = $objPHPExcel->createSheet();
$objWorksheet3->setTitle('DATA');
$objPHPExcel->setActiveSheetIndexByName('DATA');
//Set sheet to very hidden (can only be undone programmically)
$objPHPExcel->getActiveSheet()->setSheetState(PHPExcel_Worksheet::SHEETSTATE_VERYHIDDEN);
//Write the dept list array to the sheet
$objPHPExcel->getActiveSheet()->fromArray($dept_list, NULL, 'A1');  
//Create a named range
$objPHPExcel->addNamedRange( new PHPExcel_NamedRange('depts', $objPHPExcel->getActiveSheet(), 'A1:A23') );
//Set the Bulk Order sheet to active
$objPHPExcel->setActiveSheetIndexByName('Bulk Order');  

for($i=2;$i<=25;$i++) {
    //Add a dropdown box to row 5
    $objValidation = $objPHPExcel->getActiveSheet()->getCellByColumnAndRow($i, 5)->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('depts');               
}