ExcelWriter::__destruct() - Code Metrics - Inspection of "add excel helper and refactor" - ellumilel/php-excel-writer - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 44810d...9c6bac )

by Denis

created 2016-09-02 07:32 UTC

ExcelWriter::__destruct() A

↳ Parent: ExcelWriter

Complexity

Conditions	4
Paths	4

Size

Total Lines	10
Code Lines	5

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	0	Features	1

Metric	Value
c	2
b	0
f	1
dl	0
loc	10
rs	9.2
cc	4
eloc	5
nc	4
nop	0

<?php
namespace Ellumilel;

use Ellumilel\DocProps\App;
use Ellumilel\DocProps\Core;
use Ellumilel\Helpers\ExcelHelper;
use Ellumilel\Rels\Relationships;
use Ellumilel\Xl\SharedStrings;
use Ellumilel\Xl\Styles;
use Ellumilel\Xl\Workbook;
use Ellumilel\Xl\Worksheets\SheetXml;

/**
 * Class ExcelWriter
 * @package Ellumilel
 * @author Denis Tikhonov <[email protected]>
 */
class ExcelWriter
{
    /** @var array */
    protected $sheets = [];
    /** @var array */
    protected $sharedStrings = [];
    /** @var int */
    protected $sharedStringCount = 0;
    /** @var array */
    protected $tempFiles = [];
    /** @var array */
    protected $cellFormats = [];
    /** @var array */
    protected $cellTypes = [];
    /** @var string  */
    protected $currentSheet = '';
    /** @var null */
    protected $tmpDir = null;
    /** @var Core */
    protected $core;
    /** @var App */
    protected $app;
    /** @var Workbook */
    protected $workbook;
    /** @var SheetXml */
    protected $sheetXml;

    /**
     * ExcelWriter constructor.
     * @throws \Exception
     */
    public function __construct()
    {
        if (!class_exists('ZipArchive')) {
            throw new \Exception('ZipArchive not found');
        }
        if (!ini_get('date.timezone')) {
            date_default_timezone_set('UTC');
        }
        $this->addCellFormat($cell_format = 'GENERAL');
        $this->core = new Core();
        $this->app = new App();
        $this->workbook = new Workbook();
        $this->sheetXml = new SheetXml();
    }

    /**
     * @param string $author
     */
    public function setAuthor($author)
    {
        $this->core->setAuthor($author);
    }

    public function __destruct()
    {
        if (!empty($this->tempFiles)) {
            foreach ($this->tempFiles as $tempFile) {
                if (file_exists($tempFile)) {
                    unlink($tempFile);
                }
            }
        }
    }

    /**
     * @param $dir
     */
    public function setTmpDir($dir)
    {
        $this->tmpDir = $dir;
    }

    /**
     * Return tmpFileName
     * @return string
     */
    protected function tempFilename()
    {
        $tmpDir = is_null($this->tmpDir) ? sys_get_temp_dir() : $this->tmpDir;
        $filename = tempnam($tmpDir, "excelWriter_");
        $this->tempFiles[] = $filename;

        return $filename;
    }

    public function writeToStdOut()
    {
        $tempFile = $this->tempFilename();
        $this->writeToFile($tempFile);
        readfile($tempFile);
    }

    /**
     * @return string
     */
    public function writeToString()
    {
        $tempFile = $this->tempFilename();
        $this->writeToFile($tempFile);
        $string = file_get_contents($tempFile);

        return $string;
    }

    /**
     * @param string $filename
     */
    public function writeToFile($filename)
    {
        $zip = new \ZipArchive();
        foreach ($this->sheets as $sheetName => $sheet) {
            $this->finalizeSheet($sheetName);
        }
        $this->checkAndUnlink($zip, $filename);
        $this->workbook->setSheet($this->sheets);

        $contentTypes = new ContentTypes(!empty($this->sharedStrings));
        $contentTypes->setSheet($this->sheets);

        $rel = new Relationships(!empty($this->sharedStrings));
        $rel->setSheet($this->sheets);
        $zip->addEmptyDir("docProps/");
        $zip->addFromString("docProps/app.xml", $this->app->buildAppXML());
        $zip->addFromString("docProps/core.xml", $this->core->buildCoreXML());
        $zip->addEmptyDir("_rels/");
        $zip->addFromString("_rels/.rels", $rel->buildRelationshipsXML());
        $zip->addEmptyDir("xl/worksheets/");
        foreach ($this->sheets as $sheet) {
            /** @var Sheet $sheet */
            $zip->addFile($sheet->getFilename(), "xl/worksheets/".$sheet->getXmlName());
        }
        if (!empty($this->sharedStrings)) {
            $zip->addFile(
                $this->writeSharedStringsXML(),
                "xl/sharedStrings.xml"
            );
        }
        $zip->addFromString("xl/workbook.xml", $this->workbook->buildWorkbookXML());
        $zip->addFile(
            $this->writeStylesXML(),
            "xl/styles.xml"
        );
        $zip->addFromString("[Content_Types].xml", $contentTypes->buildContentTypesXML());
        $zip->addEmptyDir("xl/_rels/");
        $zip->addFromString("xl/_rels/workbook.xml.rels", $rel->buildWorkbookRelationshipsXML());
        $zip->close();
    }

