initial commit

7 files changed, 516 insertions, 0 deletions

diff --git a/src/Plugin/CellStyleFixer.php b/src/Plugin/CellStyleFixer.php
new file mode 100644
index 0000000..cbbb4af
--- /dev/null
+++ b/src/Plugin/CellStyleFixer.php
@@ -0,0 +1,120 @@
+<?php
+
+
+namespace PHPExcel\StyleFixer\Plugin;
+
+
+use PHPExcel\StyleFixer\Util\Book as BookUtil;
+use PHPExcel\StyleFixer\Util\Sheet as SheetUtil;
+use PHPExcel\StyleFixer\Util\XmlNamespace;
+
+class CellStyleFixer implements Plugin
+{
+    /**
+     * @var \PHPExcel\StyleFixer\Util\Book
+     */
+    private $bookUtil;
+
+    /**
+     * @var \PHPExcel\StyleFixer\Util\Sheet
+     */
+    private $sheetUtil;
+
+    /**
+     * @param BookUtil $bookUtil
+     * @param SheetUtil $sheetUtil
+     */
+    public function __construct(BookUtil $bookUtil, SheetUtil $sheetUtil)
+    {
+        $this->bookUtil = $bookUtil;
+        $this->sheetUtil = $sheetUtil;
+    }
+
+    /**
+     * 各ワークシート内の各セルに付属している書式設定を修復する
+     *
+     * @param \ZipArchive $output
+     * @param \ZipArchive $template
+     */
+    public function execute(\ZipArchive $output, \ZipArchive $template)
+    {
+        // テンプレート側のシート名のマッピング
+        $srcSheetMap = $this->bookUtil->makeSheetMap($template);
+        // 出力ファイル側のシート名のマッピング
+        $distSheetMap = $this->bookUtil->makeSheetMap($output);
+        // 出力ファイル側のシートの印刷範囲マッピング
+        $distPrintAreas = $this->bookUtil->makePrintAreaMap($output);
+
+        foreach ($distSheetMap as $sheetName => $distSheetPath) {
+            $distXml = $output->getFromName($distSheetPath);
+            $distDom = new \DOMDocument;
+            $distDom->loadXML($distXml);
+            $distXPath = new \DOMXPath($distDom);
+            $distXPath->registerNamespace('s', XmlNamespace::SPREADSHEETML_NS_URL);
+
+            $srcXml = $template->getFromName($srcSheetMap[$sheetName]);
+            $srcDom = new \DOMDocument;
+            $srcDom->loadXML($srcXml);
+            $srcXPath = new \DOMXPath($srcDom);
+            $srcXPath->registerNamespace('s', XmlNamespace::SPREADSHEETML_NS_URL);
+
+            $printArea = null;
+            if (isset($distPrintAreas[$sheetName])) {
+                list(,$printArea) = explode('!', $distPrintAreas[$sheetName]);
+            }
+
+            // セル番地 => スタイル番号
+            $styleMap = [];
+            foreach ($srcXPath->query('//s:worksheet/s:sheetData/s:row') as $srcRow) {
+                /** @var \DOMElement $srcRow */
+                if ($srcRow->hasAttribute('s')) {
+                    /** @var \DOMElement $distRow */
+                    $distRow = $distXPath->query('//s:worksheet/s:sheetData/s:row[@r="'.$srcRow->getAttribute('r').'"]')->item(0);
+                    if ($distRow) {
+                        $distRow->setAttribute('s', $srcRow->getAttribute('s'));
+                    }
+                }
+
+                if ($srcRow->hasChildNodes()) {
+                    foreach ($srcRow->childNodes as $srcCell) {
+                        if ($srcCell instanceOf \DOMElement && $srcCell->tagName == 'c' && $srcCell->hasAttribute('s')) {
+                            $styleMap[$srcCell->getAttribute('r')] = $srcCell->getAttribute('s');
+                        }
+                    }
+                }
+            }
+
+            foreach ($distXPath->query('//s:worksheet/s:sheetData/s:row/s:c') as $distCell) {
+                /** @var \DOMElement $distCell */
+                $coordinate = $distCell->getAttribute('r');
+                if (isset($styleMap[$coordinate]) && ($this->cellHasValue($distCell) || !$printArea || $this->sheetUtil->inRange($coordinate, $printArea))) {
+                    $distCell->setAttribute('s', $styleMap[$coordinate]);
+                } else {
+                    $distCell->removeAttribute('s');
+                }
+            }
+
+            $output->addFromString($distSheetPath, $distDom->saveXML());
+        }
+    }
+
+    /**
+     * @param \DOMElement $cell
+     * @return bool
+     */
+    private function cellHasValue(\DOMElement $cell)
+    {
+        $hasValue = false;
+
+        if ($cell->hasChildNodes()) {
+            foreach ($cell->childNodes as $child) {
+                if ($child instanceOf \DOMElement && $child->tagName == 'v') {
+                    $hasValue = true;
+                    break;
+                }
+            }
+        }
+
+        return $hasValue;
+    }
+}
diff --git a/src/Plugin/ConditionalFormatFixer.php b/src/Plugin/ConditionalFormatFixer.php
new file mode 100644
index 0000000..2c6638b
--- /dev/null
+++ b/src/Plugin/ConditionalFormatFixer.php
@@ -0,0 +1,77 @@
+<?php
+
+namespace PHPExcel\StyleFixer\Plugin;
+
+use PHPExcel\StyleFixer\Util\Book as BookUtil;
+use PHPExcel\StyleFixer\Util\XmlNamespace;
+
+/**
+ * Class ConditionalFormatFixer
+ * PHPExcelが壊した条件付き書式を修復する
+ */
+class ConditionalFormatFixer implements Plugin
+{
+    /**
+     * @var \PHPExcel\StyleFixer\Util\Book
+     */
+    private $bookUtil;
+
+    public function __construct(BookUtil $bookUtil)
+    {
+        $this->bookUtil = $bookUtil;
+    }
+
+    /**
+     * 各ワークシート内の条件付き書式設定を修復する
+     *
+     * @param \ZipArchive $output
+     * @param \ZipArchive $template
+     */
+    public function execute(\ZipArchive $output, \ZipArchive $template)
+    {
+        // テンプレート側のシート名のマッピング
+        $srcSheetMap = $this->bookUtil->makeSheetMap($template);
+        // 出力ファイル側のシート名のマッピング
+        $distSheetMap = $this->bookUtil->makeSheetMap($output);
+
+        foreach ($distSheetMap as $sheetName => $distSheetPath) {
+            $distXml = $output->getFromName($distSheetPath);
+            if (false !== strpos($distXml, 'conditionalFormatting')) {
+                $distDom = new \DOMDocument;
+                $distDom->loadXML($distXml);
+                $distXPath = new \DOMXPath($distDom);
+                $distXPath->registerNamespace('s', XmlNamespace::SPREADSHEETML_NS_URL);
+                $distRoot = $distXPath->query('//s:worksheet')->item(0);
+                $distConditionalFormattings = $distXPath->query('//s:worksheet/s:conditionalFormatting');
+                $elementAfterConditionalFormatting = null;
+                foreach ($distConditionalFormattings as $distConditionalFormatting) {
+                    $elementAfterConditionalFormatting = $distConditionalFormatting->nextSibling;
+                    $distRoot->removeChild($distConditionalFormatting);
+                }
+
+                while ($elementAfterConditionalFormatting instanceOf \DOMNode && !$elementAfterConditionalFormatting instanceOf \DOMElement) {
+                    $elementAfterConditionalFormatting = $elementAfterConditionalFormatting->nextSibling;
+                }
+                if (!$elementAfterConditionalFormatting instanceOf \DOMElement) {
+                    break;
+                }
+
+                $srcSheetPath = $srcSheetMap[$sheetName];
+                $srcXml = $template->getFromName($srcSheetPath);
+                $srcDom = new \DOMDocument;
+                $srcDom->loadXML($srcXml);
+                $srcXPath = new \DOMXPath($srcDom);
+                $srcXPath->registerNamespace('s', XmlNamespace::SPREADSHEETML_NS_URL);
+                
+                $conditionalFormattings = $srcXPath->query('//s:worksheet/s:conditionalFormatting');
+                foreach ($conditionalFormattings as $conditionalFormatting) {
+                    /** @var \DOMElement $conditionalFormatting */
+                    $newDistConditionalFormatting = $distDom->importNode($conditionalFormatting, true);
+                    $distRoot->insertBefore($newDistConditionalFormatting, $elementAfterConditionalFormatting);
+                }
+
+                $output->addFromString($distSheetPath, $distDom->saveXML());
+            }
+        }
+    }
+}
diff --git a/src/Plugin/Plugin.php b/src/Plugin/Plugin.php
new file mode 100644
index 0000000..8229040
--- /dev/null
+++ b/src/Plugin/Plugin.php
@@ -0,0 +1,10 @@
+<?php
+
+
+namespace PHPExcel\StyleFixer\Plugin;
+
+
+interface Plugin
+{
+    public function execute(\ZipArchive $output, \ZipArchive $template);
+}
diff --git a/src/StyleFixer.php b/src/StyleFixer.php
new file mode 100644
index 0000000..2e4457a
--- /dev/null
+++ b/src/StyleFixer.php
@@ -0,0 +1,77 @@
+<?php
+
+namespace PHPExcel\StyleFixer;
+
+use PHPExcel\StyleFixer\Plugin\Plugin;
+
+/**
+ * Class StyleFixer
+ * PHPExcelが壊した書式設定を修復（テンプレートからコピー）する
+ */
+class StyleFixer
+{
+    const STYLES_XML_PATH = 'xl/styles.xml';
+
+    /**
+     * @var Plugin[]
+     */
+    private $plugins;
+
+    public function __construct(array $plugins = null)
+    {
+        $this->plugins = $plugins;
+    }
+    
+    /**
+     * @param string $outputPath
+     * @param string $templatePath
+     */
+    public function execute($outputPath, $templatePath)
+    {
+        $output = $this->openFile($outputPath);
+        $template = $this->openFile($templatePath);
+
+        // スタイル定義を修復
+        $this->fixStyles($output, $template);
+        $output->close();
+
+        // 個別のシートを修復
+        if (null !== $this->plugins) {
+            foreach ($this->plugins as $fixer) {
+
+                $output = $this->openFile($outputPath);
+
+                $fixer->execute($output, $template);
+
+                $output->close();
+            }
+        }
+
+        $template->close();
+    }
+
+    /**
+     * @param string $path
+     * @return \ZipArchive
+     */
+    protected function openFile($path)
+    {
+        $zip = new \ZipArchive;
+        $zip->open($path);
+        
+        return $zip;
+    }
+
+    /**
+     * xl/styles.xml全体を修復（テンプレートからコピー）する
+     *
+     * @param \ZipArchive $output
+     * @param \ZipArchive $template
+     */
+    private function fixStyles(\ZipArchive $output, \ZipArchive $template)
+    {
+        $srcStylesXml = $template->getFromName(self::STYLES_XML_PATH);
+
+        $output->addFromString(self::STYLES_XML_PATH, $srcStylesXml);
+    }
+}
diff --git a/src/Util/Book.php b/src/Util/Book.php
new file mode 100644
index 0000000..ac8d557
--- /dev/null
+++ b/src/Util/Book.php
@@ -0,0 +1,146 @@
+<?php
+
+
+namespace PHPExcel\StyleFixer\Util;
+
+
+class Book
+{
+    /**
+     * 人間可読なシート名 => シートのリレーションファイルのパス の連想配列を作る
+     *
+     * @param \ZipArchive $zip
+     * @return array
+     */
+    public function makeSheetRelationMap(\ZipArchive $zip)
+    {
+        $sheetMap = $this->makeSheetMap($zip);
+
+        array_walk($sheetMap, function(&$xmlPath){
+            $xmlPath = str_replace('worksheets/', 'worksheets/_rels/', $xmlPath).'.rels';
+        });
+
+        return $sheetMap;
+    }
+
+    /**
+     * 人間可読なシート名 => シートファイルのパス の連想配列を作る
+     *
+     * @param \ZipArchive $zip
+     * @return array
+     */
+    public function makeSheetMap(\ZipArchive $zip)
+    {
+        $relXml = $zip->getFromName('xl/_rels/workbook.xml.rels');
+
+        $relDom = new \DOMDocument;
+        $relDom->loadXml($relXml);
+        $relXPath = new \DOMXPath($relDom);
+        $relXPath->registerNamespace('r', XmlNamespace::RELATIONSHIPS_NS_URL);
+
+        $sheetRelationIdMaps = $this->makeSheetRelationIdMap($zip);
+
+        $map = [];
+        foreach ($sheetRelationIdMaps as $sheetName => $relId) {
+            /** @var null|\DOMElement $relEntry */
+            $relEntry = $relXPath->query('//r:Relationships/r:Relationship[@Id="'.$relId.'"]')->item(0);
+            if ($relEntry) {
+                $map[$sheetName] = 'xl/'.$relEntry->getAttribute('Target');
+            }
+        }
+
+        return $map;
+    }
+
+    /**
+     * 人間可読なシート名 => relationship id の連想配列を作る
+     * @param \ZipArchive $zip
+     * @return array
+     */
+    private function makeSheetRelationIdMap(\ZipArchive $zip)
+    {
+        $xml = $zip->getFromName('xl/workbook.xml');
+
+        $dom = new \DOMDocument;
+        $dom->loadXML($xml);
+        $xpath = new \DOMXPath($dom);
+        $xpath->registerNamespace('s', XmlNamespace::SPREADSHEETML_NS_URL);
+
+        $map = [];
+        foreach ($xpath->query('//s:workbook/s:sheets/s:sheet') as $sheet) {
+            /** @var \DOMElement $sheet */
+            $map[$sheet->getAttribute('name')] = $sheet->getAttribute('r:id');
+        }
+
+        return $map;
+    }
+
+    /**
+     * 人間可読なシート名 => 印刷範囲 の連想配列を作る
+     *
+     * @param \ZipArchive $zip
+     * @return array
+     */
+    public function makePrintAreaMap(\ZipArchive $zip)
+    {
+        $xml = $zip->getFromName('xl/workbook.xml');
+
+        $dom = new \DOMDocument;
+        $dom->loadXML($xml);
+        $xpath = new \DOMXPath($dom);
+        $xpath->registerNamespace('s', XmlNamespace::SPREADSHEETML_NS_URL);
+
+        $sheets = $xpath->query('//s:workbook/s:sheets/s:sheet');
+        $printAreas = $xpath->query('//s:workbook/s:definedNames/s:definedName[@name="_xlnm.Print_Area"]');
+        $map = [];
+        $localSheetId = 0;
+        foreach ($sheets as $sheet) {
+            /** @var \DOMElement $definedName */
+            $definedName = $printAreas->item($localSheetId);
+            if ($definedName) {
+                /** @var \DOMElement $sheet */
+                $map[$sheet->getAttribute('name')] = $definedName->nodeValue;
+            }
+            $localSheetId++;
+        }
+
+        return $map;
+    }
+
+    /**
+     * 人間可読なシート名 => drawing.xmlファイルの配列　の連想配列を作る
+     *
+     * @param \ZipArchive $zip
+     * @return array
+     */
+    public function makeDrawingMap(\ZipArchive $zip)
+    {
+        $map = [];
+
+        $sheetRelationMap = $this->makeSheetRelationMap($zip);
+        foreach ($sheetRelationMap as $sheetName => $sheetRelFile) {
+            $drawings = [];
+
+            $relXml = $zip->getFromName($sheetRelFile);
+            $dom = new \DOMDocument();
+            $dom->loadXML($relXml);
+            $xpath = new \DOMXPath($dom);
+            $xpath->registerNamespace('r', XmlNamespace::RELATIONSHIPS_NS_URL);
+
+            $drawingFiles = $xpath->query('//r:Relationships/r:Relationship[@Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing"]');
+            foreach ($drawingFiles as $drawingFile) {
+                /** @var \DOMElement $drawingFile */
+                $drawings[] = str_replace('../', 'xl/', $drawingFile->getAttribute('Target'));
+            }
+
+            $xpath = null;
+            $dom = null;
+
+            if (0 !== count($drawings)) {
+                $map[$sheetName] = $drawings;
+            }
+        }
+
+        return $map;
+    }
+}
diff --git a/src/Util/Sheet.php b/src/Util/Sheet.php
new file mode 100644
index 0000000..6e11e1d
--- /dev/null
+++ b/src/Util/Sheet.php
@@ -0,0 +1,70 @@
+<?php
+
+
+namespace PHPExcel\StyleFixer\Util;
+
+
+class Sheet
+{
+
+    /**
+     * セル番地がExcel形式のセル範囲(コロン区切りのみ対応)に含まれるかどうか
+     *
+     * @param string $coordinate
+     * @param string $cellRange
+     * @return bool
+     */
+    public function inRange($coordinate, $cellRange)
+    {
+        // 絶対参照の$を削除
+        $cellRange = str_replace('$', '', $cellRange);
+        list($startCell, $endCell) = explode(':', $cellRange);
+
+        // 行の範囲チェック
+        $rowNumber = $this->detectRowNumberFromCoordinate($coordinate);
+        if ($rowNumber < $this->detectRowNumberFromCoordinate($startCell) || $rowNumber > $this->detectRowNumberFromCoordinate($endCell)) {
+            return false;
+        }
+
+        // 列の範囲チェック
+        $colNumber = $this->detectColNumberFromCoordinate($coordinate);
+        if ($colNumber < $this->detectColNumberFromCoordinate($startCell) || $colNumber > $this->detectColNumberFromCoordinate($endCell)) {
+            return false;
+        }
+
+        return true;
+    }
+
+    /**
+     * セル番地から行番号を取り出す
+     *
+     * @param string $coordinate
+     * @return int
+     */
+    private function detectRowNumberFromCoordinate($coordinate)
+    {
+        return intval(preg_replace('/[A-Z]+/', '', $coordinate));
+    }
+
+    /**
+     * セル番地から列番号を取り出す
+     *
+     * @param string $coordinate
+     * @return int
+     */
+    private function detectColNumberFromCoordinate($coordinate)
+    {
+        $char = preg_replace('/\d+/', '', $coordinate);
+
+        $charA = 65;
+
+        if (strlen($char) == 1) {
+            return ord($char) - $charA + 1;
+        }
+
+        $quot = ord($char[0]) - $charA + 1;
+        $mod = ord($char[1]) - $charA + 1;
+
+        return $quot * 26 + $mod;
+    }
+}
diff --git a/src/Util/XmlNamespace.php b/src/Util/XmlNamespace.php
new file mode 100644
index 0000000..581510e
--- /dev/null
+++ b/src/Util/XmlNamespace.php
@@ -0,0 +1,16 @@
+<?php
+
+
+namespace PHPExcel\StyleFixer\Util;
+
+
+class XmlNamespace
+{
+    const SPREADSHEETML_NS_URL = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
+    const RELATIONSHIPS_NS_URL = 'http://schemas.openxmlformats.org/package/2006/relationships';
+
+    // drawing関係
+    const SPREADSHEETDRAWING_NS_URL = 'http://schemas.openxmlformats.org/drawingml/2006/spreadsheet';
+    const DRAWINGML_NS_URL = 'http://schemas.openxmlformats.org/drawingml/2006/main';
+    const DRAWINGML_CHART_NS_URL = 'http://schemas.openxmlformats.org/drawingml/2006/chart';
+}