CalculationTest::testFullExecutionDataPruning() - Code Metrics - Inspection of "Resolution for Issue 3335 - Calculation Engine doe..." - PHPOffice/PhpSpreadsheet - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#3336)

by Mark

created 2023-02-01 20:10 UTC

CalculationTest::testFullExecutionDataPruning() A

↳ Parent: CalculationTest

Complexity

Conditions	3
Paths	4

Size

Total Lines	35
Code Lines	19

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	19
c	0
b	0
f	0
dl	0
loc	35
rs	9.6333
cc	3
nc	4
nop	6

<?php

namespace PhpOffice\PhpSpreadsheetTests\Calculation;

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PHPUnit\Framework\TestCase;

class CalculationTest extends TestCase
{
    /**
     * @var string
     */
    private $compatibilityMode;

    /**
     * @var string
     */
    private $locale;

    protected function setUp(): void
    {
        $this->compatibilityMode = Functions::getCompatibilityMode();
        $calculation = Calculation::getInstance();
        $this->locale = $calculation->getLocale();
        Functions::setCompatibilityMode(Functions::COMPATIBILITY_EXCEL);
    }

    protected function tearDown(): void
    {
        Functions::setCompatibilityMode($this->compatibilityMode);
        $calculation = Calculation::getInstance();
        $calculation->setLocale($this->locale);
    }

    /**
     * @dataProvider providerBinaryComparisonOperation
     *
     * @param mixed $formula
     * @param mixed $expectedResultExcel
     * @param mixed $expectedResultOpenOffice
     */
    public function testBinaryComparisonOperation($formula, $expectedResultExcel, $expectedResultOpenOffice): void
    {
        Functions::setCompatibilityMode(Functions::COMPATIBILITY_EXCEL);
        $resultExcel = Calculation::getInstance()->_calculateFormulaValue($formula);
        self::assertEquals($expectedResultExcel, $resultExcel, 'should be Excel compatible');

        Functions::setCompatibilityMode(Functions::COMPATIBILITY_OPENOFFICE);
        $resultOpenOffice = Calculation::getInstance()->_calculateFormulaValue($formula);
        self::assertEquals($expectedResultOpenOffice, $resultOpenOffice, 'should be OpenOffice compatible');
    }

    public function providerBinaryComparisonOperation(): array
    {
        return require 'tests/data/CalculationBinaryComparisonOperation.php';
    }

    public function testDoesHandleXlfnFunctions(): void
    {
        $calculation = Calculation::getInstance();

        $tree = $calculation->parseFormula('=_xlfn.ISFORMULA(A1)');
        self::assertIsArray($tree);
        self::assertCount(3, $tree);
        $function = $tree[2];
        self::assertEquals('Function', $function['type']);

        $tree = $calculation->parseFormula('=_xlfn.STDEV.S(A1:B2)');
        self::assertIsArray($tree);
        self::assertCount(5, $tree);
        $function = $tree[4];
        self::assertEquals('Function', $function['type']);
    }

    public function testFormulaWithOptionalArgumentsAndRequiredCellReferenceShouldPassNullForMissingArguments(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->fromArray(
            [
                [1, 2, 3],
                [4, 5, 6],
                [7, 8, 9],
            ]
        );

        $cell = $sheet->getCell('E5');
        $cell->setValue('=OFFSET(D3, -1, -2, 1, 1)');
        self::assertEquals(5, $cell->getCalculatedValue(), 'with all arguments');

        $cell = $sheet->getCell('F6');
        $cell->setValue('=OFFSET(D3, -1, -2)');
        self::assertEquals(5, $cell->getCalculatedValue(), 'missing arguments should be filled with null');
    }

    public function testCellSetAsQuotedText(): void
    {
        $spreadsheet = new Spreadsheet();
        $workSheet = $spreadsheet->getActiveSheet();
        $cell = $workSheet->getCell('A1');

        $cell->setValue("=cmd|'/C calc'!A0");
        $cell->getStyle()->setQuotePrefix(true);

        self::assertEquals("=cmd|'/C calc'!A0", $cell->getCalculatedValue());

        $cell2 = $workSheet->getCell('A2');
        $cell2->setValueExplicit('ABC', DataType::TYPE_FORMULA);
        self::assertEquals('ABC', $cell2->getCalculatedValue());

        $cell3 = $workSheet->getCell('A3');
        $cell3->setValueExplicit('=', DataType::TYPE_FORMULA);
        self::assertEquals('', $cell3->getCalculatedValue());
    }