    /**
     * @param string $sheetName
     */
    protected function initializeSheet($sheetName)
    {
        if ($this->currentSheet == $sheetName || isset($this->sheets[$sheetName])) {
            return;
        }
        $sheetFilename = $this->tempFilename();
        $sheetXmlName = 'sheet' . (count($this->sheets) + 1).".xml";
        $sheetObj = new Sheet();
        $sheetObj
            ->setFilename($sheetFilename)
            ->setSheetName($sheetName)
            ->setXmlName($sheetXmlName)
            ->setWriter(new Writer($sheetFilename))
        ;
        $this->sheets[$sheetName] = $sheetObj;
        /** @var Sheet $sheet */
        $sheet = &$this->sheets[$sheetName];
        $selectedTab = count($this->sheets) == 1 ? 'true' : 'false';
        $maxCell = ExcelHelper::xlsCell(ExcelHelper::EXCEL_MAX_ROW, ExcelHelper::EXCEL_MAX_COL);
        $sheet->getWriter()->write($this->sheetXml->getXml());
        $sheet->getWriter()->write($this->sheetXml->getWorksheet());
        $sheet->getWriter()->write($this->sheetXml->getSheetPr());
        $sheet->setMaxCellTagStart($sheet->getWriter()->fTell());
        $sheet->getWriter()->write($this->sheetXml->getDimension($maxCell));
        $sheet->setMaxCellTagEnd($sheet->getWriter()->fTell());
        $sheet->getWriter()->write($this->sheetXml->getSheetViews($selectedTab));
        $sheet->getWriter()->write($this->sheetXml->getCools());
        $sheet->getWriter()->write('<sheetData>');
    }

    /**
     * @param $cellFormat
     *
     * @return string
     */
    private function determineCellType($cellFormat)
    {
        $cellFormat = str_replace("[RED]", "", $cellFormat);
        if ($cellFormat == 'GENERAL') {
            return 'string';
        }
        if ($cellFormat == '0') {
            return 'numeric';
        }
        $checkArray = [
            'datetime' => [
                "/[H]{1,2}:[M]{1,2}/",
                "/[M]{1,2}:[S]{1,2}/",
            ],
            'numeric' => [
                "/0/",
            ],
            'date' => [
                "/[YY]{2,4}/",
                "/[D]{1,2}/",
                "/[M]{1,2}/",
            ],
            'currency' => [
                "/$/",
            ],
            'percent' => [
                "/%/",
            ],
        ];
        foreach ($checkArray as $type => $item) {
            foreach ($item as $prMatch) {
                if (preg_match($prMatch, $cellFormat)) {
                    return $type;
                }
            }
        }

        return 'string';
    }

    /**
     * @todo  check escaping
     *
     * @param $cellFormat
     *
     * @return string
     */
    private function escapeCellFormat($cellFormat)
    {
        $ignoreUntil = '';
        $escaped = '';
        for ($i = 0, $ix = strlen($cellFormat); $i < $ix; $i++) {
            $c = $cellFormat[$i];
            if ($ignoreUntil == '' && $c == '[') {
                $ignoreUntil = ']';
            } else {
                if ($ignoreUntil == '' && $c == '"') {
                    $ignoreUntil = '"';
                } else {
                    if ($ignoreUntil == $c) {
                        $ignoreUntil = '';
                    }
                }
            }
            if ($ignoreUntil == '' &&
                ($c == ' ' || $c == '-' || $c == '(' || $c == ')') &&
                ($i == 0 || $cellFormat[$i - 1] != '_')
            ) {
                $escaped .= "\\".$c;
            } else {
                $escaped .= $c;
            }
        }

        return $escaped;
    }

    /**
     * backwards compatibility
     *
     * @param $cellFormat
     *
     * @return int|mixed
     */
    private function addCellFormat($cellFormat)
    {


        $cellFormat = strtoupper($this->getCellFormat($cellFormat));
        $position = array_search($cellFormat, $this->cellFormats, $strict = true);
        if ($position === false) {
            $position = count($this->cellFormats);
            $this->cellFormats[] = $this->escapeCellFormat($cellFormat);
            $this->cellTypes[] = $this->determineCellType($cellFormat);
        }

        return $position;
    }

    /**
     * @param string $cellFormat
     *
     * @return string
     */
    private function getCellFormat($cellFormat)
    {
        $formatArray = [
            'string' => 'GENERAL',
            'integer' => '0',
            'date' => 'YYYY-MM-DD',
            'datetime' => 'YYYY-MM-DD HH:MM:SS',
            'dollar' => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',
            'money' => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',
            'euro' => '#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]',
            'NN' => 'DDD',
            'NNN' => 'DDDD',
            'NNNN' => 'DDDD", "',
        ];

        if (array_key_exists($cellFormat, $formatArray)) {
            return $formatArray[$cellFormat];
        }
        return $cellFormat;
    }

