public function read(ZipArchive $xlsx, $targetSheetName = null) { // $targetSheetNameのシートxmlファイルを取得 $sheetFileMap = $this->bookUtil->makeSheetFileMap($xlsx); if (!isset($sheetFileMap[$targetSheetName])) { throw new \RuntimeException(sprintf('Worksheet "%s" not found', $targetSheetName)); } $worksheetXml = $xlsx->getFromName($sheetFileMap[$targetSheetName]); if (!$worksheetXml) { throw new \RuntimeException(sprintf('Worksheet "%s" is empty or broken.', $targetSheetName)); } // 選択されているセルの番地を調べる $dom = new \DOMDocument(); $dom->loadXML($worksheetXml); $xpath = new \DOMXPath($dom); $xpath->registerNamespace('s', XmlNamespace::SPREADSHEETML_NS_URL); $activeCell = null; $selections = $xpath->query('//s:worksheet/s:sheetViews/s:sheetView/s:selection'); if ($selections->length == 1) { /** @var \DOMElement $selection */ $selection = $selections->item(0); $activeCell = $selection->getAttribute('activeCell'); } return $activeCell ?: 'A1'; }
/** * @param ZipArchive $xlsx * @param string $targetSheetName * @param string $targetCell */ public function set(ZipArchive $xlsx, $targetSheetName = null, $targetCell = null) { // $targetSheetNameのシートxmlファイルを取得 $sheetFileMap = $this->bookUtil->makeSheetFileMap($xlsx); if (!isset($sheetFileMap[$targetSheetName])) { throw new \RuntimeException(sprintf('Worksheet "%s" not found', $targetSheetName)); } $path = $sheetFileMap[$targetSheetName]; $worksheetXml = $xlsx->getFromName($path); if (!$worksheetXml) { throw new \RuntimeException(sprintf('Worksheet "%s" is empty or broken.', $targetSheetName)); } // selectionがあればactiveCellセット、なければselectionを作ってからactiveCellセット $dom = new \DOMDocument(); $dom->loadXML($worksheetXml); $xpath = new \DOMXPath($dom); $xpath->registerNamespace('s', XmlNamespace::SPREADSHEETML_NS_URL); $selections = $xpath->query('//s:worksheet/s:sheetViews/s:sheetView/s:selection'); if ($selections->length == 1) { /** @var \DOMElement $selection */ $selection = $selections->item(0); $selection->setAttribute('activeCell', $targetCell); $selection->setAttribute('sqref', $targetCell); } else { $selection = $dom->createElement('sheetView'); $selection->setAttribute('activeCell', $targetCell); $selection->setAttribute('sqref', $targetCell); $sheetView = $xpath->query('//s:worksheet/s:sheetViews/s:sheetView')->item(0); $sheetView->appendChild($selection); } // zipに書き込む $xlsx->addFromString($path, $dom->saveXML()); }
private function setInWorksheet(ZipArchive $xlsx, $targetSheetName) { $sheetFileMap = $this->bookUtil->makeSheetFileMap($xlsx); foreach ($sheetFileMap as $sheetName => $sheetFile) { $worksheetXml = $xlsx->getFromName($sheetFile); $tabSelected = $sheetName == $targetSheetName ? 1 : 0; $dom = new \DOMDocument(); $dom->loadXML($worksheetXml); $xpath = new \DOMXPath($dom); $xpath->registerNamespace('s', XmlNamespace::SPREADSHEETML_NS_URL); /** @var \DOMElement $sheetView */ $sheetView = $xpath->query('//s:worksheet/s:sheetViews/s:sheetView')->item(0); $sheetView->setAttribute('tabSelected', $tabSelected); $xlsx->addFromString($sheetFile, $dom->saveXML()); } }