Repository: PHPOffice/PhpSpreadsheet (php)

Issue:
ANCHORARRAY() doen't work in validation
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?
The correct rendered of ANCHORARRAY() in validatios

### What is the current behavior?
When use ANCHORARRAY() in validations, doesn't rendered

$validation->setFormula1('=ANCHORARRAY($B$1)');

![imagen](https://github.com/user-attachments/assets/8596fa9b-4e9e-4512-89c0-2ccad01b973c)

### What are the steps to reproduce?

Please provide a [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) of code that exhibits the issue without relying on an external Excel file or a web server:

```php
<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$sheet = $spreadsheet->getActiveSheet();

$validation = $sheet->getCell("A1")->getDataValidation();

$validation->setType(DataValidation::TYPE_LIST);
$validation->setErrorStyle(DataValidation::STYLE_STOP);
$validation->setAllowBlank(true);
$validation->setShowDropDown(true);
$validation->setFormula1('=ANCHORARRAY($B$1)');

$sheet->getCell("A1")->setDataValidation($validation);

```
If this is an issue with reading a specific spreadsheet file, then it may be appropriate to provide a sample file that demonstrates the problem; but please keep it as small as possible, and sanitize any confidential information before uploading.

### What features do you think are causing the issue

- [ ] Reader
- [ ] Writer
- [ ] Styles
- [x ] Data Validations
- [x ] Formula Calculations
- [ ] Charts
- [ ] AutoFilter
- [ ] Form Elements

### Does an issue affect all spreadsheet file formats? If not, which formats are affected?


### Which versions of PhpSpreadsheet and PHP are affected?


Pull request description:
Allow ANCHORARRAY as Valid DataValidation List
Fix #4197. Overlooked in the introduction of Dynamic Arrays, Data Validation can specify a list to be a result of the spill operator, which is implemented via the ANCHORARRAY function.

It appears that function `DataValidator::isValid` will not work if the list is specified in this manner, nor if it is specified as a defined name. Fixing those situations will be difficult (defined names probably easier than ANCHORARRAY), and there is no reason to delay this change waiting for those to be fixed. I will open a new issue when this PR is merged.

This is:

- [x] a bugfix
- [ ] a new feature
- [ ] refactoring
- [ ] additional unit tests

Checklist:

- [x] Changes are covered by unit tests
  - [ ] Changes are covered by existing unit tests
  - [x] New unit tests have been added
- [x] Code style is respected
- [x] Commit message explains **why** the change is made (see https://github.com/erlang/otp/wiki/Writing-good-commit-messages)
- [ ] CHANGELOG.md contains a short summary of the change and a link to the pull request if applicable
- [ ] Documentation is updated as necessary

### Why this change is needed?

Provide an explanation of why this change is needed, with links to any Issues (if appropriate).
If this is a bugfix or a new feature, and there are no existing Issues, then please also create an issue that will make it easier to track progress with this PR.


Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index c208ec4f8..06a6e2de6 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -19,7 +19,9 @@ and this project adheres to [Semantic Versioning](https://semver.org).
 
 ### Deprecated
 
-- IREADER::SKIP_EMPTY_CELLS - use its alias IGNORE_EMPTY_CELLS instead.
+- IReader::SKIP_EMPTY_CELLS - use its alias IGNORE_EMPTY_CELLS instead.
+- Worksheet::getProtectedCells was deprecated in release 2, but was not properly documented, and not removed in release 3. Use getProtectedCellRanges instead.
+- Writer/Html::isMpdf property was deprecated in release 2, but was not properly documented, and not removed in release 3. Use instanceof Mpdf instead.
 
 ### Moved
 
@@ -35,6 +37,7 @@ and this project adheres to [Semantic Versioning](https://semver.org).
 - Option to Write Hyperlink Rather Than Label to Csv. [Issue #1412](https://github.com/PHPOffice/PhpSpreadsheet/issues/1412) [PR #4151](https://github.com/PHPOffice/PhpSpreadsheet/pull/4151)
 - Invalid Html Due to Cached Filesize. [Issue #1107](https://github.com/PHPOffice/PhpSpreadsheet/issues/1107) [PR #4184](https://github.com/PHPOffice/PhpSpreadsheet/pull/4184)
 - Excel 2003 Allows Html Entities. [Issue #2157](https://github.com/PHPOffice/PhpSpreadsheet/issues/2157) [PR #4187](https://github.com/PHPOffice/PhpSpreadsheet/pull/4187)
+- Allow ANCHORARRAY as Data Validation list. [Issue #4197](https://github.com/PHPOffice/PhpSpreadsheet/issues/4197) [PR #4203](https://github.com/PHPOffice/PhpSpreadsheet/pull/4203)
 
 ## 2024-09-29 - 3.3.0 (no 3.0.\*, 3.1.\*, 3.2.\*)
 
diff --git a/src/PhpSpreadsheet/Reader/Xlsx.php b/src/PhpSpreadsheet/Reader/Xlsx.php
index d86102c3c..88d59634c 100644
--- a/src/PhpSpreadsheet/Reader/Xlsx.php
+++ b/src/PhpSpreadsheet/Reader/Xlsx.php
@@ -304,6 +304,11 @@ class Xlsx extends BaseReader
         return isset($c, $c->v) ? (string) $c->v : null;
     }
 
+    public static function replacePrefixes(string $formula): string
+    {
+        return str_replace(['_xlfn.', '_xlws.'], '', $formula);
+    }
+
     private function castToFormula(?SimpleXMLElement $c, string $r, string &$cellDataType, mixed &$value, mixed &$calculatedValue, string $castBaseType, bool $updateSharedCells = true): void
     {
         if ($c === null) {
@@ -311,8 +316,7 @@ class Xlsx extends BaseReader
         }
         $attr = $c->f->attributes();
         $cellDataType = DataType::TYPE_FORMULA;
-        $formula = (string) $c->f;
-        $formula = str_replace(['_xlfn.', '_xlws.'], '', $formula);
+        $formula = self::replacePrefixes((string) $c->f);
         $value = "=$formula";
         $calculatedValue = self::$castBaseType($c);
 
diff --git a/src/PhpSpreadsheet/Reader/Xlsx/DataValidations.php b/src/PhpSpreadsheet/Reader/Xlsx/DataValidations.php
index d494dc9da..d519002a8 100644
--- a/src/PhpSpreadsheet/Reader/Xlsx/DataValidations.php
+++ b/src/PhpSpreadsheet/Reader/Xlsx/DataValidations.php
@@ -3,6 +3,7 @@
 namespace PhpOffice\PhpSpreadsheet\Reader\Xlsx;
 
 use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
+use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
 use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
 use SimpleXMLElement;
 
@@ -55,8 +56,8 @@ class DataValidations
                     $docValidation->setError((string) $dataValidation['error']);
                     $docValidation->setPromptTitle((string) $dataValidation['promptTitle']);
                     $docValidation->setPrompt((string) $dataValidation['prompt']);
-                    $docValidation->setFormula1((string) $dataValidation->formula1);
-                    $docValidation->setFormula2((string) $dataValidation->formula2);
+                    $docValidation->setFormula1(Xlsx::replacePrefixes((string) $dataValidation->formula1));
+                    $docValidation->setFormula2(Xlsx::replacePrefixes((string) $dataValidation->formula2));
                     $docValidation->setSqref($range);
                 }
             }
diff --git a/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php b/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php
index 247f7ce62..8df38ddce 100644
--- a/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php
+++ b/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php
@@ -983,10 +983,10 @@ class Worksheet extends WriterPart
                 $objWriter->writeAttribute('sqref', $dv->getSqref() ?? $coordinate);
 
                 if ($dv->getFormula1() !== '') {
-                    $objWriter->writeElement('formula1', $dv->getFormula1());
+                    $objWriter->writeElement('formula1', FunctionPrefix::addFunctionPrefix($dv->getFormula1()));
                 }
                 if ($dv->getFormula2() !== '') {
-                    $objWriter->writeElement('formula2', $dv->getFormula2());
+                    $objWriter->writeElement('formula2', FunctionPrefix::addFunctionPrefix($dv->getFormula2()));
                 }
 
                 $objWriter->endElement();


Test patch:
diff --git a/tests/PhpSpreadsheetTests/Writer/Xlsx/ArrayFormulaPrefixTest.php b/tests/PhpSpreadsheetTests/Writer/Xlsx/ArrayFormulaPrefixTest.php
index 55eacf68f..0222b5c2c 100644
--- a/tests/PhpSpreadsheetTests/Writer/Xlsx/ArrayFormulaPrefixTest.php
+++ b/tests/PhpSpreadsheetTests/Writer/Xlsx/ArrayFormulaPrefixTest.php
@@ -11,11 +11,6 @@ use PhpOffice\PhpSpreadsheetTests\Functional\AbstractFunctional;
 
 class ArrayFormulaPrefixTest extends AbstractFunctional
 {
-    protected function setUp(): void
-    {
-        parent::setUp();
-    }
-
     /**
      * Test to ensure that xlfn prefix is being added to functions
      * included in an array formula, if appropriate.
diff --git a/tests/PhpSpreadsheetTests/Writer/Xlsx/ArrayFormulaValidationTest.php b/tests/PhpSpreadsheetTests/Writer/Xlsx/ArrayFormulaValidationTest.php
new file mode 100644
index 000000000..a907b96cf
--- /dev/null
+++ b/tests/PhpSpreadsheetTests/Writer/Xlsx/ArrayFormulaValidationTest.php
@@ -0,0 +1,87 @@
+<?php
+
+declare(strict_types=1);
+
+namespace PhpOffice\PhpSpreadsheetTests\Writer\Xlsx;
+
+use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
+use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
+use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;
+use PhpOffice\PhpSpreadsheet\Shared\File;
+use PhpOffice\PhpSpreadsheet\Spreadsheet;
+use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWriter;
+use PHPUnit\Framework\TestCase;
+
+class ArrayFormulaValidationTest extends TestCase
+{
+    private string $outputFile = '';
+
+    protected function tearDown(): void
+    {
+        if ($this->outputFile !== '') {
+            unlink($this->outputFile);
+            $this->outputFile = '';
+        }
+    }
+
+    /**
+     * @dataProvider validationProvider
+     */
+    public function testWriteArrayFormulaValidation(string $formula): void
+    {
+        $spreadsheet = new Spreadsheet();
+        Calculation::getInstance($spreadsheet)
+            ->setInstanceArrayReturnType(
+                Calculation::RETURN_ARRAY_AS_ARRAY
+            );
+        $sheet = $spreadsheet->getActiveSheet();
+        $sheet->setCellValue('C1', 1);
+        $sheet->setCellValue('C2', 2);
+        $sheet->setCellValue('C3', 3);
+        $sheet->setCellValue('C4', 3);
+        $sheet->setCellValue('C5', 5);
+        $sheet->setCellValue('C6', 6);
+        $sheet->setCellValue('B1', '=UNIQUE(C1:C6)');
+
+        $validation = $sheet->getCell('A1')->getDataValidation();
+        $validation->setType(DataValidation::TYPE_LIST);
+        $validation->setErrorStyle(DataValidation::STYLE_STOP);
+        $validation->setAllowBlank(true);
+        $validation->setShowDropDown(true);
+        $validation->setShowErrorMessage(true);
+        $validation->setError('Invalid input');
+        $validation->setFormula1($formula);
+        $sheet->getCell('A1')->setDataValidation($validation);
+
+        $this->outputFile = File::temporaryFilename();
+        $writer = new XlsxWriter($spreadsheet);
+        $writer->save($this->outputFile);
+        $spreadsheet->disconnectWorksheets();
+
+        $reader = new XlsxReader();
+        $spreadsheet2 = $reader->load($this->outputFile);
+        Calculation::getInstance($spreadsheet2)->setInstanceArrayReturnType(Calculation::RETURN_ARRAY_AS_ARRAY);
+        $sheet2 = $spreadsheet2->getActiveSheet();
+        $validation2 = $sheet2->getCell('A1')->getDataValidation();
+        self::assertSame('ANCHORARRAY($B$1)', $validation2->getFormula1());
+        $spreadsheet2->disconnectWorksheets();
+
+        $file = 'zip://';
+        $file .= $this->outputFile;
+        $file .= '#xl/worksheets/sheet1.xml';
+        $data = file_get_contents($file);
+        if ($data === false) {
+            self::fail('Unable to read file');
+        } else {
+            self::assertStringContainsString('<formula1>_xlfn.ANCHORARRAY($B$1)</formula1>', $data);
+        }
+    }
+
+    public static function validationProvider(): array
+    {
+        return [
+            ['ANCHORARRAY($B$1)'],
+            ['$B$1#'],
+        ];
+    }
+}