geckon01/PhpSpreadsheet
1
0
Fork 0
mirror of https://github.com/PHPOffice/PhpSpreadsheet.git synced 2026-09-11 18:46:49 +00:00
Code Issues Packages Projects Releases Wiki Activity
Files
1.30.x
PhpSpreadsheet/tests/PhpSpreadsheetTests/Calculation/ParseFormulaTest.php
T
oleibman e9cf27354d PhpUnit 10 Compatibility Part 2 (#3526) 
Successor to PR #3523. There are 494 single-line changes (`public function provider` to `public static function provider`) in this PR. None of these were made manually; they were all created with the following script (adapted from
https://stackoverflow.com/questions/25909820/how-to-recursively-iterate-through-files-in-php):
```php
$dir = 'C:/git/unit10prep2/tests/PhpSpreadsheetTests';
$it = new RecursiveDirectoryIterator($dir);

// Loop through files
foreach(new RecursiveIteratorIterator($it) as $file) {
    if ($file->getExtension() === 'php') {
        $contents = file_get_contents($file);
        $new = preg_replace('/public function (\\w*)([Pp])rovider/', 'public static function $1$2rovider', $contents);
        if ($new !== $contents) {
            echo "changing $file\n";
            file_put_contents($file, $new);
        }
    }
}
```

After this PR, there will be one more, with a small number of test changes, and enabling PhpUnit 10 for Php 8.1+.
2023-04-20 13:48:00 -07:00

357 lines
19 KiB
PHP
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
<?php
namespace PhpOffice\PhpSpreadsheetTests\Calculation;
use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use PhpOffice\PhpSpreadsheet\Calculation\Engine\Operands\StructuredReference;
use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PHPUnit\Framework\TestCase;
class ParseFormulaTest extends TestCase
{
/**
* @dataProvider providerBinaryOperations
*/
public function testParseOperations(array $expectedStack, string $formula): void
{
$spreadsheet = new Spreadsheet();
$spreadsheet->addNamedRange(new NamedRange('GROUP1', $spreadsheet->getActiveSheet(), 'B2:D4'));
$spreadsheet->addNamedRange(new NamedRange('GROUP2', $spreadsheet->getActiveSheet(), 'D4:F6'));
$parser = Calculation::getInstance($spreadsheet);
$stack = $parser->parseFormula($formula);
self::assertEquals($expectedStack, $stack);
}
public static function providerBinaryOperations(): array
{
return [
'Unary negative with Value' => [
[
['type' => 'Value', 'value' => 3, 'reference' => null],
['type' => 'Unary Operator', 'value' => '~', 'reference' => null],
],
'=-3',
],
'Unary negative percentage with Value' => [
[
['type' => 'Value', 'value' => 3, 'reference' => null],
['type' => 'Unary Operator', 'value' => '%', 'reference' => null],
['type' => 'Unary Operator', 'value' => '~', 'reference' => null],
],
'=-3%',
],
'Binary minus with Values' => [
[
['type' => 'Value', 'value' => 3, 'reference' => null],
['type' => 'Value', 'value' => 4, 'reference' => null],
['type' => 'Binary Operator', 'value' => '-', 'reference' => null],
],
'=3-4',
],
'Unary negative with Cell Reference' => [
[
['type' => 'Cell Reference', 'value' => 'A1', 'reference' => 'A1'],
['type' => 'Unary Operator', 'value' => '~', 'reference' => null],
],
'=-A1',
],
'Unary negative with FQ Cell Reference' => [
[
['type' => 'Cell Reference', 'value' => "'Sheet 1'!A1", 'reference' => "'Sheet 1'!A1"],
['type' => 'Unary Operator', 'value' => '~', 'reference' => null],
],
"=-'Sheet 1'!A1",
],
'Unary negative percentage with Cell Reference' => [
[
['type' => 'Cell Reference', 'value' => 'A1', 'reference' => 'A1'],
['type' => 'Unary Operator', 'value' => '%', 'reference' => null],
['type' => 'Unary Operator', 'value' => '~', 'reference' => null],
],
'=-A1%',
],
'Unary negative with Defined Name' => [
[
['type' => 'Defined Name', 'value' => 'DEFINED_NAME', 'reference' => 'DEFINED_NAME'],
['type' => 'Unary Operator', 'value' => '~', 'reference' => null],
],
'=-DEFINED_NAME',
],
'Unary negative percentage with Defined Name' => [
[
['type' => 'Defined Name', 'value' => 'DEFINED_NAME', 'reference' => 'DEFINED_NAME'],
['type' => 'Unary Operator', 'value' => '%', 'reference' => null],
['type' => 'Unary Operator', 'value' => '~', 'reference' => null],
],
'=-DEFINED_NAME%',
],
'Integer Numbers with Operator' => [
[
['type' => 'Value', 'value' => 2, 'reference' => null],
['type' => 'Value', 'value' => 3, 'reference' => null],
['type' => 'Binary Operator', 'value' => '*', 'reference' => null],
],
'=2*3',
],
'Float Numbers with Operator' => [
[
['type' => 'Value', 'value' => 2.5, 'reference' => null],
['type' => 'Value', 'value' => 3.5, 'reference' => null],
['type' => 'Binary Operator', 'value' => '*', 'reference' => null],
],
'=2.5*3.5',
],
'Strings with Operator' => [
[
['type' => 'Value', 'value' => '"HELLO"', 'reference' => null],
['type' => 'Value', 'value' => '"WORLD"', 'reference' => null],
['type' => 'Binary Operator', 'value' => '&', 'reference' => null],
],
'="HELLO"&"WORLD"',
],
'Error' => [
[
['type' => 'Value', 'value' => '#DIV0!', 'reference' => null],
],
'=#DIV0!',
],
'Cell Range' => [
[
['type' => 'Cell Reference', 'value' => 'A1', 'reference' => 'A1'],
['type' => 'Cell Reference', 'value' => 'C3', 'reference' => 'C3'],
['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
],
'=A1:C3',
],
'Chained Cell Range' => [
[
['type' => 'Cell Reference', 'value' => 'A1', 'reference' => 'A1'],
['type' => 'Cell Reference', 'value' => 'C3', 'reference' => 'C3'],
['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
['type' => 'Cell Reference', 'value' => 'E5', 'reference' => 'E5'],
['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
],
'=A1:C3:E5',
],
'Cell Range Intersection' => [
[
['type' => 'Cell Reference', 'value' => 'A1', 'reference' => 'A1'],
['type' => 'Cell Reference', 'value' => 'C3', 'reference' => 'C3'],
['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
['type' => 'Cell Reference', 'value' => 'B2', 'reference' => 'B2'],
['type' => 'Cell Reference', 'value' => 'D4', 'reference' => 'D4'],
['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
['type' => 'Binary Operator', 'value' => '∩', 'reference' => null],
],
'=A1:C3 B2:D4',
],
'Row Range' => [
[
['type' => 'Row Reference', 'value' => 'A2', 'reference' => 'A2'],
['type' => 'Row Reference', 'value' => 'XFD3', 'reference' => 'XFD3'],
['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
],
'=2:3',
],
'Column Range' => [
[
['type' => 'Column Reference', 'value' => 'B1', 'reference' => 'B1'],
['type' => 'Column Reference', 'value' => 'C1048576', 'reference' => 'C1048576'],
['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
],
'=B:C',
],
'Combined Cell Reference and Column Range' => [
[
['type' => 'Column Reference', 'value' => "'sheet1'!A1", 'reference' => "'sheet1'!A1"],
['type' => 'Column Reference', 'value' => "'sheet1'!A1048576", 'reference' => "'sheet1'!A1048576"],
['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
['type' => 'Operand Count for Function MIN()', 'value' => 1, 'reference' => null],
['type' => 'Function', 'value' => 'MIN(', 'reference' => null],
['type' => 'Cell Reference', 'value' => "'sheet1'!A1", 'reference' => "'sheet1'!A1"],
['type' => 'Binary Operator', 'value' => '+', 'reference' => null],
],
"=MIN('sheet1'!A:A) + 'sheet1'!A1",
],
'Combined Cell Reference and Column Range with quote' => [
[
['type' => 'Column Reference', 'value' => "'Mark's sheet1'!A1", 'reference' => "'Mark's sheet1'!A1"],
['type' => 'Column Reference', 'value' => "'Mark's sheet1'!A1048576", 'reference' => "'Mark's sheet1'!A1048576"],
['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
['type' => 'Operand Count for Function MIN()', 'value' => 1, 'reference' => null],
['type' => 'Function', 'value' => 'MIN(', 'reference' => null],
['type' => 'Cell Reference', 'value' => "'Mark's sheet1'!A1", 'reference' => "'Mark's sheet1'!A1"],
['type' => 'Binary Operator', 'value' => '+', 'reference' => null],
],
"=MIN('Mark''s sheet1'!A:A) + 'Mark''s sheet1'!A1",
],
'Combined Cell Reference and Column Range with unescaped quote' => [
[
['type' => 'Column Reference', 'value' => "'Mark's sheet1'!A1", 'reference' => "'Mark's sheet1'!A1"],
['type' => 'Column Reference', 'value' => "'Mark's sheet1'!A1048576", 'reference' => "'Mark's sheet1'!A1048576"],
['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
['type' => 'Operand Count for Function MIN()', 'value' => 1, 'reference' => null],
['type' => 'Function', 'value' => 'MIN(', 'reference' => null],
['type' => 'Cell Reference', 'value' => "'Mark's sheet1'!A1", 'reference' => "'Mark's sheet1'!A1"],
['type' => 'Binary Operator', 'value' => '+', 'reference' => null],
],
"=MIN('Mark's sheet1'!A:A) + 'Mark's sheet1'!A1",
],
'Combined Column Range and Cell Reference' => [
[
['type' => 'Cell Reference', 'value' => "'sheet1'!A1", 'reference' => "'sheet1'!A1"],
['type' => 'Column Reference', 'value' => "'sheet1'!A1", 'reference' => "'sheet1'!A1"],
['type' => 'Column Reference', 'value' => "'sheet1'!A1048576", 'reference' => "'sheet1'!A1048576"],
['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
['type' => 'Operand Count for Function MIN()', 'value' => 1, 'reference' => null],
['type' => 'Function', 'value' => 'MIN(', 'reference' => null],
['type' => 'Binary Operator', 'value' => '+', 'reference' => null],
],
"='sheet1'!A1 + MIN('sheet1'!A:A)",
],
'Combined Column Range and Cell Reference with quote' => [
[
['type' => 'Cell Reference', 'value' => "'Mark's sheet1'!A1", 'reference' => "'Mark's sheet1'!A1"],
['type' => 'Column Reference', 'value' => "'Mark's sheet1'!A1", 'reference' => "'Mark's sheet1'!A1"],
['type' => 'Column Reference', 'value' => "'Mark's sheet1'!A1048576", 'reference' => "'Mark's sheet1'!A1048576"],
['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
['type' => 'Operand Count for Function MIN()', 'value' => 1, 'reference' => null],
['type' => 'Function', 'value' => 'MIN(', 'reference' => null],
['type' => 'Binary Operator', 'value' => '+', 'reference' => null],
],
"='Mark''s sheet1'!A1 + MIN('Mark''s sheet1'!A:A)",
],
'Combined Column Range and Cell Reference with unescaped quote' => [
[
['type' => 'Cell Reference', 'value' => "'Mark's sheet1'!A1", 'reference' => "'Mark's sheet1'!A1"],
['type' => 'Column Reference', 'value' => "'Mark's sheet1'!A1", 'reference' => "'Mark's sheet1'!A1"],
['type' => 'Column Reference', 'value' => "'Mark's sheet1'!A1048576", 'reference' => "'Mark's sheet1'!A1048576"],
['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
['type' => 'Operand Count for Function MIN()', 'value' => 1, 'reference' => null],
['type' => 'Function', 'value' => 'MIN(', 'reference' => null],
['type' => 'Binary Operator', 'value' => '+', 'reference' => null],
],
"='Mark's sheet1'!A1 + MIN('Mark's sheet1'!A:A)",
],
'Range with Defined Names' => [
[
['type' => 'Defined Name', 'value' => 'GROUP1', 'reference' => 'GROUP1'],
['type' => 'Defined Name', 'value' => 'D4', 'reference' => 'GROUP2'],
['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
['type' => 'Defined Name', 'value' => 'F6', 'reference' => 'GROUP2'],
['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
],
'=GROUP1:GROUP2',
],
'Named Range with Binary Operator' => [
[
['type' => 'Defined Name', 'value' => 'DEFINED_NAME_1', 'reference' => 'DEFINED_NAME_1'],
['type' => 'Defined Name', 'value' => 'DEFINED_NAME_2', 'reference' => 'DEFINED_NAME_2'],
['type' => 'Binary Operator', 'value' => '/', 'reference' => null],
],
'=DEFINED_NAME_1/DEFINED_NAME_2',
],
'Named Range Intersection' => [
[
['type' => 'Defined Name', 'value' => 'DEFINED_NAME_1', 'reference' => 'DEFINED_NAME_1'],
['type' => 'Defined Name', 'value' => 'DEFINED_NAME_2', 'reference' => 'DEFINED_NAME_2'],
['type' => 'Binary Operator', 'value' => '∩', 'reference' => null],
],
'=DEFINED_NAME_1 DEFINED_NAME_2',
],
'Fully Qualified Structured Reference' => [
[
['type' => 'Structured Reference', 'value' => new StructuredReference('DeptSales[@Commission Amount]'), 'reference' => null],
],
'=DeptSales[@Commission Amount]',
],
'Fully Qualified Nested Structured Reference' => [
[
['type' => 'Structured Reference', 'value' => new StructuredReference('DeptSales[[#Totals],[Sales Amount]]'), 'reference' => null],
],
'=DeptSales[[#Totals],[Sales Amount]]',
],
'Complex Range Fully Qualified Nested Structured Reference' => [
[
['type' => 'Structured Reference', 'value' => new StructuredReference('Sales_Data[[#This Row],[Q1]:[Q4]]'), 'reference' => null],
],
'=Sales_Data[[#This Row],[Q1]:[Q4]]',
],
'Complex Range Fully Qualified Nested Structured Reference 2' => [
[
['type' => 'Structured Reference', 'value' => new StructuredReference('DeptSales[[#Headers],[Region]:[Commission Amount]]'), 'reference' => null],
],
'=DeptSales[[#Headers],[Region]:[Commission Amount]]',
],
[
'Multi-RowGroup Fully Qualified Nested Structured Reference' => [
['type' => 'Structured Reference', 'value' => new StructuredReference('DeptSales[[#Headers],[#Data],[% Commission]]'), 'reference' => null],
],
'=DeptSales[[#Headers],[#Data],[% Commission]]',
],
'Unqualified Structured Reference' => [
[
['type' => 'Structured Reference', 'value' => new StructuredReference('[@Quantity]'), 'reference' => null],
],
'=[@Quantity]',
],
'Unqualified Nested Structured Reference' => [
[
['type' => 'Structured Reference', 'value' => new StructuredReference('[@[Unit Price]]'), 'reference' => null],
],
'=[@[Unit Price]]',
],
'Fully Qualified Full Table Structured Reference' => [
[
['type' => 'Structured Reference', 'value' => new StructuredReference('DeptSales[]'), 'reference' => null],
],
'=DeptSales[]',
],
'Unqualified Full Table Structured Reference' => [
[
['type' => 'Structured Reference', 'value' => new StructuredReference('[]'), 'reference' => null],
],
'=[]',
],
'Structured Reference Arithmetic' => [
[
['type' => 'Structured Reference', 'value' => new StructuredReference('[@Quantity]'), 'reference' => null],
['type' => 'Structured Reference', 'value' => new StructuredReference('[@[Unit Price]]'), 'reference' => null],
['type' => 'Binary Operator', 'value' => '*', 'reference' => null],
],
'=[@Quantity]*[@[Unit Price]]',
],
'Structured Reference Intersection' => [
[
['type' => 'Structured Reference', 'value' => new StructuredReference('DeptSales[[Sales Person]:[Sales Amount]]'), 'reference' => null],
['type' => 'Structured Reference', 'value' => new StructuredReference('DeptSales[[Region]:[% Commission]]'), 'reference' => null],
['type' => 'Binary Operator', 'value' => '∩', 'reference' => null],
],
'=DeptSales[[Sales Person]:[Sales Amount]] DeptSales[[Region]:[% Commission]]',
],
// 'Cell Range Union' => [
// [
// ['type' => 'Cell Reference', 'value' => 'A1', 'reference' => 'A1'],
// ['type' => 'Cell Reference', 'value' => 'C3', 'reference' => 'C3'],
// ['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
// ['type' => 'Cell Reference', 'value' => 'B2', 'reference' => 'B2'],
// ['type' => 'Cell Reference', 'value' => 'D4', 'reference' => 'D4'],
// ['type' => 'Binary Operator', 'value' => ':', 'reference' => null],
// ['type' => 'Binary Operator', 'value' => '∪', 'reference' => null],
// ],
// '=A1:C3,B2:D4',
// ],
// 'Named Range Union' => [
// [
// ['type' => 'Defined Name', 'value' => 'DEFINED_NAME_1', 'reference' => 'DEFINED_NAME_1'],
// ['type' => 'Defined Name', 'value' => 'DEFINED_NAME_2', 'reference' => 'DEFINED_NAME_2'],
// ['type' => 'Binary Operator', 'value' => '∪', 'reference' => null],
// ],
// '=DEFINED_NAME_1,DEFINED_NAME_2',
// ],
];
}
}
Reference in New Issue View Git Blame Copy Permalink