How can I read dropdown from excel and get all options list?

[mrmarcin12]

Hello,

I want to read dropdown options list from excel that is generated using formula in Excel. Can I do it using this libary?

[MarkBaker]

If it's a dataValidation list, then take a look the dataValidation methods

[mrmarcin12]

Thank you. Can you help me what method should I use to get this list? I used getDataValidation() and I get information that its list:

["type":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=> string(4) "list"

How to read this list?

[MarkBaker]

If the type is list then the list itself is defined in formula1, either as a comma-separated list of values wrapped in double quotes (e.g. "Item A,Item B,Item C"), or as a cell range if the list values are maintained as a range of cells (e.g. $D$2:$D$6), so use the getType()method to identify if the dataValidation is a list, and the getFormula1() method to retrieve the list.

Note that documentation for this library is available online: for general information about how the library works, with lots of recipes and examples, and also the API documentation for all the classes.

[mrmarcin12]

Thank you. I was trying it already, but getFormula1() returns only name of this list I think, because it's not this options that are visible in dropdown in Excel. I was trying already Google it and I coudn't find solution.

[MarkBaker]

If it's a named list, then you need to read the Named Ranges object for the spreadsheet to get the range: the Spreadsheet object's getNamedRange() method will return a NamedRange when provided with the name as an argument; and then getRange() method will return the cell range

[mrmarcin12]

Thank you. I checked it and getFormula1() as I see return something that its not returned by getNamedRanges, I was checking all of them and there is not same name like getFormula1() returns. So I was wrong, it looks that its not named list. So now I dont have any idea how to get this dropdown options like in Excel.

When I do var_dump on result of getDataValidation() from my cell with dropdown it looks like this:

object(PhpOffice\PhpSpreadsheet\Cell\DataValidation)#20708 (14) {
["formula1":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=>
string(7) "Disc"
["formula2":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=>
string(0) ""
["type":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=>
string(4) "list"
["errorStyle":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=>
string(0) ""
["operator":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=>
string(0) ""
["allowBlank":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=>
bool(true)
["showDropDown":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=>
bool(true)
["showInputMessage":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=>
bool(true)
["showErrorMessage":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=>
bool(true)
["errorTitle":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=>
string(0) ""
["error":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=>
string(0) ""
["promptTitle":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=>
string(0) ""
["prompt":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=>
string(0) ""
["sqref":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=>
string(2) "C6"
}

[MarkBaker]

So what are the options? Or even better, can you provide an example file so that I can see for myself? Disc isn't quote-wrapped, so it must be a reference. It's not a cell reference, so it could be a table name or named range, but I can't know without seeing what else is in that file

[mrmarcin12]

Im sorry but I cant provide this file. But "Disc" in excel is inside "Name manager", where name is "Disc" and it contains formula with IF statement.

[MarkBaker]

In that case, then the evaluation of that formula should give you the list

[mrmarcin12]

I noticed that getNamedRanges() not returning all data that I see inside "Name manager" in Excel. Is it correct or Im doing something wrong?

[MarkBaker]

A Named Range is not the same as a Named Formula, although they are both Defined Names: getNamedRanges() returns an array of Named Ranges; getNamedRange(<defined Name>) will return a specified Named Range; ``getNamedFormulae(); will return an array of Named Formulae; getNamedFormula(<defined Name>) will return a specified Named Formula; `getDefinedNames()` will return an array of all Defined Names (both Named Ranges and Named Formulae); `getDefinedName()` will return a specified Defined Name. For the latter two, you'll need to call the object's `isFormula()` method to determine whether it is a Named Formula or a Named Range.

See the documentation for details

[mrmarcin12]

Its great! I found its formula and I wanted to "calculate" it. So I get formula to string, then I set some cell to this value to calculate it and used getCalculatedValue on this cell. But problem is I see only first record, formula looks like this "IF(Page!$C$3<300,'prices'!$E$1,Spreedsheat3!$A$2:$A$6)" and it should return Spreadsheat3 A2 to A6 but it returns only first one. If I will get all them then I wil get all dropdown values. I dont want to take A2 to A6 manually because its many formula like this in file and later they can be changed. Could you give me idea how to do it?

[MarkBaker]

The Calculation engine has a setting to return the whole set of values for a final result: that setting is turned off by default, because when you're calculating the value for a cell, you're only interested in the value for that cell (note that array formulae change the rules, but you'll need to wait for version 2.0 for array formula support). That setting is a static method in the Calculation Engine, called with the value: Calculation::setArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY).
Remember to reset afterwards using
Calculation::setArrayReturnType(Calculation::RETURN_ARRAY_AS_VALUE).

[mrmarcin12]

Thank you but its still returns single value as string. Here is code:

    Calculation::setArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
    $spreadsheet->getActiveSheet()->setCellValue('C100', '=' . $range->getFormula());
    $retVal = $spreadsheet->getActiveSheet()->getCell('C100')->getCalculatedValue();
    var_dump($retVal);

Here is what it should return: Spreedsheat3!$A$2:$A$6 but it returns only A2

[akbarali1]

I wrote a code that retrieves the Dropdown from another column. Maybe you will be interested. https://github.com/akbarali1/excel-upload-filter/blob/main/app/Http/Controllers/ExcelAutoFilterController.php

helloWorld.xlsx
I've uploaded an Excel file so you can see how it generated Excel

[mrmarcin12]

Thank you but Im afraid it will not solve my problem. List isn't inside Named Ranges returned by method getNamedRanges as on your example.

[mrmarcin12]

I did it!

I extracted formula from this cell to $range and then I used this formula to random cell. Then I used method setCellValue with this formula, then I read it using calculateCellValue to get array!

@MarkBaker thank you for support, I even didn't need to set Calculation::setArrayReturnType(Calculation::RETURN_ARRAY_AS_VALUE)

    $range = $spreadsheet->getNamedFormula('Disc');

    $spreadsheet->getActiveSheet()->setCellValue('C100', '=' . $range->getFormula());
    var_dump(Calculation::getInstance($spreadsheet)->calculateCellValue($spreadsheet->getActiveSheet()->getCell('C100')));

[adpstore]

hi, i have the same problem but when i try to getNamedFormula i get NULL
my code:

$inputFileName = 'Template/file.xlsx';
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
$sheet = $spreadsheet->getSheet(2);  
$validation = $sheet->getCell('F3')->getDataValidation();
var_dump($validation)
$name = $validation->getFormula1();

//$range = $spreadsheet->getNamedFormula('A_46dcadba550817f2');
var_dump($spreadsheet->getNamedFormula($name));

here my output:
object(PhpOffice\PhpSpreadsheet\Cell\DataValidation)#431205 (14) { ["formula1":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=> string(18) "A_46dcadba550817f2" ["formula2":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=> string(0) "" ["type":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=> string(4) "list" ["errorStyle":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=> string(0) "" ["operator":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=> string(0) "" ["allowBlank":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=> bool(true) ["showDropDown":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=> bool(true) ["showInputMessage":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=> bool(false) ["showErrorMessage":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=> bool(true) ["errorTitle":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=> string(0) "" ["error":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=> string(0) "" ["promptTitle":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=> string(0) "" ["prompt":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=> string(0) "" ["sqref":"PhpOffice\PhpSpreadsheet\Cell\DataValidation":private]=> string(9) "F3:F10003" }
NULL

thank you

[MarkBaker]

hi, i have the same problem but when i try to getNamedFormula i get NULL

That's because A_46dcadba550817f2 is a NamedRange, not a NamedFormula

Use the getDefinedName() method instead; then the DefinedName object that's returned will tell you whether it's a NamedRange or a NamedFormula
The object has an isFormula() method that will tell you whether it's a DefinedName or a DefinedFormula

[adpstore]

Thank you!!!! everythings works fine!!!!!

[josseve]

Hi for me doesnt work....
i have multi values in combobox, i try to read it, i can read only first value

$reader2 = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
$reader2->setReadDataOnly( false );
$spreadsheet2 = $reader2->load($inputFileName);
$spreadsheet2->setActiveSheetIndex(2);
$spreadsheet2->getActiveSheet()->getProtection()->setSheet(false);
$worksheet2 = $spreadsheet2->getActiveSheet();

$Formula1 = $worksheet2->getCell("E2")->getDataValidation()->getFormula1();
var_dump($Formula1); //Values_0_8_0_0
$getNamedRange = $spreadsheet2->getNamedRange($Formula1);
$range = $getNamedRange->getValue();
var_dump($range); //$A$1:$A$9

$worksheet2->setCellValue('J25000', '=' . $Formula1);
$retVal = $worksheet2->getCell('J25000')->getValue();
var_dump($retVal); //string(37) "=Values_0_8_0_0"
$retVal = $worksheet2->getCell('J25000')->getCalculatedValue();
var_dump($retVal); //string(24) "only_one_value_of_combobox"

So i try function to recover other values of combobox...
$worksheet2->setCellValue('K25000', '=JOINDRE(' . $Formula1.';"|")');
$retVal = $worksheet2->getCell('K25000')->getValue();
var_dump($retVal);//string(37) "=JOINDRE(Values_0_8_0_0;"|")"
$retVal = $worksheet2->getCell('K25000')->getCalculatedValue();
var_dump($retVal);//Error "Formula Error: An unexpected error occurred"

I try "=JOINDRE(' . $range.';"|")" same error

When i put this in excel.. this work...
someone have an idea?

Thanks

[josseve]

Ok thats works whith the name of ActiveSheet before the formula
$worksheet2->setCellValue('F30001', '=NameOfMySheet!' . $Formula1);
$arrayValues = \PhpOffice\PhpSpreadsheet\Calculation\Calculation::getInstance($spreadsheet2)->calculateCellValue($spreadsheet2->getActiveSheet()->getCell('F30001'));