Phpspreadsheet: Data validation not working when opening & saving a file

[tntsoft]

This is:

- [x ] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What am I trying to accomplish?

I want to open an XLSX file that has some List Dropdown validation rules in it, write some data in it and save it.

What is the expected behavior?

When opening and saving an Excel file, it should retain all the data validation inside it.

What is the current behavior?

It loses all data validation fields ( dropdowns, explanations etc)

What are the steps to reproduce?

Open the bellow excel file with data validation -> save it -> it loses validation.

Sample XLSX file to help reproduce

sample.xlsx

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php

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

use PhpOffice\PhpSpreadsheet\IOFactory;

// Declare the path of the file
$inputFileName = __DIR__ . '/sample.xlsx';

// Load the file
$spreadsheet = IOFactory::load($inputFileName);

// Get first sheet in preparation for changes
$sheetData = $spreadsheet->getSheet(0);

// Create an XLSX writer
$writer = IOFactory::createWriter($spreadsheet, "Xlsx");

// Save the file
$writer->save("05featuredemo.xlsx");

Which versions of PhpSpreadsheet and PHP are affected?

"phpoffice/phpspreadsheet": "^1.11"
PHP 7.3

[tntsoft]

No suggestions, anybody?

[zzhovo]

1.12.0

still doesn't work

[PowerKiKi]

Try to open-and-save without doing anything at all in between. Then unzip both xlsx files and compare the content of each files. Can you pinpoint what changed?

Second question, are you able to create a file from scratch, create the validation and save to for properly?

[zzhovo]

Look at this line, https://github.com/PHPOffice/PhpSpreadsheet/blob/ac7fb4a31d16cec9ee8313cd7ffe357bc9039447/src/PhpSpreadsheet/Reader/Xlsx.php#L787

it is expected from the $xmlSheet to have validations, but sometimes the validations are here $xmlSheet->extLst->ext->children('x14', TRUE)->dataValidations->dataValidation

so I've solved the problem by adding this code just before the line mentioned

// handle Microsoft extension if present
if (isset ($xmlSheet->extLst, $xmlSheet->extLst->ext, $xmlSheet->extLst->ext['uri'])
    && $xmlSheet->extLst->ext['uri'] == "{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" )
{
    if( ! $xmlSheet->dataValidations ){
        $xmlSheet->addChild('dataValidations');
    }
    // retreive MS extension data to create a node that matches expectations.
    foreach ($xmlSheet->extLst->ext->children('x14', TRUE)->dataValidations->dataValidation as $item)
    {
        $node = $xmlSheet->dataValidations->addChild('dataValidation');
        foreach ($item->attributes() as $attr)
            $node->addAttribute($attr->getName(), $attr);
        $node->addAttribute('sqref', $item->children('xm',TRUE)->sqref);
        $node->addChild('formula1', $item->formula1->children('xm',TRUE)->f);
    }
}

which checks for other possible validations to exist and if so, it creates the validations node and then clones the other validations into it.

I think this is just a workaround that works for my current project, so I'll not suggest this as a patch.

[tiggeymone]

Look at this line,

https://github.com/PHPOffice/PhpSpreadsheet/blob/ac7fb4a31d16cec9ee8313cd7ffe357bc9039447/src/PhpSpreadsheet/Reader/Xlsx.php#L787

it is expected from the $xmlSheet to have validations, but sometimes the validations are here $xmlSheet->extLst->ext->children('x14', TRUE)->dataValidations->dataValidation

so I've solved the problem by adding this code just before the line mentioned

// handle Microsoft extension if present
if (isset ($xmlSheet->extLst, $xmlSheet->extLst->ext, $xmlSheet->extLst->ext['uri'])
  && $xmlSheet->extLst->ext['uri'] == "{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" )
{
  if( ! $xmlSheet->dataValidations ){
      $xmlSheet->addChild('dataValidations');
  }
  // retreive MS extension data to create a node that matches expectations.
  foreach ($xmlSheet->extLst->ext->children('x14', TRUE)->dataValidations->dataValidation as $item)
  {
      $node = $xmlSheet->dataValidations->addChild('dataValidation');
      foreach ($item->attributes() as $attr)
          $node->addAttribute($attr->getName(), $attr);
      $node->addAttribute('sqref', $item->children('xm',TRUE)->sqref);
      $node->addChild('formula1', $item->formula1->children('xm',TRUE)->f);
  }
}

which checks for other possible validations to exist and if so, it creates the validations node and then clones the other validations into it.

I think this is just a workaround that works for my current project, so I'll not suggest this as a patch.

Thank you man! I put your changes in a "patches" folder, overriding through composer the default class autoload behavior for this specific class. It works like a charm. I also fixed the composer dependency to the current version, to avoid breaking changes. I hope it could be fixed in the near future.

[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.

[eugenio11]

Hello,
has this fix been implemented in the latest release of PhpSpreadsheet?
I've just installed it, I have Data validation list with values loaded from another sheet and those lists are lost after opening with PhpSpreadsheet.

Thanks.

[tntsoft]

Thank you @eugenio11!

[eugenio11]

More precisely, what happens in my case is:

• data validation lists with values coming from another sheet are not preserved
• "hardcoded" data validation lists are preserved but, for some reason, in the wrong cell (correct col, wrong row)