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,

PhpSpreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php

Line 787 in ac7fb4a

if ($xmlSheet && $xmlSheet->dataValidations && !$this->readDataOnly) {

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,

PhpSpreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php

Line 787 in ac7fb4a

if ($xmlSheet && $xmlSheet->dataValidations && !$this->readDataOnly) {

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]

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)

[otargetj2s]

UP !

Hello,
This regression was reported in early 2018.
The correction code is provided.
In the end, this code is still not merged.
Can you do what's nessessary? I absolutely need it for my development.
Thanks.

See links :
#388
#991
#1432

[pich]

I'm also on the waitlist. Any update on that?

[tntsoft]

Let's get this fixed, please! It's been a while!

[oleibman]

I do not understand enough about this problem to work on it. But perhaps I can make a suggestion that will resonate with someone. The code to read the data validations from the alternate location in Xlsx Reader was merged some time ago, and I believe it is working correctly. I think the problem is happening in Xlsx writer. Xlsx Reader can now read from:

<extLst>
<ext uri=...>
<x14:dataValidations count=...>
<x14:dataValidation type="list" ...>
<x14:formula1><xm:f>Sheet2!$D$3:$D$4</xm:f></x14:formula1>
<xm:sqref>...</xm:sqref>
</x14:dataValidation>
</x14:dataValidations>
</ext>
</extlst>

That's how it reads the list from another sheet. But, from the same sheet, it reads:

<dataValidations count="1">
<dataValidation type="list" ... sqref="A2" xr:uid="{63893217-F310-4747-9BDC-1350626D1438}">
<formula1>$D$3:$D$4</formula1>
</dataValidation>
</dataValidations>

That's how it reads the list from the same sheet. (Just for clarity's sake, no extLst, no ext, no namespaces, except for xr:uid, which appears to not be required.) I don't know if the reader can handle both formats on the same worksheet.

At this point, I'll add that I haven't seen what happens when you have both types of list. I also don't know what happens when you combine the first format with other things that require extLst (e.g. conditional formatting). Nevertheless, the solution might be to have the writer generate the appropriate format for each data validation if you can figure out what the appropriate format should be in each case.

[oleibman]

I have tried changing Xlsx writer to use extLst in this case. Excel complains "Replaced Part: /xl/worksheets/sheet1.xml part with XML error. Load error. Line 2, column 0." Nevertheless, that part loads successfully as Xml, and looks pretty much identical to the corresponding part in the input dataset. No idea how to proceed without better diagnostics from Excel.

[oleibman]

Aha! I found a helpful comment in Writer\Xlsx\Worksheet.

        // ConditionalFormattingRuleExtensionList
        // (Must be inserted last. Not insert last, an Excel parse error will occur)

I am as baffled by this requirement as the original author for Conditional Formatting must have been. But ... when I use extLst to write data validations after everything else, Excel does read it correctly. There are still lots of edge cases to worry about.

• Can you write "internal" data validations in extLst section?
• If not, how do you distinguish internal from external? Easy if the sheet name is part of the formula or not present at all, not so easy if, e.g., a defined name is used for the list, or a more complicated formula.
• Is there an ordering requirement for sheets that have data validations and conditional formatting? (And, apparently, data bars with conditional formatting are a separate case altogether.)

So, progress, but lots more work to go before slaying this one.

[oleibman]

It does look like "internal" data validations can be placed in extLst, so that makes things a little less complicated.

[mupic]

Version 1.18 - Doesn't work.
Version 1.17 - Works well.

[oleibman]

PR 2265 has been applied only in master, if you want to test with that. I am unsure of when the next official release will happen.

[PowerKiKi]

Fixed in 1.19.0

[DeekshaPrabhub96]

Facing the Same issue with 1.19.

Steps to recreate,

Create a dummy excel with a data validation of type List,
Read this file using PHPSpreadsheet and save the file with a different name.
The output excel is corrupted and MSExcel tries to recover it and deletes the Sheet.

$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($filePath);
$spreadsheet->getCalculationEngine()->suppressFormulaErrors=true;
$writer = new Xlsx($spreadsheet);
$writer->save('test.xlsm');

[mupic]

@DeekshaPrabhub96 I got it broken in ms office 2007 version, have you tried opening the file in newer versions? For example, you can open it in Google Docs, it worked for me.

[DeekshaPrabhub96]

Yes Working Fine with Google Docs but same issue with latest version of MSExcel (16.54).