how can i assign a NamedRange to datavalidation like "=yaer" yaer is a NamedRange

Topics: Developer Forum, Project Management Forum, User Forum
Feb 17, 2012 at 2:30 AM

here is my code
$namedRange = new PHPExcel_NamedRange("grade", $activeDataSheet, "A1:{$endCell}1");
                $PHPExcel->addNamedRange($namedRange);
//                //设置数据有效性
                $objValidation2 = $activeSheet->getCell("A3")->getDataValidation();
                $objValidation2->setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
                    ->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                    ->setAllowBlank(false)
                    ->setShowInputMessage(true)
                    ->setShowErrorMessage(true)
                    ->setShowDropDown(true)
                    ->setErrorTitle('输入的值有误')
                    ->setError('您输入的值不在下拉框列表内.')
                    ->setPromptTitle('下拉选择框')
                    ->setPrompt('请从下拉框中选择年级!')
                    ->setFormula1("grade");  //dosen't work....grade is a NamedRange 
                $objValidation3 = $activeSheet->getCell("B3")->getDataValidation();
                $objValidation3->setType(PHPExcel_Cell_DataValidation::TYPE_LIST)
                    ->setErrorStyle(PHPExcel_Cell_DataValidation::STYLE_INFORMATION)
                    ->setAllowBlank(false)
                    ->setShowInputMessage(true)
                    ->setShowErrorMessage(true)
                    ->setShowDropDown(true)
                    ->setErrorTitle('输入的值有误')
                    ->setError('您输入的值不在下拉框列表内.')
                    ->setPromptTitle('下拉选择框')
                    ->setPrompt('请从下拉框中选择班级!')
                    ->setFormula1('INDIRECT(A3)');//dosen't work....

INDIRECT is a excle formula, in excal can use like that : "=INDIRECT(A3)";

 ->setFormula1("Worksheet!A1:{$endCell}1");// work....

Coordinator
Feb 19, 2012 at 10:13 PM
Edited Feb 19, 2012 at 10:13 PM

Try using "=grade" as you're effectively setting a formula, likewise "INDIRECT(A3)" should also be a formula, so would need "=INDIRECT(A3)".

If that doesn't work, raise this as a work item.

Jun 8, 2012 at 3:27 PM

I finally solved NamedRange & Excel5 problem.
Please see my blog. 

How to use a name definition for the input rule of a cell by PHPExcel 
http://hello.lumiere-couleur.com/smilkobuta/2012/06/08/phpexcel%E3%81%A7%E3%82%BB%E3%83%AB%E3%81%AE%E5%85%A5%E5%8A%9B%E8%A6%8F%E5%89%87%E3%81%AB%E5%90%8D%E5%89%8D%E5%AE%9A%E7%BE%A9%E3%82%92%E4%BD%BF%E3%81%86%E6%96%B9%E6%B3%95/

This is Japanese page but you can read these three chunks of code.
2 PHPExcel files modification and $validation->setFormula1('INDIRECT(CONCATENATE("grade"))').


Coordinator
Jun 11, 2012 at 1:35 PM

Thanks for this. Google translate doesn't work very welll from Japanese to English, but I think I get the general meaning; and I'm currently testing your modifications to the parser and writer code

Jun 11, 2012 at 10:27 PM
Edited Jun 11, 2012 at 10:27 PM

Sorry, I've forgotten copy & past "PHPExcel/Writer/Excel5/Parser.php1585~1588. I updated my blog.

Modification's essence is first INDIRECT function (last one in RPN) has to return REFERENCE instead of VALUE.
Thank you for checking my comment!