mirror of
https://github.com/PHPOffice/PhpSpreadsheet.git
synced 2026-09-02 05:27:44 +00:00
oleibman
dcc25637f2
Fix #641 (marked stale in 2018, but now reopened). When a sheet's title is changed, PhpSpreadsheet updates references to the old sheet name found in formulas. Which is a good idea when the sheet is attached to the spreadsheet, but a bad idea when it isn't (often because it has been cloned without re-attaching to the spreadsheet). This PR continues to change formulas in the former case, but will no longer do so for the latter.
83 lines
3.0 KiB
PHP
83 lines
3.0 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace PhpOffice\PhpSpreadsheetTests\Worksheet;
|
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
use PHPUnit\Framework\TestCase;
|
|
|
|
class Issue641Test extends TestCase
|
|
{
|
|
/**
|
|
* Problem cloning sheet referred to in formulas.
|
|
*/
|
|
public function testIssue641(): void
|
|
{
|
|
$xlsx = new Spreadsheet();
|
|
$xlsx->removeSheetByIndex(0);
|
|
$availableWs = [];
|
|
|
|
$worksheet = $xlsx->createSheet();
|
|
$worksheet->setTitle('Condensed A');
|
|
$worksheet->getCell('A1')->setValue("=SUM('Detailed A'!A1:A10)");
|
|
$worksheet->getCell('A2')->setValue(mt_rand(1, 30));
|
|
$availableWs[] = 'Condensed A';
|
|
|
|
$worksheet = $xlsx->createSheet();
|
|
$worksheet->setTitle('Condensed B');
|
|
$worksheet->getCell('A1')->setValue("=SUM('Detailed B'!A1:A10)");
|
|
$worksheet->getCell('A2')->setValue(mt_rand(1, 30));
|
|
$availableWs[] = 'Condensed B';
|
|
|
|
// at this point the value in worksheet 'Condensed B' cell A1 is
|
|
// =SUM('Detailed B'!A1:A10)
|
|
|
|
// worksheet in question is cloned and totals are attached
|
|
$totalWs1 = clone $xlsx->getSheet($xlsx->getSheetCount() - 1);
|
|
$totalWs1->setTitle('Condensed Total');
|
|
$xlsx->addSheet($totalWs1);
|
|
$formula = '=';
|
|
foreach ($availableWs as $ws) {
|
|
$formula .= sprintf("+'%s'!A2", $ws);
|
|
}
|
|
$totalWs1->getCell('A1')->setValue("=SUM('Detailed Total'!A1:A10)");
|
|
$totalWs1->getCell('A2')->setValue($formula);
|
|
|
|
$availableWs = [];
|
|
|
|
$worksheet = $xlsx->createSheet();
|
|
$worksheet->setTitle('Detailed A');
|
|
for ($step = 1; $step <= 10; ++$step) {
|
|
$worksheet->getCell("A{$step}")->setValue(mt_rand(1, 30));
|
|
}
|
|
$availableWs[] = 'Detailed A';
|
|
|
|
$worksheet = $xlsx->createSheet();
|
|
$worksheet->setTitle('Detailed B');
|
|
for ($step = 1; $step <= 10; ++$step) {
|
|
$worksheet->getCell("A{$step}")->setValue(mt_rand(1, 30));
|
|
}
|
|
$availableWs[] = 'Detailed B';
|
|
|
|
$totalWs2 = clone $xlsx->getSheet($xlsx->getSheetCount() - 1);
|
|
$totalWs2->setTitle('Detailed Total');
|
|
$xlsx->addSheet($totalWs2);
|
|
|
|
for ($step = 1; $step <= 10; ++$step) {
|
|
$formula = '=';
|
|
foreach ($availableWs as $ws) {
|
|
$formula .= sprintf("+'%s'!A%s", $ws, $step);
|
|
}
|
|
$totalWs2->getCell("A{$step}")->setValue($formula);
|
|
}
|
|
|
|
self::assertSame("=SUM('Detailed A'!A1:A10)", $xlsx->getSheetByName('Condensed A')?->getCell('A1')?->getValue());
|
|
self::assertSame("=SUM('Detailed B'!A1:A10)", $xlsx->getSheetByName('Condensed B')?->getCell('A1')?->getValue());
|
|
self::assertSame("=SUM('Detailed Total'!A1:A10)", $xlsx->getSheetByName('Condensed Total')?->getCell('A1')?->getValue());
|
|
self::assertSame("=+'Detailed A'!A1+'Detailed B'!A1", $xlsx->getSheetByName('Detailed Total')?->getCell('A1')?->getValue());
|
|
|
|
$xlsx->disconnectWorksheets();
|
|
}
|
|
}
|