Repository: PHPOffice/PhpSpreadsheet (php)

Issue:
HYPERLINK function not working correctly when you try to link a cell to another spreadsheet
When you set the content of a cell to =HYPERLINK("#anotherspreadshet!A1","link") the class throws the next Fatal error:
```

Uncaught exception 'PhpOffice\PhpSpreadsheet\Calculation\Exception' with message 'anotherspreadshet!A1 -> Formula Error: An unexpected error occured' in \src\PhpSpreadsheet\Cell.php:280
Stack trace:
#0 src\PhpSpreadsheet\Writer\Xlsx\Worksheet.php(1080): PhpOffice\PhpSpreadsheet\Cell->&gt;getCalculatedValue()
#1 src\PhpSpreadsheet\Writer\Xlsx\Worksheet.php(1027): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCell(Object(PhpOffice\PhpSpreadsheet\Shared\XMLWriter), Object(PhpOffice\PhpSpreadsheet\Worksheet), 'A1', Array, Array)
#2 src\PhpSpreadsheet\Writer\Xlsx\Worksheet.php(80): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeSheetData(Object(PhpOffice\PhpSpreadsheet\Shared\XMLWriter), Object(PhpOffice\PhpSpreadsheet\Worksheet), Array)
#3 srcl\PhpSpreadsheet\W src\Cell.php on line 280

```

Pull request description:
Xls Writer Treat Hyperlink Starting with # as Internal
Fix #56, which had gone stale but is now reopened. The problem which it reported with Xlsx Writer was fixed long ago. However, Xls Writer has 2 problems regarding hyperlinks. First, some logic which should have been `if ... elseif ... else ...` was coded as `if ... if ... unconditional ...`, resulting in multiple writes and a corrupt worksheet (which Excel does fix correctly). Second, it treated a hyperlink url starting with `#` (pointer to a cell in the same spreadsheet) as external, when it should be treated as internal.

Also changed `Hyperlink::isInternal` to recognize a starting `#`, and to use `str_starts_with('sheet://')` rather than `str_contains`.

This is:

- [x] a bugfix
- [ ] a new feature
- [ ] refactoring
- [ ] additional unit tests

Checklist:

- [x] Changes are covered by unit tests
  - [x] Changes are covered by existing unit tests
  - [x] New unit tests have been added
