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