ExcelWriter::checkAndUnlink() - Code Metrics - Inspection of "set your filename" - ellumilel/php-excel-writer - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 6a28d0...1b231c )

by Denis

created 2016-09-12 17:44 UTC

ExcelWriter::checkAndUnlink() B

↳ Parent: ExcelWriter

Complexity

Conditions	5
Paths	4

Size

Total Lines	11
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	11
rs	8.8571
c	0
b	0
f	0
cc	5
eloc	6
nc	4
nop	2

<?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 null */
    protected $tmpFileName = 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;
    }

    /**
     * Set output filename
     * exp: yourFileName.xlsx
     *
     * @param string $tmpFileName
     */
    public function setTmpFileName($tmpFileName)
    {
        $this->tmpFileName = $tmpFileName;

    }

    /**
     * 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;
    }

    /**
     * @param bool $headers
     */
    public function writeToStdOut($headers = true)
    {
        if (empty($this->tmpDir)) {
            $tempFile = $this->tempFilename().'.xlsx';
        } else {
            $tempFile = $this->tmpFileName;
        }

        $this->writeToFile($tempFile);
        if (file_exists($tempFile)) {
            if ($headers) {
                header('Content-Description: File Transfer');
                header('Content-Type: application/octet-stream');
                header('Content-Disposition: attachment; filename="'.basename($tempFile).'"');
                header('Expires: 0');
                header('Cache-Control: must-revalidate');
                header('Pragma: public');
                header('Content-Length: '.filesize($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';
    }

    /**
     * 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[] = ExcelHelper::escapeCellFormat($cellFormat);
            $this->cellTypes[] = $this->determineCellType($cellFormat);
        }

        return $position;
    }

    /**
     * @link https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformats(v=office.15).aspx
     *
     * @param string $cellFormat
     *
     * @return string
     */
    private function getCellFormat($cellFormat)
    {
        $formatArray = [
            'string' => 'GENERAL',
            'integer' => '0',
            'float_with_sep' => '#,##0.00',
            'float' => '0.00',
            '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]',
            'rub' => '#,##0.00 [$₽-419];[Red]-#,##0.00 [$₽-419]',
            '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(0, 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] instanceof Sheet &&
                $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 int $rowNumber
     * @param int $columnNumber
     * @param mixed $value
     * @param $cellIndex
     */
    protected function writeCell(Writer $file, $rowNumber, $columnNumber, $value, $cellIndex)
    {
        $cellType = $this->cellTypes[$cellIndex];
        $cellName = ExcelHelper::xlsCell($rowNumber, $columnNumber);
        $cell = $this->sheetXml->getCell($cellName, $cellIndex, $cellType, $value);
        if ($cell === false) {
            $file->write(
                '<c r="'.$cellName.'" s="'.$cellIndex.'" t="s"><v>'.ExcelHelper::xmlspecialchars(
                    $this->setSharedString($value)
                ).'</v></c>'
            );
        } else {
            $file->write($cell);
        }
    }

    /**
     * @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 \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 null */
37			protected $tmpFileName = null;
38			/** @var Core */
39			protected $core;
40			/** @var App */
41			protected $app;
42			/** @var Workbook */
43			protected $workbook;
44			/** @var SheetXml */
45			protected $sheetXml;
46
47			/**
48			* ExcelWriter constructor.
49			* @throws \Exception
50			*/
51			public function __construct()
52			{
53			if (!class_exists('ZipArchive')) {
54			throw new \Exception('ZipArchive not found');
55			}
56			if (!ini_get('date.timezone')) {
57			date_default_timezone_set('UTC');
58			}
59			$this->addCellFormat($cell_format = 'GENERAL');
60			$this->core = new Core();
61			$this->app = new App();
62			$this->workbook = new Workbook();
63			$this->sheetXml = new SheetXml();
64			}
65
66			/**
67			* @param string $author
68			*/
69			public function setAuthor($author)
70			{
71			$this->core->setAuthor($author);
72			}
73
74			public function __destruct()
75			{
76			if (!empty($this->tempFiles)) {
77			foreach ($this->tempFiles as $tempFile) {
78			if (file_exists($tempFile)) {
79			unlink($tempFile);
80			}
81			}
82			}
83			}
84
85			/**
86			* @param $dir
87			*/
88			public function setTmpDir($dir)
89			{
90			$this->tmpDir = $dir;
91			}
92
93			/**
94			* Set output filename
95			* exp: yourFileName.xlsx
96			*
97			* @param string $tmpFileName
98			*/
99			public function setTmpFileName($tmpFileName)
100			{
101			$this->tmpFileName = $tmpFileName;
			0 ignored issues – show Documentation Bug introduced 2016-09-12 17:47 UTC by Report Bug Copy Issue Report It seems like `$tmpFileName` of type `string` is incompatible with the declared type `null` of property `$tmpFileName`. Our type inference engine has found an assignment to a property that is incompatible with the declared type of that property. Either this assignment is in error or the assigned type should be added to the documentation/type hint for that property.. Loading history...
102			}
103
104			/**
105			* Return tmpFileName
106			* @return string
107			*/
108			protected function tempFilename()
109			{
110			$tmpDir = is_null($this->tmpDir) ? sys_get_temp_dir() : $this->tmpDir;
111			$filename = tempnam($tmpDir, "excelWriter_");
112			$this->tempFiles[] = $filename;
113
114			return $filename;
115			}
116
117			/**
118			* @param bool $headers
119			*/
120			public function writeToStdOut($headers = true)
121			{
122			if (empty($this->tmpDir)) {
123			$tempFile = $this->tempFilename().'.xlsx';
124			} else {
125			$tempFile = $this->tmpFileName;
126			}
127
128			$this->writeToFile($tempFile);
129			if (file_exists($tempFile)) {
130			if ($headers) {
131			header('Content-Description: File Transfer');
132			header('Content-Type: application/octet-stream');
133			header('Content-Disposition: attachment; filename="'.basename($tempFile).'"');
134			header('Expires: 0');
135			header('Cache-Control: must-revalidate');
136			header('Pragma: public');
137			header('Content-Length: '.filesize($tempFile));
138			}
139			readfile($tempFile);
140			}
141			}
142
143			/**
144			* @return string
145			*/
146			public function writeToString()
147			{
148			$tempFile = $this->tempFilename();
149			$this->writeToFile($tempFile);
150			$string = file_get_contents($tempFile);
151
152			return $string;
153			}
154
155			/**
156			* @param string $filename
157			*/
158			public function writeToFile($filename)
159			{
160			$zip = new \ZipArchive();
161			foreach ($this->sheets as $sheetName => $sheet) {
162			$this->finalizeSheet($sheetName);
163			}
164			$this->checkAndUnlink($zip, $filename);
165			$this->workbook->setSheet($this->sheets);
166
167			$contentTypes = new ContentTypes(!empty($this->sharedStrings));
168			$contentTypes->setSheet($this->sheets);
169
170			$rel = new Relationships(!empty($this->sharedStrings));
171			$rel->setSheet($this->sheets);
172			$zip->addEmptyDir("docProps/");
173			$zip->addFromString("docProps/app.xml", $this->app->buildAppXML());
174			$zip->addFromString("docProps/core.xml", $this->core->buildCoreXML());
175			$zip->addEmptyDir("_rels/");
176			$zip->addFromString("_rels/.rels", $rel->buildRelationshipsXML());
177			$zip->addEmptyDir("xl/worksheets/");
178			foreach ($this->sheets as $sheet) {
179			/** @var Sheet $sheet */
180			$zip->addFile($sheet->getFilename(), "xl/worksheets/".$sheet->getXmlName());
181			}
182			if (!empty($this->sharedStrings)) {
183			$zip->addFile($this->writeSharedStringsXML(), "xl/sharedStrings.xml");
184			}
185			$zip->addFromString("xl/workbook.xml", $this->workbook->buildWorkbookXML());
186			$zip->addFile($this->writeStylesXML(), "xl/styles.xml");
187			$zip->addFromString("[Content_Types].xml", $contentTypes->buildContentTypesXML());
188			$zip->addEmptyDir("xl/_rels/");
189			$zip->addFromString("xl/_rels/workbook.xml.rels", $rel->buildWorkbookRelationshipsXML());
190			$zip->close();
191			}
192
193			/**
194			* @param string $sheetName
195			*/
196			protected function initializeSheet($sheetName)
197			{
198			if ($this->currentSheet == $sheetName \|\| isset($this->sheets[$sheetName])) {
199			return;
200			}
201			$sheetFilename = $this->tempFilename();
202			$sheetXmlName = 'sheet' . (count($this->sheets) + 1).".xml";
203			$sheetObj = new Sheet();
204			$sheetObj
205			->setFilename($sheetFilename)
206			->setSheetName($sheetName)
207			->setXmlName($sheetXmlName)
208			->setWriter(new Writer($sheetFilename))
209			;
210			$this->sheets[$sheetName] = $sheetObj;
211			/** @var Sheet $sheet */
212			$sheet = &$this->sheets[$sheetName];
213			$selectedTab = count($this->sheets) == 1 ? 'true' : 'false';
214			$maxCell = ExcelHelper::xlsCell(ExcelHelper::EXCEL_MAX_ROW, ExcelHelper::EXCEL_MAX_COL);
215			$sheet->getWriter()->write($this->sheetXml->getXml());
216			$sheet->getWriter()->write($this->sheetXml->getWorksheet());
217			$sheet->getWriter()->write($this->sheetXml->getSheetPr());
218			$sheet->setMaxCellTagStart($sheet->getWriter()->fTell());
219			$sheet->getWriter()->write($this->sheetXml->getDimension($maxCell));
220			$sheet->setMaxCellTagEnd($sheet->getWriter()->fTell());
221			$sheet->getWriter()->write($this->sheetXml->getSheetViews($selectedTab));
222			$sheet->getWriter()->write($this->sheetXml->getCools());
223			$sheet->getWriter()->write('<sheetData>');
224			}
225
226			/**
227			* @param $cellFormat
228			*
229			* @return string
230			*/
231			private function determineCellType($cellFormat)
232			{
233			$cellFormat = str_replace("[RED]", "", $cellFormat);
234			if ($cellFormat == 'GENERAL') {
235			return 'string';
236			}
237			if ($cellFormat == '0') {
238			return 'numeric';
239			}
240			$checkArray = [
241			'datetime' => [
242			"/[H]{1,2}:[M]{1,2}/",
243			"/[M]{1,2}:[S]{1,2}/",
244			],
245			'numeric' => [
246			"/0/",
247			],
248			'date' => [
249			"/[YY]{2,4}/",
250			"/[D]{1,2}/",
251			"/[M]{1,2}/",
252			],
253			'currency' => [
254			"/$/",
255			],
256			'percent' => [
257			"/%/",
258			],
259			];
260			foreach ($checkArray as $type => $item) {
261			foreach ($item as $prMatch) {
262			if (preg_match($prMatch, $cellFormat)) {
263			return $type;
264			}
265			}
266			}
267
268			return 'string';
269			}
270
271			/**
272			* backwards compatibility
273			*
274			* @param $cellFormat
275			*
276			* @return int\|mixed
277			*/
278			private function addCellFormat($cellFormat)
279			{
280			$cellFormat = strtoupper($this->getCellFormat($cellFormat));
281			$position = array_search($cellFormat, $this->cellFormats, $strict = true);
282			if ($position === false) {
283			$position = count($this->cellFormats);
284			$this->cellFormats[] = ExcelHelper::escapeCellFormat($cellFormat);
285			$this->cellTypes[] = $this->determineCellType($cellFormat);
286			}
287
288			return $position;
289			}
290
291			/**
292			* @link https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformats(v=office.15).aspx
293			*
294			* @param string $cellFormat
295			*
296			* @return string
297			*/
298			private function getCellFormat($cellFormat)
299			{
300			$formatArray = [
301			'string' => 'GENERAL',
302			'integer' => '0',
303			'float_with_sep' => '#,##0.00',
304			'float' => '0.00',
305			'date' => 'YYYY-MM-DD',
306			'datetime' => 'YYYY-MM-DD HH:MM:SS',
307			'dollar' => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',
308			'money' => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',
309			'euro' => '#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]',
310			'rub' => '#,##0.00 [$₽-419];[Red]-#,##0.00 [$₽-419]',
311			'NN' => 'DDD',
312			'NNN' => 'DDDD',
313			'NNNN' => 'DDDD", "',
314			];
315
316			if (array_key_exists($cellFormat, $formatArray)) {
317			return $formatArray[$cellFormat];
318			}
319			return $cellFormat;
320			}
321
322			/**
323			* @param string $sheetName
324			* @param array $headerTypes
325			* @param bool $suppressRow
326			*/
327			public function writeSheetHeader($sheetName, array $headerTypes, $suppressRow = false)
328			{
329			if (empty($sheetName) \|\| empty($headerTypes) \|\| !empty($this->sheets[$sheetName])) {
330			return;
331			}
332			$this->initializeSheet($sheetName);
333			/** @var Sheet $sheet */
334			$sheet = &$this->sheets[$sheetName];
335			$sheet->setColumns([]);
336			foreach ($headerTypes as $val) {
337			$sheet->setColumn($this->addCellFormat($val));
338			}
339			if (!$suppressRow) {
340			$this->writeRowHeader($sheet, array_keys($headerTypes));
341			$sheet->increaseRowCount();
342			}
343			$this->currentSheet = $sheetName;
344			}
345
346			/**
347			* @param Sheet $sheet
348			* @param array $headerRow
349			*/
350			private function writeRowHeader(Sheet $sheet, $headerRow)
351			{
352			$sheet->getWriter()->write(
353			'<row collapsed="false" customFormat="false"
354			customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="'.(1).'">'
355			);
356			foreach ($headerRow as $k => $v) {
357			$this->writeCell($sheet->getWriter(), 0, $k, $v, $cell_format_index = '0');
358			}
359			$sheet->getWriter()->write('</row>');
360			}
361
362			/**
363			* @param string $sheetName
364			* @param array $row
365			*/
366			public function writeSheetRow($sheetName, array $row)
367			{
368			if (empty($sheetName) \|\| empty($row)) {
369			return;
370			}
371			$this->initializeSheet($sheetName);
372			/** @var Sheet $sheet */
373			$sheet = &$this->sheets[$sheetName];
374			$columns = $sheet->getColumns();
375			if (empty($columns)) {
376			$sheet->setColumns(array_fill(0, count($row), '0'));
377			}
378			$sheet->getWriter()->write(
379			'<row collapsed="false" customFormat="false" customHeight="false"
380			hidden="false" ht="12.1" outlineLevel="0" r="'.($sheet->getRowCount() + 1).'">'
381			);
382			$column_count = 0;
383			$sheetColumns = $sheet->getColumns();
384			foreach ($row as $k => $v) {
385			$this->writeCell(
386			$sheet->getWriter(),
387			$sheet->getRowCount(),
388			$column_count,
389			$v,
390			$sheetColumns[$column_count]
391			);
392			$column_count++;
393			}
394			$sheet->getWriter()->write('</row>');
395			$sheet->increaseRowCount();
396			$this->currentSheet = $sheetName;
397			}
398
399			/**
400			* @param string $sheetName
401			*/
402			protected function finalizeSheet($sheetName)
403			{
404			if (empty($sheetName) \|\| ($this->sheets[$sheetName] instanceof Sheet &&
405			$this->sheets[$sheetName]->getFinalized()
406			)
407			) {
408			return;
409			}
410
411			/** @var Sheet $sheet */
412			$sheet = &$this->sheets[$sheetName];
413			$sheet->getWriter()->write('</sheetData>');
414			$mergeCells = $sheet->getMergeCells();
415			if (!empty($mergeCells)) {
416			$sheet->getWriter()->write($this->sheetXml->getMergeCells($mergeCells));
417			}
418			$sheet->getWriter()->write($this->sheetXml->getPrintOptions());
419			$sheet->getWriter()->write($this->sheetXml->getPageMargins());
420			$sheet->getWriter()->write($this->sheetXml->getPageSetup());
421			$sheet->getWriter()->write($this->sheetXml->getHeaderFooter());
422			$sheet->getWriter()->write('</worksheet>');
423			$maxCell = ExcelHelper::xlsCell($sheet->getRowCount() - 1, count($sheet->getColumns()) - 1);
424			$maxCellTag = $this->sheetXml->getDimension($maxCell);
425			$paddingLength = $sheet->getMaxCellTagEnd() - $sheet->getMaxCellTagStart() - strlen($maxCellTag);
426			$sheet->getWriter()->fSeek($sheet->getMaxCellTagStart());
427			$sheet->getWriter()->write($maxCellTag.str_repeat(" ", $paddingLength));
428			$sheet->getWriter()->close();
429			$sheet->setFinalized(true);
430			}
431
432			/**
433			* @param string $sheetName
434			* @param int $startCellRow
435			* @param int $startCellColumn
436			* @param int $endCellRow
437			* @param int $endCellColumn
438			*/
439			public function markMergedCell($sheetName, $startCellRow, $startCellColumn, $endCellRow, $endCellColumn)
440			{
441			if (empty($sheetName) \|\| $this->sheets[$sheetName]->getFinalized()) {
442			return;
443			}
444			$this->initializeSheet($sheetName);
445			/** @var Sheet $sheet */
446			$sheet = &$this->sheets[$sheetName];
447			$startCell = ExcelHelper::xlsCell($startCellRow, $startCellColumn);
448			$endCell = ExcelHelper::xlsCell($endCellRow, $endCellColumn);
449			$sheet->setMergeCells($startCell.":".$endCell);
450			}
451
452			/**
453			* @param array $data
454			* @param string $sheetName
455			* @param array $headerTypes
456			*/
457			public function writeSheet(array $data, $sheetName = '', array $headerTypes = [])
458			{
459			$sheetName = empty($sheetName) ? 'Sheet1' : $sheetName;
460			$data = empty($data) ? [['']] : $data;
461			if (!empty($headerTypes)) {
462			$this->writeSheetHeader($sheetName, $headerTypes);
463			}
464			foreach ($data as $i => $row) {
465			$this->writeSheetRow($sheetName, $row);
466			}
467			$this->finalizeSheet($sheetName);
468			}
469
470			/**
471			* @param Writer $file
472			* @param int $rowNumber
473			* @param int $columnNumber
474			* @param mixed $value
475			* @param $cellIndex
476			*/
477			protected function writeCell(Writer $file, $rowNumber, $columnNumber, $value, $cellIndex)
478			{
479			$cellType = $this->cellTypes[$cellIndex];
480			$cellName = ExcelHelper::xlsCell($rowNumber, $columnNumber);
481			$cell = $this->sheetXml->getCell($cellName, $cellIndex, $cellType, $value);
482			if ($cell === false) {
483			$file->write(
484			'<c r="'.$cellName.'" s="'.$cellIndex.'" t="s"><v>'.ExcelHelper::xmlspecialchars(
485			$this->setSharedString($value)
486			).'</v></c>'
487			);
488			} else {
489			$file->write($cell);
490			}
491			}
492
493			/**
494			* @return string
495			*/
496			protected function writeStylesXML()
497			{
498			$temporaryFilename = $this->tempFilename();
499			$file = new Writer($temporaryFilename);
500			$styles = new Styles();
501			$styles->setCellFormats($this->cellFormats);
502			$file->write($styles->buildStylesXML());
503
504			return $temporaryFilename;
505			}
506
507			/**
508			* @param $v
509			*
510			* @return int\|mixed
511			*/
512			protected function setSharedString($v)
513			{
514			if (isset($this->sharedStrings[$v])) {
515			$stringValue = $this->sharedStrings[$v];
516			} else {
517			$stringValue = count($this->sharedStrings);
518			$this->sharedStrings[$v] = $stringValue;
519			}
520			$this->sharedStringCount++;
521
522			return $stringValue;
523			}
524
525			/**
526			* @return string
527			*/
528			protected function writeSharedStringsXML()
529			{
530			$tempFilename = $this->tempFilename();
531			$file = new Writer($tempFilename);
532			$sharedStrings = new SharedStrings($this->sharedStringCount, $this->sharedStrings);
533			$file->write($sharedStrings->buildSharedStringsXML());
534			$file->close();
535
536			return $tempFilename;
537			}
538
539			/**
540			* @param \ZipArchive $zip
541			* @param string $filename
542			*/
543			private function checkAndUnlink(\ZipArchive $zip, $filename)
544			{
545			if (file_exists($filename) && is_writable($filename)) {
546			unlink($filename);
547			}
548			if (empty($this->sheets) \|\| !$zip->open($filename, \ZipArchive::CREATE)) {
549			throw new \RuntimeException(
550			"Error in ".__CLASS__."::".__FUNCTION__.", no worksheets defined or unable to create zip."
551			);
552			}
553			}
554			}
555

ellumilel / php-excel-writer

GitHub Access Token became invalid

Push — master ( 6a28d0...1b231c )

ExcelWriter::checkAndUnlink() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like