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()); }
public function setSelection(ZipArchive $xlsx) { $sheets = $this->bookUtil->makeSheetMap($xlsx); foreach ($sheets as $sheetName) { $this->cellSetter->set($xlsx, $sheetName, $this->firstCell); } $firstSheet = reset($sheets); $this->sheetSetter->set($xlsx, $firstSheet); }
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()); } }
/** * @param ZipArchive $xlsx * @param ZipArchive|null $template */ public function setSelection(ZipArchive $xlsx, ZipArchive $template = null) { if (!$template instanceof ZipArchive) { throw new \RuntimeException('Invalid template'); } // テンプレートで選択されているシートを取得 $selectedSheet = $this->sheetReader->read($template); // テンプレートの各シートで選択されているセルを取得 $sheets = $this->bookUtil->makeSheetMap($template); $selectedCells = []; foreach ($sheets as $sheetId => $sheetName) { $selectedCells[$sheetName] = $this->cellReader->read($template, $sheetName); } // 選択シートを書き込む $this->sheetSetter->set($xlsx, $selectedSheet); // 各シートの選択セルを書き込む foreach ($selectedCells as $sheetName => $cellPosition) { $this->cellSetter->set($xlsx, $sheetName, $cellPosition); } }
/** * 選択されているシート名を返す * @inheritdoc */ public function read(ZipArchive $xlsx) { $worksheetXml = $xlsx->getFromName('xl/workbook.xml'); if (!$worksheetXml) { throw new \RuntimeException('Could not find workbook.xml'); } $sheets = $this->bookUtil->makeSheetMap($xlsx); $dom = new \DOMDocument(); $dom->loadXML($worksheetXml); $xpath = new \DOMXPath($dom); $xpath->registerNamespace('s', XmlNamespace::SPREADSHEETML_NS_URL); // 選択されているシートIDを調べる $activeSheetId = null; $views = $xpath->query('//s:workbook/s:bookViews/s:workbookView'); if ($views->length == 1) { /** @var \DOMElement $workbookView */ $workbookView = $views->item(0); $activeSheetId = $workbookView->getAttribute('activeTab'); } return $activeSheetId && isset($sheets[$activeSheetId]) ? $sheets[$activeSheetId] : reset($sheets); }