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