    public function testCellWithDdeExpresion(): void
    {
        $spreadsheet = new Spreadsheet();
        $workSheet = $spreadsheet->getActiveSheet();
        $cell = $workSheet->getCell('A1');

        $cell->setValue("=cmd|'/C calc'!A0");

        self::assertEquals("=cmd|'/C calc'!A0", $cell->getCalculatedValue());
    }

    public function testFormulaReferencingWorksheetWithEscapedApostrophe(): void
    {
        $spreadsheet = new Spreadsheet();
        $workSheet = $spreadsheet->getActiveSheet();
        $workSheet->setTitle("Catégorie d'absence");

        $workSheet->setCellValue('A1', 'HELLO');
        $workSheet->setCellValue('B1', ' ');
        $workSheet->setCellValue('C1', 'WORLD');
        $workSheet->setCellValue(
            'A2',
            "=CONCAT('Catégorie d''absence'!A1, 'Catégorie d''absence'!B1, 'Catégorie d''absence'!C1)"
        );

        $cellValue = $workSheet->getCell('A2')->getCalculatedValue();
        self::assertSame('HELLO WORLD', $cellValue);
    }

    public function testFormulaReferencingWorksheetWithUnescapedApostrophe(): void
    {
        $spreadsheet = new Spreadsheet();
        $workSheet = $spreadsheet->getActiveSheet();
        $workSheet->setTitle("Catégorie d'absence");

        $workSheet->setCellValue('A1', 'HELLO');
        $workSheet->setCellValue('B1', ' ');
        $workSheet->setCellValue('C1', 'WORLD');
        $workSheet->setCellValue(
            'A2',
            "=CONCAT('Catégorie d'absence'!A1, 'Catégorie d'absence'!B1, 'Catégorie d'absence'!C1)"
        );

        $cellValue = $workSheet->getCell('A2')->getCalculatedValue();
        self::assertSame('HELLO WORLD', $cellValue);
    }

    public function testCellWithFormulaTwoIndirect(): void
    {
        $spreadsheet = new Spreadsheet();
        $workSheet = $spreadsheet->getActiveSheet();
        $cell1 = $workSheet->getCell('A1');
        $cell1->setValue('2');
        $cell2 = $workSheet->getCell('B1');
        $cell2->setValue('3');
        $cell2 = $workSheet->getCell('C1');
        $cell2->setValue('4');
        $cell3 = $workSheet->getCell('D1');
        $cell3->setValue('=SUM(INDIRECT("A"&ROW()),INDIRECT("B"&ROW()),INDIRECT("C"&ROW()))');

        self::assertEquals('9', $cell3->getCalculatedValue());
    }

    public function testCellWithStringNumeric(): void
    {
        $spreadsheet = new Spreadsheet();
        $workSheet = $spreadsheet->getActiveSheet();
        $cell1 = $workSheet->getCell('A1');
        $cell1->setValue('+2.5');
        $cell2 = $workSheet->getCell('B1');
        $cell2->setValue('=100*A1');

        self::assertSame(250.0, $cell2->getCalculatedValue());
    }

    public function testCellWithStringFraction(): void
    {
        $spreadsheet = new Spreadsheet();
        $workSheet = $spreadsheet->getActiveSheet();
        $cell1 = $workSheet->getCell('A1');
        $cell1->setValue('3/4');
        $cell2 = $workSheet->getCell('B1');
        $cell2->setValue('=100*A1');

        self::assertSame(75.0, $cell2->getCalculatedValue());
    }

    public function testCellWithStringPercentage(): void
    {
        $spreadsheet = new Spreadsheet();
        $workSheet = $spreadsheet->getActiveSheet();
        $cell1 = $workSheet->getCell('A1');
        $cell1->setValue('2%');
        $cell2 = $workSheet->getCell('B1');
        $cell2->setValue('=100*A1');

        self::assertSame(2.0, $cell2->getCalculatedValue());
    }

    public function testCellWithStringCurrency(): void
    {
        $currencyCode = StringHelper::getCurrencyCode();

        $spreadsheet = new Spreadsheet();
        $workSheet = $spreadsheet->getActiveSheet();
        $cell1 = $workSheet->getCell('A1');
        $cell1->setValue($currencyCode . '2');
        $cell2 = $workSheet->getCell('B1');
        $cell2->setValue('=100*A1');

        self::assertSame(200.0, $cell2->getCalculatedValue());
    }

