Xls and Xlsx readers miss data validation for files saved with Excel

[Dfred]

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 is the expected behavior?

Readers should be able to read cells' DataValidation info from .xls and .xlsx files wether or not they are generated by MS-Excel or PhpSpreadsheet.

What is the current behavior?

Currently, xls and xlsx files generated with PhpSpreadsheet, then opened and saved with MS-Excel are missing all cross-sheet DataValidation of the form "sheet_name!ref:ref" .
In this case, calling getDataValidationCollection() will only return correct DataValidation for cells in the target sheet.

What are the steps to reproduce?

<?php

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

function test_save(
  $fileFormat   = 'Xls',                  // change to .Xlsx to make this example work.
  $test_cellRef = 'A1',
  $fileName     = 'dataValidationBug.' )
{
  $fileName .= strtolower($fileFormat);
  
  // Create new Spreadsheet object with 2 cells in 2nd sheet, then create a dropDown in each sheet. 
  $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
  $worksheet1 = $spreadsheet->getActiveSheet();
  $worksheet2 = $spreadsheet->createSheet()->setTitle('Worksheet2');
  $worksheet3 = $spreadsheet->createSheet()->setTitle('Worksheet3');

  $worksheet2->getCell('B1')->setValue(5);
  $worksheet2->getCell('B2')->setValue(6);
  foreach ($spreadsheet->getWorksheetIterator() as $ws) {
    $ws->getCell($test_cellRef)->getDataValidation()->
          setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST)->
          setFormula1($worksheet2->getTitle() . '!B1:B2')->
          setShowDropDown(TRUE);
    $ws->getCell($test_cellRef.'0')->getDataValidation()->
          setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST)->
          setFormula1($worksheet2->getTitle() . '!$B1:$B2')->
          setShowDropDown(TRUE);
  }
  // save the file.
  $writerClass = "\\PhpOffice\\PhpSpreadsheet\\Writer\\$fileFormat";
  $writer = new $writerClass($spreadsheet);
  $writer->save($fileName);               // the file is as expected with Excel.
  echo ">>> wrote\t $fileName with ".get_class($writer)." ===\n";
}

// if loading an Xls file saved by PhpSpreadsheet, all data is read as expected.
// if loading an Xls file saved by Microsoft Excel, it warns of dropped functionality: all cross-sheet refs in validation are discarded. 
// if loading an Xlsx file saved by PhpSpreadsheet, all data is read as expected.
// if loading an Xlsx file saved by Microsoft Excel, all cross-sheet refs in validation are ignored. 
function test_load(
  $fileFormat   = 'Xls',                  // change to .Xlsx to make this example work.
  $test_cellRef = 'A1',
  $fileName     = 'dataValidationBug.' )
{
  $fileName .= strtolower($fileFormat);
  echo "\n<<< reading\t $fileName ===\n";
  foreach (\PhpOffice\PhpSpreadsheet\IOFactory::load($fileName)->getWorksheetIterator() as $ws) {
    echo "\n - sheet {$ws->getTitle()} - ";
    echo "DataValidationCollection: "; var_dump($ws->getDataValidationCollection());
  }
}

// run the test with Xls format
test_save();
test_load();

// run the test with Xls format
test_save('Xlsx');
test_load('Xlsx');

Which versions of PhpSpreadsheet and PHP are affected?

development branch, php 5.6, MS Excel Office 2010

[PowerKiKi]

Could you suggest a PR for that ?

[Dfred]

that's planned, but can't do it before April!

[Dfred]

So the error comes from simplexml_load_string() failing to read Microsoft extensions (see https://msdn.microsoft.com/en-us/library/dd905242(v=office.12).aspx)

In my case the xml looks like this:

<extLst>
  <ext uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
    <x14:dataValidations count="1" xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
      <x14:dataValidation type="list" operator="equal" allowBlank="1" showErrorMessage="1">
        <x14:formula1>
          <xm:f>myWorksheet1!$A$13:$C$13</xm:f>
        </x14:formula1>
        <xm:sqref>E4:F4</xm:sqref>
      </x14:dataValidation>
    </x14:dataValidations>
  </ext>
</extLst>
</worksheet>

The call only loads the uri attribute of the ext element.

[stale]

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.

[billblume]

FYI. I am still encountering this issue. The provided fix and pull request does work for me.

[PowerKiKi]

The PR still misses tests to be merged, consider contributing some if you can.

[diego-sorribas]

I think there is a problem with that code, if I dont have data validations on the same sheet it not going to enter in the if condition. I think that it must be outside of the if condition on line 1337.

[Dfred]

Can you provide more context (branch, commit ID, ...) about the code you're mentioning, as well as a test for this case?

[diego-sorribas]

Im talking about this line

I think this works if you dont have data validation in the same sheet

<?php
if (isset ($xmlSheet->extLst, $xmlSheet->extLst->ext, $xmlSheet->extLst->ext['uri'])
	 && $xmlSheet->extLst->ext['uri'] == "{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" ) {

// Create dataValidations node if does not exists, maybe is better inside the foreach ?
if(!$xmlSheet->dataValidations) {
	$xmlSheet->addChild('dataValidations');
}

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);
	}
  }
}

if ($xmlSheet && $xmlSheet->dataValidations && !$this->readDataOnly) {
 // Continue PhpSpreadhseet code....
 }

[imrohan22]

Make sure there is no space in your Sheet name in setFormula1 function call, otherwise this fix won't work.

[nooraldeen-albakri]

Im talking about this line

I think this works if you dont have data validation in the same sheet

<?php
if (isset ($xmlSheet->extLst, $xmlSheet->extLst->ext, $xmlSheet->extLst->ext['uri'])
	 && $xmlSheet->extLst->ext['uri'] == "{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}" ) {

// Create dataValidations node if does not exists, maybe is better inside the foreach ?
if(!$xmlSheet->dataValidations) {
	$xmlSheet->addChild('dataValidations');
}

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);
	}
  }
}

if ($xmlSheet && $xmlSheet->dataValidations && !$this->readDataOnly) {
 // Continue PhpSpreadhseet code....
 }

thanks, worked successfully.

[gil04]

Did someone created a pull request for this? Cause with the actual version of PHPOffice/PhpSpreadsheet this code is not included and I also still have this problem.

[kgbph]

Any updates about this?

[oleibman]

Fix was provided by PR #3681 in August 2023.