can't get dropdown list values in xlsx

[adpstore]

This is:

- a bug report or a feature request

i need to get values of dropdown list in xlsx file
but with

$test = $validation->getFormula1();
i get only the nameof the dropdown but no values

in this link xlsx image https://i.stack.imgur.com/mJ73K.png
and here Inserimento_Personal Computer (acquisto).xlsx the intrested file

What are the steps to reproduce?

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

// add code that show the issue here...
$inputFileName = 'file.xlsx';
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
$sheet = $spreadsheet->getSheet(2);

$validation = $sheet->getCell('F3')
->getDataValidation();
$test = $validation->getFormula1();
$test1 = $validation->getFormula2();

What features do you think are causing the issue

• [ x] Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

xlsx files

Which versions of PhpSpreadsheet and PHP are affected?

php 7.4 --- PhpSpreadsheet 1.25.2

[MarkBaker]

DataValidations (dropdowns) don't have a name. What you find in $test is a Named Range for the list expression. The getFormula() method actually returns a formula in this case.

You will need to identify that this is a list dropdown; that the formula matches a Defined Name; retrieve that defined name and verify whether it is a Named Range or a Named Formula; and evaluate the formula to get the actual list of values if it is a Formula, or access the cells referenced in the range if it is a Named Range.

PhpSpreadsheet does not provide a direct method for retrieving the dropdown list values, because a DataValidation might not be a list, but you might read the linked discussion that described how to retrieve the list for a DataValidation

Discussion #2963 - How can I read dropdown from excel and get all options list? - provides some basic explanation showing how to do all this

[adpstore]

hi, i have this problem :
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]

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

[MarkBaker]

Or you can even bypass the DefinedName checks, and let the Calculation Engine handle that by treating formula1 as an actual formula:

$sheet = $spreadsheet->getSheet(2);
$validation = $sheet->getDataValidation('F3');
$validationFormula = $validation->getFormula1();

$calculator = Calculation::getInstance($spreadsheet);
$result = $calculator->_calculateFormulaValue('=' . $validationFormula, 'A1', $sheet->getCell('A1'));

var_dump($result);

Using A1 as a dummy "hook". You might also want to flush the calculation cache before afterwards, because this will cache the result as though it was evaluated for cell A1, or use any existing calculated result for cell A1.

[oleibman]

Issue seems answered. Closing.