    public function testBranchPruningFormulaParsingSimpleCase(): void
    {
        $calculation = Calculation::getInstance();
        $calculation->flushInstance(); // resets the ids

        // Very simple formula
        $formula = '=IF(A1="please +",B1)';
        $tokens = $calculation->parseFormula($formula);
        self::assertIsArray($tokens);

        $foundEqualAssociatedToStoreKey = false;
        $foundConditionalOnB1 = false;
        foreach ($tokens as $token) {
            $isBinaryOperator = $token['type'] == 'Binary Operator';
            $isEqual = $token['value'] == '=';
            $correctStoreKey = ($token['storeKey'] ?? '') == 'storeKey-0';
            $correctOnlyIf = ($token['onlyIf'] ?? '') == 'storeKey-0';
            $isB1Reference = ($token['reference'] ?? '') == 'B1';

            $foundEqualAssociatedToStoreKey = $foundEqualAssociatedToStoreKey ||
                ($isBinaryOperator && $isEqual && $correctStoreKey);

            $foundConditionalOnB1 = $foundConditionalOnB1 ||
                ($isB1Reference && $correctOnlyIf);
        }
        self::assertTrue($foundEqualAssociatedToStoreKey);
        self::assertTrue($foundConditionalOnB1);
    }

    public function testBranchPruningFormulaParsingMultipleIfsCase(): void
    {
        $calculation = Calculation::getInstance();
        $calculation->flushInstance(); // resets the ids

        //
        // Internal operation
        $formula = '=IF(A1="please +",SUM(B1:B3))+IF(A2="please *",PRODUCT(C1:C3), C1)';
        $tokens = $calculation->parseFormula($formula);
        self::assertIsArray($tokens);

        $plusGotTagged = false;
        $productFunctionCorrectlyTagged = false;
        foreach ($tokens as $token) {
            $isBinaryOperator = $token['type'] == 'Binary Operator';
            $isPlus = $token['value'] == '+';
            $anyStoreKey = isset($token['storeKey']);
            $anyOnlyIf = isset($token['onlyIf']);
            $anyOnlyIfNot = isset($token['onlyIfNot']);
            $plusGotTagged = $plusGotTagged ||
                ($isBinaryOperator && $isPlus &&
                    ($anyStoreKey || $anyOnlyIfNot || $anyOnlyIf));

            $isFunction = $token['type'] == 'Function';
            $isProductFunction = $token['value'] == 'PRODUCT(';
            $correctOnlyIf = ($token['onlyIf'] ?? '') == 'storeKey-1';
            $productFunctionCorrectlyTagged = $productFunctionCorrectlyTagged || ($isFunction && $isProductFunction && $correctOnlyIf);
        }
        self::assertFalse($plusGotTagged, 'chaining IF( should not affect the external operators');
        self::assertTrue($productFunctionCorrectlyTagged, 'function nested inside if should be tagged to be processed only if parent branching requires it');
    }

    public function testBranchPruningFormulaParingNestedIfCase(): void
    {
        $calculation = Calculation::getInstance();
        $calculation->flushInstance(); // resets the ids

        $formula = '=IF(A1="please +",SUM(B1:B3),1+IF(NOT(A2="please *"),C2-C1,PRODUCT(C1:C3)))';
        $tokens = $calculation->parseFormula($formula);
        self::assertIsArray($tokens);

        $plusCorrectlyTagged = false;
        $productFunctionCorrectlyTagged = false;
        $notFunctionCorrectlyTagged = false;
        $findOneOperandCountTagged = false;
        foreach ($tokens as $token) {
            $value = $token['value'];
            $isPlus = $value == '+';
            $isProductFunction = $value == 'PRODUCT(';
            $isNotFunction = $value == 'NOT(';
            $isIfOperand = $token['type'] == 'Operand Count for Function IF()';
            $isOnlyIfNotDepth1 = (array_key_exists('onlyIfNot', $token) ? $token['onlyIfNot'] : null) == 'storeKey-1';
            $isStoreKeyDepth1 = (array_key_exists('storeKey', $token) ? $token['storeKey'] : null) == 'storeKey-1';
            $isOnlyIfNotDepth0 = (array_key_exists('onlyIfNot', $token) ? $token['onlyIfNot'] : null) == 'storeKey-0';

            $plusCorrectlyTagged = $plusCorrectlyTagged || ($isPlus && $isOnlyIfNotDepth0);
            $notFunctionCorrectlyTagged = $notFunctionCorrectlyTagged || ($isNotFunction && $isOnlyIfNotDepth0 && $isStoreKeyDepth1);
            $productFunctionCorrectlyTagged = $productFunctionCorrectlyTagged || ($isProductFunction && $isOnlyIfNotDepth1 && !$isStoreKeyDepth1 && !$isOnlyIfNotDepth0);
            $findOneOperandCountTagged = $findOneOperandCountTagged || ($isIfOperand && $isOnlyIfNotDepth0);
        }
        self::assertTrue($plusCorrectlyTagged);
        self::assertTrue($productFunctionCorrectlyTagged);
        self::assertTrue($notFunctionCorrectlyTagged);
    }

