php - PHPExcel Multiple Dropdown list that dependent

Question

I am trying to set up multiple dropdowns in phpexcel that are dependent. Basically, when you select a value in a dropdown in a column A, it loads different content in the dropdown in column B.

I am able to comfortably set up dropdown lists on cells. I am trying to figure out how to load content based on selection of one dropdown.

Any feedback or solutions or guide will be greatly appreciated

Answer 1

Well I managed to figure it out. Here's the gist for anyone in the future:

Best to use Named Ranges. Basically define before hand the list items (named ranges) in cells. this can be on a different worksheet. So, lets use an example of countries and cities. dropdown 1 will have counties, dropdown 2 will have cities. So define the named ranges. One will be called countries. the other two named ranges will be called after the name of the countries. so, lets go.

$objPHPExcel->getSheetByName('Worksheet 1')->SetCellValue("A1", "UK");
    $objPHPExcel->getSheetByName('Worksheet 1')->SetCellValue("A2", "USA");

    $objPHPExcel->addNamedRange( 
        new PHPExcel_NamedRange(
            'countries', 
            $objPHPExcel->getSheetByName('Worksheet 1'), 
            'A1:A2'
        ) 
    );

$objPHPExcel->getSheetByName('Worksheet 1')->SetCellValue("B1", "London");
    $objPHPExcel->getSheetByName('Worksheet 1')->SetCellValue("B2", "Birmingham");
    $objPHPExcel->getSheetByName('Worksheet 1')->SetCellValue("B3", "Leeds");
    $objPHPExcel->addNamedRange( 
        new PHPExcel_NamedRange(
            'UK', 
            $objPHPExcel->getSheetByName('Worksheet 1'), 
            'B1:B3'
        ) 
    );

$objPHPExcel->getSheetByName('Worksheet 1')->SetCellValue("C1", "Atlanta");
    $objPHPExcel->getSheetByName('Worksheet 1')->SetCellValue("C2", "New York");
    $objPHPExcel->getSheetByName('Worksheet 1')->SetCellValue("C3", "Los Angeles");
    $objPHPExcel->addNamedRange( 
        new PHPExcel_NamedRange(
            'USA', 
            $objPHPExcel->getSheetByName('Worksheet 1'), 
            'C1:C3'
        ) 
    );

So thats the named ranges. One is the country, the others are the ranges for the cities for each of the countries. Now to load up the first dropdown to select the countries.

$objValidation = $objPHPExcel->getActiveSheet()->getCell('A1')->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("=countries"); //note this!

Now for the dropdown to load the cities depending on the country. This uses an excel function called Indirect. Basically returns the selected value. hence the similarly named ranges. so i select "UK", it loads the named range called "UK" which has the UK cities.

$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.');
    $objValidation->setFormula1('=INDIRECT($A$1)'); 

Notes: I have used two sheets. Worksheet 1 to hold the data and sheet 0 or default to hold the dropdowns. All the best.