setDataValidation Critical Error in PROTECTION_UNPROTECTED

[sysmaya]

This is: A Bug (Error) Report

What is the expected behavior?

I create a DataValidation only for cell A1. I add protection to the sheet. There should only be validation in A1

What is the current behavior?

I create a DataValidation only for cell A1. I add protection to the sheet. The DataValidation is mistakenly copied to the unprotected cells.

What are the steps to reproduce?

<?php

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

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

//create sheet 2                                             
$objWorksheet2 = $spreadsheet->createSheet();
$objWorksheet2->setTitle('Logros');
$spreadsheet->setActiveSheetIndex(1)->setCellValue("A1", 'Logro1');
$spreadsheet->setActiveSheetIndex(1)->setCellValue("A2", 'Logro2');
$spreadsheet->setActiveSheetIndex(1)->setCellValue("A3", 'Logro3');
$spreadsheet->setActiveSheetIndex(1)->setCellValue("A4", 'Logro4');
$spreadsheet->setActiveSheetIndex(1)->setCellValue("A5", 'Logro5'); 

// Set validation data ONLY cell A1
$spreadsheet->setActiveSheetIndex(0);
$objValidation = $spreadsheet->getActiveSheet()->getCell("A1")->getDataValidation();
$objValidation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST  );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Ese ID de Logro no esta en la lista');
$objValidation->setPromptTitle('Seleccione ID Logro');
$objValidation->setPrompt('En la Hoja Logros esta la definicion.');
$objValidation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );

$objValidation->setFormula1('Logros!$A$1:$A$5');
$spreadsheet->getActiveSheet()->getCell("A1")->setDataValidation($objValidation);

// Set pasword and unprotect cells
$spreadsheet->setActiveSheetIndex(0);
$spreadsheet->getActiveSheet()->getProtection()->setSheet(true)->setPassword('myPasword');

$spreadsheet->getActiveSheet()->getStyle("A1")
              ->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_UNPROTECTED);
$spreadsheet->getActiveSheet()->getStyle("B1:B3")
              ->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_UNPROTECTED);

$spreadsheet->setActiveSheetIndex(0); 

//error  PROTECTION_UNPROTECTED create and activate datavalidation other cells            

$writer = new Xls($spreadsheet);
$writer->save('helloworld7.xls');`

Which versions of PhpSpreadsheet and PHP are affected?

1.15.0 - 2020-10-11

[oleibman]

I am not able to duplicate your problem with the code above and the current version of PhpSpreadsheet - the output file is as expected, with A1 using data validation, and B1:B3 not, with the rest of the sheet locked. Do you still see the problem with the current version?

[sysmaya]

I just put the validation rule for A1. The error is that adjacent cells show the same validation rule. See attached image

$spreadsheet  = new Spreadsheet(); 
$sheet1 = $spreadsheet->getActiveSheet(); 

$sheet1->setCellValue("B1", 'xx');
$sheet1->setCellValue("B2", 'yy');     

$validation = $sheet1->getCell('A1')->getDataValidation();
$validation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
$validation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
$validation->setAllowBlank(false);
$validation->setShowInputMessage(true);
$validation->setShowErrorMessage(true);
$validation->setShowDropDown(true);
$validation->setErrorTitle('Input error');
$validation->setError('Value is not in list.');
$validation->setPromptTitle('Pick from list');
$validation->setPrompt('Please pick a value from the drop-down list.');
$validation->setFormula1('"Item A,Item B,Item C"');

$sheet1->getCell("A1")->setDataValidation($validation);                 

$writer = new Xls($spreadsheet);
$writer->save('helloworld7.xls');

Why is the validation rule displayed in column B?

I install the latest version 1.16 from: https://php-download.com/package/phpoffice/phpspreadsheet

[oleibman]

Sorry, I am still unable to duplicate your symptom. In Excel, I see the dropdown in A, as expected. I see the same in LibreOffice Calc. You are using OpenOffice Calc, which I do not have installed. Is it possible for you to see how your file looks in Excel or LibreOffice?

Here's B:

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue for you, please try to help by debugging it further and sharing your results. Thank you for your contributions.

[oleibman]

Unable to duplicate. No update to ticket since June. Closing.