Data validation produces unreadable file on 1.19

[ionutantohi]

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?

Data validation of type LIST to be applied on a range of cells and the file to opened by Excel. The below example works on 1.18

What is the current behavior?

Generated file can not be opened by excel

What are the steps to reproduce?

<?php

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

$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

$validation = $sheet->getDataValidation('A1:A10');

$validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
$validation->setShowDropDown(true);
$validation->setFormula1('"Option 1, Option 2"');

(new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet))->save('---your-path---');

Which versions of PhpSpreadsheet and PHP are affected?

1.19

[oleibman]

PR #2265 moved the place where data validations were written to the worksheet. PR #1694 was installed afterwards, and accidentally restored the original location, so validations are now being written twice. This is what is causing the error in Excel. PR on the way.

[oleibman]

Fixed in master. Please test if possible.

[mupic]

@oleibman Installed from the master, in MS Excel 2007 it no longer displays an error for me, but the validation does not work either. In Google Docs, validation works as expected.
Also, as I understood there was a problem in MS Excel 2021 (16.54). #1432

[ionutantohi]

In my case, the fix on master fixes the issue described above.

[jacques]

Tested an exported file opens fine on older versions of Excel for Windows and on Excel for Mac version 16.53.

[jacques]

Will you be releasing a 1.19.1 release of phpspreadsheet?

[DeekshaPrabhub96]

I am still facing issue with XLSM file type and MS Excel for Mac version 16.54.

I am opening an XLSM file and updating a cell value with text and saving it as different file.

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

[oleibman]

I will have a new PR within the next couple of days.

[oleibman]

I believe the PR 2375 should address the problems reported with some Excel versions since PR 2369. I don't have access to all those versions. If some of you who have problems can test against 2375, it would be helpful to know those results before 2375 is merged.

[oleibman]

No, there's a problem with that PR. It'll take a day or two to resolve. Sorry for the delay.

[DeekshaPrabhub96]

I am observing another issue with Data Validation.
Steps to reproduce:
Create an xlsx. Add a simple List type data validation from A1 to A5, A10 to A14, A20 to A24. Ensure they are the same data validation with same values.
Open this file using PHPSpreadsheet and save it with a different file name. Only one set of DataValidations will be available in the new excel(A1 to A5)

I have attached the sample xlsx(input and output) and the screenshot of xml tags from sheet1.xml. The xml doesn't have multiple entries for individual sets of validation.

DataValidationIssueInput.xlsx
DataValidationIssueOutput.xlsx

[oleibman]

Thank you for the sample files. I believe this problem has existed all along, and is not a regression. The input file declares a list of ranges for a single Data Validation entry, but PhpSpreadsheet is expecting only a single range. I will look into that.

[oleibman]

No, I am mistaken, the read routine is accounting for multiple ranges correctly. The write routine is trying to merge ranges in the Data Validation Collection, and that's where this problem is occurring. Investigating.

[oleibman]

The sqref field (the range for the validation) is not being used to compute the hash value for a Data Validation object, but it must be used to avoid merging distinct ranges. I will have a fix within a day or two.

[oleibman]

I believe my latest push (#2377) should address this problem, as well as the failure of some versions of Excel to read the Data Validations correctly. Please test if possible and let me know your results.

[DeekshaPrabhub96]

Awesome that was quick. This fixed the issue.

Thank you for the quick solution.

[ricardoee]

When is the next release expected?

[EricSocs]

Fix working for me. Using dev-master in my composer.json until new release.

Given the following code:

        $validation = $this->spreadsheet
            ->getActiveSheet()
            ->getCell(Coordinate::stringFromColumnIndex($column) . ($row + 1))
            ->getDataValidation();

        $validation->setType(DataValidation::TYPE_LIST);
        $validation->setErrorStyle(DataValidation::STYLE_INFORMATION);
        $validation->setAllowBlank($false);
        $validation->setShowInputMessage(true);
        $validation->setShowErrorMessage(true);
        $validation->setShowDropDown(true);
        $validation->setErrorTitle('');
        $validation->setError("Cell must be y or n");
        $validation->setPromptTitle('');
        $validation->setPrompt("Please select y or n");
        $validation->setFormula1('"y,n"');

dev-master produces the following data validation. The resulting file can be open in Excel v16.54 without error and with working drop downs:

<dataValidations count="1">
    <dataValidation type="list" 
                    errorStyle="information" 
                    operator="between" 
                    allowBlank="0" 
                    showDropDown="0"
                    showInputMessage="1" 
                    showErrorMessage="1" 
                    error="Value is not y or n" 
                    prompt="y or n"
                    sqref="B5:B28">
        <formula1>&quot;y,n&quot;</formula1>
    </dataValidation>
</dataValidations>

v1.9 produces the following, which makes the file fail in Excel v16.54 (error when opening, drop downs stripped out):

<x14:dataValidations count="1" 
                     xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
    <x14:dataValidation type="list" 
                        errorStyle="information" 
                        operator="between" 
                        allowBlank="0" 
                        showDropDown="0"
                        showInputMessage="1" 
                        showErrorMessage="1" 
                        error="Value is not y or n" 
                        prompt="y or n">
        <x14:formula1>
            <xm:f>&quot;y,n&quot;</xm:f>
        </x14:formula1>
        <xm:sqref>B5:B28</xm:sqref>
    </x14:dataValidation>
</x14:dataValidations>

[VincentLanglet]

Awesome that was quick. This fixed the issue.

Thank you for the quick solution.

I think this issue should be closed then.

@PowerKiKi Do you have time to release the 1.19.1 ? Thanks :)

[PowerKiKi]

Fixed via da5c2b1, and released as 1.20.0