Named Range Data Validation Blank dropdown

Topics: Developer Forum, User Forum
Sep 13, 2010 at 8:27 PM

I am attempting to use Named Ranges for Data Validation to work around the 255 char limit in formulas.
The generated excel file has UNPOPULATED (empty) drop downs (with no errors reported writing out or opening the xls)
I get the same result writing both excel5 and excel2007.

I am using PHPExcel @version 1.7.4, 2010-08-26 to generate  and excel2010Beta to open the files.

I have tried a number of different values for <SheetName>: Sheet, Sheet0, Sheet1.  All fail to populate dropdown
I have also tried using setCellValueByColumnAndRow(0, 1, 'item1') etc, fails to populated dropdown

Am I doing something incorrectly?  Any advice would be greatly appreciated.

Sample code follows:

    require_once 'lib/phpexcel/PHPExcel.php';
    $objPHPExcel = new PHPExcel();
    $objPHPExcel->setActiveSheetIndex(0);

    $objPHPExcel->getActiveSheet()->setCellValue("A1", "item1");
    $objPHPExcel->getActiveSheet()->setCellValue("A2", "item2");
    $objPHPExcel->getActiveSheet()->setCellValue("A3", "item3");

    $objValidation = $objPHPExcel->getActiveSheet()->getCell("B1")->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.');
    //Using a comma separated list here works, but using a range comes back empty
    $objValidation->setFormula1('Sheet!$A$1:$A$3');

    //// Export as Excel5: fails to populate dropdown
    //header('Content-Type: application/vnd.ms-excel');
    //header('Content-Disposition: attachment;filename="dropdown_test.xls"');
    //header('Cache-Control: max-age=0');
    //$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    //$objWriter->save('php://output');
    //exit;

    // Export as Excel2007: fails to populate dropdown
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="dropdown_test.xlsx"');
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save('php://output');
    exit;

TIA

Note: this is a repost from thread: http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=80422

Sep 14, 2010 at 6:32 PM

 I also tried setting the sheet name explicitly and then using that name in setFormula1, but I am still received a blank dropdown.

Can anyone see why my code is not working?  Does anyone have working sample code that uses a range for data validation?

I am running php v5.2.9 via xampp on Windows 7 if that matters.

Please let me know if I should be providing any additional information, or if I should be posting elsewhere.

Any assistance would be greatly appreciated,

Tim

Coordinator
Sep 16, 2010 at 10:30 PM
Edited Sep 16, 2010 at 10:31 PM

Your example code works correctly if the correct worksheet title is used in the formula. The default title for the worksheet created when you instantiate a new PHPExcel object is 'Worksheet' and using a formula of  $objValidation->setFormula1('Worksheet!$A$1:$A$3'); does produce the correct dropdown. Likewise, explicitly naming the worksheet using $objPHPExcel->getActiveSheet()->setTitle('Test Worksheet #1'); and then setting the formula as $objValidation->setFormula1('\'Test Worksheet #1\'!$A$1:$A$3'); also works correctly. Note that worksheet names are case-sensitive.

Sep 16, 2010 at 10:52 PM

Thank you for the feedback.  I have tested and confirmed  you are correct: my problem was the incorrect sheet name.

The source of my misunderstanding stemmed from the "PHPExcel developer documentation.doc", which refers to the name "Sheet!" rather than "Worksheet!", leading me to erroneously conclude the default name is 'Sheet'  (although I'm sure if I had read the entire manual I'd have spotted the cause of my problem).   From the doc Section 4.6.27 (pg 27):

  • " Therefore it is normally a better idea to type the item values directly in some cell range, say A1:A3, and instead use, say, $objValidation->setFormula1('Sheet!$A$1:$A$3');"

Thanks again for you time and focus,

Tim