.xlsx data validation - PHPSpreadsheet expects operator, but Excel omits it for default "between"

[jonas-grobe]

This is a bug report.

If I save a file as .xlsx in Excel (I am using the most recent version, 2312) and I use a "between" data validation, this is how Excel saves the validation:

<dataValidation type="whole" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="A1" xr:uid="{465FAF3B-BCA5-447F-AA54-1A5FA9E135C4}">

No operator="between" like Excel would add for other operators and PHPSpreadsheet seems to expect.

What is the expected behavior?

I think if no operator is given, "between" should be assumed.
From the xlsx spec:

<xsd:attribute name="operator" type="x:ST_DataValidationOperator" use="optional" default="between"/>

What is the current behavior?

$dataValidation['operator'] is null and gets parsed into an empty string "".

What are the steps to reproduce?

min_example_validation_between.xlsx

<?php

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

use PhpOffice\PhpSpreadsheet\Cell\DataValidation;

$file = "min_example_validation_between.xlsx";
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($file);
$sheet = $spreadsheet->getSheetByName('01');
$validation_type = $sheet->getCell("A1")->getDataValidation()->getOperator();
var_dump(DataValidation::OPERATOR_BETWEEN); // "between"
echo "<br>";
var_dump($validation_type); // "", expected: "between"
echo "<br>";
var_dump($validation_type == DataValidation::OPERATOR_BETWEEN); // false, expected: true

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

.xlsx is affected, didn't try others yet.

Which versions of PhpSpreadsheet and PHP are affected?

I am using PHPSpreadsheet 1.29.0 with PHP 8.0.12