    /**
     * @param string $sheetName
     * @param array $headerTypes
     * @param bool $suppressRow
     */
    public function writeSheetHeader($sheetName, array $headerTypes, $suppressRow = false)
    {
        if (empty($sheetName) || empty($headerTypes) || !empty($this->sheets[$sheetName])) {
            return;
        }
        $this->initializeSheet($sheetName);
        /** @var Sheet $sheet */
        $sheet = &$this->sheets[$sheetName];
        $sheet->setColumns([]);
        foreach ($headerTypes as $val) {
            $sheet->setColumn($this->addCellFormat($val));
        }
        if (!$suppressRow) {
            $this->writeRowHeader($sheet, array_keys($headerTypes));
            $sheet->increaseRowCount();
        }
        $this->currentSheet = $sheetName;
    }

    /**
     * @param Sheet $sheet
     * @param array $headerRow
     */
    private function writeRowHeader(Sheet $sheet, $headerRow)
    {
        $sheet->getWriter()->write(
            '<row collapsed="false" customFormat="false" 
                customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="'.(1).'">'
        );
        foreach ($headerRow as $k => $v) {
            $this->writeCell($sheet->getWriter(), 0, $k, $v, $cell_format_index = '0');
        }
        $sheet->getWriter()->write('</row>');
    }

    /**
     * @param string $sheetName
     * @param array $row
     */
    public function writeSheetRow($sheetName, array $row)
    {
        if (empty($sheetName) || empty($row)) {
            return;
        }
        $this->initializeSheet($sheetName);
        /** @var Sheet $sheet */
        $sheet = &$this->sheets[$sheetName];
        $columns = $sheet->getColumns();
        if (empty($columns)) {
            $sheet->setColumns(array_fill($from = 0, $until = count($row), '0'));
        }
        $sheet->getWriter()->write(
            '<row collapsed="false" customFormat="false" customHeight="false" 
            hidden="false" ht="12.1" outlineLevel="0" r="'.($sheet->getRowCount() + 1).'">'
        );
        $column_count = 0;
        $sheetColumns = $sheet->getColumns();
        foreach ($row as $k => $v) {
            $this->writeCell(
                $sheet->getWriter(),
                $sheet->getRowCount(),
                $column_count,
                $v,
                $sheetColumns[$column_count]
            );
            $column_count++;
        }
        $sheet->getWriter()->write('</row>');
        $sheet->increaseRowCount();
        $this->currentSheet = $sheetName;
    }

    /**
     * @param string $sheetName
     */
    protected function finalizeSheet($sheetName)
    {
        if (empty($sheetName) || $this->sheets[$sheetName]->getFinalized()) {
            return;
        }
        /** @var Sheet $sheet */
        $sheet = &$this->sheets[$sheetName];
        $sheet->getWriter()->write('</sheetData>');
        $mergeCells = $sheet->getMergeCells();
        if (!empty($mergeCells)) {
            $sheet->getWriter()->write($this->sheetXml->getMergeCells($mergeCells));
        }
        $sheet->getWriter()->write($this->sheetXml->getPrintOptions());
        $sheet->getWriter()->write($this->sheetXml->getPageMargins());
        $sheet->getWriter()->write($this->sheetXml->getPageSetup());
        $sheet->getWriter()->write($this->sheetXml->getHeaderFooter());
        $sheet->getWriter()->write('</worksheet>');
        $maxCell = ExcelHelper::xlsCell($sheet->getRowCount() - 1, count($sheet->getColumns()) - 1);
        $maxCellTag = $this->sheetXml->getDimension($maxCell);
        $paddingLength = $sheet->getMaxCellTagEnd() - $sheet->getMaxCellTagStart() - strlen($maxCellTag);
        $sheet->getWriter()->fSeek($sheet->getMaxCellTagStart());
        $sheet->getWriter()->write($maxCellTag.str_repeat(" ", $paddingLength));
        $sheet->getWriter()->close();
        $sheet->setFinalized(true);
    }

    /**
     * @param string $sheetName
     * @param int $startCellRow
     * @param int $startCellColumn
     * @param int $endCellRow
     * @param int $endCellColumn
     */
    public function markMergedCell($sheetName, $startCellRow, $startCellColumn, $endCellRow, $endCellColumn)
    {
        if (empty($sheetName) || $this->sheets[$sheetName]->getFinalized()) {
            return;
        }
        $this->initializeSheet($sheetName);
        /** @var Sheet $sheet */
        $sheet = &$this->sheets[$sheetName];
        $startCell = ExcelHelper::xlsCell($startCellRow, $startCellColumn);
        $endCell = ExcelHelper::xlsCell($endCellRow, $endCellColumn);
        $sheet->setMergeCells($startCell.":".$endCell);
    }

    /**
     * @param array $data
     * @param string $sheetName
     * @param array $headerTypes
     */
    public function writeSheet(array $data, $sheetName = '', array $headerTypes = [])
    {
        $sheetName = empty($sheetName) ? 'Sheet1' : $sheetName;
        $data = empty($data) ? [['']] : $data;
        if (!empty($headerTypes)) {
            $this->writeSheetHeader($sheetName, $headerTypes);
        }
        foreach ($data as $i => $row) {
            $this->writeSheetRow($sheetName, $row);
        }
        $this->finalizeSheet($sheetName);
    }