    public function testBranchPruningFormulaParsingNoArgumentFunctionCase(): void
    {
        $calculation = Calculation::getInstance();
        $calculation->flushInstance(); // resets the ids

        $formula = '=IF(AND(TRUE(),A1="please +"),2,3)';
        // this used to raise a parser error, we keep it even though we don't
        // test the output
        $calculation->parseFormula($formula);
        self::assertTrue(true);
    }

    public function testBranchPruningFormulaParsingInequalitiesConditionsCase(): void
    {
        $calculation = Calculation::getInstance();
        $calculation->flushInstance(); // resets the ids

        $formula = '=IF(A1="flag",IF(A2<10, 0) + IF(A3<10000, 0))';
        $tokens = $calculation->parseFormula($formula);
        self::assertIsArray($tokens);

        $properlyTaggedPlus = false;
        foreach ($tokens as $token) {
            $isPlus = $token['value'] === '+';
            $hasOnlyIf = !empty($token['onlyIf']);

            $properlyTaggedPlus = $properlyTaggedPlus ||
                ($isPlus && $hasOnlyIf);
        }
        self::assertTrue($properlyTaggedPlus);
    }

    /**
     * @param mixed $expectedResult
     * @param mixed $dataArray
     * @param string $formula
     * @param string $cellCoordinates where to put the formula
     * @param string[] $shouldBeSetInCacheCells coordinates of cells that must
     *  be set in cache
     * @param string[] $shouldNotBeSetInCacheCells coordinates of cells that must
     *  not be set in cache because of pruning
     *
     * @dataProvider dataProviderBranchPruningFullExecution
     */
    public function testFullExecutionDataPruning(
        $expectedResult,
        $dataArray,
        $formula,
        $cellCoordinates,
        $shouldBeSetInCacheCells = [],
        $shouldNotBeSetInCacheCells = []
    ): void {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->fromArray($dataArray);
        $cell = $sheet->getCell($cellCoordinates);
        $calculation = Calculation::getInstance($cell->getWorksheet()->getParent());

        $cell->setValue($formula);
        $calculated = $cell->getCalculatedValue();
        self::assertEquals($expectedResult, $calculated);

        // this mostly to ensure that at least some cells are cached
        foreach ($shouldBeSetInCacheCells as $setCell) {
            unset($inCache);
            $calculation->getValueFromCache('Worksheet!' . $setCell, $inCache);
            self::assertNotEmpty($inCache);
        }

        foreach ($shouldNotBeSetInCacheCells as $notSetCell) {
            unset($inCache);
            $calculation->getValueFromCache('Worksheet!' . $notSetCell, $inCache);
            self::assertEmpty($inCache);
        }

        $calculation->disableBranchPruning();
        $calculated = $cell->getCalculatedValue();
        self::assertEquals($expectedResult, $calculated);
    }

    public function dataProviderBranchPruningFullExecution(): array
    {
        return require 'tests/data/Calculation/Calculation.php';
    }
}