- [x] Code style is respected
- [x] Commit message explains **why** the change is made (see https://github.com/erlang/otp/wiki/Writing-good-commit-messages)
- [ ] CHANGELOG.md contains a short summary of the change and a link to the pull request if applicable
- [ ] Documentation is updated as necessary



Patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 0f13b3928..7900e663f 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -31,6 +31,7 @@ and this project adheres to [Semantic Versioning](https://semver.org).
 
 - Micro-optimization for excelToDateTimeObject. [Issue #4438](https://github.com/PHPOffice/PhpSpreadsheet/issues/4438) [PR #4442](https://github.com/PHPOffice/PhpSpreadsheet/pull/4442)
 - Print Area and Row Break. [Issue #1275](https://github.com/PHPOffice/PhpSpreadsheet/issues/1275) [PR #4450](https://github.com/PHPOffice/PhpSpreadsheet/pull/4450)
+- Xls Writer Treat Hyperlink Starting with # as Internal. [Issue #56](https://github.com/PHPOffice/PhpSpreadsheet/issues/56) [PR #4453](https://github.com/PHPOffice/PhpSpreadsheet/pull/4453)
 
 ## 2025-04-16 - 4.2.0
 
diff --git a/src/PhpSpreadsheet/Cell/Hyperlink.php b/src/PhpSpreadsheet/Cell/Hyperlink.php
index 3117a7d86..5f1c52162 100644
--- a/src/PhpSpreadsheet/Cell/Hyperlink.php
+++ b/src/PhpSpreadsheet/Cell/Hyperlink.php
@@ -68,11 +68,11 @@ class Hyperlink
     }
 
     /**
-     * Is this hyperlink internal? (to another worksheet).
+     * Is this hyperlink internal? (to another worksheet or a cell in this worksheet).
      */
     public function isInternal(): bool
     {
-        return str_contains($this->url, 'sheet://');
+        return str_starts_with($this->url, 'sheet://') || str_starts_with($this->url, '#');
     }
 
     public function getTypeHyperlink(): string
diff --git a/src/PhpSpreadsheet/Writer/Xls/Worksheet.php b/src/PhpSpreadsheet/Writer/Xls/Worksheet.php
index 0207a14a2..8744f9fee 100644
--- a/src/PhpSpreadsheet/Writer/Xls/Worksheet.php
+++ b/src/PhpSpreadsheet/Writer/Xls/Worksheet.php
@@ -467,8 +467,9 @@ class Worksheet extends BIFFwriter
             [$column, $row] = Coordinate::indexesFromString($coordinate);
 
             $url = $hyperlink->getUrl();
-
-            if (str_contains($url, 'sheet://')) {
+            if ($url[0] === '#') {
+                $url = "internal:$url";
+            } elseif (str_starts_with($url, 'sheet://')) {
                 // internal to current workbook
                 $url = str_replace('sheet://', 'internal:', $url);
             } elseif (Preg::isMatch('/^(http:|https:|ftp:|mailto:)/', $url)) {
@@ -955,12 +956,11 @@ class Worksheet extends BIFFwriter
         // Check for internal/external sheet links or default to web link
         if (Preg::isMatch('[^internal:]', $url)) {
             $this->writeUrlInternal($row1, $col1, $row2, $col2, $url);
-        }
-        if (Preg::isMatch('[^external:]', $url)) {
+        } elseif (Preg::isMatch('[^external:]', $url)) {
             $this->writeUrlExternal($row1, $col1, $row2, $col2, $url);
+        } else {
+            $this->writeUrlWeb($row1, $col1, $row2, $col2, $url);
         }
-
-        $this->writeUrlWeb($row1, $col1, $row2, $col2, $url);
     }
 
     /**


Test patch:
diff --git a/tests/PhpSpreadsheetTests/Writer/Xls/HyperlinkTest.php b/tests/PhpSpreadsheetTests/Writer/Xls/HyperlinkTest.php
new file mode 100644
index 000000000..6b8cbb5bf
--- /dev/null
+++ b/tests/PhpSpreadsheetTests/Writer/Xls/HyperlinkTest.php
@@ -0,0 +1,38 @@
+<?php
+
+declare(strict_types=1);
+
+namespace PhpOffice\PhpSpreadsheetTests\Writer\Xls;
+
+use PhpOffice\PhpSpreadsheet\Spreadsheet;
+use PhpOffice\PhpSpreadsheetTests\Functional\AbstractFunctional;
+
+class HyperlinkTest extends AbstractFunctional
+{
+    public function testHyperlink(): void
+    {
+        $spreadsheet = new Spreadsheet();
+        $sheet1 = $spreadsheet->getActiveSheet();
+        $sheet1->setTitle('First');
+        $sheet2 = $spreadsheet->createSheet();
+        $sheet2->setTitle('Second');
+        $sheet2->setCellValue('A100', 'other sheet');
+        $sheet1->setCellValue('A100', 'this sheet');
+        $sheet1->setCellValue('A1', '=HYPERLINK("#A100", "here")');
+        $sheet1->setCellValue('A2', '=HYPERLINK("#Second!A100", "there")');
+        $sheet1->setCellValue('A3', '=HYPERLINK("http://example.com", "external")');
+        $sheet1->setCellValue('A4', 'gotoA101');
+        $sheet1->getCell('A4')
+            ->getHyperlink()
+            ->setUrl('#A101');
+
+        $robj = $this->writeAndReload($spreadsheet, 'Xls');
+        $spreadsheet->disconnectWorksheets();
+        $sheet0 = $robj->setActiveSheetIndex(0);
+        self::assertSame('sheet://#A100', $sheet0->getCell('A1')->getHyperlink()->getUrl());
+        self::assertSame('sheet://#Second!A100', $sheet0->getCell('A2')->getHyperlink()->getUrl());
+        self::assertSame('http://example.com', $sheet0->getCell('A3')->getHyperlink()->getUrl());
+        self::assertSame('sheet://#A101', $sheet0->getCell('A4')->getHyperlink()->getUrl());
+        $robj->disconnectWorksheets();
+    }
+}
diff --git a/tests/PhpSpreadsheetTests/Writer/Xlsx/HyperlinkTest.php b/tests/PhpSpreadsheetTests/Writer/Xlsx/HyperlinkTest.php
new file mode 100644
index 000000000..ba7fa2087
--- /dev/null
+++ b/tests/PhpSpreadsheetTests/Writer/Xlsx/HyperlinkTest.php
@@ -0,0 +1,38 @@
+<?php
+
+declare(strict_types=1);
+
+namespace PhpOffice\PhpSpreadsheetTests\Writer\Xlsx;
+
+use PhpOffice\PhpSpreadsheet\Spreadsheet;
+use PhpOffice\PhpSpreadsheetTests\Functional\AbstractFunctional;
+
+class HyperlinkTest extends AbstractFunctional
+{
+    public function testHyperlink(): void
+    {
+        $spreadsheet = new Spreadsheet();
+        $sheet1 = $spreadsheet->getActiveSheet();
+        $sheet1->setTitle('First');
+        $sheet2 = $spreadsheet->createSheet();
+        $sheet2->setTitle('Second');
+        $sheet2->setCellValue('A100', 'other sheet');
+        $sheet1->setCellValue('A100', 'this sheet');
+        $sheet1->setCellValue('A1', '=HYPERLINK("#A100", "here")');
+        $sheet1->setCellValue('A2', '=HYPERLINK("#Second!A100", "there")');
+        $sheet1->setCellValue('A3', '=HYPERLINK("http://example.com", "external")');
+        $sheet1->setCellValue('A4', 'gotoA101');
+        $sheet1->getCell('A4')
+            ->getHyperlink()
+            ->setUrl('#A101');
+
+        $robj = $this->writeAndReload($spreadsheet, 'Xlsx');
+        $spreadsheet->disconnectWorksheets();
+        $sheet0 = $robj->setActiveSheetIndex(0);
+        self::assertSame('sheet://#A100', $sheet0->getCell('A1')->getHyperlink()->getUrl());
+        self::assertSame('sheet://#Second!A100', $sheet0->getCell('A2')->getHyperlink()->getUrl());
+        self::assertSame('http://example.com', $sheet0->getCell('A3')->getHyperlink()->getUrl());
+        self::assertSame('sheet://#A101', $sheet0->getCell('A4')->getHyperlink()->getUrl());
+        $robj->disconnectWorksheets();
+    }
+}