Some Problems With HYPERLINK Function

[oleibman]

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?

See "current behavior" section below.

What is the current behavior?

The following code works as designed (I think):

$sheet->getCell('A2')->setValue('=HYPERLINK("http://www.example.com")');
$sheet->getCell('A3')->setValue('=HYPERLINK("http://www.example.com", "Example")');

The calculated value of the cell is the Url in A2 and the description in A3, and getHyperlink on each cell shows the correct value.

The following code does not work as well:

$sheet->getCell('B1')->setValue('http://www.example.com');
$sheet->getCell('B2')->setValue('Example');
$sheet->getCell('A2')->setValue('=HYPERLINK(B1)');
$sheet->getCell('A3')->setValue('=HYPERLINK(B1,B2)');

The calculated values in A2 and A3 are still correct, but the hyperlink on each cell is empty. And, in fact, there are non-empty hyperlinks on B1 (which is sort of defensible even though it shouldn't be so) and B2 (which shouldn't happen at all).

Another problem, even when using literals in the formula:

$sheet->getCell('A4')->setValue('=LEN(HYPERLINK("http://www.example.com", "Example"))');

Here, the calculated value is correct. But the cell also has a hyperlink attached, which I don't think is correct.

It also seems likely that if a cell has a non-empty hyperlink and the cell value is changed, the hyperlink sticks around.

The big problem here is, of course, the examples where cells are used rather than literals in the HYPERLINK function. Something is going wrong deep in the bowels of the Calculation engine, and it may take some time to figure out what that is.

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

See "current behavior" section above.

Which versions of PhpSpreadsheet and PHP are affected?

All

[TobiasBg]

Excel's (version 16.81 on a Mac) behavior for the formula

=LEN(HYPERLINK("http://www.example.com", "Example"))

is also quite interesting: It seems to add a hyperlink to the cell, where the cell content is the calculated string length -- but when trying to click the hyperlink, the URL is reported to be invalid. So, PHPSpreadsheets behavior of adding the link to the cell should be fine.