1			<?php
2
3			namespace PhpOffice\PhpSpreadsheetTests\Calculation;
4
5			use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
6			use PhpOffice\PhpSpreadsheet\Calculation\Functions;
7			use PhpOffice\PhpSpreadsheet\Cell\DataType;
8			use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
9			use PhpOffice\PhpSpreadsheet\Spreadsheet;
10			use PHPUnit\Framework\TestCase;
11
12			class CalculationTest extends TestCase
13			{
14			/**
15			* @var string
16			*/
17			private $compatibilityMode;
18
19			/**
20			* @var string
21			*/
22			private $locale;
23
24			protected function setUp(): void
25			{
26			$this->compatibilityMode = Functions::getCompatibilityMode();
27			$calculation = Calculation::getInstance();
28			$this->locale = $calculation->getLocale();
29			Functions::setCompatibilityMode(Functions::COMPATIBILITY_EXCEL);
30			}
31
32			protected function tearDown(): void
33			{
34			Functions::setCompatibilityMode($this->compatibilityMode);
35			$calculation = Calculation::getInstance();
36			$calculation->setLocale($this->locale);
37			}
38
39			/**
40			* @dataProvider providerBinaryComparisonOperation
41			*
42			* @param mixed $formula
43			* @param mixed $expectedResultExcel
44			* @param mixed $expectedResultOpenOffice
45			*/
46			public function testBinaryComparisonOperation($formula, $expectedResultExcel, $expectedResultOpenOffice): void
47			{
48			Functions::setCompatibilityMode(Functions::COMPATIBILITY_EXCEL);
49			$resultExcel = Calculation::getInstance()->_calculateFormulaValue($formula);
50			self::assertEquals($expectedResultExcel, $resultExcel, 'should be Excel compatible');
51
52			Functions::setCompatibilityMode(Functions::COMPATIBILITY_OPENOFFICE);
53			$resultOpenOffice = Calculation::getInstance()->_calculateFormulaValue($formula);
54			self::assertEquals($expectedResultOpenOffice, $resultOpenOffice, 'should be OpenOffice compatible');
55			}
56
57			public function providerBinaryComparisonOperation(): array
58			{
59			return require 'tests/data/CalculationBinaryComparisonOperation.php';
60			}
61
62			public function testDoesHandleXlfnFunctions(): void
63			{
64			$calculation = Calculation::getInstance();
65
66			$tree = $calculation->parseFormula('=_xlfn.ISFORMULA(A1)');
67			self::assertIsArray($tree);
68			self::assertCount(3, $tree);
69			$function = $tree[2];
70			self::assertEquals('Function', $function['type']);
71
72			$tree = $calculation->parseFormula('=_xlfn.STDEV.S(A1:B2)');
73			self::assertIsArray($tree);
74			self::assertCount(5, $tree);
75			$function = $tree[4];
76			self::assertEquals('Function', $function['type']);
77			}
78
79			public function testFormulaWithOptionalArgumentsAndRequiredCellReferenceShouldPassNullForMissingArguments(): void
80			{
81			$spreadsheet = new Spreadsheet();
82			$sheet = $spreadsheet->getActiveSheet();
83
84			$sheet->fromArray(
85			[
86			[1, 2, 3],
87			[4, 5, 6],
88			[7, 8, 9],
89			]
90			);
91
92			$cell = $sheet->getCell('E5');
93			$cell->setValue('=OFFSET(D3, -1, -2, 1, 1)');
94			self::assertEquals(5, $cell->getCalculatedValue(), 'with all arguments');
95
96			$cell = $sheet->getCell('F6');
97			$cell->setValue('=OFFSET(D3, -1, -2)');
98			self::assertEquals(5, $cell->getCalculatedValue(), 'missing arguments should be filled with null');
99			}
100
101			public function testCellSetAsQuotedText(): void
102			{
103			$spreadsheet = new Spreadsheet();
104			$workSheet = $spreadsheet->getActiveSheet();
105			$cell = $workSheet->getCell('A1');
106
107			$cell->setValue("=cmd\|'/C calc'!A0");
108			$cell->getStyle()->setQuotePrefix(true);
109
110			self::assertEquals("=cmd\|'/C calc'!A0", $cell->getCalculatedValue());
111
112			$cell2 = $workSheet->getCell('A2');
113			$cell2->setValueExplicit('ABC', DataType::TYPE_FORMULA);
114			self::assertEquals('ABC', $cell2->getCalculatedValue());
115
116			$cell3 = $workSheet->getCell('A3');
117			$cell3->setValueExplicit('=', DataType::TYPE_FORMULA);
118			self::assertEquals('', $cell3->getCalculatedValue());
119			}
120
121			public function testCellWithDdeExpresion(): void
122			{
123			$spreadsheet = new Spreadsheet();
124			$workSheet = $spreadsheet->getActiveSheet();
125			$cell = $workSheet->getCell('A1');
126
127			$cell->setValue("=cmd\|'/C calc'!A0");
128
129			self::assertEquals("=cmd\|'/C calc'!A0", $cell->getCalculatedValue());
130			}
131
132			public function testFormulaReferencingWorksheetWithEscapedApostrophe(): void
133			{
134			$spreadsheet = new Spreadsheet();
135			$workSheet = $spreadsheet->getActiveSheet();
136			$workSheet->setTitle("Catégorie d'absence");
137
138			$workSheet->setCellValue('A1', 'HELLO');
139			$workSheet->setCellValue('B1', ' ');
140			$workSheet->setCellValue('C1', 'WORLD');
141			$workSheet->setCellValue(
142			'A2',
143			"=CONCAT('Catégorie d''absence'!A1, 'Catégorie d''absence'!B1, 'Catégorie d''absence'!C1)"
144			);
145
146			$cellValue = $workSheet->getCell('A2')->getCalculatedValue();
147			self::assertSame('HELLO WORLD', $cellValue);
148			}
149
150			public function testFormulaReferencingWorksheetWithUnescapedApostrophe(): void
151			{
152			$spreadsheet = new Spreadsheet();
153			$workSheet = $spreadsheet->getActiveSheet();
154			$workSheet->setTitle("Catégorie d'absence");
155
156			$workSheet->setCellValue('A1', 'HELLO');
157			$workSheet->setCellValue('B1', ' ');
158			$workSheet->setCellValue('C1', 'WORLD');
159			$workSheet->setCellValue(
160			'A2',
161			"=CONCAT('Catégorie d'absence'!A1, 'Catégorie d'absence'!B1, 'Catégorie d'absence'!C1)"
162			);
163
164			$cellValue = $workSheet->getCell('A2')->getCalculatedValue();
165			self::assertSame('HELLO WORLD', $cellValue);
166			}
167
168			public function testCellWithFormulaTwoIndirect(): void
169			{
170			$spreadsheet = new Spreadsheet();
171			$workSheet = $spreadsheet->getActiveSheet();
172			$cell1 = $workSheet->getCell('A1');
173			$cell1->setValue('2');
174			$cell2 = $workSheet->getCell('B1');
175			$cell2->setValue('3');
176			$cell2 = $workSheet->getCell('C1');
177			$cell2->setValue('4');
178			$cell3 = $workSheet->getCell('D1');
179			$cell3->setValue('=SUM(INDIRECT("A"&ROW()),INDIRECT("B"&ROW()),INDIRECT("C"&ROW()))');
180
181			self::assertEquals('9', $cell3->getCalculatedValue());
182			}
183
184			public function testCellWithStringNumeric(): void
185			{
186			$spreadsheet = new Spreadsheet();
187			$workSheet = $spreadsheet->getActiveSheet();
188			$cell1 = $workSheet->getCell('A1');
189			$cell1->setValue('+2.5');
190			$cell2 = $workSheet->getCell('B1');
191			$cell2->setValue('=100*A1');
192
193			self::assertSame(250.0, $cell2->getCalculatedValue());
194			}
195
196			public function testCellWithStringFraction(): void
197			{
198			$spreadsheet = new Spreadsheet();
199			$workSheet = $spreadsheet->getActiveSheet();
200			$cell1 = $workSheet->getCell('A1');
201			$cell1->setValue('3/4');
202			$cell2 = $workSheet->getCell('B1');
203			$cell2->setValue('=100*A1');
204
205			self::assertSame(75.0, $cell2->getCalculatedValue());
206			}
207
208			public function testCellWithStringPercentage(): void
209			{
210			$spreadsheet = new Spreadsheet();
211			$workSheet = $spreadsheet->getActiveSheet();
212			$cell1 = $workSheet->getCell('A1');
213			$cell1->setValue('2%');
214			$cell2 = $workSheet->getCell('B1');
215			$cell2->setValue('=100*A1');
216
217			self::assertSame(2.0, $cell2->getCalculatedValue());
218			}
219
220			public function testCellWithStringCurrency(): void
221			{
222			$currencyCode = StringHelper::getCurrencyCode();
223
224			$spreadsheet = new Spreadsheet();
225			$workSheet = $spreadsheet->getActiveSheet();
226			$cell1 = $workSheet->getCell('A1');
227			$cell1->setValue($currencyCode . '2');
228			$cell2 = $workSheet->getCell('B1');
229			$cell2->setValue('=100*A1');
230
231			self::assertSame(200.0, $cell2->getCalculatedValue());
232			}
233
234			public function testBranchPruningFormulaParsingSimpleCase(): void
235			{
236			$calculation = Calculation::getInstance();
237			$calculation->flushInstance(); // resets the ids
238
239			// Very simple formula
240			$formula = '=IF(A1="please +",B1)';
241			$tokens = $calculation->parseFormula($formula);
242			self::assertIsArray($tokens);
243
244			$foundEqualAssociatedToStoreKey = false;
245			$foundConditionalOnB1 = false;
246			foreach ($tokens as $token) {
247			$isBinaryOperator = $token['type'] == 'Binary Operator';
248			$isEqual = $token['value'] == '=';
249			$correctStoreKey = ($token['storeKey'] ?? '') == 'storeKey-0';
250			$correctOnlyIf = ($token['onlyIf'] ?? '') == 'storeKey-0';
251			$isB1Reference = ($token['reference'] ?? '') == 'B1';
252
253			$foundEqualAssociatedToStoreKey = $foundEqualAssociatedToStoreKey \|\|
254			($isBinaryOperator && $isEqual && $correctStoreKey);
255
256			$foundConditionalOnB1 = $foundConditionalOnB1 \|\|
257			($isB1Reference && $correctOnlyIf);
258			}
259			self::assertTrue($foundEqualAssociatedToStoreKey);
260			self::assertTrue($foundConditionalOnB1);
261			}
262
263			public function testBranchPruningFormulaParsingMultipleIfsCase(): void
264			{
265			$calculation = Calculation::getInstance();
266			$calculation->flushInstance(); // resets the ids
267
268			//
269			// Internal operation
270			$formula = '=IF(A1="please +",SUM(B1:B3))+IF(A2="please *",PRODUCT(C1:C3), C1)';
271			$tokens = $calculation->parseFormula($formula);
272			self::assertIsArray($tokens);
273
274			$plusGotTagged = false;
275			$productFunctionCorrectlyTagged = false;
276			foreach ($tokens as $token) {
277			$isBinaryOperator = $token['type'] == 'Binary Operator';
278			$isPlus = $token['value'] == '+';
279			$anyStoreKey = isset($token['storeKey']);
280			$anyOnlyIf = isset($token['onlyIf']);
281			$anyOnlyIfNot = isset($token['onlyIfNot']);
282			$plusGotTagged = $plusGotTagged \|\|
283			($isBinaryOperator && $isPlus &&
284			($anyStoreKey \|\| $anyOnlyIfNot \|\| $anyOnlyIf));
285
286			$isFunction = $token['type'] == 'Function';
287			$isProductFunction = $token['value'] == 'PRODUCT(';
288			$correctOnlyIf = ($token['onlyIf'] ?? '') == 'storeKey-1';
289			$productFunctionCorrectlyTagged = $productFunctionCorrectlyTagged \|\| ($isFunction && $isProductFunction && $correctOnlyIf);
290			}
291			self::assertFalse($plusGotTagged, 'chaining IF( should not affect the external operators');
292			self::assertTrue($productFunctionCorrectlyTagged, 'function nested inside if should be tagged to be processed only if parent branching requires it');
293			}
294
295			public function testBranchPruningFormulaParingNestedIfCase(): void
296			{
297			$calculation = Calculation::getInstance();
298			$calculation->flushInstance(); // resets the ids
299
300			$formula = '=IF(A1="please +",SUM(B1:B3),1+IF(NOT(A2="please *"),C2-C1,PRODUCT(C1:C3)))';
301			$tokens = $calculation->parseFormula($formula);
302			self::assertIsArray($tokens);
303
304			$plusCorrectlyTagged = false;
305			$productFunctionCorrectlyTagged = false;
306			$notFunctionCorrectlyTagged = false;
307			$findOneOperandCountTagged = false;
308			foreach ($tokens as $token) {
309			$value = $token['value'];
310			$isPlus = $value == '+';
311			$isProductFunction = $value == 'PRODUCT(';
312			$isNotFunction = $value == 'NOT(';
313			$isIfOperand = $token['type'] == 'Operand Count for Function IF()';
314			$isOnlyIfNotDepth1 = (array_key_exists('onlyIfNot', $token) ? $token['onlyIfNot'] : null) == 'storeKey-1';
315			$isStoreKeyDepth1 = (array_key_exists('storeKey', $token) ? $token['storeKey'] : null) == 'storeKey-1';
316			$isOnlyIfNotDepth0 = (array_key_exists('onlyIfNot', $token) ? $token['onlyIfNot'] : null) == 'storeKey-0';
317
318			$plusCorrectlyTagged = $plusCorrectlyTagged \|\| ($isPlus && $isOnlyIfNotDepth0);
319			$notFunctionCorrectlyTagged = $notFunctionCorrectlyTagged \|\| ($isNotFunction && $isOnlyIfNotDepth0 && $isStoreKeyDepth1);
320			$productFunctionCorrectlyTagged = $productFunctionCorrectlyTagged \|\| ($isProductFunction && $isOnlyIfNotDepth1 && !$isStoreKeyDepth1 && !$isOnlyIfNotDepth0);
321			$findOneOperandCountTagged = $findOneOperandCountTagged \|\| ($isIfOperand && $isOnlyIfNotDepth0);
322			}
323			self::assertTrue($plusCorrectlyTagged);
324			self::assertTrue($productFunctionCorrectlyTagged);
325			self::assertTrue($notFunctionCorrectlyTagged);
326			}
327
328			public function testBranchPruningFormulaParsingNoArgumentFunctionCase(): void
329			{
330			$calculation = Calculation::getInstance();
331			$calculation->flushInstance(); // resets the ids
332
333			$formula = '=IF(AND(TRUE(),A1="please +"),2,3)';
334			// this used to raise a parser error, we keep it even though we don't
335			// test the output
336			$calculation->parseFormula($formula);
337			self::assertTrue(true);
338			}
339
340			public function testBranchPruningFormulaParsingInequalitiesConditionsCase(): void
341			{
342			$calculation = Calculation::getInstance();
343			$calculation->flushInstance(); // resets the ids
344
345			$formula = '=IF(A1="flag",IF(A2<10, 0) + IF(A3<10000, 0))';
346			$tokens = $calculation->parseFormula($formula);
347			self::assertIsArray($tokens);
348
349			$properlyTaggedPlus = false;
350			foreach ($tokens as $token) {
351			$isPlus = $token['value'] === '+';
352			$hasOnlyIf = !empty($token['onlyIf']);
353
354			$properlyTaggedPlus = $properlyTaggedPlus \|\|
355			($isPlus && $hasOnlyIf);
356			}
357			self::assertTrue($properlyTaggedPlus);
358			}
359
360			/**
361			* @param mixed $expectedResult
362			* @param mixed $dataArray
363			* @param string $formula
364			* @param string $cellCoordinates where to put the formula
365			* @param string[] $shouldBeSetInCacheCells coordinates of cells that must
366			* be set in cache
367			* @param string[] $shouldNotBeSetInCacheCells coordinates of cells that must
368			* not be set in cache because of pruning
369			*
370			* @dataProvider dataProviderBranchPruningFullExecution
371			*/
372			public function testFullExecutionDataPruning(
373			$expectedResult,
374			$dataArray,
375			$formula,
376			$cellCoordinates,
377			$shouldBeSetInCacheCells = [],
378			$shouldNotBeSetInCacheCells = []
379			): void {
380			$spreadsheet = new Spreadsheet();
381			$sheet = $spreadsheet->getActiveSheet();
382
383			$sheet->fromArray($dataArray);
384			$cell = $sheet->getCell($cellCoordinates);
385			$calculation = Calculation::getInstance($cell->getWorksheet()->getParent());
386
387			$cell->setValue($formula);
388			$calculated = $cell->getCalculatedValue();
389			self::assertEquals($expectedResult, $calculated);
390
391			// this mostly to ensure that at least some cells are cached
392			foreach ($shouldBeSetInCacheCells as $setCell) {
393			unset($inCache);
394			$calculation->getValueFromCache('Worksheet!' . $setCell, $inCache);
395			self::assertNotEmpty($inCache);
396			}
397
398			foreach ($shouldNotBeSetInCacheCells as $notSetCell) {
399			unset($inCache);
400			$calculation->getValueFromCache('Worksheet!' . $notSetCell, $inCache);
401			self::assertEmpty($inCache);
402			}
403
404			$calculation->disableBranchPruning();
405			$calculated = $cell->getCalculatedValue();
406			self::assertEquals($expectedResult, $calculated);
407			}
408
409			public function dataProviderBranchPruningFullExecution(): array
410			{
411			return require 'tests/data/Calculation/Calculation.php';
412			}
413			}
414

PHPOffice / PhpSpreadsheet

Pull Request — master (#3336)

CalculationTest::testFullExecutionDataPruning() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like