[QUESTION] addNamedRange issue after update

[szabizs]

Prerequisites

• [X] Have read and understood: https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/.github/SUPPORT.md
• [X] Checked if your Laravel Excel version is still supported: https://docs.laravel-excel.com/3.1/getting-started/support.html#supported-versions
• [X] Checked that your question isn't already asked before.
• [X] Filled in the entire issue template

Versions

• PHP version: 7.4.12
• Laravel version: 8.16.1
• Package version: 3.1

Description

After upgraing to the newest Laravel and packages, I've lost the functionality of addNamedRange, functionality that was used for a long time, until now. I am generating a sheet for manufacturers that holds all the data for my dropdown list DataValidation::TYPE_LIST

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function(AfterSheet $event){
                $event->getDelegate()->setSheetState(Worksheet::SHEETSTATE_VERYHIDDEN);
            }
        ];
    }

This is great, and populated with hundreds of manufacturers. Now with this method, I am referencing that sheet, all the restrictions work, except de data.

/**
 * validation for manufacturers
 */
for ( $i = 2; $i <= 1500; $i ++ ) {
    $sheet->getCell('C' . $i)->getDataValidation()->setType(DataValidation::TYPE_LIST);
    $sheet->getCell('C' . $i)->getDataValidation()->setErrorStyle(DataValidation::STYLE_STOP);
    $sheet->getCell('C' . $i)->getDataValidation()->setAllowBlank( false );
    $sheet->getCell('C' . $i)->getDataValidation()->setShowInputMessage( true );
    $sheet->getCell('C' . $i)->getDataValidation()->setShowErrorMessage( true );
    $sheet->getCell('C' . $i)->getDataValidation()->setShowDropDown( true );
    $sheet->getCell('C' . $i)->getDataValidation()->setErrorTitle( 'Eroare valoare' );
    $sheet->getCell('C' . $i)->getDataValidation()->setError( 'Valoarea nu este acceptată, nu se află în listă.' );
    $sheet->getCell('C' . $i)->getDataValidation()->setPromptTitle( 'Selectează o valoare din lista predefinită' );
    $sheet->getCell('C' . $i)->getDataValidation()->setPrompt( 'Selectează producătorul.' );
    $sheet->getCell('C' . $i)->getDataValidation()->setFormula1( 'manufacturers' ); //note this!
}

/** gathering data from sheet at first index and get cells till its last entry. */
$manufacturers = $sheet->getDelegate()->getParent()->getSheetByName('manufacturers');
$sheet->getParent()->addNamedRange(new NamedRange('manufacturers', $manufacturers, "A1:A" . $manufacturers->getHighestRow())); //populator id!

$manufacturers sheet with data.

As you can see, the restrictions are there, but the data fails to populate the dropdown, but the sheet with data is there.

Proof, that the manufacturers sheet is populated, commented out Worksheet::SHEETSTATE_VERYHIDDEN

If I added the validation formula by referencing manually the sheet, it does work.

$sheet->getCell('C' . $i)->getDataValidation()->setFormula1('manufacturers!$A$1:$A$231'); //note this!
# instead of
$sheet->getCell('C' . $i)->getDataValidation()->setFormula1( 'manufacturers' ); //note this!

[szabizs]

LE: I found this in the PhpSpreadsheet documentation, this would be the problem? This is if we want to add manually the $manufacturers.

manuContainer = '"
                    81010 - 3M,
                    80340 - A.I.C,
                    80895 - Anest Iwata,
                    80284 - Antec,
                    80008 - Aral,
                    80910 - Arnott Europe,
                    80705 - ATE,
                    80630 - Audi,
                    80790 - Aurora,
                    80721 - Austroil,
                    80890 - Avanti,
                    80280 - Barum,
                    80331 - Bendix,
                    80019 - Beru,
                    80798 - Bestoil
                "';

                /**
                 * validation for manufacturers
                 */
                for ( $i = 2; $i <= 1500; $i ++ ) {
                    $sheet->getCell('C' . $i)->getDataValidation()->setType(DataValidation::TYPE_LIST);
                    $sheet->getCell('C' . $i)->getDataValidation()->setErrorStyle(DataValidation::STYLE_STOP);
                    $sheet->getCell('C' . $i)->getDataValidation()->setAllowBlank( false );
                    $sheet->getCell('C' . $i)->getDataValidation()->setShowInputMessage( true );
                    $sheet->getCell('C' . $i)->getDataValidation()->setShowErrorMessage( true );
                    $sheet->getCell('C' . $i)->getDataValidation()->setShowDropDown( true );
                    $sheet->getCell('C' . $i)->getDataValidation()->setErrorTitle( 'Eroare valoare' );
                    $sheet->getCell('C' . $i)->getDataValidation()->setError( 'Valoarea nu este acceptată, nu se află în listă.' );
                    $sheet->getCell('C' . $i)->getDataValidation()->setPromptTitle( 'Selectează o valoare din lista predefinită' );
                    $sheet->getCell('C' . $i)->getDataValidation()->setPrompt( 'Selectează producătorul.' );
//                  $sheet->getCell('C' . $i)->getDataValidation()->setFormula1( 'manufacturers' ); //note this!
                    $sheet->getCell('C' . $i)->getDataValidation()->setFormula1($manuContainer); //note this!
                }

It is important to remember that any string participating in an Excel formula is allowed to be maximum 255 characters (not bytes). This sets a limit on how many items you can have in the string "Item A,Item B,Item C". Therefore it is normally a better idea to type the item values directly in some cell range, say A1:A3, and instead use, say, $validation->setFormula1('Sheet!$A$1:$A$3'). Another benefit is that the item values themselves can contain the comma , character itself.

[patrickbrouwers]

I'm not sure how I can help, it sounds like very specific towards PhpSpreadsheet functionality which I'm not familiar with. Perhaps Stackoverflow will be of more help.

[szabizs]

I will reference this issue to the PHPOffice package, since there is a bug.

https://github.com/PHPOffice/PhpSpreadsheet/issues/1739

[patrickbrouwers]

Thanks for linking!

[alanjose333]

Hi I am trying to set a dropdown field with some values in excel export and selected a particular value in the dropdown.But its not working,i am getting this error "Class 'App\Exports\NamedRange' not found". Please help me.Thanks in advance.

[patrickbrouwers]

You have to use the correct class: use PhpOffice\PhpSpreadsheet\NamedRange;