PHPOffice /
PhpSpreadsheet
| Conditions | 75 |
| Paths | 41 |
| Total Lines | 341 |
| Code Lines | 218 |
| Lines | 0 |
| Ratio | 0 % |
| Tests | 127 |
| CRAP Score | 484.0532 |
| Changes | 0 | ||
Small methods make your code easier to understand, in particular if combined with a good name. Besides, if your method is small, finding a good name is usually much easier.
For example, if you find yourself adding comments to a method's body, this is usually a good sign to extract the commented part to a new method, and use the comment as a starting point when coming up with a good name for this new method.
Commonly applied refactorings include:
If many parameters/temporary variables are present:
| 1 | <?php |
||
| 313 | 3 | public function loadIntoExisting($pFilename, Spreadsheet $spreadsheet) |
|
| 314 | { |
||
| 315 | 3 | File::assertFile($pFilename); |
|
| 316 | 3 | if (!$this->canRead($pFilename)) { |
|
| 317 | throw new Exception($pFilename . ' is an Invalid Spreadsheet file.'); |
||
| 318 | } |
||
| 319 | |||
| 320 | 3 | $xml = $this->trySimpleXMLLoadString($pFilename); |
|
| 321 | |||
| 322 | 3 | $namespaces = $xml->getNamespaces(true); |
|
| 323 | |||
| 324 | 3 | $docProps = $spreadsheet->getProperties(); |
|
| 325 | 3 | if (isset($xml->DocumentProperties[0])) { |
|
| 326 | foreach ($xml->DocumentProperties[0] as $propertyName => $propertyValue) { |
||
| 327 | switch ($propertyName) { |
||
| 328 | case 'Title': |
||
| 329 | $docProps->setTitle(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 330 | |||
| 331 | break; |
||
| 332 | case 'Subject': |
||
| 333 | $docProps->setSubject(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 334 | |||
| 335 | break; |
||
| 336 | case 'Author': |
||
| 337 | $docProps->setCreator(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 338 | |||
| 339 | break; |
||
| 340 | case 'Created': |
||
| 341 | $creationDate = strtotime($propertyValue); |
||
| 342 | $docProps->setCreated($creationDate); |
||
| 343 | |||
| 344 | break; |
||
| 345 | case 'LastAuthor': |
||
| 346 | $docProps->setLastModifiedBy(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 347 | |||
| 348 | break; |
||
| 349 | case 'LastSaved': |
||
| 350 | $lastSaveDate = strtotime($propertyValue); |
||
| 351 | $docProps->setModified($lastSaveDate); |
||
| 352 | |||
| 353 | break; |
||
| 354 | case 'Company': |
||
| 355 | $docProps->setCompany(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 356 | |||
| 357 | break; |
||
| 358 | case 'Category': |
||
| 359 | $docProps->setCategory(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 360 | |||
| 361 | break; |
||
| 362 | case 'Manager': |
||
| 363 | $docProps->setManager(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 364 | |||
| 365 | break; |
||
| 366 | case 'Keywords': |
||
| 367 | $docProps->setKeywords(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 368 | |||
| 369 | break; |
||
| 370 | case 'Description': |
||
| 371 | $docProps->setDescription(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 372 | |||
| 373 | break; |
||
| 374 | } |
||
| 375 | } |
||
| 376 | } |
||
| 377 | 3 | if (isset($xml->CustomDocumentProperties)) { |
|
| 378 | foreach ($xml->CustomDocumentProperties[0] as $propertyName => $propertyValue) { |
||
| 379 | $propertyAttributes = $propertyValue->attributes($namespaces['dt']); |
||
| 380 | $propertyName = preg_replace_callback('/_x([0-9a-z]{4})_/', ['self', 'hex2str'], $propertyName); |
||
| 381 | $propertyType = Properties::PROPERTY_TYPE_UNKNOWN; |
||
| 382 | switch ((string) $propertyAttributes) { |
||
| 383 | case 'string': |
||
| 384 | $propertyType = Properties::PROPERTY_TYPE_STRING; |
||
| 385 | $propertyValue = trim($propertyValue); |
||
| 386 | |||
| 387 | break; |
||
| 388 | case 'boolean': |
||
| 389 | $propertyType = Properties::PROPERTY_TYPE_BOOLEAN; |
||
| 390 | $propertyValue = (bool) $propertyValue; |
||
| 391 | |||
| 392 | break; |
||
| 393 | case 'integer': |
||
| 394 | $propertyType = Properties::PROPERTY_TYPE_INTEGER; |
||
| 395 | $propertyValue = (int) $propertyValue; |
||
| 396 | |||
| 397 | break; |
||
| 398 | case 'float': |
||
| 399 | $propertyType = Properties::PROPERTY_TYPE_FLOAT; |
||
| 400 | $propertyValue = (float) $propertyValue; |
||
| 401 | |||
| 402 | break; |
||
| 403 | case 'dateTime.tz': |
||
| 404 | $propertyType = Properties::PROPERTY_TYPE_DATE; |
||
| 405 | $propertyValue = strtotime(trim($propertyValue)); |
||
| 406 | |||
| 407 | break; |
||
| 408 | } |
||
| 409 | $docProps->setCustomProperty($propertyName, $propertyValue, $propertyType); |
||
| 410 | } |
||
| 411 | } |
||
| 412 | |||
| 413 | 3 | $this->parseStyles($xml, $namespaces); |
|
|
1 ignored issue
–
show
|
|||
| 414 | |||
| 415 | 3 | $worksheetID = 0; |
|
| 416 | 3 | $xml_ss = $xml->children($namespaces['ss']); |
|
| 417 | |||
| 418 | 3 | foreach ($xml_ss->Worksheet as $worksheet) { |
|
| 419 | 3 | $worksheet_ss = $worksheet->attributes($namespaces['ss']); |
|
| 420 | |||
| 421 | 3 | if ((isset($this->loadSheetsOnly)) && (isset($worksheet_ss['Name'])) && |
|
| 422 | 3 | (!in_array($worksheet_ss['Name'], $this->loadSheetsOnly))) { |
|
| 423 | continue; |
||
| 424 | } |
||
| 425 | |||
| 426 | // Create new Worksheet |
||
| 427 | 3 | $spreadsheet->createSheet(); |
|
| 428 | 3 | $spreadsheet->setActiveSheetIndex($worksheetID); |
|
| 429 | 3 | if (isset($worksheet_ss['Name'])) { |
|
| 430 | 3 | $worksheetName = self::convertStringEncoding((string) $worksheet_ss['Name'], $this->charSet); |
|
| 431 | // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in |
||
| 432 | // formula cells... during the load, all formulae should be correct, and we're simply bringing |
||
| 433 | // the worksheet name in line with the formula, not the reverse |
||
| 434 | 3 | $spreadsheet->getActiveSheet()->setTitle($worksheetName, false, false); |
|
| 435 | } |
||
| 436 | |||
| 437 | 3 | $columnID = 'A'; |
|
| 438 | 3 | if (isset($worksheet->Table->Column)) { |
|
| 439 | 3 | foreach ($worksheet->Table->Column as $columnData) { |
|
| 440 | 3 | $columnData_ss = $columnData->attributes($namespaces['ss']); |
|
| 441 | 3 | if (isset($columnData_ss['Index'])) { |
|
| 442 | 3 | $columnID = Coordinate::stringFromColumnIndex((int) $columnData_ss['Index']); |
|
| 443 | } |
||
| 444 | 3 | if (isset($columnData_ss['Width'])) { |
|
| 445 | 3 | $columnWidth = $columnData_ss['Width']; |
|
| 446 | 3 | $spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4); |
|
| 447 | } |
||
| 448 | 3 | ++$columnID; |
|
| 449 | } |
||
| 450 | } |
||
| 451 | |||
| 452 | 3 | $rowID = 1; |
|
| 453 | 3 | if (isset($worksheet->Table->Row)) { |
|
| 454 | 3 | $additionalMergedCells = 0; |
|
| 455 | 3 | foreach ($worksheet->Table->Row as $rowData) { |
|
| 456 | 3 | $rowHasData = false; |
|
| 457 | 3 | $row_ss = $rowData->attributes($namespaces['ss']); |
|
| 458 | 3 | if (isset($row_ss['Index'])) { |
|
| 459 | 2 | $rowID = (int) $row_ss['Index']; |
|
| 460 | } |
||
| 461 | |||
| 462 | 3 | $columnID = 'A'; |
|
| 463 | 3 | foreach ($rowData->Cell as $cell) { |
|
| 464 | 3 | $cell_ss = $cell->attributes($namespaces['ss']); |
|
| 465 | 3 | if (isset($cell_ss['Index'])) { |
|
| 466 | 2 | $columnID = Coordinate::stringFromColumnIndex((int) $cell_ss['Index']); |
|
| 467 | } |
||
| 468 | 3 | $cellRange = $columnID . $rowID; |
|
| 469 | |||
| 470 | 3 | if ($this->getReadFilter() !== null) { |
|
| 471 | 3 | if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) { |
|
| 472 | ++$columnID; |
||
| 473 | |||
| 474 | continue; |
||
| 475 | } |
||
| 476 | } |
||
| 477 | |||
| 478 | 3 | if (isset($cell_ss['HRef'])) { |
|
| 479 | 2 | $spreadsheet->getActiveSheet()->getCell($cellRange)->getHyperlink()->setUrl($cell_ss['HRef']); |
|
| 480 | } |
||
| 481 | |||
| 482 | 3 | if ((isset($cell_ss['MergeAcross'])) || (isset($cell_ss['MergeDown']))) { |
|
| 483 | 2 | $columnTo = $columnID; |
|
| 484 | 2 | if (isset($cell_ss['MergeAcross'])) { |
|
| 485 | 2 | $additionalMergedCells += (int) $cell_ss['MergeAcross']; |
|
| 486 | 2 | $columnTo = Coordinate::stringFromColumnIndex(Coordinate::columnIndexFromString($columnID) + $cell_ss['MergeAcross']); |
|
| 487 | } |
||
| 488 | 2 | $rowTo = $rowID; |
|
| 489 | 2 | if (isset($cell_ss['MergeDown'])) { |
|
| 490 | 2 | $rowTo = $rowTo + $cell_ss['MergeDown']; |
|
| 491 | } |
||
| 492 | 2 | $cellRange .= ':' . $columnTo . $rowTo; |
|
| 493 | 2 | $spreadsheet->getActiveSheet()->mergeCells($cellRange); |
|
| 494 | } |
||
| 495 | |||
| 496 | 3 | $cellIsSet = $hasCalculatedValue = false; |
|
| 497 | 3 | $cellDataFormula = ''; |
|
| 498 | 3 | if (isset($cell_ss['Formula'])) { |
|
| 499 | 2 | $cellDataFormula = $cell_ss['Formula']; |
|
| 500 | 2 | $hasCalculatedValue = true; |
|
| 501 | } |
||
| 502 | 3 | if (isset($cell->Data)) { |
|
| 503 | 3 | $cellValue = $cellData = $cell->Data; |
|
| 504 | 3 | $type = DataType::TYPE_NULL; |
|
| 505 | 3 | $cellData_ss = $cellData->attributes($namespaces['ss']); |
|
| 506 | 3 | if (isset($cellData_ss['Type'])) { |
|
| 507 | 3 | $cellDataType = $cellData_ss['Type']; |
|
| 508 | 3 | switch ($cellDataType) { |
|
| 509 | /* |
||
| 510 | const TYPE_STRING = 's'; |
||
| 511 | const TYPE_FORMULA = 'f'; |
||
| 512 | const TYPE_NUMERIC = 'n'; |
||
| 513 | const TYPE_BOOL = 'b'; |
||
| 514 | const TYPE_NULL = 'null'; |
||
| 515 | const TYPE_INLINE = 'inlineStr'; |
||
| 516 | const TYPE_ERROR = 'e'; |
||
| 517 | */ |
||
| 518 | 3 | case 'String': |
|
| 519 | 3 | $cellValue = self::convertStringEncoding($cellValue, $this->charSet); |
|
| 520 | 3 | $type = DataType::TYPE_STRING; |
|
| 521 | |||
| 522 | 3 | break; |
|
| 523 | 3 | case 'Number': |
|
| 524 | 3 | $type = DataType::TYPE_NUMERIC; |
|
| 525 | 3 | $cellValue = (float) $cellValue; |
|
| 526 | 3 | if (floor($cellValue) == $cellValue) { |
|
| 527 | 3 | $cellValue = (int) $cellValue; |
|
| 528 | } |
||
| 529 | |||
| 530 | 3 | break; |
|
| 531 | 2 | case 'Boolean': |
|
| 532 | 2 | $type = DataType::TYPE_BOOL; |
|
| 533 | 2 | $cellValue = ($cellValue != 0); |
|
| 534 | |||
| 535 | 2 | break; |
|
| 536 | 2 | case 'DateTime': |
|
| 537 | 2 | $type = DataType::TYPE_NUMERIC; |
|
| 538 | 2 | $cellValue = Date::PHPToExcel(strtotime($cellValue)); |
|
| 539 | |||
| 540 | 2 | break; |
|
| 541 | case 'Error': |
||
| 542 | $type = DataType::TYPE_ERROR; |
||
| 543 | |||
| 544 | break; |
||
| 545 | } |
||
| 546 | } |
||
| 547 | |||
| 548 | 3 | if ($hasCalculatedValue) { |
|
| 549 | 2 | $type = DataType::TYPE_FORMULA; |
|
| 550 | 2 | $columnNumber = Coordinate::columnIndexFromString($columnID); |
|
| 551 | 2 | if (substr($cellDataFormula, 0, 3) == 'of:') { |
|
| 552 | 2 | $cellDataFormula = substr($cellDataFormula, 3); |
|
| 553 | 2 | $temp = explode('"', $cellDataFormula); |
|
| 554 | 2 | $key = false; |
|
| 555 | 2 | foreach ($temp as &$value) { |
|
| 556 | // Only replace in alternate array entries (i.e. non-quoted blocks) |
||
| 557 | 2 | if ($key = !$key) { |
|
| 558 | 2 | $value = str_replace(['[.', '.', ']'], '', $value); |
|
| 559 | } |
||
| 560 | } |
||
| 561 | } else { |
||
| 562 | // Convert R1C1 style references to A1 style references (but only when not quoted) |
||
| 563 | $temp = explode('"', $cellDataFormula); |
||
| 564 | $key = false; |
||
| 565 | foreach ($temp as &$value) { |
||
| 566 | // Only replace in alternate array entries (i.e. non-quoted blocks) |
||
| 567 | if ($key = !$key) { |
||
| 568 | preg_match_all('/(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE); |
||
| 569 | // Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way |
||
| 570 | // through the formula from left to right. Reversing means that we work right to left.through |
||
| 571 | // the formula |
||
| 572 | $cellReferences = array_reverse($cellReferences); |
||
| 573 | // Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent, |
||
| 574 | // then modify the formula to use that new reference |
||
| 575 | foreach ($cellReferences as $cellReference) { |
||
| 576 | $rowReference = $cellReference[2][0]; |
||
| 577 | // Empty R reference is the current row |
||
| 578 | if ($rowReference == '') { |
||
| 579 | $rowReference = $rowID; |
||
| 580 | } |
||
| 581 | // Bracketed R references are relative to the current row |
||
| 582 | if ($rowReference[0] == '[') { |
||
| 583 | $rowReference = $rowID + trim($rowReference, '[]'); |
||
| 584 | } |
||
| 585 | $columnReference = $cellReference[4][0]; |
||
| 586 | // Empty C reference is the current column |
||
| 587 | if ($columnReference == '') { |
||
| 588 | $columnReference = $columnNumber; |
||
| 589 | } |
||
| 590 | // Bracketed C references are relative to the current column |
||
| 591 | if ($columnReference[0] == '[') { |
||
| 592 | $columnReference = $columnNumber + trim($columnReference, '[]'); |
||
| 593 | } |
||
| 594 | $A1CellReference = Coordinate::stringFromColumnIndex($columnReference) . $rowReference; |
||
| 595 | $value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0])); |
||
| 596 | } |
||
| 597 | } |
||
| 598 | } |
||
| 599 | } |
||
| 600 | 2 | unset($value); |
|
| 601 | // Then rebuild the formula string |
||
| 602 | 2 | $cellDataFormula = implode('"', $temp); |
|
| 603 | } |
||
| 604 | |||
| 605 | 3 | $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $cellValue), $type); |
|
| 606 | 3 | if ($hasCalculatedValue) { |
|
| 607 | 2 | $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue); |
|
| 608 | } |
||
| 609 | 3 | $cellIsSet = $rowHasData = true; |
|
| 610 | } |
||
| 611 | |||
| 612 | 3 | if (isset($cell->Comment)) { |
|
| 613 | 3 | $commentAttributes = $cell->Comment->attributes($namespaces['ss']); |
|
| 614 | 3 | $author = 'unknown'; |
|
| 615 | 3 | if (isset($commentAttributes->Author)) { |
|
| 616 | $author = (string) $commentAttributes->Author; |
||
| 617 | } |
||
| 618 | 3 | $node = $cell->Comment->Data->asXML(); |
|
| 619 | 3 | $annotation = strip_tags($node); |
|
| 620 | 3 | $spreadsheet->getActiveSheet()->getComment($columnID . $rowID)->setAuthor(self::convertStringEncoding($author, $this->charSet))->setText($this->parseRichText($annotation)); |
|
| 621 | } |
||
| 622 | |||
| 623 | 3 | if (($cellIsSet) && (isset($cell_ss['StyleID']))) { |
|
| 624 | 2 | $style = (string) $cell_ss['StyleID']; |
|
| 625 | 2 | if ((isset($this->styles[$style])) && (!empty($this->styles[$style]))) { |
|
| 626 | 2 | if (!$spreadsheet->getActiveSheet()->cellExists($columnID . $rowID)) { |
|
| 627 | $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setValue(null); |
||
| 628 | } |
||
| 629 | 2 | $spreadsheet->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->styles[$style]); |
|
| 630 | } |
||
| 631 | } |
||
| 632 | 3 | ++$columnID; |
|
| 633 | 3 | while ($additionalMergedCells > 0) { |
|
| 634 | 2 | ++$columnID; |
|
| 635 | 2 | --$additionalMergedCells; |
|
| 636 | } |
||
| 637 | } |
||
| 638 | |||
| 639 | 3 | if ($rowHasData) { |
|
| 640 | 3 | if (isset($row_ss['Height'])) { |
|
| 641 | 3 | $rowHeight = $row_ss['Height']; |
|
| 642 | 3 | $spreadsheet->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight); |
|
| 643 | } |
||
| 644 | } |
||
| 645 | |||
| 646 | 3 | ++$rowID; |
|
| 647 | } |
||
| 648 | } |
||
| 649 | 3 | ++$worksheetID; |
|
| 650 | } |
||
| 651 | |||
| 652 | // Return |
||
| 653 | 3 | return $spreadsheet; |
|
| 654 | } |
||
| 888 |
Loading history...
