PHPOffice /
PhpSpreadsheet
Duplicate code is one of the most pungent code smells. A rule that is often used is to re-structure code once it is duplicated in three or more places.
Common duplication problems, and corresponding solutions are:
Complex classes like Xml 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. You can also have a look at the cohesion graph to spot any un-connected, or weakly-connected components.
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 Xml, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 34 | class Xml extends BaseReader implements IReader |
||
| 35 | { |
||
| 36 | /** |
||
| 37 | * Formats. |
||
| 38 | * |
||
| 39 | * @var array |
||
| 40 | */ |
||
| 41 | protected $styles = []; |
||
| 42 | |||
| 43 | /** |
||
| 44 | * Character set used in the file. |
||
| 45 | * |
||
| 46 | * @var string |
||
| 47 | */ |
||
| 48 | protected $charSet = 'UTF-8'; |
||
| 49 | |||
| 50 | /** |
||
| 51 | * Create a new Excel2003XML Reader instance. |
||
| 52 | */ |
||
| 53 | 2 | public function __construct() |
|
| 57 | |||
| 58 | /** |
||
| 59 | * Can the current IReader read the file? |
||
| 60 | * |
||
| 61 | * @param string $pFilename |
||
| 62 | * |
||
| 63 | * @throws Exception |
||
| 64 | * |
||
| 65 | * @return bool |
||
| 66 | */ |
||
| 67 | 2 | public function canRead($pFilename) |
|
| 68 | { |
||
| 69 | // Office xmlns:o="urn:schemas-microsoft-com:office:office" |
||
| 70 | // Excel xmlns:x="urn:schemas-microsoft-com:office:excel" |
||
| 71 | // XML Spreadsheet xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" |
||
| 72 | // Spreadsheet component xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" |
||
| 73 | // XML schema xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" |
||
| 74 | // XML data type xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" |
||
| 75 | // MS-persist recordset xmlns:rs="urn:schemas-microsoft-com:rowset" |
||
| 76 | // Rowset xmlns:z="#RowsetSchema" |
||
| 77 | // |
||
| 78 | |||
| 79 | $signature = [ |
||
| 80 | 2 | '<?xml version="1.0"', |
|
| 81 | '<?mso-application progid="Excel.Sheet"?>', |
||
| 82 | ]; |
||
| 83 | |||
| 84 | // Open file |
||
| 85 | 2 | $this->openFile($pFilename); |
|
| 86 | 2 | $fileHandle = $this->fileHandle; |
|
| 87 | |||
| 88 | // Read sample data (first 2 KB will do) |
||
| 89 | 2 | $data = fread($fileHandle, 2048); |
|
| 90 | 2 | fclose($fileHandle); |
|
| 91 | 2 | $data = strtr($data, "'", '"'); // fix headers with single quote |
|
| 92 | |||
| 93 | 2 | $valid = true; |
|
| 94 | 2 | foreach ($signature as $match) { |
|
| 95 | // every part of the signature must be present |
||
| 96 | 2 | if (strpos($data, $match) === false) { |
|
| 97 | $valid = false; |
||
| 98 | break; |
||
| 99 | } |
||
| 100 | } |
||
| 101 | |||
| 102 | // Retrieve charset encoding |
||
| 103 | 2 | if (preg_match('/<?xml.*encoding=[\'"](.*?)[\'"].*?>/um', $data, $matches)) { |
|
| 104 | 2 | $this->charSet = strtoupper($matches[1]); |
|
| 105 | } |
||
| 106 | |||
| 107 | 2 | return $valid; |
|
| 108 | } |
||
| 109 | |||
| 110 | /** |
||
| 111 | * Reads names of the worksheets from a file, without parsing the whole file to a Spreadsheet object. |
||
| 112 | * |
||
| 113 | * @param string $pFilename |
||
| 114 | * |
||
| 115 | * @throws Exception |
||
| 116 | */ |
||
| 117 | public function listWorksheetNames($pFilename) |
||
| 141 | |||
| 142 | /** |
||
| 143 | * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns). |
||
| 144 | * |
||
| 145 | * @param string $pFilename |
||
| 146 | * |
||
| 147 | * @throws Exception |
||
| 148 | */ |
||
| 149 | public function listWorksheetInfo($pFilename) |
||
| 213 | |||
| 214 | /** |
||
| 215 | * Loads Spreadsheet from file. |
||
| 216 | * |
||
| 217 | * @param string $pFilename |
||
| 218 | * |
||
| 219 | * @throws Exception |
||
| 220 | * |
||
| 221 | * @return \PhpOffice\PhpSpreadsheet\Spreadsheet |
||
| 222 | */ |
||
| 223 | 1 | public function load($pFilename) |
|
| 232 | |||
| 233 | 1 | View Code Duplication | protected static function identifyFixedStyleValue($styleList, &$styleAttributeValue) |
| 246 | |||
| 247 | /** |
||
| 248 | * pixel units to excel width units(units of 1/256th of a character width). |
||
| 249 | * |
||
| 250 | * @param pxs |
||
| 251 | * @param mixed $pxs |
||
| 252 | * |
||
| 253 | * @return |
||
| 254 | */ |
||
| 255 | protected static function pixel2WidthUnits($pxs) |
||
| 264 | |||
| 265 | /** |
||
| 266 | * excel width units(units of 1/256th of a character width) to pixel units. |
||
| 267 | * |
||
| 268 | * @param widthUnits |
||
| 269 | * @param mixed $widthUnits |
||
| 270 | * |
||
| 271 | * @return |
||
| 272 | */ |
||
| 273 | protected static function widthUnits2Pixel($widthUnits) |
||
| 281 | |||
| 282 | protected static function hex2str($hex) |
||
| 286 | |||
| 287 | /** |
||
| 288 | * Loads from file into Spreadsheet instance. |
||
| 289 | * |
||
| 290 | * @param string $pFilename |
||
| 291 | * @param \PhpOffice\PhpSpreadsheet\Spreadsheet $spreadsheet |
||
| 292 | * |
||
| 293 | * @throws Exception |
||
| 294 | * |
||
| 295 | * @return \PhpOffice\PhpSpreadsheet\Spreadsheet |
||
| 296 | */ |
||
| 297 | 1 | public function loadIntoExisting($pFilename, \PhpOffice\PhpSpreadsheet\Spreadsheet $spreadsheet) |
|
| 298 | { |
||
| 299 | 1 | $fromFormats = ['\-', '\ ']; |
|
| 300 | 1 | $toFormats = ['-', ' ']; |
|
| 301 | |||
| 302 | $underlineStyles = [ |
||
| 303 | 1 | \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_NONE, |
|
| 304 | 1 | \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_DOUBLE, |
|
| 305 | 1 | \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_DOUBLEACCOUNTING, |
|
| 306 | 1 | \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_SINGLE, |
|
| 307 | 1 | \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_SINGLEACCOUNTING, |
|
| 308 | ]; |
||
| 309 | $verticalAlignmentStyles = [ |
||
| 310 | 1 | \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_BOTTOM, |
|
| 311 | 1 | \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP, |
|
| 312 | 1 | \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER, |
|
| 313 | 1 | \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_JUSTIFY, |
|
| 314 | ]; |
||
| 315 | $horizontalAlignmentStyles = [ |
||
| 316 | 1 | \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_GENERAL, |
|
| 317 | 1 | \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT, |
|
| 318 | 1 | \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT, |
|
| 319 | 1 | \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER, |
|
| 320 | 1 | \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER_CONTINUOUS, |
|
| 321 | 1 | \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_JUSTIFY, |
|
| 322 | ]; |
||
| 323 | |||
| 324 | 1 | $timezoneObj = new \DateTimeZone('Europe/London'); |
|
| 325 | 1 | $GMT = new \DateTimeZone('UTC'); |
|
| 326 | |||
| 327 | 1 | File::assertFile($pFilename); |
|
| 328 | 1 | if (!$this->canRead($pFilename)) { |
|
| 329 | throw new Exception($pFilename . ' is an Invalid Spreadsheet file.'); |
||
| 330 | } |
||
| 331 | |||
| 332 | 1 | $xml = simplexml_load_string( |
|
| 333 | 1 | $this->securityScan(file_get_contents($pFilename)), |
|
| 334 | 1 | 'SimpleXMLElement', |
|
| 335 | 1 | \PhpOffice\PhpSpreadsheet\Settings::getLibXmlLoaderOptions() |
|
| 336 | ); |
||
| 337 | 1 | $namespaces = $xml->getNamespaces(true); |
|
| 338 | |||
| 339 | 1 | $docProps = $spreadsheet->getProperties(); |
|
| 340 | 1 | if (isset($xml->DocumentProperties[0])) { |
|
| 341 | foreach ($xml->DocumentProperties[0] as $propertyName => $propertyValue) { |
||
| 342 | switch ($propertyName) { |
||
| 343 | case 'Title': |
||
| 344 | $docProps->setTitle(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 345 | break; |
||
| 346 | case 'Subject': |
||
| 347 | $docProps->setSubject(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 348 | break; |
||
| 349 | case 'Author': |
||
| 350 | $docProps->setCreator(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 351 | break; |
||
| 352 | case 'Created': |
||
| 353 | $creationDate = strtotime($propertyValue); |
||
| 354 | $docProps->setCreated($creationDate); |
||
| 355 | break; |
||
| 356 | case 'LastAuthor': |
||
| 357 | $docProps->setLastModifiedBy(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 358 | break; |
||
| 359 | case 'LastSaved': |
||
| 360 | $lastSaveDate = strtotime($propertyValue); |
||
| 361 | $docProps->setModified($lastSaveDate); |
||
| 362 | break; |
||
| 363 | case 'Company': |
||
| 364 | $docProps->setCompany(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 365 | break; |
||
| 366 | case 'Category': |
||
| 367 | $docProps->setCategory(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 368 | break; |
||
| 369 | case 'Manager': |
||
| 370 | $docProps->setManager(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 371 | break; |
||
| 372 | case 'Keywords': |
||
| 373 | $docProps->setKeywords(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 374 | break; |
||
| 375 | case 'Description': |
||
| 376 | $docProps->setDescription(self::convertStringEncoding($propertyValue, $this->charSet)); |
||
| 377 | break; |
||
| 378 | } |
||
| 379 | } |
||
| 380 | } |
||
| 381 | 1 | if (isset($xml->CustomDocumentProperties)) { |
|
| 382 | foreach ($xml->CustomDocumentProperties[0] as $propertyName => $propertyValue) { |
||
| 383 | $propertyAttributes = $propertyValue->attributes($namespaces['dt']); |
||
| 384 | $propertyName = preg_replace_callback('/_x([0-9a-z]{4})_/', ['self', 'hex2str'], $propertyName); |
||
| 385 | $propertyType = \PhpOffice\PhpSpreadsheet\Document\Properties::PROPERTY_TYPE_UNKNOWN; |
||
| 386 | switch ((string) $propertyAttributes) { |
||
| 387 | case 'string': |
||
| 388 | $propertyType = \PhpOffice\PhpSpreadsheet\Document\Properties::PROPERTY_TYPE_STRING; |
||
| 389 | $propertyValue = trim($propertyValue); |
||
| 390 | break; |
||
| 391 | case 'boolean': |
||
| 392 | $propertyType = \PhpOffice\PhpSpreadsheet\Document\Properties::PROPERTY_TYPE_BOOLEAN; |
||
| 393 | $propertyValue = (bool) $propertyValue; |
||
| 394 | break; |
||
| 395 | case 'integer': |
||
| 396 | $propertyType = \PhpOffice\PhpSpreadsheet\Document\Properties::PROPERTY_TYPE_INTEGER; |
||
| 397 | $propertyValue = (int) $propertyValue; |
||
| 398 | break; |
||
| 399 | case 'float': |
||
| 400 | $propertyType = \PhpOffice\PhpSpreadsheet\Document\Properties::PROPERTY_TYPE_FLOAT; |
||
| 401 | $propertyValue = (float) $propertyValue; |
||
| 402 | break; |
||
| 403 | case 'dateTime.tz': |
||
| 404 | $propertyType = \PhpOffice\PhpSpreadsheet\Document\Properties::PROPERTY_TYPE_DATE; |
||
| 405 | $propertyValue = strtotime(trim($propertyValue)); |
||
| 406 | break; |
||
| 407 | } |
||
| 408 | $docProps->setCustomProperty($propertyName, $propertyValue, $propertyType); |
||
| 409 | } |
||
| 410 | } |
||
| 411 | |||
| 412 | 1 | foreach ($xml->Styles[0] as $style) { |
|
| 413 | 1 | $style_ss = $style->attributes($namespaces['ss']); |
|
| 414 | 1 | $styleID = (string) $style_ss['ID']; |
|
| 415 | 1 | $this->styles[$styleID] = (isset($this->styles['Default'])) ? $this->styles['Default'] : []; |
|
| 416 | 1 | foreach ($style as $styleType => $styleData) { |
|
| 417 | 1 | $styleAttributes = $styleData->attributes($namespaces['ss']); |
|
| 418 | switch ($styleType) { |
||
| 419 | 1 | case 'Alignment': |
|
| 420 | 1 | foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { |
|
| 421 | 1 | $styleAttributeValue = (string) $styleAttributeValue; |
|
| 422 | switch ($styleAttributeKey) { |
||
| 423 | 1 | case 'Vertical': |
|
| 424 | 1 | if (self::identifyFixedStyleValue($verticalAlignmentStyles, $styleAttributeValue)) { |
|
| 425 | 1 | $this->styles[$styleID]['alignment']['vertical'] = $styleAttributeValue; |
|
| 426 | } |
||
| 427 | 1 | break; |
|
| 428 | 1 | case 'Horizontal': |
|
| 429 | 1 | if (self::identifyFixedStyleValue($horizontalAlignmentStyles, $styleAttributeValue)) { |
|
| 430 | 1 | $this->styles[$styleID]['alignment']['horizontal'] = $styleAttributeValue; |
|
| 431 | } |
||
| 432 | 1 | break; |
|
| 433 | 1 | case 'WrapText': |
|
| 434 | 1 | $this->styles[$styleID]['alignment']['wrap'] = true; |
|
| 435 | 1 | break; |
|
| 436 | } |
||
| 437 | } |
||
| 438 | 1 | break; |
|
| 439 | 1 | case 'Borders': |
|
| 440 | 1 | foreach ($styleData->Border as $borderStyle) { |
|
| 441 | 1 | $borderAttributes = $borderStyle->attributes($namespaces['ss']); |
|
| 442 | 1 | $thisBorder = []; |
|
| 443 | 1 | foreach ($borderAttributes as $borderStyleKey => $borderStyleValue) { |
|
| 444 | switch ($borderStyleKey) { |
||
| 445 | 1 | case 'LineStyle': |
|
| 446 | 1 | $thisBorder['style'] = \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM; |
|
| 447 | 1 | break; |
|
| 448 | 1 | case 'Weight': |
|
| 449 | 1 | break; |
|
| 450 | 1 | case 'Position': |
|
| 451 | 1 | $borderPosition = strtolower($borderStyleValue); |
|
| 452 | 1 | break; |
|
| 453 | 1 | case 'Color': |
|
| 454 | 1 | $borderColour = substr($borderStyleValue, 1); |
|
| 455 | 1 | $thisBorder['color']['rgb'] = $borderColour; |
|
| 456 | 1 | break; |
|
| 457 | } |
||
| 458 | } |
||
| 459 | 1 | if (!empty($thisBorder)) { |
|
| 460 | 1 | if (($borderPosition == 'left') || ($borderPosition == 'right') || ($borderPosition == 'top') || ($borderPosition == 'bottom')) { |
|
| 461 | 1 | $this->styles[$styleID]['borders'][$borderPosition] = $thisBorder; |
|
| 462 | } |
||
| 463 | } |
||
| 464 | } |
||
| 465 | 1 | break; |
|
| 466 | 1 | case 'Font': |
|
| 467 | 1 | foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { |
|
| 468 | 1 | $styleAttributeValue = (string) $styleAttributeValue; |
|
| 469 | switch ($styleAttributeKey) { |
||
| 470 | 1 | case 'FontName': |
|
| 471 | 1 | $this->styles[$styleID]['font']['name'] = $styleAttributeValue; |
|
| 472 | 1 | break; |
|
| 473 | 1 | case 'Size': |
|
| 474 | 1 | $this->styles[$styleID]['font']['size'] = $styleAttributeValue; |
|
| 475 | 1 | break; |
|
| 476 | 1 | View Code Duplication | case 'Color': |
| 477 | 1 | $this->styles[$styleID]['font']['color']['rgb'] = substr($styleAttributeValue, 1); |
|
| 478 | 1 | break; |
|
| 479 | 1 | case 'Bold': |
|
| 480 | 1 | $this->styles[$styleID]['font']['bold'] = true; |
|
| 481 | 1 | break; |
|
| 482 | 1 | case 'Italic': |
|
| 483 | 1 | $this->styles[$styleID]['font']['italic'] = true; |
|
| 484 | 1 | break; |
|
| 485 | 1 | case 'Underline': |
|
| 486 | 1 | if (self::identifyFixedStyleValue($underlineStyles, $styleAttributeValue)) { |
|
| 487 | 1 | $this->styles[$styleID]['font']['underline'] = $styleAttributeValue; |
|
| 488 | } |
||
| 489 | 1 | break; |
|
| 490 | } |
||
| 491 | } |
||
| 492 | 1 | break; |
|
| 493 | 1 | case 'Interior': |
|
| 494 | 1 | foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { |
|
| 495 | switch ($styleAttributeKey) { |
||
| 496 | 1 | View Code Duplication | case 'Color': |
| 497 | 1 | $this->styles[$styleID]['fill']['color']['rgb'] = substr($styleAttributeValue, 1); |
|
| 498 | 1 | break; |
|
| 499 | 1 | case 'Pattern': |
|
| 500 | 1 | $this->styles[$styleID]['fill']['type'] = strtolower($styleAttributeValue); |
|
| 501 | 1 | break; |
|
| 502 | } |
||
| 503 | } |
||
| 504 | 1 | break; |
|
| 505 | 1 | case 'NumberFormat': |
|
| 506 | 1 | foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { |
|
| 507 | 1 | $styleAttributeValue = str_replace($fromFormats, $toFormats, $styleAttributeValue); |
|
| 508 | switch ($styleAttributeValue) { |
||
| 509 | 1 | case 'Short Date': |
|
| 510 | 1 | $styleAttributeValue = 'dd/mm/yyyy'; |
|
| 511 | 1 | break; |
|
| 512 | } |
||
| 513 | 1 | if ($styleAttributeValue > '') { |
|
| 514 | 1 | $this->styles[$styleID]['numberformat']['code'] = $styleAttributeValue; |
|
| 515 | } |
||
| 516 | } |
||
| 517 | 1 | break; |
|
| 518 | case 'Protection': |
||
| 519 | foreach ($styleAttributes as $styleAttributeKey => $styleAttributeValue) { |
||
| 520 | } |
||
| 521 | 1 | break; |
|
| 522 | } |
||
| 523 | } |
||
| 524 | } |
||
| 525 | |||
| 526 | 1 | $worksheetID = 0; |
|
| 527 | 1 | $xml_ss = $xml->children($namespaces['ss']); |
|
| 528 | |||
| 529 | 1 | foreach ($xml_ss->Worksheet as $worksheet) { |
|
| 530 | 1 | $worksheet_ss = $worksheet->attributes($namespaces['ss']); |
|
| 531 | |||
| 532 | 1 | if ((isset($this->loadSheetsOnly)) && (isset($worksheet_ss['Name'])) && |
|
| 533 | (!in_array($worksheet_ss['Name'], $this->loadSheetsOnly))) { |
||
| 534 | continue; |
||
| 535 | } |
||
| 536 | |||
| 537 | // Create new Worksheet |
||
| 538 | 1 | $spreadsheet->createSheet(); |
|
| 539 | 1 | $spreadsheet->setActiveSheetIndex($worksheetID); |
|
| 540 | 1 | if (isset($worksheet_ss['Name'])) { |
|
| 541 | 1 | $worksheetName = self::convertStringEncoding((string) $worksheet_ss['Name'], $this->charSet); |
|
| 542 | // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in |
||
| 543 | // formula cells... during the load, all formulae should be correct, and we're simply bringing |
||
| 544 | // the worksheet name in line with the formula, not the reverse |
||
| 545 | 1 | $spreadsheet->getActiveSheet()->setTitle($worksheetName, false); |
|
| 546 | } |
||
| 547 | |||
| 548 | 1 | $columnID = 'A'; |
|
| 549 | 1 | if (isset($worksheet->Table->Column)) { |
|
| 550 | 1 | foreach ($worksheet->Table->Column as $columnData) { |
|
| 551 | 1 | $columnData_ss = $columnData->attributes($namespaces['ss']); |
|
| 552 | 1 | if (isset($columnData_ss['Index'])) { |
|
| 553 | 1 | $columnID = \PhpOffice\PhpSpreadsheet\Cell::stringFromColumnIndex($columnData_ss['Index'] - 1); |
|
| 554 | } |
||
| 555 | 1 | if (isset($columnData_ss['Width'])) { |
|
| 556 | 1 | $columnWidth = $columnData_ss['Width']; |
|
| 557 | 1 | $spreadsheet->getActiveSheet()->getColumnDimension($columnID)->setWidth($columnWidth / 5.4); |
|
| 558 | } |
||
| 559 | 1 | ++$columnID; |
|
| 560 | } |
||
| 561 | } |
||
| 562 | |||
| 563 | 1 | $rowID = 1; |
|
| 564 | 1 | if (isset($worksheet->Table->Row)) { |
|
| 565 | 1 | $additionalMergedCells = 0; |
|
| 566 | 1 | foreach ($worksheet->Table->Row as $rowData) { |
|
| 567 | 1 | $rowHasData = false; |
|
| 568 | 1 | $row_ss = $rowData->attributes($namespaces['ss']); |
|
| 569 | 1 | if (isset($row_ss['Index'])) { |
|
| 570 | 1 | $rowID = (int) $row_ss['Index']; |
|
| 571 | } |
||
| 572 | |||
| 573 | 1 | $columnID = 'A'; |
|
| 574 | 1 | foreach ($rowData->Cell as $cell) { |
|
| 575 | 1 | $cell_ss = $cell->attributes($namespaces['ss']); |
|
| 576 | 1 | if (isset($cell_ss['Index'])) { |
|
| 577 | 1 | $columnID = \PhpOffice\PhpSpreadsheet\Cell::stringFromColumnIndex($cell_ss['Index'] - 1); |
|
| 578 | } |
||
| 579 | 1 | $cellRange = $columnID . $rowID; |
|
| 580 | |||
| 581 | 1 | View Code Duplication | if ($this->getReadFilter() !== null) { |
| 582 | 1 | if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) { |
|
| 583 | ++$columnID; |
||
| 584 | continue; |
||
| 585 | } |
||
| 586 | } |
||
| 587 | |||
| 588 | 1 | if ((isset($cell_ss['MergeAcross'])) || (isset($cell_ss['MergeDown']))) { |
|
| 589 | 1 | $columnTo = $columnID; |
|
| 590 | 1 | if (isset($cell_ss['MergeAcross'])) { |
|
| 591 | 1 | $additionalMergedCells += (int) $cell_ss['MergeAcross']; |
|
| 592 | 1 | $columnTo = \PhpOffice\PhpSpreadsheet\Cell::stringFromColumnIndex(\PhpOffice\PhpSpreadsheet\Cell::columnIndexFromString($columnID) + $cell_ss['MergeAcross'] - 1); |
|
| 593 | } |
||
| 594 | 1 | $rowTo = $rowID; |
|
| 595 | 1 | if (isset($cell_ss['MergeDown'])) { |
|
| 596 | 1 | $rowTo = $rowTo + $cell_ss['MergeDown']; |
|
| 597 | } |
||
| 598 | 1 | $cellRange .= ':' . $columnTo . $rowTo; |
|
| 599 | 1 | $spreadsheet->getActiveSheet()->mergeCells($cellRange); |
|
| 600 | } |
||
| 601 | |||
| 602 | 1 | $cellIsSet = $hasCalculatedValue = false; |
|
| 603 | 1 | $cellDataFormula = ''; |
|
| 604 | 1 | if (isset($cell_ss['Formula'])) { |
|
| 605 | 1 | $cellDataFormula = $cell_ss['Formula']; |
|
| 606 | // added this as a check for array formulas |
||
| 607 | 1 | if (isset($cell_ss['ArrayRange'])) { |
|
| 608 | $cellDataCSEFormula = $cell_ss['ArrayRange']; |
||
| 609 | } |
||
| 610 | 1 | $hasCalculatedValue = true; |
|
| 611 | } |
||
| 612 | 1 | if (isset($cell->Data)) { |
|
| 613 | 1 | $cellValue = $cellData = $cell->Data; |
|
| 614 | 1 | $type = \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NULL; |
|
| 615 | 1 | $cellData_ss = $cellData->attributes($namespaces['ss']); |
|
| 616 | 1 | if (isset($cellData_ss['Type'])) { |
|
| 617 | 1 | $cellDataType = $cellData_ss['Type']; |
|
| 618 | switch ($cellDataType) { |
||
| 619 | /* |
||
| 620 | const TYPE_STRING = 's'; |
||
| 621 | const TYPE_FORMULA = 'f'; |
||
| 622 | const TYPE_NUMERIC = 'n'; |
||
| 623 | const TYPE_BOOL = 'b'; |
||
| 624 | const TYPE_NULL = 'null'; |
||
| 625 | const TYPE_INLINE = 'inlineStr'; |
||
| 626 | const TYPE_ERROR = 'e'; |
||
| 627 | */ |
||
| 628 | 1 | case 'String': |
|
| 629 | 1 | $cellValue = self::convertStringEncoding($cellValue, $this->charSet); |
|
| 630 | 1 | $type = \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING; |
|
| 631 | 1 | break; |
|
| 632 | 1 | case 'Number': |
|
| 633 | 1 | $type = \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC; |
|
| 634 | 1 | $cellValue = (float) $cellValue; |
|
| 635 | 1 | if (floor($cellValue) == $cellValue) { |
|
| 636 | 1 | $cellValue = (int) $cellValue; |
|
| 637 | } |
||
| 638 | 1 | break; |
|
| 639 | 1 | case 'Boolean': |
|
| 640 | 1 | $type = \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_BOOL; |
|
| 641 | 1 | $cellValue = ($cellValue != 0); |
|
| 642 | 1 | break; |
|
| 643 | 1 | case 'DateTime': |
|
| 644 | 1 | $type = \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_NUMERIC; |
|
| 645 | 1 | $cellValue = \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel(strtotime($cellValue)); |
|
| 646 | 1 | break; |
|
| 647 | case 'Error': |
||
| 648 | $type = \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_ERROR; |
||
| 649 | break; |
||
| 650 | } |
||
| 651 | } |
||
| 652 | |||
| 653 | 1 | if ($hasCalculatedValue) { |
|
| 654 | 1 | $type = \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_FORMULA; |
|
| 655 | 1 | $columnNumber = \PhpOffice\PhpSpreadsheet\Cell::columnIndexFromString($columnID); |
|
| 656 | 1 | if (substr($cellDataFormula, 0, 3) == 'of:') { |
|
| 657 | 1 | $cellDataFormula = substr($cellDataFormula, 3); |
|
| 658 | 1 | $temp = explode('"', $cellDataFormula); |
|
| 659 | 1 | $key = false; |
|
| 660 | 1 | foreach ($temp as &$value) { |
|
| 661 | // Only replace in alternate array entries (i.e. non-quoted blocks) |
||
| 662 | 1 | if ($key = !$key) { |
|
| 663 | 1 | $value = str_replace(['[.', '.', ']'], '', $value); |
|
| 664 | } |
||
| 665 | } |
||
| 666 | } else { |
||
| 667 | // Convert R1C1 style references to A1 style references (but only when not quoted) |
||
| 668 | $temp = explode('"', $cellDataFormula); |
||
| 669 | $key = false; |
||
| 670 | View Code Duplication | foreach ($temp as &$value) { |
|
| 671 | // Only replace in alternate array entries (i.e. non-quoted blocks) |
||
| 672 | if ($key = !$key) { |
||
| 673 | preg_match_all('/(R(\[?-?\d*\]?))(C(\[?-?\d*\]?))/', $value, $cellReferences, PREG_SET_ORDER + PREG_OFFSET_CAPTURE); |
||
| 674 | // Reverse the matches array, otherwise all our offsets will become incorrect if we modify our way |
||
| 675 | // through the formula from left to right. Reversing means that we work right to left.through |
||
| 676 | // the formula |
||
| 677 | $cellReferences = array_reverse($cellReferences); |
||
| 678 | // Loop through each R1C1 style reference in turn, converting it to its A1 style equivalent, |
||
| 679 | // then modify the formula to use that new reference |
||
| 680 | foreach ($cellReferences as $cellReference) { |
||
| 681 | $rowReference = $cellReference[2][0]; |
||
| 682 | // Empty R reference is the current row |
||
| 683 | if ($rowReference == '') { |
||
| 684 | $rowReference = $rowID; |
||
| 685 | } |
||
| 686 | // Bracketed R references are relative to the current row |
||
| 687 | if ($rowReference[0] == '[') { |
||
| 688 | $rowReference = $rowID + trim($rowReference, '[]'); |
||
| 689 | } |
||
| 690 | $columnReference = $cellReference[4][0]; |
||
| 691 | // Empty C reference is the current column |
||
| 692 | if ($columnReference == '') { |
||
| 693 | $columnReference = $columnNumber; |
||
| 694 | } |
||
| 695 | // Bracketed C references are relative to the current column |
||
| 696 | if ($columnReference[0] == '[') { |
||
| 697 | $columnReference = $columnNumber + trim($columnReference, '[]'); |
||
| 698 | } |
||
| 699 | $A1CellReference = \PhpOffice\PhpSpreadsheet\Cell::stringFromColumnIndex($columnReference - 1) . $rowReference; |
||
| 700 | $value = substr_replace($value, $A1CellReference, $cellReference[0][1], strlen($cellReference[0][0])); |
||
| 701 | } |
||
| 702 | } |
||
| 703 | } |
||
| 704 | } |
||
| 705 | 1 | unset($value); |
|
| 706 | // Then rebuild the formula string |
||
| 707 | 1 | $cellDataFormula = implode('"', $temp); |
|
| 708 | } |
||
| 709 | |||
| 710 | 1 | $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setValueExplicit((($hasCalculatedValue) ? $cellDataFormula : $cellValue), $type); |
|
| 711 | 1 | if ($hasCalculatedValue) { |
|
| 712 | 1 | $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setCalculatedValue($cellValue); |
|
| 713 | } |
||
| 714 | 1 | $cellIsSet = $rowHasData = true; |
|
| 715 | } |
||
| 716 | |||
| 717 | 1 | if (isset($cell->Comment)) { |
|
| 718 | 1 | $commentAttributes = $cell->Comment->attributes($namespaces['ss']); |
|
| 719 | 1 | $author = 'unknown'; |
|
| 720 | 1 | if (isset($commentAttributes->Author)) { |
|
| 721 | $author = (string) $commentAttributes->Author; |
||
| 722 | } |
||
| 723 | 1 | $node = $cell->Comment->Data->asXML(); |
|
| 724 | 1 | $annotation = strip_tags($node); |
|
| 725 | 1 | $spreadsheet->getActiveSheet()->getComment($columnID . $rowID)->setAuthor(self::convertStringEncoding($author, $this->charSet))->setText($this->parseRichText($annotation)); |
|
| 726 | } |
||
| 727 | |||
| 728 | 1 | if (($cellIsSet) && (isset($cell_ss['StyleID']))) { |
|
| 729 | 1 | $style = (string) $cell_ss['StyleID']; |
|
| 730 | 1 | if ((isset($this->styles[$style])) && (!empty($this->styles[$style]))) { |
|
| 731 | 1 | if (!$spreadsheet->getActiveSheet()->cellExists($columnID . $rowID)) { |
|
| 732 | $spreadsheet->getActiveSheet()->getCell($columnID . $rowID)->setValue(null); |
||
| 733 | } |
||
| 734 | 1 | $spreadsheet->getActiveSheet()->getStyle($cellRange)->applyFromArray($this->styles[$style]); |
|
| 735 | } |
||
| 736 | } |
||
| 737 | 1 | ++$columnID; |
|
| 738 | 1 | while ($additionalMergedCells > 0) { |
|
| 739 | 1 | ++$columnID; |
|
| 740 | 1 | --$additionalMergedCells; |
|
| 741 | } |
||
| 742 | } |
||
| 743 | |||
| 744 | 1 | if ($rowHasData) { |
|
| 745 | 1 | if (isset($row_ss['StyleID'])) { |
|
| 746 | $rowStyle = $row_ss['StyleID']; |
||
| 747 | } |
||
| 748 | 1 | if (isset($row_ss['Height'])) { |
|
| 749 | 1 | $rowHeight = $row_ss['Height']; |
|
| 750 | 1 | $spreadsheet->getActiveSheet()->getRowDimension($rowID)->setRowHeight($rowHeight); |
|
| 751 | } |
||
| 752 | } |
||
| 753 | |||
| 754 | 1 | ++$rowID; |
|
| 755 | } |
||
| 756 | } |
||
| 757 | 1 | ++$worksheetID; |
|
| 758 | } |
||
| 759 | |||
| 760 | // Return |
||
| 761 | 1 | return $spreadsheet; |
|
| 762 | } |
||
| 763 | |||
| 764 | 1 | protected static function convertStringEncoding($string, $charset) |
|
| 772 | |||
| 773 | 1 | protected function parseRichText($is = '') |
|
| 781 | } |
||
| 782 |
Loading history...
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.