PhpSpreadsheet/tests/PhpSpreadsheetTests/Calculation/Issue4656Test.php

<?php

declare(strict_types=1);

namespace PhpOffice\PhpSpreadsheetTests\Calculation;

use PhpOffice\PhpSpreadsheet\NamedRange;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PHPUnit\Framework\TestCase;

class Issue4656Test extends TestCase
{
    public function testIssue4656(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('B1', 2);
        $sheet->setCellValue('C1', 1);
        $sheet->setCellValue('D1', 3);
        $sheet->setCellValue('E1', 2);
        $sheet->setCellValue('A1', '=RANK(B1,(C1,E1))');
        $sheet->setCellValue('A2', '=RANK(B1,C1:E1)');
        self::assertSame(1, $sheet->getCell('A1')->getCalculatedValue());
        self::assertSame(2, $sheet->getCell('A2')->getCalculatedValue());
        $spreadsheet->disconnectWorksheets();
    }

    public function testIssue4656Original(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue('B1', 1);
        $sheet->setCellValue('C1', 1);
        $sheet->setCellValue('D1', 2);
        $sheet->setCellValue('A1', '=RANK(B1,(C1,D1))');
        self::assertSame(2, $sheet->getCell('A1')->getCalculatedValue());
        $spreadsheet->disconnectWorksheets();
    }

    public static function testIssue4832a(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet1 = $spreadsheet->getActiveSheet();
        $sheet1->setTitle('Feuil1');
        $sheet2 = $spreadsheet->createSheet();
        $sheet2->setTitle('Feuil2');
        $sheet1->setCellValue('A1', '=DATE(2020,1,1)');
        $sheet1->setCellValue('A2', '=DATE(2026,1,1)');
        $sheet2->setCellValue('A3', '=YEARFRAC(Feuil1!A1,Feuil1!A2)');
        $sheet2->setCellValue('A4', '=(YEARFRAC(Feuil1!A1,Feuil1!A2))');
        $sheet2->setCellValue('A5', '=(YEARFRAC(Feuil1!A1,Feuil1!A2)*360)');
        $sheet2->setCellValue('A6', '=360*(YEARFRAC(Feuil1!A1,Feuil1!A2))');
        $sheet2->setCellValue('A7', '=( YEARFRAC(Feuil1!A1,Feuil1!A2))');

        self::assertSame(6, $sheet2->getCell('A3')->getCalculatedValue());
        self::assertSame(6, $sheet2->getCell('A4')->getCalculatedValue());
        self::assertSame(2160, $sheet2->getCell('A5')->getCalculatedValue());
        self::assertSame(2160, $sheet2->getCell('A6')->getCalculatedValue());
        self::assertSame(6, $sheet2->getCell('A7')->getCalculatedValue());
        $spreadsheet->disconnectWorksheets();
    }

    public static function testIssue4832b(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet1 = $spreadsheet->getActiveSheet();
        $sheet1->setTitle('SHEET1');
        $sheet2 = $spreadsheet->createSheet();
        $sheet2->setTitle('Feuil2');
        $sheet1->setCellValue('B1', 1);
        $sheet1->setCellValue('C1', 1);
        $sheet1->setCellValue('D1', 2);
        $sheet2->setCellValue('A1', '=(RANK(SHEET1!B1,(SHEET1!C1,SHEET1!D1)))');
        self::assertSame(2, $sheet2->getCell('A1')->getCalculatedValue());
        $spreadsheet->disconnectWorksheets();
    }

    public static function testCellRanges(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->fromArray([
            [10, 100, 1000],
            [20, 200, 2000],
            [30, 300, 3000],
            [40, 400, 4000],
            [50, 500, 5000],
        ]);
        $spreadsheet->addNamedRange(new NamedRange('ColumnA', $sheet, '$A$1:$A$5'));
        $spreadsheet->addNamedRange(new NamedRange('Rows3And4', $sheet, '$A$3:$C$4'));

        $sheet->setCellValue('F1', '=SUM((A1:A3, A5), (B1:B2, B4:B5), C3)');
        self::assertSame(4310, $sheet->getCell('F1')->getCalculatedValue());
        $sheet->setCellValue('F2', '=LARGE((A1:A5, C1:C5), 6)');
        self::assertSame(50, $sheet->getCell('F2')->getCalculatedValue());

        // test some intersections while we're at it
        $sheet->setCellValue('F3', '=LARGE((A1:A5 A3:C4), 2)');
        self::assertSame(30, $sheet->getCell('F3')->getCalculatedValue());
        $sheet->setCellValue('F4', '=LARGE((ColumnA Rows3And4), 1)');
        self::assertSame(40, $sheet->getCell('F4')->getCalculatedValue());
        $sheet->setCellValue('F5', '=LARGE((ColumnA Rows3And4), 3)');
        self::assertSame('#NUM!', $sheet->getCell('F5')->getCalculatedValue(), 'only 2 cells in intersection so cannot compute 3rd largest');

        // back to unions
        $sheet->setCellValue('G3', '=LARGE((A1:A5,A3:C4), 2)');
        self::assertSame(3000, $sheet->getCell('G3')->getCalculatedValue());
        $sheet->setCellValue('G4', '=LARGE((ColumnA,Rows3And4), 1)');
        self::assertSame(4000, $sheet->getCell('G4')->getCalculatedValue());
        $spreadsheet->disconnectWorksheets();
    }
}