nfTlWNl rn Ѻq$³*LJ0Y8i(?Q&VY{ V~PQ?ltjdݽ"Vy̾,_NgMvO)^+ֹ\`Lٔ*ܝIc_i+pVN7Kjm)(6ɶuk Ѻq$³*LJ0Y8i(?bD)?Ael p&R0H3W-Ign&aIYu W< á/X!" CӝINgm-P*zh4Tю{t:EۈC CӝINghk)X{'1`Uⓙ}]w&]T;m]ى8XzH4Q\X8&D&oAY\QN#]>t'#s8UΚ.QyC#֒Hnn!V5Y KR~+ѬρnH1s A_#]HA*IƳ%#[yt-ڈdv0|"W|u;Ki=hbV|:Lїf.j-OBM*3$0o3d1Ŗ@wH5\ V!@qd.-HXX=y g#׎,LXU bXM+c1k->kQ!,5shE8j{wtbPYFo/I.pNXX"t3$'ߪ4u;R'jF/8=}UoR\X92z"" +bkxgl1e{Qʜ3Dq)žz0sD3X3H¬8WnFEܹ24fM^B`ꮿ( \16N/5KUhVqm`<à%x#Wg +bX0XVpo'!C+_`da ;vF|W+k =]emy;m GiZ.[5W&O_uHRj ~DX ҷ)eFE`LgVjJ(mYvn4zMrEK1K#pҕbJa0 \ѢVg>֓)Y$^G8&kUz %+4. cٌ(>AY+^L?V'c&D&&v8$H>,"/rtOZhPEΐ4OȻD%vX+R|{"9P-> 6a|U@b*oV- {tRe=ٔe ӓ"XsVǖq(rJ/]T lfs@Ny{Q"Lsp&`^(-Arq4:Ns=a/˖^uh:&:'F2[t0E.g ?].4\ùoCTw #pU(/f?seBɬfu>q(rJ/]JsbBqiye8PсTa~!(CW" L'Ь]D$iQ&ELvl(lf,"Ugxd*W^AJ.1(e_%~h kYҒLihcNp>p?Q7}ĮDfdkb5g|| !O4,}%=҂_yƃ䰋?w!h R&: /S3p RvIw"A h[P4u,_f S&|t{iqhca%8|Z'3 (f_)%Gcv/whBe7x0·aVS:gnOkO}dh*&ABUe1DEKY9ڬKl@lx#эNTYŴG}1N<駬)^f aFqGk!>׻վ0{{2 VZ[P\ :g" RHUu,^2قk\a"^`]7EYcN]@}nK :ϕ7lMp׭'ITCZ-9kg uԏ{aV- qrzX):G/r1=<&P M^||fWIc+|1x}cJ6Z4G4H"(W?HAΣSaٳeΏV城XZC ɬd1Cx"+;*.dY0&ZY/A5itkfx%ZB)F^nWl 컅\>DG:oY28^E"/kROex i[g[_Z<\;fRנ`P6<4 eMYBՖ>Ba<f! E6W ըO̥ʃP؊l] ODH,S55agOge yTB1a6a_N:B7?L\~YFQ:OD#:@S5)݇n Ȃx~vP=f&w`_p%ӷDZ_^/2uAAڋ;>}3|%Gc eLßp "jW}д|B+LQ,dwuZ]<]O;KbW,:eNS9 ܑ”e;zѷ3`=/tCT~}WG;~NwR5LstLh7DorD# #5 $AC(+ZMd v&WEn>q)L\lBH_Q bo&'4O{.9zZ:;I4Yugk7Uk7&$DEKXc9"ս) OlnSO>@׀"ѓ/̇HG'wr5a<3Faj>ct7dEd꾽QYv 25KCAE֑'x4h빍R=P1MZ!AQJTpʪ1L˳pϚʍQ0  2v}׳ݭI*˼Ưw"y+i¸F[r.t>*kZxxm6/a呵A -eR @i>BT^fmd -$ ~tCRhy_"tIʹri&9 Lםtl# &ݜ.5.ps-ĠJW{*C~k|ztLߒ{4U&^ƍ|l6|92Tڋ1} 3.}a9J_6Ǔsg~*KyVGޚ]+ߓ[:^(@RqjO"/AI5M+r}26y-+ )$6%LUؼ -iS}˃j"lw#K|DYMN~ ު!3"5b׈.UZ2 w- zlaչ YuiZ9AȖpGCБ68+A1 ӗeʣ\rjJ(mYvn4z8J^QM}1^ _a2ݼI0 Last Column Index, Total Rows, Total Columns). * * @param string $filename * * @return array */ public function listWorksheetInfo($filename) { File::assertFile($filename); if (!$this->canRead($filename)) { throw new Exception($filename . ' is an invalid Gnumeric file.'); } $xml = new XMLReader(); $contents = $this->gzfileGetContents($filename); $xml->xml($contents, null, Settings::getLibXmlLoaderOptions()); $xml->setParserProperty(2, true); $worksheetInfo = []; while ($xml->read()) { if (self::matchXml($xml, 'Sheet')) { $tmpInfo = [ 'worksheetName' => '', 'lastColumnLetter' => 'A', 'lastColumnIndex' => 0, 'totalRows' => 0, 'totalColumns' => 0, ]; while ($xml->read()) { if (self::matchXml($xml, 'Name')) { $xml->read(); // Move onto the value node $tmpInfo['worksheetName'] = (string) $xml->value; } elseif (self::matchXml($xml, 'MaxCol')) { $xml->read(); // Move onto the value node $tmpInfo['lastColumnIndex'] = (int) $xml->value; $tmpInfo['totalColumns'] = (int) $xml->value + 1; } elseif (self::matchXml($xml, 'MaxRow')) { $xml->read(); // Move onto the value node $tmpInfo['totalRows'] = (int) $xml->value + 1; break; } } $tmpInfo['lastColumnLetter'] = Coordinate::stringFromColumnIndex($tmpInfo['lastColumnIndex'] + 1); $worksheetInfo[] = $tmpInfo; } } return $worksheetInfo; } /** * @param string $filename * * @return string */ private function gzfileGetContents($filename) { $data = ''; $contents = @file_get_contents($filename); if ($contents !== false) { if (substr($contents, 0, 2) === "\x1f\x8b") { // Check if gzlib functions are available if (function_exists('gzdecode')) { $contents = @gzdecode($contents); if ($contents !== false) { $data = $contents; } } } else { $data = $contents; } } if ($data !== '') { $data = $this->getSecurityScannerOrThrow()->scan($data); } return $data; } public static function gnumericMappings(): array { return array_merge(self::$mappings, Styles::$mappings); } private function processComments(SimpleXMLElement $sheet): void { if ((!$this->readDataOnly) && (isset($sheet->Objects))) { foreach ($sheet->Objects->children(self::NAMESPACE_GNM) as $key => $comment) { $commentAttributes = $comment->attributes(); // Only comment objects are handled at the moment if ($commentAttributes && $commentAttributes->Text) { $this->spreadsheet->getActiveSheet()->getComment((string) $commentAttributes->ObjectBound) ->setAuthor((string) $commentAttributes->Author) ->setText($this->parseRichText((string) $commentAttributes->Text)); } } } } /** * @param mixed $value */ private static function testSimpleXml($value): SimpleXMLElement { return ($value instanceof SimpleXMLElement) ? $value : new SimpleXMLElement(''); } /** * Loads Spreadsheet from file. */ protected function loadSpreadsheetFromFile(string $filename): Spreadsheet { // Create new Spreadsheet $spreadsheet = new Spreadsheet(); $spreadsheet->removeSheetByIndex(0); // Load into this instance return $this->loadIntoExisting($filename, $spreadsheet); } /** * Loads from file into Spreadsheet instance. */ public function loadIntoExisting(string $filename, Spreadsheet $spreadsheet): Spreadsheet { $this->spreadsheet = $spreadsheet; File::assertFile($filename); if (!$this->canRead($filename)) { throw new Exception($filename . ' is an invalid Gnumeric file.'); } $gFileData = $this->gzfileGetContents($filename); $xml2 = simplexml_load_string($gFileData, 'SimpleXMLElement', Settings::getLibXmlLoaderOptions()); $xml = self::testSimpleXml($xml2); $gnmXML = $xml->children(self::NAMESPACE_GNM); (new Properties($this->spreadsheet))->readProperties($xml, $gnmXML); $worksheetID = 0; foreach ($gnmXML->Sheets->Sheet as $sheetOrNull) { $sheet = self::testSimpleXml($sheetOrNull); $worksheetName = (string) $sheet->Name; if (is_array($this->loadSheetsOnly) && !in_array($worksheetName, $this->loadSheetsOnly, true)) { continue; } $maxRow = $maxCol = 0; // Create new Worksheet $this->spreadsheet->createSheet(); $this->spreadsheet->setActiveSheetIndex($worksheetID); // Use false for $updateFormulaCellReferences to prevent adjustment of worksheet references in formula // cells... during the load, all formulae should be correct, and we're simply bringing the worksheet // name in line with the formula, not the reverse $this->spreadsheet->getActiveSheet()->setTitle($worksheetName, false, false); $visibility = $sheet->attributes()['Visibility'] ?? 'GNM_SHEET_VISIBILITY_VISIBLE'; if ((string) $visibility !== 'GNM_SHEET_VISIBILITY_VISIBLE') { $this->spreadsheet->getActiveSheet()->setSheetState(Worksheet::SHEETSTATE_HIDDEN); } if (!$this->readDataOnly) { (new PageSetup($this->spreadsheet)) ->printInformation($sheet) ->sheetMargins($sheet); } foreach ($sheet->Cells->Cell as $cellOrNull) { $cell = self::testSimpleXml($cellOrNull); $cellAttributes = self::testSimpleXml($cell->attributes()); $row = (int) $cellAttributes->Row + 1; $column = (int) $cellAttributes->Col; $maxRow = max($maxRow, $row); $maxCol = max($maxCol, $column); $column = Coordinate::stringFromColumnIndex($column + 1); // Read cell? if ($this->getReadFilter() !== null) { if (!$this->getReadFilter()->readCell($column, $row, $worksheetName)) { continue; } } $this->loadCell($cell, $worksheetName, $cellAttributes, $column, $row); } if ($sheet->Styles !== null) { (new Styles($this->spreadsheet, $this->readDataOnly))->read($sheet, $maxRow, $maxCol); } $this->processComments($sheet); $this->processColumnWidths($sheet, $maxCol); $this->processRowHeights($sheet, $maxRow); $this->processMergedCells($sheet); $this->processAutofilter($sheet); $this->setSelectedCells($sheet); ++$worksheetID; } $this->processDefinedNames($gnmXML); $this->setSelectedSheet($gnmXML); // Return return $this->spreadsheet; } private function setSelectedSheet(SimpleXMLElement $gnmXML): void { if (isset($gnmXML->UIData)) { $attributes = self::testSimpleXml($gnmXML->UIData->attributes()); $selectedSheet = (int) $attributes['SelectedTab']; $this->spreadsheet->setActiveSheetIndex($selectedSheet); } } private function setSelectedCells(?SimpleXMLElement $sheet): void { if ($sheet !== null && isset($sheet->Selections)) { foreach ($sheet->Selections as $selection) { $startCol = (int) ($selection->StartCol ?? 0); $startRow = (int) ($selection->StartRow ?? 0) + 1; $endCol = (int) ($selection->EndCol ?? $startCol); $endRow = (int) ($selection->endRow ?? 0) + 1; $startColumn = Coordinate::stringFromColumnIndex($startCol + 1); $endColumn = Coordinate::stringFromColumnIndex($endCol + 1); $startCell = "{$startColumn}{$startRow}"; $endCell = "{$endColumn}{$endRow}"; $selectedRange = $startCell . (($endCell !== $startCell) ? ':' . $endCell : ''); $this->spreadsheet->getActiveSheet()->setSelectedCell($selectedRange); break; } } } private function processMergedCells(?SimpleXMLElement $sheet): void { // Handle Merged Cells in this worksheet if ($sheet !== null && isset($sheet->MergedRegions)) { foreach ($sheet->MergedRegions->Merge as $mergeCells) { if (strpos((string) $mergeCells, ':') !== false) { $this->spreadsheet->getActiveSheet()->mergeCells($mergeCells, Worksheet::MERGE_CELL_CONTENT_HIDE); } } } } private function processAutofilter(?SimpleXMLElement $sheet): void { if ($sheet !== null && isset($sheet->Filters)) { foreach ($sheet->Filters->Filter as $autofilter) { if ($autofilter !== null) { $attributes = $autofilter->attributes(); if (isset($attributes['Area'])) { $this->spreadsheet->getActiveSheet()->setAutoFilter((string) $attributes['Area']); } } } } } private function setColumnWidth(int $whichColumn, float $defaultWidth): void { $columnDimension = $this->spreadsheet->getActiveSheet() ->getColumnDimension(Coordinate::stringFromColumnIndex($whichColumn + 1)); if ($columnDimension !== null) { $columnDimension->setWidth($defaultWidth); } } private function setColumnInvisible(int $whichColumn): void { $columnDimension = $this->spreadsheet->getActiveSheet() ->getColumnDimension(Coordinate::stringFromColumnIndex($whichColumn + 1)); if ($columnDimension !== null) { $columnDimension->setVisible(false); } } private function processColumnLoop(int $whichColumn, int $maxCol, ?SimpleXMLElement $columnOverride, float $defaultWidth): int { $columnOverride = self::testSimpleXml($columnOverride); $columnAttributes = self::testSimpleXml($columnOverride->attributes()); $column = $columnAttributes['No']; $columnWidth = ((float) $columnAttributes['Unit']) / 5.4; $hidden = (isset($columnAttributes['Hidden'])) && ((string) $columnAttributes['Hidden'] == '1'); $columnCount = (int) ($columnAttributes['Count'] ?? 1); while ($whichColumn < $column) { $this->setColumnWidth($whichColumn, $defaultWidth); ++$whichColumn; } while (($whichColumn < ($column + $columnCount)) && ($whichColumn <= $maxCol)) { $this->setColumnWidth($whichColumn, $columnWidth); if ($hidden) { $this->setColumnInvisible($whichColumn); } ++$whichColumn; } return $whichColumn; } private function processColumnWidths(?SimpleXMLElement $sheet, int $maxCol): void { if ((!$this->readDataOnly) && $sheet !== null && (isset($sheet->Cols))) { // Column Widths $defaultWidth = 0; $columnAttributes = $sheet->Cols->attributes(); if ($columnAttributes !== null) { $defaultWidth = $columnAttributes['DefaultSizePts'] / 5.4; } $whichColumn = 0; foreach ($sheet->Cols->ColInfo as $columnOverride) { $whichColumn = $this->processColumnLoop($whichColumn, $maxCol, $columnOverride, $defaultWidth); } while ($whichColumn <= $maxCol) { $this->setColumnWidth($whichColumn, $defaultWidth); ++$whichColumn; } } } private function setRowHeight(int $whichRow, float $defaultHeight): void { $rowDimension = $this->spreadsheet->getActiveSheet()->getRowDimension($whichRow); if ($rowDimension !== null) { $rowDimension->setRowHeight($defaultHeight); } } private function setRowInvisible(int $whichRow): void { $rowDimension = $this->spreadsheet->getActiveSheet()->getRowDimension($whichRow); if ($rowDimension !== null) { $rowDimension->setVisible(false); } } private function processRowLoop(int $whichRow, int $maxRow, ?SimpleXMLElement $rowOverride, float $defaultHeight): int { $rowOverride = self::testSimpleXml($rowOverride); $rowAttributes = self::testSimpleXml($rowOverride->attributes()); $row = $rowAttributes['No']; $rowHeight = (float) $rowAttributes['Unit']; $hidden = (isset($rowAttributes['Hidden'])) && ((string) $rowAttributes['Hidden'] == '1'); $rowCount = (int) ($rowAttributes['Count'] ?? 1); while ($whichRow < $row) { ++$whichRow; $this->setRowHeight($whichRow, $defaultHeight); } while (($whichRow < ($row + $rowCount)) && ($whichRow < $maxRow)) { ++$whichRow; $this->setRowHeight($whichRow, $rowHeight); if ($hidden) { $this->setRowInvisible($whichRow); } } return $whichRow; } private function processRowHeights(?SimpleXMLElement $sheet, int $maxRow): void { if ((!$this->readDataOnly) && $sheet !== null && (isset($sheet->Rows))) { // Row Heights $defaultHeight = 0; $rowAttributes = $sheet->Rows->attributes(); if ($rowAttributes !== null) { $defaultHeight = (float) $rowAttributes['DefaultSizePts']; } $whichRow = 0; foreach ($sheet->Rows->RowInfo as $rowOverride) { $whichRow = $this->processRowLoop($whichRow, $maxRow, $rowOverride, $defaultHeight); } // never executed, I can't figure out any circumstances // under which it would be executed, and, even if // such exist, I'm not convinced this is needed. //while ($whichRow < $maxRow) { // ++$whichRow; // $this->spreadsheet->getActiveSheet()->getRowDimension($whichRow)->setRowHeight($defaultHeight); //} } } private function processDefinedNames(?SimpleXMLElement $gnmXML): void { // Loop through definedNames (global named ranges) if ($gnmXML !== null && isset($gnmXML->Names)) { foreach ($gnmXML->Names->Name as $definedName) { $name = (string) $definedName->name; $value = (string) $definedName->value; if (stripos($value, '#REF!') !== false) { continue; } [$worksheetName] = Worksheet::extractSheetTitle($value, true); $worksheetName = trim($worksheetName, "'"); $worksheet = $this->spreadsheet->getSheetByName($worksheetName); // Worksheet might still be null if we're only loading selected sheets rather than the full spreadsheet if ($worksheet !== null) { $this->spreadsheet->addDefinedName(DefinedName::createInstance($name, $worksheet, $value)); } } } } private function parseRichText(string $is): RichText { $value = new RichText(); $value->createText($is); return $value; } private function loadCell( SimpleXMLElement $cell, string $worksheetName, SimpleXMLElement $cellAttributes, string $column, int $row ): void { $ValueType = $cellAttributes->ValueType; $ExprID = (string) $cellAttributes->ExprID; $type = DataType::TYPE_FORMULA; if ($ExprID > '') { if (((string) $cell) > '') { $this->expressions[$ExprID] = [ 'column' => $cellAttributes->Col, 'row' => $cellAttributes->Row, 'formula' => (string) $cell, ]; } else { $expression = $this->expressions[$ExprID]; $cell = $this->referenceHelper->updateFormulaReferences( $expression['formula'], 'A1', $cellAttributes->Col - $expression['column'], $cellAttributes->Row - $expression['row'], $worksheetName ); } $type = DataType::TYPE_FORMULA; } else { $vtype = (string) $ValueType; if (array_key_exists($vtype, self::$mappings['dataType'])) { $type = self::$mappings['dataType'][$vtype]; } if ($vtype === '20') { // Boolean $cell = $cell == 'TRUE'; } } $this->spreadsheet->getActiveSheet()->getCell($column . $row)->setValueExplicit((string) $cell, $type); if (isset($cellAttributes->ValueFormat)) { $this->spreadsheet->getActiveSheet()->getCell($column . $row) ->getStyle()->getNumberFormat() ->setFormatCode((string) $cellAttributes->ValueFormat); } } }