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());
}
}
