Differences Between: [Versions 310 and 311] [Versions 311 and 400] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]
1 <?php 2 3 namespace PhpOffice\PhpSpreadsheet\Reader; 4 5 use DateTime; 6 use DateTimeZone; 7 use DOMAttr; 8 use DOMDocument; 9 use DOMElement; 10 use DOMNode; 11 use PhpOffice\PhpSpreadsheet\Calculation\Calculation; 12 use PhpOffice\PhpSpreadsheet\Cell\Coordinate; 13 use PhpOffice\PhpSpreadsheet\Cell\DataType; 14 use PhpOffice\PhpSpreadsheet\DefinedName; 15 use PhpOffice\PhpSpreadsheet\Reader\Exception as ReaderException; 16 use PhpOffice\PhpSpreadsheet\Reader\Ods\PageSettings; 17 use PhpOffice\PhpSpreadsheet\Reader\Ods\Properties as DocumentProperties; 18 use PhpOffice\PhpSpreadsheet\Reader\Security\XmlScanner; 19 use PhpOffice\PhpSpreadsheet\RichText\RichText; 20 use PhpOffice\PhpSpreadsheet\Settings; 21 use PhpOffice\PhpSpreadsheet\Shared\Date; 22 use PhpOffice\PhpSpreadsheet\Shared\File; 23 use PhpOffice\PhpSpreadsheet\Spreadsheet; 24 use PhpOffice\PhpSpreadsheet\Style\NumberFormat; 25 use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet; 26 use XMLReader; 27 use ZipArchive; 28 29 class Ods extends BaseReader 30 { 31 /** 32 * Create a new Ods Reader instance. 33 */ 34 public function __construct() 35 { 36 parent::__construct(); 37 $this->securityScanner = XmlScanner::getInstance($this); 38 } 39 40 /** 41 * Can the current IReader read the file? 42 * 43 * @param string $pFilename 44 * 45 * @return bool 46 */ 47 public function canRead($pFilename) 48 { 49 File::assertFile($pFilename); 50 51 $mimeType = 'UNKNOWN'; 52 53 // Load file 54 55 $zip = new ZipArchive(); 56 if ($zip->open($pFilename) === true) { 57 // check if it is an OOXML archive 58 $stat = $zip->statName('mimetype'); 59 if ($stat && ($stat['size'] <= 255)) { 60 $mimeType = $zip->getFromName($stat['name']); 61 } elseif ($zip->statName('META-INF/manifest.xml')) { 62 $xml = simplexml_load_string( 63 $this->securityScanner->scan($zip->getFromName('META-INF/manifest.xml')), 64 'SimpleXMLElement', 65 Settings::getLibXmlLoaderOptions() 66 ); 67 $namespacesContent = $xml->getNamespaces(true); 68 if (isset($namespacesContent['manifest'])) { 69 $manifest = $xml->children($namespacesContent['manifest']); 70 foreach ($manifest as $manifestDataSet) { 71 $manifestAttributes = $manifestDataSet->attributes($namespacesContent['manifest']); 72 if ($manifestAttributes->{'full-path'} == '/') { 73 $mimeType = (string) $manifestAttributes->{'media-type'}; 74 75 break; 76 } 77 } 78 } 79 } 80 81 $zip->close(); 82 } 83 84 return $mimeType === 'application/vnd.oasis.opendocument.spreadsheet'; 85 } 86 87 /** 88 * Reads names of the worksheets from a file, without parsing the whole file to a PhpSpreadsheet object. 89 * 90 * @param string $pFilename 91 * 92 * @return string[] 93 */ 94 public function listWorksheetNames($pFilename) 95 { 96 File::assertFile($pFilename); 97 98 $zip = new ZipArchive(); 99 if ($zip->open($pFilename) !== true) { 100 throw new ReaderException('Could not open ' . $pFilename . ' for reading! Error opening file.'); 101 } 102 103 $worksheetNames = []; 104 105 $xml = new XMLReader(); 106 $xml->xml( 107 $this->securityScanner->scanFile('zip://' . realpath($pFilename) . '#content.xml'), 108 null, 109 Settings::getLibXmlLoaderOptions() 110 ); 111 $xml->setParserProperty(2, true); 112 113 // Step into the first level of content of the XML 114 $xml->read(); 115 while ($xml->read()) { 116 // Quickly jump through to the office:body node 117 while ($xml->name !== 'office:body') { 118 if ($xml->isEmptyElement) { 119 $xml->read(); 120 } else { 121 $xml->next(); 122 } 123 } 124 // Now read each node until we find our first table:table node 125 while ($xml->read()) { 126 if ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT) { 127 // Loop through each table:table node reading the table:name attribute for each worksheet name 128 do { 129 $worksheetNames[] = $xml->getAttribute('table:name'); 130 $xml->next(); 131 } while ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT); 132 } 133 } 134 } 135 136 return $worksheetNames; 137 } 138 139 /** 140 * Return worksheet info (Name, Last Column Letter, Last Column Index, Total Rows, Total Columns). 141 * 142 * @param string $pFilename 143 * 144 * @return array 145 */ 146 public function listWorksheetInfo($pFilename) 147 { 148 File::assertFile($pFilename); 149 150 $worksheetInfo = []; 151 152 $zip = new ZipArchive(); 153 if ($zip->open($pFilename) !== true) { 154 throw new ReaderException('Could not open ' . $pFilename . ' for reading! Error opening file.'); 155 } 156 157 $xml = new XMLReader(); 158 $xml->xml( 159 $this->securityScanner->scanFile('zip://' . realpath($pFilename) . '#content.xml'), 160 null, 161 Settings::getLibXmlLoaderOptions() 162 ); 163 $xml->setParserProperty(2, true); 164 165 // Step into the first level of content of the XML 166 $xml->read(); 167 while ($xml->read()) { 168 // Quickly jump through to the office:body node 169 while ($xml->name !== 'office:body') { 170 if ($xml->isEmptyElement) { 171 $xml->read(); 172 } else { 173 $xml->next(); 174 } 175 } 176 // Now read each node until we find our first table:table node 177 while ($xml->read()) { 178 if ($xml->name == 'table:table' && $xml->nodeType == XMLReader::ELEMENT) { 179 $worksheetNames[] = $xml->getAttribute('table:name'); 180 181 $tmpInfo = [ 182 'worksheetName' => $xml->getAttribute('table:name'), 183 'lastColumnLetter' => 'A', 184 'lastColumnIndex' => 0, 185 'totalRows' => 0, 186 'totalColumns' => 0, 187 ]; 188 189 // Loop through each child node of the table:table element reading 190 $currCells = 0; 191 do { 192 $xml->read(); 193 if ($xml->name == 'table:table-row' && $xml->nodeType == XMLReader::ELEMENT) { 194 $rowspan = $xml->getAttribute('table:number-rows-repeated'); 195 $rowspan = empty($rowspan) ? 1 : $rowspan; 196 $tmpInfo['totalRows'] += $rowspan; 197 $tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'], $currCells); 198 $currCells = 0; 199 // Step into the row 200 $xml->read(); 201 do { 202 $doread = true; 203 if ($xml->name == 'table:table-cell' && $xml->nodeType == XMLReader::ELEMENT) { 204 if (!$xml->isEmptyElement) { 205 ++$currCells; 206 $xml->next(); 207 $doread = false; 208 } 209 } elseif ($xml->name == 'table:covered-table-cell' && $xml->nodeType == XMLReader::ELEMENT) { 210 $mergeSize = $xml->getAttribute('table:number-columns-repeated'); 211 $currCells += (int) $mergeSize; 212 } 213 if ($doread) { 214 $xml->read(); 215 } 216 } while ($xml->name != 'table:table-row'); 217 } 218 } while ($xml->name != 'table:table'); 219 220 $tmpInfo['totalColumns'] = max($tmpInfo['totalColumns'], $currCells); 221 $tmpInfo['lastColumnIndex'] = $tmpInfo['totalColumns'] - 1; 222 $tmpInfo['lastColumnLetter'] = Coordinate::stringFromColumnIndex($tmpInfo['lastColumnIndex'] + 1); 223 $worksheetInfo[] = $tmpInfo; 224 } 225 } 226 } 227 228 return $worksheetInfo; 229 } 230 231 /** 232 * Loads PhpSpreadsheet from file. 233 * 234 * @param string $pFilename 235 * 236 * @return Spreadsheet 237 */ 238 public function load($pFilename) 239 { 240 // Create new Spreadsheet 241 $spreadsheet = new Spreadsheet(); 242 243 // Load into this instance 244 return $this->loadIntoExisting($pFilename, $spreadsheet); 245 } 246 247 /** 248 * Loads PhpSpreadsheet from file into PhpSpreadsheet instance. 249 * 250 * @param string $pFilename 251 * 252 * @return Spreadsheet 253 */ 254 public function loadIntoExisting($pFilename, Spreadsheet $spreadsheet) 255 { 256 File::assertFile($pFilename); 257 258 $timezoneObj = new DateTimeZone('Europe/London'); 259 $GMT = new DateTimeZone('UTC'); 260 261 $zip = new ZipArchive(); 262 if ($zip->open($pFilename) !== true) { 263 throw new Exception("Could not open {$pFilename} for reading! Error opening file."); 264 } 265 266 // Meta 267 268 $xml = @simplexml_load_string( 269 $this->securityScanner->scan($zip->getFromName('meta.xml')), 270 'SimpleXMLElement', 271 Settings::getLibXmlLoaderOptions() 272 ); 273 if ($xml === false) { 274 throw new Exception('Unable to read data from {$pFilename}'); 275 } 276 277 $namespacesMeta = $xml->getNamespaces(true); 278 279 (new DocumentProperties($spreadsheet))->load($xml, $namespacesMeta); 280 281 // Styles 282 283 $dom = new DOMDocument('1.01', 'UTF-8'); 284 $dom->loadXML( 285 $this->securityScanner->scan($zip->getFromName('styles.xml')), 286 Settings::getLibXmlLoaderOptions() 287 ); 288 289 $pageSettings = new PageSettings($dom); 290 291 // Main Content 292 293 $dom = new DOMDocument('1.01', 'UTF-8'); 294 $dom->loadXML( 295 $this->securityScanner->scan($zip->getFromName('content.xml')), 296 Settings::getLibXmlLoaderOptions() 297 ); 298 299 $officeNs = $dom->lookupNamespaceUri('office'); 300 $tableNs = $dom->lookupNamespaceUri('table'); 301 $textNs = $dom->lookupNamespaceUri('text'); 302 $xlinkNs = $dom->lookupNamespaceUri('xlink'); 303 304 $pageSettings->readStyleCrossReferences($dom); 305 306 // Content 307 308 $spreadsheets = $dom->getElementsByTagNameNS($officeNs, 'body') 309 ->item(0) 310 ->getElementsByTagNameNS($officeNs, 'spreadsheet'); 311 312 foreach ($spreadsheets as $workbookData) { 313 /** @var DOMElement $workbookData */ 314 $tables = $workbookData->getElementsByTagNameNS($tableNs, 'table'); 315 316 $worksheetID = 0; 317 foreach ($tables as $worksheetDataSet) { 318 /** @var DOMElement $worksheetDataSet */ 319 $worksheetName = $worksheetDataSet->getAttributeNS($tableNs, 'name'); 320 321 // Check loadSheetsOnly 322 if ( 323 isset($this->loadSheetsOnly) 324 && $worksheetName 325 && !in_array($worksheetName, $this->loadSheetsOnly) 326 ) { 327 continue; 328 } 329 330 $worksheetStyleName = $worksheetDataSet->getAttributeNS($tableNs, 'style-name'); 331 332 // Create sheet 333 if ($worksheetID > 0) { 334 $spreadsheet->createSheet(); // First sheet is added by default 335 } 336 $spreadsheet->setActiveSheetIndex($worksheetID); 337 338 if ($worksheetName) { 339 // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in 340 // formula cells... during the load, all formulae should be correct, and we're simply 341 // bringing the worksheet name in line with the formula, not the reverse 342 $spreadsheet->getActiveSheet()->setTitle((string) $worksheetName, false, false); 343 } 344 345 // Go through every child of table element 346 $rowID = 1; 347 foreach ($worksheetDataSet->childNodes as $childNode) { 348 /** @var DOMElement $childNode */ 349 350 // Filter elements which are not under the "table" ns 351 if ($childNode->namespaceURI != $tableNs) { 352 continue; 353 } 354 355 $key = $childNode->nodeName; 356 357 // Remove ns from node name 358 if (strpos($key, ':') !== false) { 359 $keyChunks = explode(':', $key); 360 $key = array_pop($keyChunks); 361 } 362 363 switch ($key) { 364 case 'table-header-rows': 365 /// TODO :: Figure this out. This is only a partial implementation I guess. 366 // ($rowData it's not used at all and I'm not sure that PHPExcel 367 // has an API for this) 368 369 // foreach ($rowData as $keyRowData => $cellData) { 370 // $rowData = $cellData; 371 // break; 372 // } 373 break; 374 case 'table-row': 375 if ($childNode->hasAttributeNS($tableNs, 'number-rows-repeated')) { 376 $rowRepeats = $childNode->getAttributeNS($tableNs, 'number-rows-repeated'); 377 } else { 378 $rowRepeats = 1; 379 } 380 381 $columnID = 'A'; 382 foreach ($childNode->childNodes as $key => $cellData) { 383 // @var \DOMElement $cellData 384 385 if ($this->getReadFilter() !== null) { 386 if (!$this->getReadFilter()->readCell($columnID, $rowID, $worksheetName)) { 387 ++$columnID; 388 389 continue; 390 } 391 } 392 393 // Initialize variables 394 $formatting = $hyperlink = null; 395 $hasCalculatedValue = false; 396 $cellDataFormula = ''; 397 398 if ($cellData->hasAttributeNS($tableNs, 'formula')) { 399 $cellDataFormula = $cellData->getAttributeNS($tableNs, 'formula'); 400 $hasCalculatedValue = true; 401 } 402 403 // Annotations 404 $annotation = $cellData->getElementsByTagNameNS($officeNs, 'annotation'); 405 406 if ($annotation->length > 0) { 407 $textNode = $annotation->item(0)->getElementsByTagNameNS($textNs, 'p'); 408 409 if ($textNode->length > 0) { 410 $text = $this->scanElementForText($textNode->item(0)); 411 412 $spreadsheet->getActiveSheet() 413 ->getComment($columnID . $rowID) 414 ->setText($this->parseRichText($text)); 415 // ->setAuthor( $author ) 416 } 417 } 418 419 // Content 420 421 /** @var DOMElement[] $paragraphs */ 422 $paragraphs = []; 423 424 foreach ($cellData->childNodes as $item) { 425 /** @var DOMElement $item */ 426 427 // Filter text:p elements 428 if ($item->nodeName == 'text:p') { 429 $paragraphs[] = $item; 430 } 431 } 432 433 if (count($paragraphs) > 0) { 434 // Consolidate if there are multiple p records (maybe with spans as well) 435 $dataArray = []; 436 437 // Text can have multiple text:p and within those, multiple text:span. 438 // text:p newlines, but text:span does not. 439 // Also, here we assume there is no text data is span fields are specified, since 440 // we have no way of knowing proper positioning anyway. 441 442 foreach ($paragraphs as $pData) { 443 $dataArray[] = $this->scanElementForText($pData); 444 } 445 $allCellDataText = implode("\n", $dataArray); 446 447 $type = $cellData->getAttributeNS($officeNs, 'value-type'); 448 449 switch ($type) { 450 case 'string': 451 $type = DataType::TYPE_STRING; 452 $dataValue = $allCellDataText; 453 454 foreach ($paragraphs as $paragraph) { 455 $link = $paragraph->getElementsByTagNameNS($textNs, 'a'); 456 if ($link->length > 0) { 457 $hyperlink = $link->item(0)->getAttributeNS($xlinkNs, 'href'); 458 } 459 } 460 461 break; 462 case 'boolean': 463 $type = DataType::TYPE_BOOL; 464 $dataValue = ($allCellDataText == 'TRUE') ? true : false; 465 466 break; 467 case 'percentage': 468 $type = DataType::TYPE_NUMERIC; 469 $dataValue = (float) $cellData->getAttributeNS($officeNs, 'value'); 470 471 // percentage should always be float 472 //if (floor($dataValue) == $dataValue) { 473 // $dataValue = (int) $dataValue; 474 //} 475 $formatting = NumberFormat::FORMAT_PERCENTAGE_00; 476 477 break; 478 case 'currency': 479 $type = DataType::TYPE_NUMERIC; 480 $dataValue = (float) $cellData->getAttributeNS($officeNs, 'value'); 481 482 if (floor($dataValue) == $dataValue) { 483 $dataValue = (int) $dataValue; 484 } 485 $formatting = NumberFormat::FORMAT_CURRENCY_USD_SIMPLE; 486 487 break; 488 case 'float': 489 $type = DataType::TYPE_NUMERIC; 490 $dataValue = (float) $cellData->getAttributeNS($officeNs, 'value'); 491 492 if (floor($dataValue) == $dataValue) { 493 if ($dataValue == (int) $dataValue) { 494 $dataValue = (int) $dataValue; 495 } 496 } 497 498 break; 499 case 'date': 500 $type = DataType::TYPE_NUMERIC; 501 $value = $cellData->getAttributeNS($officeNs, 'date-value'); 502 503 $dateObj = new DateTime($value, $GMT); 504 $dateObj->setTimeZone($timezoneObj); 505 [$year, $month, $day, $hour, $minute, $second] = explode( 506 ' ', 507 $dateObj->format('Y m d H i s') 508 ); 509 510 $dataValue = Date::formattedPHPToExcel( 511 (int) $year, 512 (int) $month, 513 (int) $day, 514 (int) $hour, 515 (int) $minute, 516 (int) $second 517 ); 518 519 if ($dataValue != floor($dataValue)) { 520 $formatting = NumberFormat::FORMAT_DATE_XLSX15 521 . ' ' 522 . NumberFormat::FORMAT_DATE_TIME4; 523 } else { 524 $formatting = NumberFormat::FORMAT_DATE_XLSX15; 525 } 526 527 break; 528 case 'time': 529 $type = DataType::TYPE_NUMERIC; 530 531 $timeValue = $cellData->getAttributeNS($officeNs, 'time-value'); 532 533 $dataValue = Date::PHPToExcel( 534 strtotime( 535 '01-01-1970 ' . implode(':', sscanf($timeValue, 'PT%dH%dM%dS')) 536 ) 537 ); 538 $formatting = NumberFormat::FORMAT_DATE_TIME4; 539 540 break; 541 default: 542 $dataValue = null; 543 } 544 } else { 545 $type = DataType::TYPE_NULL; 546 $dataValue = null; 547 } 548 549 if ($hasCalculatedValue) { 550 $type = DataType::TYPE_FORMULA; 551 $cellDataFormula = substr($cellDataFormula, strpos($cellDataFormula, ':=') + 1); 552 $cellDataFormula = $this->convertToExcelFormulaValue($cellDataFormula); 553 } 554 555 if ($cellData->hasAttributeNS($tableNs, 'number-columns-repeated')) { 556 $colRepeats = (int) $cellData->getAttributeNS($tableNs, 'number-columns-repeated'); 557 } else { 558 $colRepeats = 1; 559 } 560 561 if ($type !== null) { 562 for ($i = 0; $i < $colRepeats; ++$i) { 563 if ($i > 0) { 564 ++$columnID; 565 } 566 567 if ($type !== DataType::TYPE_NULL) { 568 for ($rowAdjust = 0; $rowAdjust < $rowRepeats; ++$rowAdjust) { 569 $rID = $rowID + $rowAdjust; 570 571 $cell = $spreadsheet->getActiveSheet() 572 ->getCell($columnID . $rID); 573 574 // Set value 575 if ($hasCalculatedValue) { 576 $cell->setValueExplicit($cellDataFormula, $type); 577 } else { 578 $cell->setValueExplicit($dataValue, $type); 579 } 580 581 if ($hasCalculatedValue) { 582 $cell->setCalculatedValue($dataValue); 583 } 584 585 // Set other properties 586 if ($formatting !== null) { 587 $spreadsheet->getActiveSheet() 588 ->getStyle($columnID . $rID) 589 ->getNumberFormat() 590 ->setFormatCode($formatting); 591 } else { 592 $spreadsheet->getActiveSheet() 593 ->getStyle($columnID . $rID) 594 ->getNumberFormat() 595 ->setFormatCode(NumberFormat::FORMAT_GENERAL); 596 } 597 598 if ($hyperlink !== null) { 599 $cell->getHyperlink() 600 ->setUrl($hyperlink); 601 } 602 } 603 } 604 } 605 } 606 607 // Merged cells 608 if ( 609 $cellData->hasAttributeNS($tableNs, 'number-columns-spanned') 610 || $cellData->hasAttributeNS($tableNs, 'number-rows-spanned') 611 ) { 612 if (($type !== DataType::TYPE_NULL) || (!$this->readDataOnly)) { 613 $columnTo = $columnID; 614 615 if ($cellData->hasAttributeNS($tableNs, 'number-columns-spanned')) { 616 $columnIndex = Coordinate::columnIndexFromString($columnID); 617 $columnIndex += (int) $cellData->getAttributeNS($tableNs, 'number-columns-spanned'); 618 $columnIndex -= 2; 619 620 $columnTo = Coordinate::stringFromColumnIndex($columnIndex + 1); 621 } 622 623 $rowTo = $rowID; 624 625 if ($cellData->hasAttributeNS($tableNs, 'number-rows-spanned')) { 626 $rowTo = $rowTo + (int) $cellData->getAttributeNS($tableNs, 'number-rows-spanned') - 1; 627 } 628 629 $cellRange = $columnID . $rowID . ':' . $columnTo . $rowTo; 630 $spreadsheet->getActiveSheet()->mergeCells($cellRange); 631 } 632 } 633 634 ++$columnID; 635 } 636 $rowID += $rowRepeats; 637 638 break; 639 } 640 } 641 $pageSettings->setPrintSettingsForWorksheet($spreadsheet->getActiveSheet(), $worksheetStyleName); 642 ++$worksheetID; 643 } 644 645 $this->readDefinedRanges($spreadsheet, $workbookData, $tableNs); 646 $this->readDefinedExpressions($spreadsheet, $workbookData, $tableNs); 647 } 648 $spreadsheet->setActiveSheetIndex(0); 649 // Return 650 return $spreadsheet; 651 } 652 653 /** 654 * Recursively scan element. 655 * 656 * @return string 657 */ 658 protected function scanElementForText(DOMNode $element) 659 { 660 $str = ''; 661 foreach ($element->childNodes as $child) { 662 /** @var DOMNode $child */ 663 if ($child->nodeType == XML_TEXT_NODE) { 664 $str .= $child->nodeValue; 665 } elseif ($child->nodeType == XML_ELEMENT_NODE && $child->nodeName == 'text:s') { 666 // It's a space 667 668 // Multiple spaces? 669 /** @var DOMAttr $cAttr */ 670 $cAttr = $child->attributes->getNamedItem('c'); 671 if ($cAttr) { 672 $multiplier = (int) $cAttr->nodeValue; 673 } else { 674 $multiplier = 1; 675 } 676 677 $str .= str_repeat(' ', $multiplier); 678 } 679 680 if ($child->hasChildNodes()) { 681 $str .= $this->scanElementForText($child); 682 } 683 } 684 685 return $str; 686 } 687 688 /** 689 * @param string $is 690 * 691 * @return RichText 692 */ 693 private function parseRichText($is) 694 { 695 $value = new RichText(); 696 $value->createText($is); 697 698 return $value; 699 } 700 701 private function convertToExcelAddressValue(string $openOfficeAddress): string 702 { 703 $excelAddress = $openOfficeAddress; 704 705 // Cell range 3-d reference 706 // As we don't support 3-d ranges, we're just going to take a quick and dirty approach 707 // and assume that the second worksheet reference is the same as the first 708 $excelAddress = preg_replace('/\$?([^\.]+)\.([^\.]+):\$?([^\.]+)\.([^\.]+)/miu', '$1!$2:$4', $excelAddress); 709 // Cell range reference in another sheet 710 $excelAddress = preg_replace('/\$?([^\.]+)\.([^\.]+):\.([^\.]+)/miu', '$1!$2:$3', $excelAddress); 711 // Cell reference in another sheet 712 $excelAddress = preg_replace('/\$?([^\.]+)\.([^\.]+)/miu', '$1!$2', $excelAddress); 713 // Cell range reference 714 $excelAddress = preg_replace('/\.([^\.]+):\.([^\.]+)/miu', '$1:$2', $excelAddress); 715 // Simple cell reference 716 $excelAddress = preg_replace('/\.([^\.]+)/miu', '$1', $excelAddress); 717 718 return $excelAddress; 719 } 720 721 private function convertToExcelFormulaValue(string $openOfficeFormula): string 722 { 723 $temp = explode('"', $openOfficeFormula); 724 $tKey = false; 725 foreach ($temp as &$value) { 726 // Only replace in alternate array entries (i.e. non-quoted blocks) 727 if ($tKey = !$tKey) { 728 // Cell range reference in another sheet 729 $value = preg_replace('/\[\$?([^\.]+)\.([^\.]+):\.([^\.]+)\]/miu', '$1!$2:$3', $value); 730 // Cell reference in another sheet 731 $value = preg_replace('/\[\$?([^\.]+)\.([^\.]+)\]/miu', '$1!$2', $value); 732 // Cell range reference 733 $value = preg_replace('/\[\.([^\.]+):\.([^\.]+)\]/miu', '$1:$2', $value); 734 // Simple cell reference 735 $value = preg_replace('/\[\.([^\.]+)\]/miu', '$1', $value); 736 737 $value = Calculation::translateSeparator(';', ',', $value, $inBraces); 738 } 739 } 740 741 // Then rebuild the formula string 742 $excelFormula = implode('"', $temp); 743 744 return $excelFormula; 745 } 746 747 /** 748 * Read any Named Ranges that are defined in this spreadsheet. 749 */ 750 private function readDefinedRanges(Spreadsheet $spreadsheet, DOMElement $workbookData, string $tableNs): void 751 { 752 $namedRanges = $workbookData->getElementsByTagNameNS($tableNs, 'named-range'); 753 foreach ($namedRanges as $definedNameElement) { 754 $definedName = $definedNameElement->getAttributeNS($tableNs, 'name'); 755 $baseAddress = $definedNameElement->getAttributeNS($tableNs, 'base-cell-address'); 756 $range = $definedNameElement->getAttributeNS($tableNs, 'cell-range-address'); 757 758 $baseAddress = $this->convertToExcelAddressValue($baseAddress); 759 $range = $this->convertToExcelAddressValue($range); 760 761 $this->addDefinedName($spreadsheet, $baseAddress, $definedName, $range); 762 } 763 } 764 765 /** 766 * Read any Named Formulae that are defined in this spreadsheet. 767 */ 768 private function readDefinedExpressions(Spreadsheet $spreadsheet, DOMElement $workbookData, string $tableNs): void 769 { 770 $namedExpressions = $workbookData->getElementsByTagNameNS($tableNs, 'named-expression'); 771 foreach ($namedExpressions as $definedNameElement) { 772 $definedName = $definedNameElement->getAttributeNS($tableNs, 'name'); 773 $baseAddress = $definedNameElement->getAttributeNS($tableNs, 'base-cell-address'); 774 $expression = $definedNameElement->getAttributeNS($tableNs, 'expression'); 775 776 $baseAddress = $this->convertToExcelAddressValue($baseAddress); 777 $expression = $this->convertToExcelFormulaValue($expression); 778 779 $this->addDefinedName($spreadsheet, $baseAddress, $definedName, $expression); 780 } 781 } 782 783 /** 784 * Assess scope and store the Defined Name. 785 */ 786 private function addDefinedName(Spreadsheet $spreadsheet, string $baseAddress, string $definedName, string $value): void 787 { 788 [$sheetReference] = Worksheet::extractSheetTitle($baseAddress, true); 789 $worksheet = $spreadsheet->getSheetByName($sheetReference); 790 // Worksheet might still be null if we're only loading selected sheets rather than the full spreadsheet 791 if ($worksheet !== null) { 792 $spreadsheet->addDefinedName(DefinedName::createInstance((string) $definedName, $worksheet, $value)); 793 } 794 } 795 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body