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

Completed

Push — master ( 1b231c...28d83c )

by Denis

created 2016-09-12 17:45 UTC

ExcelWriter::setFileName() A

↳ Parent: ExcelWriter

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Importance

Changes

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

<?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 $fileName = 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: yourFileName.xlsx
     *
     * @param string $fileName
     */
    public function setFileName($fileName)
    {
        $this->fileName = $fileName;

    }

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

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

ellumilel / php-excel-writer

GitHub Access Token became invalid

Push — master ( 1b231c...28d83c )

ExcelWriter::setFileName() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like