    /**
     * @param Writer $file
     * @param $rowNumber
     * @param $columnNumber
     * @param $value
     * @param $cellIndex
     */
    protected function writeCell(
        Writer $file,
        $rowNumber,
        $columnNumber,
        $value,
        $cellIndex
    ) {
        $cellType = $this->cellTypes[$cellIndex];
        $cellName = ExcelHelper::xlsCell($rowNumber, $columnNumber);
        if (!is_scalar($value) || $value === '') {
            $file->write('<c r="'.$cellName.'" s="'.$cellIndex.'"/>');
        } elseif (is_string($value) && $value{0} == '=') {
            $file->write(
                sprintf(
                    '<c r="%s" s="%s" t="s"><f>%s</f></c>',
                    $cellName,
                    $cellIndex,
                    ExcelHelper::xmlspecialchars($value)
                )
            );
        } elseif ($cellType == 'date') {
            $file->write(
                sprintf(
                    '<c r="%s" s="%s" t="n"><v>%s</v></c>',
                    $cellName,
                    $cellIndex,
                    ExcelHelper::convertDateTime($value)
                )
            );
        } elseif ($cellType == 'datetime') {
            $file->write(
                '<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.ExcelHelper::convertDateTime($value).'</v></c>'
            );
        } elseif ($cellType == 'currency' || $cellType == 'percent' || $cellType == 'numeric') {
            $file->write(
                '<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.ExcelHelper::xmlspecialchars($value).'</v></c>'
            );
        } else {
            if (!is_string($value)) {
                $file->write('<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.($value * 1).'</v></c>');
            } else {
                if ($value{0} != '0' && $value{0} != '+' && filter_var(
                    $value,
                    FILTER_VALIDATE_INT,
                    ['options' => ['max_range' => ExcelHelper::EXCEL_MAX_RANGE]]
                )) {
                    $file->write('<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.($value * 1).'</v></c>');
                } else {
                    $file->write(
                        '<c r="'.$cellName.'" s="'.$cellIndex.'" t="s"><v>'.ExcelHelper::xmlspecialchars(
                            $this->setSharedString($value)
                        ).'</v></c>'
                    );
                }
            }
        }
    }

    /**
     * @return string
     */
    protected function writeStylesXML()
    {
        $temporaryFilename = $this->tempFilename();
        $file = new Writer($temporaryFilename);
        $styles = new Styles();
        $styles->setCellFormats($this->cellFormats);
        $file->write($styles->buildStylesXML());

        return $temporaryFilename;
    }

    /**
     * @param $v
     *
     * @return int|mixed
     */
    protected function setSharedString($v)
    {
        if (isset($this->sharedStrings[$v])) {
            $stringValue = $this->sharedStrings[$v];
        } else {
            $stringValue = count($this->sharedStrings);
            $this->sharedStrings[$v] = $stringValue;
        }
        $this->sharedStringCount++;

        return $stringValue;
    }

    /**
     * @return string
     */
    protected function writeSharedStringsXML()
    {
        $tempFilename = $this->tempFilename();
        $file = new Writer($tempFilename);
        $sharedStrings = new SharedStrings($this->sharedStringCount, $this->sharedStrings);
        $file->write($sharedStrings->buildSharedStringsXML());
        $file->close();

        return $tempFilename;
    }

    /**
     * @param $string
     */
    public static function log($string)
    {
        file_put_contents(
            "php://stderr",
            date("Y-m-d H:i:s:").rtrim(is_array($string) ? json_encode($string) : $string)."\n"
        );
    }

    /**
     * @link https://msdn.microsoft.com/ru-RU/library/aa365247%28VS.85%29.aspx
     *
     * @param string $filename
     *
     * @return mixed
     */
    public static function checkFilename($filename)

    {
        $invalidCharacter = array_merge(
            array_map('chr', range(0, 31)),
            ['<', '>', '?', '"', ':', '|', '\\', '/', '*', '&']
        );

        return str_replace($invalidCharacter, '', $filename);
    }

    /**
     * @param \ZipArchive $zip
     * @param string $filename
     */
    private function checkAndUnlink(\ZipArchive $zip, $filename)
    {
        if (file_exists($filename) && is_writable($filename)) {
            unlink($filename);
        }
        if (empty($this->sheets) || !$zip->open($filename, \ZipArchive::CREATE)) {
            throw new \RuntimeException(
                "Error in ".__CLASS__."::".__FUNCTION__.", no worksheets defined or unable to create zip."
            );
        }
    }
}


1		<?php
2		namespace Ellumilel;
3
4		use Ellumilel\DocProps\App;
5		use Ellumilel\DocProps\Core;
6		use Ellumilel\Helpers\ExcelHelper;
7		use Ellumilel\Rels\Relationships;
8		use Ellumilel\Xl\SharedStrings;
9		use Ellumilel\Xl\Styles;
10		use Ellumilel\Xl\Workbook;
11		use Ellumilel\Xl\Worksheets\SheetXml;
12
13		/**
14		* Class ExcelWriter
15		* @package Ellumilel
16		* @author Denis Tikhonov <[email protected]>
17		*/
18		class ExcelWriter
19		{
20		/** @var array */
21		protected $sheets = [];
22		/** @var array */
23		protected $sharedStrings = [];
24		/** @var int */
25		protected $sharedStringCount = 0;
26		/** @var array */
27		protected $tempFiles = [];
28		/** @var array */
29		protected $cellFormats = [];
30		/** @var array */
31		protected $cellTypes = [];
32		/** @var string */
33		protected $currentSheet = '';
34		/** @var null */
35		protected $tmpDir = null;
36		/** @var Core */
37		protected $core;
38		/** @var App */
39		protected $app;
40		/** @var Workbook */
41		protected $workbook;
42		/** @var SheetXml */
43		protected $sheetXml;
44
45		/**
46		* ExcelWriter constructor.
47		* @throws \Exception
48		*/
49		public function __construct()
50		{
51		if (!class_exists('ZipArchive')) {
52		throw new \Exception('ZipArchive not found');
53		}
54		if (!ini_get('date.timezone')) {
55		date_default_timezone_set('UTC');
56		}
57		$this->addCellFormat($cell_format = 'GENERAL');
58		$this->core = new Core();
59		$this->app = new App();
60		$this->workbook = new Workbook();
61		$this->sheetXml = new SheetXml();
62		}
63
64		/**
65		* @param string $author
66		*/
67		public function setAuthor($author)
68		{
69		$this->core->setAuthor($author);
70		}
71
72		public function __destruct()
73		{
74		if (!empty($this->tempFiles)) {
75		foreach ($this->tempFiles as $tempFile) {
76		if (file_exists($tempFile)) {
77		unlink($tempFile);
78		}
79		}
80		}
81		}
82
83		/**
84		* @param $dir
85		*/
86		public function setTmpDir($dir)
87		{
88		$this->tmpDir = $dir;
89		}
90
91		/**
92		* Return tmpFileName
93		* @return string
94		*/
95		protected function tempFilename()
96		{
97		$tmpDir = is_null($this->tmpDir) ? sys_get_temp_dir() : $this->tmpDir;
98		$filename = tempnam($tmpDir, "excelWriter_");
99		$this->tempFiles[] = $filename;
100
101		return $filename;
102		}
103
104		public function writeToStdOut()
105		{
106		$tempFile = $this->tempFilename();
107		$this->writeToFile($tempFile);
108		readfile($tempFile);
109		}
110
111		/**
112		* @return string
113		*/
114		public function writeToString()
115		{
116		$tempFile = $this->tempFilename();
117		$this->writeToFile($tempFile);
118		$string = file_get_contents($tempFile);
119
120		return $string;
121		}
122
123		/**
124		* @param string $filename
125		*/
126		public function writeToFile($filename)
127		{
128		$zip = new \ZipArchive();
129		foreach ($this->sheets as $sheetName => $sheet) {
130		$this->finalizeSheet($sheetName);
131		}
132		$this->checkAndUnlink($zip, $filename);
133		$this->workbook->setSheet($this->sheets);
134
135		$contentTypes = new ContentTypes(!empty($this->sharedStrings));
136		$contentTypes->setSheet($this->sheets);
137
138		$rel = new Relationships(!empty($this->sharedStrings));
139		$rel->setSheet($this->sheets);
140		$zip->addEmptyDir("docProps/");
141		$zip->addFromString("docProps/app.xml", $this->app->buildAppXML());
142		$zip->addFromString("docProps/core.xml", $this->core->buildCoreXML());
143		$zip->addEmptyDir("_rels/");
144		$zip->addFromString("_rels/.rels", $rel->buildRelationshipsXML());
145		$zip->addEmptyDir("xl/worksheets/");
146		foreach ($this->sheets as $sheet) {
147		/** @var Sheet $sheet */
148		$zip->addFile($sheet->getFilename(), "xl/worksheets/".$sheet->getXmlName());
149		}
150		if (!empty($this->sharedStrings)) {
151		$zip->addFile(
152		$this->writeSharedStringsXML(),
153		"xl/sharedStrings.xml"
154		);
155		}
156		$zip->addFromString("xl/workbook.xml", $this->workbook->buildWorkbookXML());
157		$zip->addFile(
158		$this->writeStylesXML(),
159		"xl/styles.xml"
160		);
161		$zip->addFromString("[Content_Types].xml", $contentTypes->buildContentTypesXML());
162		$zip->addEmptyDir("xl/_rels/");
163		$zip->addFromString("xl/_rels/workbook.xml.rels", $rel->buildWorkbookRelationshipsXML());
164		$zip->close();
165		}
166
167		/**
168		* @param string $sheetName
169		*/
170		protected function initializeSheet($sheetName)
171		{
172		if ($this->currentSheet == $sheetName \|\| isset($this->sheets[$sheetName])) {
173		return;
174		}
175		$sheetFilename = $this->tempFilename();
176		$sheetXmlName = 'sheet' . (count($this->sheets) + 1).".xml";
177		$sheetObj = new Sheet();
178		$sheetObj
179		->setFilename($sheetFilename)
180		->setSheetName($sheetName)
181		->setXmlName($sheetXmlName)
182		->setWriter(new Writer($sheetFilename))
183		;
184		$this->sheets[$sheetName] = $sheetObj;
185		/** @var Sheet $sheet */
186		$sheet = &$this->sheets[$sheetName];
187		$selectedTab = count($this->sheets) == 1 ? 'true' : 'false';
188		$maxCell = ExcelHelper::xlsCell(ExcelHelper::EXCEL_MAX_ROW, ExcelHelper::EXCEL_MAX_COL);
189		$sheet->getWriter()->write($this->sheetXml->getXml());
190		$sheet->getWriter()->write($this->sheetXml->getWorksheet());
191		$sheet->getWriter()->write($this->sheetXml->getSheetPr());
192		$sheet->setMaxCellTagStart($sheet->getWriter()->fTell());
193		$sheet->getWriter()->write($this->sheetXml->getDimension($maxCell));
194		$sheet->setMaxCellTagEnd($sheet->getWriter()->fTell());
195		$sheet->getWriter()->write($this->sheetXml->getSheetViews($selectedTab));
196		$sheet->getWriter()->write($this->sheetXml->getCools());
197		$sheet->getWriter()->write('<sheetData>');
198		}
199
200		/**
201		* @param $cellFormat
202		*
203		* @return string
204		*/
205		private function determineCellType($cellFormat)
206		{
207		$cellFormat = str_replace("[RED]", "", $cellFormat);
208		if ($cellFormat == 'GENERAL') {
209		return 'string';
210		}
211		if ($cellFormat == '0') {
212		return 'numeric';
213		}
214		$checkArray = [
215		'datetime' => [
216		"/[H]{1,2}:[M]{1,2}/",
217		"/[M]{1,2}:[S]{1,2}/",
218		],
219		'numeric' => [
220		"/0/",
221		],
222		'date' => [
223		"/[YY]{2,4}/",
224		"/[D]{1,2}/",
225		"/[M]{1,2}/",
226		],
227		'currency' => [
228		"/$/",
229		],
230		'percent' => [
231		"/%/",
232		],
233		];
234		foreach ($checkArray as $type => $item) {
235		foreach ($item as $prMatch) {
236		if (preg_match($prMatch, $cellFormat)) {
237		return $type;
238		}
239		}
240		}
241
242		return 'string';
243		}
244
245		/**
246		* @todo check escaping
247		*
248		* @param $cellFormat
249		*
250		* @return string
251		*/
252		private function escapeCellFormat($cellFormat)
253		{
254		$ignoreUntil = '';
255		$escaped = '';
256		for ($i = 0, $ix = strlen($cellFormat); $i < $ix; $i++) {
257		$c = $cellFormat[$i];
258		if ($ignoreUntil == '' && $c == '[') {
259		$ignoreUntil = ']';
260		} else {
261		if ($ignoreUntil == '' && $c == '"') {
262		$ignoreUntil = '"';
263		} else {
264		if ($ignoreUntil == $c) {
265		$ignoreUntil = '';
266		}
267		}
268		}
269		if ($ignoreUntil == '' &&
270		($c == ' ' \|\| $c == '-' \|\| $c == '(' \|\| $c == ')') &&
271		($i == 0 \|\| $cellFormat[$i - 1] != '_')
272		) {
273		$escaped .= "\\".$c;
274		} else {
275		$escaped .= $c;
276		}
277		}
278
279		return $escaped;
280		}
281
282		/**
283		* backwards compatibility
284		*
285		* @param $cellFormat
286		*
287		* @return int\|mixed
288		*/
289		private function addCellFormat($cellFormat)
290		{
291
292
293		$cellFormat = strtoupper($this->getCellFormat($cellFormat));
294		$position = array_search($cellFormat, $this->cellFormats, $strict = true);
295		if ($position === false) {
296		$position = count($this->cellFormats);
297		$this->cellFormats[] = $this->escapeCellFormat($cellFormat);
298		$this->cellTypes[] = $this->determineCellType($cellFormat);
299		}
300
301		return $position;
302		}
303
304		/**
305		* @param string $cellFormat
306		*
307		* @return string
308		*/
309		private function getCellFormat($cellFormat)
310		{
311		$formatArray = [
312		'string' => 'GENERAL',
313		'integer' => '0',
314		'date' => 'YYYY-MM-DD',
315		'datetime' => 'YYYY-MM-DD HH:MM:SS',
316		'dollar' => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',
317		'money' => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',
318		'euro' => '#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]',
319		'NN' => 'DDD',
320		'NNN' => 'DDDD',
321		'NNNN' => 'DDDD", "',
322		];
323
324		if (array_key_exists($cellFormat, $formatArray)) {
325		return $formatArray[$cellFormat];
326		}
327		return $cellFormat;
328		}
329
330		/**
331		* @param string $sheetName
332		* @param array $headerTypes
333		* @param bool $suppressRow
334		*/
335		public function writeSheetHeader($sheetName, array $headerTypes, $suppressRow = false)
336		{
337		if (empty($sheetName) \|\| empty($headerTypes) \|\| !empty($this->sheets[$sheetName])) {
338		return;
339		}
340		$this->initializeSheet($sheetName);
341		/** @var Sheet $sheet */
342		$sheet = &$this->sheets[$sheetName];
343		$sheet->setColumns([]);
344		foreach ($headerTypes as $val) {
345		$sheet->setColumn($this->addCellFormat($val));
346		}
347		if (!$suppressRow) {
348		$this->writeRowHeader($sheet, array_keys($headerTypes));
349		$sheet->increaseRowCount();
350		}
351		$this->currentSheet = $sheetName;
352		}
353
354		/**
355		* @param Sheet $sheet
356		* @param array $headerRow
357		*/
358		private function writeRowHeader(Sheet $sheet, $headerRow)
359		{
360		$sheet->getWriter()->write(
361		'<row collapsed="false" customFormat="false"
362		customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="'.(1).'">'
363		);
364		foreach ($headerRow as $k => $v) {
365		$this->writeCell($sheet->getWriter(), 0, $k, $v, $cell_format_index = '0');
366		}
367		$sheet->getWriter()->write('</row>');
368		}
369
370		/**
371		* @param string $sheetName
372		* @param array $row
373		*/
374		public function writeSheetRow($sheetName, array $row)
375		{
376		if (empty($sheetName) \|\| empty($row)) {
377		return;
378		}
379		$this->initializeSheet($sheetName);
380		/** @var Sheet $sheet */
381		$sheet = &$this->sheets[$sheetName];
382		$columns = $sheet->getColumns();
383		if (empty($columns)) {
384		$sheet->setColumns(array_fill($from = 0, $until = count($row), '0'));
385		}
386		$sheet->getWriter()->write(
387		'<row collapsed="false" customFormat="false" customHeight="false"
388		hidden="false" ht="12.1" outlineLevel="0" r="'.($sheet->getRowCount() + 1).'">'
389		);
390		$column_count = 0;
391		$sheetColumns = $sheet->getColumns();
392		foreach ($row as $k => $v) {
393		$this->writeCell(
394		$sheet->getWriter(),
395		$sheet->getRowCount(),
396		$column_count,
397		$v,
398		$sheetColumns[$column_count]
399		);
400		$column_count++;
401		}
402		$sheet->getWriter()->write('</row>');
403		$sheet->increaseRowCount();
404		$this->currentSheet = $sheetName;
405		}
406
407		/**
408		* @param string $sheetName
409		*/
410		protected function finalizeSheet($sheetName)
411		{
412		if (empty($sheetName) \|\| $this->sheets[$sheetName]->getFinalized()) {
413		return;
414		}
415		/** @var Sheet $sheet */
416		$sheet = &$this->sheets[$sheetName];
417		$sheet->getWriter()->write('</sheetData>');
418		$mergeCells = $sheet->getMergeCells();
419		if (!empty($mergeCells)) {
420		$sheet->getWriter()->write($this->sheetXml->getMergeCells($mergeCells));
421		}
422		$sheet->getWriter()->write($this->sheetXml->getPrintOptions());
423		$sheet->getWriter()->write($this->sheetXml->getPageMargins());
424		$sheet->getWriter()->write($this->sheetXml->getPageSetup());
425		$sheet->getWriter()->write($this->sheetXml->getHeaderFooter());
426		$sheet->getWriter()->write('</worksheet>');
427		$maxCell = ExcelHelper::xlsCell($sheet->getRowCount() - 1, count($sheet->getColumns()) - 1);
428		$maxCellTag = $this->sheetXml->getDimension($maxCell);
429		$paddingLength = $sheet->getMaxCellTagEnd() - $sheet->getMaxCellTagStart() - strlen($maxCellTag);
430		$sheet->getWriter()->fSeek($sheet->getMaxCellTagStart());
431		$sheet->getWriter()->write($maxCellTag.str_repeat(" ", $paddingLength));
432		$sheet->getWriter()->close();
433		$sheet->setFinalized(true);
434		}
435
436		/**
437		* @param string $sheetName
438		* @param int $startCellRow
439		* @param int $startCellColumn
440		* @param int $endCellRow
441		* @param int $endCellColumn
442		*/
443		public function markMergedCell($sheetName, $startCellRow, $startCellColumn, $endCellRow, $endCellColumn)
444		{
445		if (empty($sheetName) \|\| $this->sheets[$sheetName]->getFinalized()) {
446		return;
447		}
448		$this->initializeSheet($sheetName);
449		/** @var Sheet $sheet */
450		$sheet = &$this->sheets[$sheetName];
451		$startCell = ExcelHelper::xlsCell($startCellRow, $startCellColumn);
452		$endCell = ExcelHelper::xlsCell($endCellRow, $endCellColumn);
453		$sheet->setMergeCells($startCell.":".$endCell);
454		}
455
456		/**
457		* @param array $data
458		* @param string $sheetName
459		* @param array $headerTypes
460		*/
461		public function writeSheet(array $data, $sheetName = '', array $headerTypes = [])
462		{
463		$sheetName = empty($sheetName) ? 'Sheet1' : $sheetName;
464		$data = empty($data) ? [['']] : $data;
465		if (!empty($headerTypes)) {
466		$this->writeSheetHeader($sheetName, $headerTypes);
467		}
468		foreach ($data as $i => $row) {
469		$this->writeSheetRow($sheetName, $row);
470		}
471		$this->finalizeSheet($sheetName);
472		}
473
474		/**
475		* @param Writer $file
476		* @param $rowNumber
477		* @param $columnNumber
478		* @param $value
479		* @param $cellIndex
480		*/
481		protected function writeCell(
482		Writer $file,
483		$rowNumber,
484		$columnNumber,
485		$value,
486		$cellIndex
487		) {
488		$cellType = $this->cellTypes[$cellIndex];
489		$cellName = ExcelHelper::xlsCell($rowNumber, $columnNumber);
490		if (!is_scalar($value) \|\| $value === '') {
491		$file->write('<c r="'.$cellName.'" s="'.$cellIndex.'"/>');
492		} elseif (is_string($value) && $value{0} == '=') {
493		$file->write(
494		sprintf(
495		'<c r="%s" s="%s" t="s"><f>%s</f></c>',
496		$cellName,
497		$cellIndex,
498		ExcelHelper::xmlspecialchars($value)
499		)
500		);
501		} elseif ($cellType == 'date') {
502		$file->write(
503		sprintf(
504		'<c r="%s" s="%s" t="n"><v>%s</v></c>',
505		$cellName,
506		$cellIndex,
507		ExcelHelper::convertDateTime($value)
508		)
509		);
510		} elseif ($cellType == 'datetime') {
511		$file->write(
512		'<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.ExcelHelper::convertDateTime($value).'</v></c>'
513		);
514		} elseif ($cellType == 'currency' \|\| $cellType == 'percent' \|\| $cellType == 'numeric') {
515		$file->write(
516		'<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.ExcelHelper::xmlspecialchars($value).'</v></c>'
517		);
518		} else {
519		if (!is_string($value)) {
520		$file->write('<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.($value * 1).'</v></c>');
521		} else {
522		if ($value{0} != '0' && $value{0} != '+' && filter_var(
523		$value,
524		FILTER_VALIDATE_INT,
525		['options' => ['max_range' => ExcelHelper::EXCEL_MAX_RANGE]]
526		)) {
527		$file->write('<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.($value * 1).'</v></c>');
528		} else {
529		$file->write(
530		'<c r="'.$cellName.'" s="'.$cellIndex.'" t="s"><v>'.ExcelHelper::xmlspecialchars(
531		$this->setSharedString($value)
532		).'</v></c>'
533		);
534		}
535		}
536		}
537		}
538
539		/**
540		* @return string
541		*/
542		protected function writeStylesXML()
543		{
544		$temporaryFilename = $this->tempFilename();
545		$file = new Writer($temporaryFilename);
546		$styles = new Styles();
547		$styles->setCellFormats($this->cellFormats);
548		$file->write($styles->buildStylesXML());
549
550		return $temporaryFilename;
551		}
552
553		/**
554		* @param $v
555		*
556		* @return int\|mixed
557		*/
558		protected function setSharedString($v)
559		{
560		if (isset($this->sharedStrings[$v])) {
561		$stringValue = $this->sharedStrings[$v];
562		} else {
563		$stringValue = count($this->sharedStrings);
564		$this->sharedStrings[$v] = $stringValue;
565		}
566		$this->sharedStringCount++;
567
568		return $stringValue;
569		}
570
571		/**
572		* @return string
573		*/
574		protected function writeSharedStringsXML()
575		{
576		$tempFilename = $this->tempFilename();
577		$file = new Writer($tempFilename);
578		$sharedStrings = new SharedStrings($this->sharedStringCount, $this->sharedStrings);
579		$file->write($sharedStrings->buildSharedStringsXML());
580		$file->close();
581
582		return $tempFilename;
583		}
584
585		/**
586		* @param $string
587		*/
588		public static function log($string)
589		{
590		file_put_contents(
591		"php://stderr",
592		date("Y-m-d H:i:s:").rtrim(is_array($string) ? json_encode($string) : $string)."\n"
593		);
594		}
595
596		/**
597		* @link https://msdn.microsoft.com/ru-RU/library/aa365247%28VS.85%29.aspx
598		*
599		* @param string $filename
600		*
601		* @return mixed
602		*/
603	View Code Duplication	public static function checkFilename($filename)
		0 ignored issues – show Duplication introduced 2016-09-02 07:36 UTC by Report Bug Copy Issue Report This method seems to be duplicated in your project. Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation. You can also find more detailed suggestions in the “Code” section of your repository. Loading history...
604		{
605		$invalidCharacter = array_merge(
606		array_map('chr', range(0, 31)),
607		['<', '>', '?', '"', ':', '\|', '\\', '/', '*', '&']
608		);
609
610		return str_replace($invalidCharacter, '', $filename);
611		}
612
613		/**
614		* @param \ZipArchive $zip
615		* @param string $filename
616		*/
617		private function checkAndUnlink(\ZipArchive $zip, $filename)
618		{
619		if (file_exists($filename) && is_writable($filename)) {
620		unlink($filename);
621		}
622		if (empty($this->sheets) \|\| !$zip->open($filename, \ZipArchive::CREATE)) {
623		throw new \RuntimeException(
624		"Error in ".__CLASS__."::".__FUNCTION__.", no worksheets defined or unable to create zip."
625		);
626		}
627		}
628		}
629

ellumilel / php-excel-writer

GitHub Access Token became invalid

Push — master ( 44810d...9c6bac )

ExcelWriter::__destruct() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like