Adding DataValidation caused the generated excel to be corrupted

[leonarta7]

This is:

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

What is the expected behavior?

Excel generated with the proper DataValidation

What is the current behavior?

The generated excel can't be open with this error message

We found a problem with some content in 'Import_User_2021-11-22_14-31-44.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

What are the steps to reproduce?

After several tinkering, it's not the format of the validation that causes the error, but simply calling the getDataValidation() method cause the excel to get corrupted

$validation = $spreadsheet->getActiveSheet()->getCell('A1')->getDataValidation();

Commenting this line will make the excel works normally.

Temporary Solution

Downgrading to 1.18.0 makes the DataValidation works again.

Some Troubleshooting

Based on what I read in #290, I extracted the excel and check the sheet's xml. Comparing it to the working excel, it seems the datavalidation segment got written twice, before and after footer.

-<extLst>
    -<ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}">
      -<x14:dataValidations xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main" count="1">
        -<x14:dataValidation prompt="Silahkan pilih cabang pada list" promptTitle="Pilih dari list cabang" error="Value is not in list." errorTitle="Input error" showErrorMessage="1" showInputMessage="1" showDropDown="0" allowBlank="1" operator="between" errorStyle="information" type="list">
          -<x14:formula1>
            <xm:f>branch_level!A1:A2</xm:f>
          </x14:formula1>
          <xm:sqref>A2</xm:sqref>
        </x14:dataValidation>
      </x14:dataValidations>
    </ext>
</extLst>
<printOptions gridLinesSet="true" gridLines="false"/>
<pageMargins footer="0.3" header="0.3" bottom="0.75" top="0.75" right="0.7" left="0.7"/>
<pageSetup pageOrder="downThenOver" fitToWidth="1" fitToHeight="1" scale="100" orientation="default" paperSize="1"/>
-<headerFooter alignWithMargins="true" scaleWithDoc="true" differentFirst="false" differentOddEven="false">
  <oddHeader/>
  <oddFooter/>
  <evenHeader/>
  <evenFooter/>
  <firstHeader/>
  <firstFooter/>
</headerFooter>
-<extLst>
  -<ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}">
    -<x14:dataValidations xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main" count="1">
      -<x14:dataValidation prompt="Silahkan pilih cabang pada list" promptTitle="Pilih dari list cabang" error="Value is not in list." errorTitle="Input error" showErrorMessage="1" showInputMessage="1" showDropDown="0" allowBlank="1" operator="between" errorStyle="information" type="list">
        -<x14:formula1>
          <xm:f>branch_level!A1:A2</xm:f>
        </x14:formula1>
        <xm:sqref>A2</xm:sqref>
      </x14:dataValidation>
    </x14:dataValidations>
  </ext>
</extLst>

Hope this helps.

Which versions of PhpSpreadsheet and PHP are affected?

PHPSpreadsheet 1.19.0 and PHP 7.4

[Hypher]

Same here.

Affects all files with validation, even a file with validation embedded, read and wrote back using phpspreadsheet without even modifying or accessing validation rules.

Note the downgrade from 1.19 to 1.18 needs to downgrade two other pacages:

phpoffice/phpspreadsheet 1.19.0 => 1.18.0
markbaker/complex 3.0.1 => 2.0.3
markbaker/matrix 3.0.0 => 2.1.3

With composer:

composer require "markbaker/complex:2.0.3" "markbaker/matrix:2.1.3" "phpoffice/phpspreadsheet:1.18"

[oleibman]

Closing as duplicate of #2368. The problem is now fixed on master.