Calculation: `HYPERLINK()` attaches hyperlink object to wrong cell

[TobiasBg]

This is:

- [X] a bug report
- [ ] a feature request
- [X] **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 HYPERLINK( url, tooltip ) function creates a \PhpOffice\PhpSpreadsheet\Cell\Hyperlink object from a given URL and link text. I expect it to attach that object to the cell that contains the formula.

What is the current behavior?

This is working correctly if both url and tooltip are passed directly as strings (example cell C1 in the code below).

However, as soon as url, tooltip, or both are a reference to a cell that contains the desired string, the Hyperlink object is attached to the last used cell reference instead -- so that the wrong cell has the hyperlink. The three cases are illustrated in cells C2, C3, and C4 in the code below.

A wild guess: Calculation::addCellReference( ... ) maybe gets the wrong $cell argument?

What are the steps to reproduce?

This example shows the four different cases of passing a string or cell reference to the HYPERLINK() functions, and how the Hyperlink object is falsely attached to the last referenced cell:

<?php

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

use PhpOffice\PhpSpreadsheet\Spreadsheet;

$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();

// Set values with HYPERLINK formulas.

$worksheet->getCell( 'A1' )->setValue( 'https://example.net/' );
$worksheet->getCell( 'B1' )->setValue( 'Link to example.net' );
$worksheet->getCell( 'C1' )->setValue( '=HYPERLINK( "https://example.net/", "Link to example.net" )' );

$worksheet->getCell( 'A2' )->setValue( 'https://example.org/' );
$worksheet->getCell( 'B2' )->setValue( 'Link to example.org' );
$worksheet->getCell( 'C2' )->setValue( '=HYPERLINK( A2, "Link to example.org" )' );

$worksheet->getCell( 'A3' )->setValue( 'https://example.edu/' );
$worksheet->getCell( 'B3' )->setValue( 'Link to example.edu' );
$worksheet->getCell( 'C3' )->setValue( '=HYPERLINK( "https://example.edu/", B3 )' );

$worksheet->getCell( 'A4' )->setValue( 'https://example.com/' );
$worksheet->getCell( 'B4' )->setValue( 'Link to example.com' );
$worksheet->getCell( 'C4' )->setValue( '=HYPERLINK( A4, B4 )' );

// Calculate HYPERLINK formulas.

$worksheet->getCell( 'C1' )->getCalculatedValue();
$worksheet->getCell( 'C2' )->getCalculatedValue();
$worksheet->getCell( 'C3' )->getCalculatedValue();
$worksheet->getCell( 'C4' )->getCalculatedValue();

// Print formulas.

echo "Formulas with HYPERLINK():\n";

echo $worksheet->getCell( 'C1' )->getCoordinate() . ': ';
echo $worksheet->getCell( 'C1' )->getValue() . "\n";
echo $worksheet->getCell( 'C2' )->getCoordinate() . ': ';
echo $worksheet->getCell( 'C2' )->getValue() . "\n";
echo $worksheet->getCell( 'C3' )->getCoordinate() . ': ';
echo $worksheet->getCell( 'C3' )->getValue() . "\n";
echo $worksheet->getCell( 'C4' )->getCoordinate() . ': ';
echo $worksheet->getCell( 'C4' )->getValue() . "\n\n";

// View hyperlinks (URL | Tooltip) attached to cells.

echo "Hyperlinks should be attached to C1 through C4,\n";
echo "but are attached to last referenced cell A2, B3, and B4:\n\n";

echo $worksheet->getCell( 'C1' )->getCoordinate() . ': ';
echo $worksheet->getCell( 'C1' )->getHyperlink()->getUrl() . ' | ';
echo $worksheet->getCell( 'C1' )->getHyperlink()->getTooltip() . "\n";

echo $worksheet->getCell( 'A2' )->getCoordinate() . ': ';
echo $worksheet->getCell( 'A2' )->getHyperlink()->getUrl() . ' | ';
echo $worksheet->getCell( 'A2' )->getHyperlink()->getTooltip() . "\n";
echo $worksheet->getCell( 'C2' )->getCoordinate() . ': ';
echo $worksheet->getCell( 'C2' )->getHyperlink()->getUrl() . ' | ';
echo $worksheet->getCell( 'C2' )->getHyperlink()->getTooltip() . "\n";

echo $worksheet->getCell( 'B3' )->getCoordinate() . ': ';
echo $worksheet->getCell( 'B3' )->getHyperlink()->getUrl() . ' | ';
echo $worksheet->getCell( 'B3' )->getHyperlink()->getTooltip() . "\n";
echo $worksheet->getCell( 'C3' )->getCoordinate() . ': ';
echo $worksheet->getCell( 'C3' )->getHyperlink()->getUrl() . ' | ';
echo $worksheet->getCell( 'C3' )->getHyperlink()->getTooltip() . "\n";

echo $worksheet->getCell( 'A4' )->getCoordinate() . ': ';
echo $worksheet->getCell( 'A4' )->getHyperlink()->getUrl() . ' | ';
echo $worksheet->getCell( 'A4' )->getHyperlink()->getTooltip() . "\n";
echo $worksheet->getCell( 'B4' )->getCoordinate() . ': ';
echo $worksheet->getCell( 'B4' )->getHyperlink()->getUrl() . ' | ';
echo $worksheet->getCell( 'B4' )->getHyperlink()->getTooltip() . "\n";
echo $worksheet->getCell( 'C4' )->getCoordinate() . ': ';
echo $worksheet->getCell( 'C4' )->getHyperlink()->getUrl() . ' | ';
echo $worksheet->getCell( 'C4' )->getHyperlink()->getTooltip() . "\n";

What features do you think are causing the issue

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

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

This is independent from the used file format.

Which versions of PhpSpreadsheet and PHP are affected?

Tested with PhpSpreadsheet 2.0.0 and PHP 8.3.2.

[oleibman]

Possible duplicate (or expansion) of issue #2464. I will review over the weekend and close one or the other. Note that 2464 has been open a long time and I haven't had any inspiration about how to deal with it.

[TobiasBg]

Ah, thanks! I have missed that ticket... Yes, seems to be the same issue.

I guess the question is whether other functions with 'passCellReference' => true are also experiencing certain wrong behavior, or if it's only noticeable with HYPERLINK() because it does something with the passed cell reference.