Bug Report: Data Validation Not Applied or Duplicated in PhpSpreadsheet

[chhameed]

Description:
When adding data validation (drop-down list) using PhpSpreadsheet, the validation either:

1. Does not appear in Excel after saving the file.
2. Is duplicated in the sheet1.xml file inside the .xlsx structure, causing unexpected behavior.

After debugging, it seems that PhpSpreadsheet does not properly handle removing old data validations, which may lead to duplication when re-adding validation to the same cell.

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

// Create new spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Set dropdown values in column B
$sheet->setCellValue('B1', 'Option 1');
$sheet->setCellValue('B2', 'Option 2');
$sheet->setCellValue('B3', 'Option 3');

// Create validation
$validation = $sheet->getCell('A1')->getDataValidation();
$validation->setType(DataValidation::TYPE_LIST);
$validation->setErrorStyle(DataValidation::STYLE_STOP);
$validation->setAllowBlank(false);
$validation->setShowDropDown(true);
$validation->setFormula1('$B$1:$B$3'); // 🔹 Reference for dropdown list

// Save file
$writer = new Xlsx($spreadsheet);
$writer->save('test.xlsx');`

Expected Behavior:

1. The drop-down should appear in A1 referencing B1:B3.
2. No duplicate or entries in sheet1.xml.

Actual Behavior:
The generated sheet1.xml contains duplicate elements, which may cause conflicts in Excel.

1. In some cases, validation does not appear in Excel when opening the file.
2. If setFormula1() is not explicitly set, the validation does not work properly.

Temporary Workaround (Using ZipArchive):

Since PhpSpreadsheet does not expose a removeDataValidation() method, a workaround is to manually edit sheet1.xml inside the .xlsx file using ZipArchive.

$zip = new ZipArchive();
$filePath = 'test.xlsx';

if ($zip->open($filePath) === true) {
    $xml = $zip->getFromName('xl/worksheets/sheet1.xml');

    // Remove duplicate <extLst> validation sections manually
    $xml = preg_replace('/<extLst>.*?<\/extLst>/s', '', $xml, 1);

    $zip->addFromString('xl/worksheets/sheet1.xml', $xml);
    $zip->close();
}

Additional Notes:

• This issue may be related to Excel's extLst schema handling.
• The getDataValidations() or removeDataValidation() methods are missing in PhpSpreadsheet, which may be causing this issue.
• Forking the repo and modifying Worksheet.php might be required to properly remove or replace existing validation instead of adding duplicate entries.
  Would appreciate any insights from the maintainers! 🙌

Link to Example File:

simple_example_debug.xlsx

[oleibman]

Unable to duplicate. I tried your code on each of the 4.0, 3.9, and 1.29 branches, and wound up with a correct spreadsheet in every case.

[chhameed]

I tested it and still encountered the duplicate extLst in the XML file.

If you'd like, I can set up the project and share it with you.

I'm using version 1.19 with PHP 7.2 (I know it's outdated, but it's required for the project).

[oleibman]

I am sorry, I am not able to help with any unsupported versions. You will have to upgrade to at least PhpSpreadsheet 1.29.10, and Php 8.1 (in a pinch Php 7.4, but not for much longer).