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

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

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 22 (3 by maintainers)

Commits related to this issue

Data Validations Referencing Another Sheet See issues #1432 and #2149. Data validations on an Xlsx worksheet can be specified in two manners - one (henceforth "internal") if a list is specified from ... — committed to oleibman/PhpSpreadsheet by oleibman 3 years ago
Data Validations Referencing Another Sheet (#2265) See issues #1432 and #2149. Data validations on an Xlsx worksheet can be specified in two manners - one (henceforth "internal") if a list is specifi... — committed to PHPOffice/PhpSpreadsheet by oleibman 3 years ago

Most upvoted comments

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.

zzhovo on May 31, 2020

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

tntsoft on Aug 3, 2021

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 : https://github.com/PHPOffice/PhpSpreadsheet/issues/388 https://github.com/PHPOffice/PhpSpreadsheet/issues/991 https://github.com/PHPOffice/PhpSpreadsheet/issues/1432

otargetj2s on Jun 9, 2021

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.

tiggeymone on Jun 6, 2020

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

DeekshaPrabhub96 on Nov 3, 2021

Fixed in 1.19.0

PowerKiKi on Oct 31, 2021

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.

eugenio11 on Apr 14, 2021

1.12.0

still doesn’t work

zzhovo on Apr 30, 2020