PHPOffice /
PhpSpreadsheet
| Total Complexity | 42 |
| Total Lines | 641 |
| Duplicated Lines | 0 % |
| Changes | 1 | ||
| Bugs | 0 | Features | 0 |
Complex classes like ReferenceHelperTest often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use ReferenceHelperTest, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 18 | class ReferenceHelperTest extends TestCase |
||
| 19 | { |
||
| 20 | public function testColumnSort(): void |
||
| 21 | { |
||
| 22 | $columnBase = $columnExpectedResult = [ |
||
| 23 | 'A', 'B', 'Z', |
||
| 24 | 'AA', 'AB', 'AZ', |
||
| 25 | 'BA', 'BB', 'BZ', |
||
| 26 | 'ZA', 'ZB', 'ZZ', |
||
| 27 | 'AAA', 'AAB', 'AAZ', |
||
| 28 | 'ABA', 'ABB', 'ABZ', |
||
| 29 | 'AZA', 'AZB', 'AZZ', |
||
| 30 | 'BAA', 'BAB', 'BAZ', |
||
| 31 | 'BBA', 'BBB', 'BBZ', |
||
| 32 | 'BZA', 'BZB', 'BZZ', |
||
| 33 | ]; |
||
| 34 | shuffle($columnBase); |
||
| 35 | usort($columnBase, [ReferenceHelper::class, 'columnSort']); |
||
| 36 | foreach ($columnBase as $key => $value) { |
||
| 37 | self::assertEquals($columnExpectedResult[$key], $value); |
||
| 38 | } |
||
| 39 | } |
||
| 40 | |||
| 41 | public function testColumnReverseSort(): void |
||
| 42 | { |
||
| 43 | $columnBase = $columnExpectedResult = [ |
||
| 44 | 'A', 'B', 'Z', |
||
| 45 | 'AA', 'AB', 'AZ', |
||
| 46 | 'BA', 'BB', 'BZ', |
||
| 47 | 'ZA', 'ZB', 'ZZ', |
||
| 48 | 'AAA', 'AAB', 'AAZ', |
||
| 49 | 'ABA', 'ABB', 'ABZ', |
||
| 50 | 'AZA', 'AZB', 'AZZ', |
||
| 51 | 'BAA', 'BAB', 'BAZ', |
||
| 52 | 'BBA', 'BBB', 'BBZ', |
||
| 53 | 'BZA', 'BZB', 'BZZ', |
||
| 54 | ]; |
||
| 55 | shuffle($columnBase); |
||
| 56 | $columnExpectedResult = array_reverse($columnExpectedResult); |
||
| 57 | usort($columnBase, [ReferenceHelper::class, 'columnReverseSort']); |
||
| 58 | foreach ($columnBase as $key => $value) { |
||
| 59 | self::assertEquals($columnExpectedResult[$key], $value); |
||
| 60 | } |
||
| 61 | } |
||
| 62 | |||
| 63 | public function testCellSort(): void |
||
| 64 | { |
||
| 65 | $cellBase = $columnExpectedResult = [ |
||
| 66 | 'A1', 'B1', 'AZB1', |
||
| 67 | 'BBB1', 'BB2', 'BAB2', |
||
| 68 | 'BZA2', 'Z3', 'AZA3', |
||
| 69 | 'BZB3', 'AB5', 'AZ6', |
||
| 70 | 'ABZ7', 'BA9', 'BZ9', |
||
| 71 | 'AAA9', 'AAZ9', 'BA10', |
||
| 72 | 'BZZ10', 'ZA11', 'AAB11', |
||
| 73 | 'BBZ29', 'BAA32', 'ZZ43', |
||
| 74 | 'AZZ43', 'BAZ67', 'ZB78', |
||
| 75 | 'ABA121', 'ABB289', 'BBA544', |
||
| 76 | ]; |
||
| 77 | shuffle($cellBase); |
||
| 78 | usort($cellBase, [ReferenceHelper::class, 'cellSort']); |
||
| 79 | foreach ($cellBase as $key => $value) { |
||
| 80 | self::assertEquals($columnExpectedResult[$key], $value); |
||
| 81 | } |
||
| 82 | } |
||
| 83 | |||
| 84 | public function testCellReverseSort(): void |
||
| 85 | { |
||
| 86 | $cellBase = $columnExpectedResult = [ |
||
| 87 | 'BBA544', 'ABB289', 'ABA121', |
||
| 88 | 'ZB78', 'BAZ67', 'AZZ43', |
||
| 89 | 'ZZ43', 'BAA32', 'BBZ29', |
||
| 90 | 'AAB11', 'ZA11', 'BZZ10', |
||
| 91 | 'BA10', 'AAZ9', 'AAA9', |
||
| 92 | 'BZ9', 'BA9', 'ABZ7', |
||
| 93 | 'AZ6', 'AB5', 'BZB3', |
||
| 94 | 'AZA3', 'Z3', 'BZA2', |
||
| 95 | 'BAB2', 'BB2', 'BBB1', |
||
| 96 | 'AZB1', 'B1', 'A1', |
||
| 97 | ]; |
||
| 98 | shuffle($cellBase); |
||
| 99 | usort($cellBase, [ReferenceHelper::class, 'cellReverseSort']); |
||
| 100 | foreach ($cellBase as $key => $value) { |
||
| 101 | self::assertEquals($columnExpectedResult[$key], $value); |
||
| 102 | } |
||
| 103 | } |
||
| 104 | |||
| 105 | #[\PHPUnit\Framework\Attributes\DataProvider('providerFormulaUpdates')] |
||
| 106 | public function testUpdateFormula(string $formula, int $insertRows, int $insertColumns, string $worksheet, string $expectedResult): void |
||
| 107 | { |
||
| 108 | $referenceHelper = ReferenceHelper::getInstance(); |
||
| 109 | |||
| 110 | $result = $referenceHelper->updateFormulaReferences($formula, 'A1', $insertRows, $insertColumns, $worksheet); |
||
| 111 | |||
| 112 | self::assertSame($expectedResult, $result); |
||
| 113 | } |
||
| 114 | |||
| 115 | public static function providerFormulaUpdates(): array |
||
| 116 | { |
||
| 117 | return require 'tests/data/ReferenceHelperFormulaUpdates.php'; |
||
| 118 | } |
||
| 119 | |||
| 120 | #[\PHPUnit\Framework\Attributes\DataProvider('providerMultipleWorksheetFormulaUpdates')] |
||
| 121 | public function testUpdateFormulaForMultipleWorksheets(string $formula, int $insertRows, int $insertColumns, string $expectedResult): void |
||
| 122 | { |
||
| 123 | $referenceHelper = ReferenceHelper::getInstance(); |
||
| 124 | |||
| 125 | $result = $referenceHelper->updateFormulaReferencesAnyWorksheet($formula, $insertRows, $insertColumns); |
||
| 126 | |||
| 127 | self::assertSame($expectedResult, $result); |
||
| 128 | } |
||
| 129 | |||
| 130 | public static function providerMultipleWorksheetFormulaUpdates(): array |
||
| 133 | } |
||
| 134 | |||
| 135 | public function testInsertNewBeforeRetainDataType(): void |
||
| 136 | { |
||
| 137 | $spreadsheet = new Spreadsheet(); |
||
| 138 | $sheet = $spreadsheet->getActiveSheet(); |
||
| 139 | $cell = $sheet->getCell('A1'); |
||
| 140 | $cell->setValueExplicit('+1', DataType::TYPE_STRING); |
||
| 141 | $oldDataType = $cell->getDataType(); |
||
| 142 | $oldValue = $cell->getValue(); |
||
| 143 | |||
| 144 | $sheet->insertNewRowBefore(1); |
||
| 145 | $newCell = $sheet->getCell('A2'); |
||
| 146 | $newDataType = $newCell->getDataType(); |
||
| 147 | $newValue = $newCell->getValue(); |
||
| 148 | |||
| 149 | self::assertSame($oldValue, $newValue); |
||
| 150 | self::assertSame($oldDataType, $newDataType); |
||
| 151 | $spreadsheet->disconnectWorksheets(); |
||
| 152 | } |
||
| 153 | |||
| 154 | public function testRemoveColumnShiftsCorrectColumnValueIntoRemovedColumnCoordinates(): void |
||
| 155 | { |
||
| 156 | $spreadsheet = new Spreadsheet(); |
||
| 157 | $sheet = $spreadsheet->getActiveSheet(); |
||
| 158 | $sheet->fromArray([ |
||
| 159 | ['a1', 'b1', 'c1'], |
||
| 160 | ['a2', 'b2', null], |
||
| 161 | ]); |
||
| 162 | |||
| 163 | $cells = $sheet->toArray(); |
||
| 164 | self::assertSame('a1', $cells[0][0]); |
||
| 165 | self::assertSame('b1', $cells[0][1]); |
||
| 166 | self::assertSame('c1', $cells[0][2]); |
||
| 167 | self::assertSame('a2', $cells[1][0]); |
||
| 168 | self::assertSame('b2', $cells[1][1]); |
||
| 169 | self::assertNull($cells[1][2]); |
||
| 170 | |||
| 171 | $sheet->removeColumn('B'); |
||
| 172 | |||
| 173 | $cells = $sheet->toArray(); |
||
| 174 | self::assertSame('a1', $cells[0][0]); |
||
| 175 | self::assertSame('c1', $cells[0][1]); |
||
| 176 | self::assertArrayNotHasKey(2, $cells[0]); |
||
| 177 | self::assertSame('a2', $cells[1][0]); |
||
| 178 | self::assertNull($cells[1][1]); |
||
| 179 | self::assertArrayNotHasKey(2, $cells[1]); |
||
| 180 | $spreadsheet->disconnectWorksheets(); |
||
| 181 | } |
||
| 182 | |||
| 183 | public function testInsertRowsWithPageBreaks(): void |
||
| 184 | { |
||
| 185 | $spreadsheet = new Spreadsheet(); |
||
| 186 | $sheet = $spreadsheet->getActiveSheet(); |
||
| 187 | $sheet->fromArray([[1, 2], [3, 4], [5, 6], [7, 8], [9, 10]], null, 'A1', true); |
||
| 188 | $sheet->setBreak('A2', Worksheet::BREAK_ROW); |
||
| 189 | $sheet->setBreak('A5', Worksheet::BREAK_ROW); |
||
| 190 | |||
| 191 | $sheet->insertNewRowBefore(2, 2); |
||
| 192 | |||
| 193 | $breaks = $sheet->getBreaks(); |
||
| 194 | ksort($breaks); |
||
| 195 | self::assertSame(['A4' => Worksheet::BREAK_ROW, 'A7' => Worksheet::BREAK_ROW], $breaks); |
||
| 196 | $spreadsheet->disconnectWorksheets(); |
||
| 197 | } |
||
| 198 | |||
| 199 | public function testDeleteRowsWithPageBreaks(): void |
||
| 212 | } |
||
| 213 | |||
| 214 | public function testInsertRowsWithComments(): void |
||
| 215 | { |
||
| 216 | $spreadsheet = new Spreadsheet(); |
||
| 217 | $sheet = $spreadsheet->getActiveSheet(); |
||
| 218 | $sheet->fromArray([[1, 2], [3, 4], [5, 6], [7, 8], [9, 10]], null, 'A1', true); |
||
| 219 | $sheet->getComment('A2')->getText()->createText('First Comment'); |
||
| 220 | $sheet->getComment('A5')->getText()->createText('Second Comment'); |
||
| 221 | |||
| 222 | $sheet->insertNewRowBefore(2, 2); |
||
| 223 | |||
| 224 | $comments = array_map( |
||
| 225 | fn (Comment $value): string => $value->getText()->getPlainText(), |
||
| 226 | $sheet->getComments() |
||
| 227 | ); |
||
| 228 | |||
| 229 | self::assertSame(['A4' => 'First Comment', 'A7' => 'Second Comment'], $comments); |
||
| 230 | $spreadsheet->disconnectWorksheets(); |
||
| 231 | } |
||
| 232 | |||
| 233 | public function testDeleteRowsWithComments(): void |
||
| 250 | } |
||
| 251 | |||
| 252 | public function testInsertRowsWithHyperlinks(): void |
||
| 253 | { |
||
| 254 | $spreadsheet = new Spreadsheet(); |
||
| 255 | $sheet = $spreadsheet->getActiveSheet(); |
||
| 256 | $sheet->fromArray([[1, 2], [3, 4], [5, 6], [7, 8], [9, 10]], null, 'A1', true); |
||
| 257 | $sheet->getCell('A2')->getHyperlink()->setUrl('https://github.com/PHPOffice/PhpSpreadsheet'); |
||
| 258 | $sheet->getCell('A5')->getHyperlink()->setUrl('https://phpspreadsheet.readthedocs.io/en/latest/'); |
||
| 259 | |||
| 260 | $sheet->insertNewRowBefore(2, 2); |
||
| 261 | |||
| 262 | $hyperlinks = array_map( |
||
| 263 | fn (Hyperlink $value) => $value->getUrl(), |
||
| 264 | $sheet->getHyperlinkCollection() |
||
| 265 | ); |
||
| 266 | ksort($hyperlinks); |
||
| 267 | |||
| 268 | self::assertSame( |
||
| 269 | [ |
||
| 270 | 'A4' => 'https://github.com/PHPOffice/PhpSpreadsheet', |
||
| 271 | 'A7' => 'https://phpspreadsheet.readthedocs.io/en/latest/', |
||
| 272 | ], |
||
| 273 | $hyperlinks |
||
| 274 | ); |
||
| 275 | $spreadsheet->disconnectWorksheets(); |
||
| 276 | } |
||
| 277 | |||
| 278 | public function testDeleteRowsWithHyperlinks(): void |
||
| 295 | } |
||
| 296 | |||
| 297 | public function testInsertRowsWithConditionalFormatting(): void |
||
| 298 | { |
||
| 299 | $spreadsheet = new Spreadsheet(); |
||
| 300 | $sheet = $spreadsheet->getActiveSheet(); |
||
| 301 | $sheet->fromArray([[1, 2, 3, 4], [3, 4, 5, 6], [5, 6, 7, 8], [7, 8, 9, 10], [9, 10, 11, 12]], null, 'C3', true); |
||
| 302 | $sheet->getCell('H5')->setValue(5); |
||
| 303 | |||
| 304 | $cellRange = 'C3:F7'; |
||
| 305 | $this->setConditionalFormatting($sheet, $cellRange); |
||
| 306 | |||
| 307 | $sheet->insertNewRowBefore(4, 2); |
||
| 308 | |||
| 309 | $styles = $sheet->getConditionalStylesCollection(); |
||
| 310 | // verify that the conditional range has been updated |
||
| 311 | self::assertSame('C3:F9', array_keys($styles)[0]); |
||
| 312 | // verify that the conditions have been updated |
||
| 313 | foreach ($styles as $style) { |
||
| 314 | foreach ($style as $conditions) { |
||
| 315 | self::assertSame('$H$7', $conditions->getConditions()[0]); |
||
| 316 | } |
||
| 317 | } |
||
| 318 | $spreadsheet->disconnectWorksheets(); |
||
| 319 | } |
||
| 320 | |||
| 321 | public function testInsertColumnssWithConditionalFormatting(): void |
||
| 322 | { |
||
| 323 | $spreadsheet = new Spreadsheet(); |
||
| 324 | $sheet = $spreadsheet->getActiveSheet(); |
||
| 325 | $sheet->fromArray([[1, 2, 3, 4], [3, 4, 5, 6], [5, 6, 7, 8], [7, 8, 9, 10], [9, 10, 11, 12]], null, 'C3', true); |
||
| 326 | $sheet->getCell('H5')->setValue(5); |
||
| 327 | |||
| 328 | $cellRange = 'C3:F7'; |
||
| 329 | $this->setConditionalFormatting($sheet, $cellRange); |
||
| 330 | |||
| 331 | $sheet->insertNewColumnBefore('C', 2); |
||
| 332 | |||
| 333 | $styles = $sheet->getConditionalStylesCollection(); |
||
| 334 | // verify that the conditional range has been updated |
||
| 335 | self::assertSame('E3:H7', array_keys($styles)[0]); |
||
| 336 | // verify that the conditions have been updated |
||
| 337 | foreach ($styles as $style) { |
||
| 338 | foreach ($style as $conditions) { |
||
| 339 | self::assertSame('$J$5', $conditions->getConditions()[0]); |
||
| 340 | } |
||
| 341 | } |
||
| 342 | $spreadsheet->disconnectWorksheets(); |
||
| 343 | } |
||
| 344 | |||
| 345 | public function testDeleteRowsWithConditionalFormatting(): void |
||
| 346 | { |
||
| 347 | $spreadsheet = new Spreadsheet(); |
||
| 348 | $sheet = $spreadsheet->getActiveSheet(); |
||
| 349 | $sheet->fromArray([[1, 2, 3, 4], [3, 4, 5, 6], [5, 6, 7, 8], [7, 8, 9, 10], [9, 10, 11, 12]], null, 'C3', true); |
||
| 350 | $sheet->getCell('H5')->setValue(5); |
||
| 351 | |||
| 352 | $cellRange = 'C3:F7'; |
||
| 353 | $this->setConditionalFormatting($sheet, $cellRange); |
||
| 354 | |||
| 355 | $sheet->removeRow(4, 2); |
||
| 356 | |||
| 357 | $styles = $sheet->getConditionalStylesCollection(); |
||
| 358 | // verify that the conditional range has been updated |
||
| 359 | self::assertSame('C3:F5', array_keys($styles)[0]); |
||
| 360 | // verify that the conditions have been updated |
||
| 361 | foreach ($styles as $style) { |
||
| 362 | foreach ($style as $conditions) { |
||
| 363 | self::assertSame('$H$5', $conditions->getConditions()[0]); |
||
| 364 | } |
||
| 365 | } |
||
| 366 | $spreadsheet->disconnectWorksheets(); |
||
| 367 | } |
||
| 368 | |||
| 369 | public function testDeleteColumnsWithConditionalFormatting(): void |
||
| 370 | { |
||
| 371 | $spreadsheet = new Spreadsheet(); |
||
| 372 | $sheet = $spreadsheet->getActiveSheet(); |
||
| 373 | $sheet->fromArray([[1, 2, 3, 4], [3, 4, 5, 6], [5, 6, 7, 8], [7, 8, 9, 10], [9, 10, 11, 12]], null, 'C3', true); |
||
| 374 | $sheet->getCell('H5')->setValue(5); |
||
| 375 | |||
| 376 | $cellRange = 'C3:F7'; |
||
| 377 | $this->setConditionalFormatting($sheet, $cellRange); |
||
| 378 | |||
| 379 | $sheet->removeColumn('D', 2); |
||
| 380 | |||
| 381 | $styles = $sheet->getConditionalStylesCollection(); |
||
| 382 | // verify that the conditional range has been updated |
||
| 383 | self::assertSame('C3:D7', array_keys($styles)[0]); |
||
| 384 | // verify that the conditions have been updated |
||
| 385 | foreach ($styles as $style) { |
||
| 386 | foreach ($style as $conditions) { |
||
| 387 | self::assertSame('$F$5', $conditions->getConditions()[0]); |
||
| 388 | } |
||
| 389 | } |
||
| 390 | $spreadsheet->disconnectWorksheets(); |
||
| 391 | } |
||
| 392 | |||
| 393 | private function setConditionalFormatting(Worksheet $sheet, string $cellRange): void |
||
| 394 | { |
||
| 395 | $conditionalStyles = []; |
||
| 396 | $wizardFactory = new Wizard($cellRange); |
||
| 397 | /** @var Wizard\CellValue $cellWizard */ |
||
| 398 | $cellWizard = $wizardFactory->newRule(Wizard::CELL_VALUE); |
||
| 399 | |||
| 400 | $cellWizard->equals('$H$5', Wizard::VALUE_TYPE_CELL); |
||
| 401 | $conditionalStyles[] = $cellWizard->getConditional(); |
||
| 402 | |||
| 403 | $cellWizard->greaterThan('$H$5', Wizard::VALUE_TYPE_CELL); |
||
| 404 | $conditionalStyles[] = $cellWizard->getConditional(); |
||
| 405 | |||
| 406 | $cellWizard->lessThan('$H$5', Wizard::VALUE_TYPE_CELL); |
||
| 407 | $conditionalStyles[] = $cellWizard->getConditional(); |
||
| 408 | |||
| 409 | $sheet->getStyle($cellWizard->getCellRange()) |
||
| 410 | ->setConditionalStyles($conditionalStyles); |
||
| 411 | } |
||
| 412 | |||
| 413 | public function testInsertRowsWithPrintArea(): void |
||
| 414 | { |
||
| 415 | $spreadsheet = new Spreadsheet(); |
||
| 416 | $sheet = $spreadsheet->getActiveSheet(); |
||
| 417 | $sheet->getPageSetup()->setPrintArea('A1:J10'); |
||
| 418 | |||
| 419 | $sheet->insertNewRowBefore(2, 2); |
||
| 420 | |||
| 421 | $printArea = $sheet->getPageSetup()->getPrintArea(); |
||
| 422 | self::assertSame('A1:J12', $printArea); |
||
| 423 | $spreadsheet->disconnectWorksheets(); |
||
| 424 | } |
||
| 425 | |||
| 426 | public function testInsertColumnsWithPrintArea(): void |
||
| 427 | { |
||
| 428 | $spreadsheet = new Spreadsheet(); |
||
| 429 | $sheet = $spreadsheet->getActiveSheet(); |
||
| 430 | $sheet->getPageSetup()->setPrintArea('A1:J10'); |
||
| 431 | |||
| 432 | $sheet->insertNewColumnBefore('B', 2); |
||
| 433 | |||
| 434 | $printArea = $sheet->getPageSetup()->getPrintArea(); |
||
| 435 | self::assertSame('A1:L10', $printArea); |
||
| 436 | $spreadsheet->disconnectWorksheets(); |
||
| 437 | } |
||
| 438 | |||
| 439 | public function testDeleteRowsWithPrintArea(): void |
||
| 450 | } |
||
| 451 | |||
| 452 | public function testDeleteColumnsWithPrintArea(): void |
||
| 453 | { |
||
| 454 | $spreadsheet = new Spreadsheet(); |
||
| 455 | $sheet = $spreadsheet->getActiveSheet(); |
||
| 456 | $sheet->getPageSetup()->setPrintArea('A1:J10'); |
||
| 457 | |||
| 458 | $sheet->removeColumn('B', 2); |
||
| 459 | |||
| 460 | $printArea = $sheet->getPageSetup()->getPrintArea(); |
||
| 461 | self::assertSame('A1:H10', $printArea); |
||
| 462 | $spreadsheet->disconnectWorksheets(); |
||
| 463 | } |
||
| 464 | |||
| 465 | public function testInsertDeleteRowsWithDefinedNames(): void |
||
| 512 | } |
||
| 513 | |||
| 514 | public function testInsertDeleteColumnsWithDefinedNames(): void |
||
| 515 | { |
||
| 516 | $spreadsheet = $this->buildDefinedNamesTestWorkbook(); |
||
| 517 | /** @var Worksheet $dataSheet */ |
||
| 518 | $dataSheet = $spreadsheet->getSheetByName('Data'); |
||
| 519 | /** @var Worksheet $totalsSheet */ |
||
| 520 | $totalsSheet = $spreadsheet->getSheetByName('Totals'); |
||
| 521 | |||
| 522 | /** @var NamedRange $firstColumn */ |
||
| 523 | $firstColumn = $spreadsheet->getNamedRange('FirstColumn'); |
||
| 524 | /** @var NamedRange $secondColumn */ |
||
| 525 | $secondColumn = $spreadsheet->getNamedRange('SecondColumn'); |
||
| 526 | |||
| 527 | $dataSheet->setCellValue('D2', '=FirstTotal'); |
||
| 528 | $dataSheet->setCellValue('D3', '=FirstTotal'); |
||
| 529 | $dataSheet->setCellValue('B2', '=SecondTotal'); |
||
| 530 | $dataSheet->setCellValue('B3', '=SecondTotal'); |
||
| 531 | $dataSheet->setCellValue('B4', '=ProductTotal'); |
||
| 532 | |||
| 533 | $dataSheet->insertNewColumnBefore('A', 3); |
||
| 534 | self::assertSame('=Data!$D$2:$D6', $firstColumn->getRange()); |
||
| 535 | self::assertSame('=Data!B$2:B6', $secondColumn->getRange()); |
||
| 536 | $dataSheet->removeColumn('A'); |
||
| 537 | self::assertSame('=Data!$C$2:$C6', $firstColumn->getRange()); |
||
| 538 | self::assertSame('=Data!B$2:B6', $secondColumn->getRange()); |
||
| 539 | |||
| 540 | self::assertSame(42, $dataSheet->getCell('F2')->getCalculatedValue()); |
||
| 541 | self::assertSame(56, $dataSheet->getCell('F3')->getCalculatedValue()); |
||
| 542 | self::assertSame(36, $dataSheet->getCell('D2')->getCalculatedValue()); |
||
| 543 | self::assertSame(49, $dataSheet->getCell('D3')->getCalculatedValue()); |
||
| 544 | |||
| 545 | $totalsSheet->setCellValue('B2', '=SecondTotal'); |
||
| 546 | $totalsSheet->setCellValue('B3', '=SecondTotal'); |
||
| 547 | self::assertSame(42, $totalsSheet->getCell('B2')->getCalculatedValue()); |
||
| 548 | self::assertSame(56, $totalsSheet->getCell('B3')->getCalculatedValue()); |
||
| 549 | |||
| 550 | self::assertSame(4608, $dataSheet->getCell('D4')->getCalculatedValue()); |
||
| 551 | |||
| 552 | $spreadsheet->disconnectWorksheets(); |
||
| 553 | } |
||
| 554 | |||
| 555 | private function buildDefinedNamesTestWorkbook(): Spreadsheet |
||
| 556 | { |
||
| 557 | $spreadsheet = new Spreadsheet(); |
||
| 558 | $dataSheet = $spreadsheet->getActiveSheet(); |
||
| 559 | $dataSheet->setTitle('Data'); |
||
| 560 | |||
| 561 | $totalsSheet = $spreadsheet->addSheet(new Worksheet()); |
||
| 562 | $totalsSheet->setTitle('Totals'); |
||
| 563 | |||
| 564 | $spreadsheet->setActiveSheetIndexByName('Data'); |
||
| 565 | |||
| 566 | $dataSheet->fromArray([['Column 1', 'Column 2'], [2, 1], [4, 3], [6, 5], [8, 7], [10, 9], [12, 11], [14, 13], [16, 15]], null, 'A1', true); |
||
| 567 | $dataSheet->insertNewColumnBefore('B', 1); |
||
| 568 | |||
| 569 | $spreadsheet->addNamedRange( |
||
| 570 | new NamedRange('FirstColumn', $spreadsheet->getActiveSheet(), '=Data!$A$2:$A6') |
||
| 571 | ); |
||
| 572 | $spreadsheet->addNamedFormula( |
||
| 573 | new NamedFormula('FirstTotal', $spreadsheet->getActiveSheet(), '=SUM(FirstColumn)') |
||
| 574 | ); |
||
| 575 | |||
| 576 | $spreadsheet->addNamedRange( |
||
| 577 | new NamedRange('SecondColumn', $spreadsheet->getActiveSheet(), '=Data!B$2:B6') |
||
| 578 | ); |
||
| 579 | $spreadsheet->addNamedFormula( |
||
| 580 | new NamedFormula('SecondTotal', $spreadsheet->getActiveSheet(), '=SUM(SecondColumn)') |
||
| 581 | ); |
||
| 582 | |||
| 583 | $spreadsheet->addNamedFormula( |
||
| 584 | new NamedFormula('ProductTotal', $spreadsheet->getActiveSheet(), '=FirstTotal*SecondTotal') |
||
| 585 | ); |
||
| 586 | |||
| 587 | return $spreadsheet; |
||
| 588 | } |
||
| 589 | |||
| 590 | private function buildDefinedNamesAbsoluteWorkbook(): Spreadsheet |
||
| 625 | } |
||
| 626 | |||
| 627 | public function testInsertBothWithDefinedNamesAbsolute(): void |
||
| 628 | { |
||
| 629 | $spreadsheet = $this->buildDefinedNamesAbsoluteWorkbook(); |
||
| 659 | } |
||
| 660 | } |
||
| 661 |
