ExcelWriter::writeCell() - Code Metrics - Inspection of "new exmaple, new format rub, some refactoring" - ellumilel/php-excel-writer - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( a3c45e...b9d52d )

by Denis

created 2016-09-02 12:13 UTC

ExcelWriter::writeCell() A

↳ Parent: ExcelWriter

Complexity

Conditions	2
Paths	2

Size

Total Lines	15
Code Lines	11

Duplication

Lines	0
Ratio	0 %

Importance

Changes	4
Bugs	1	Features	1

Metric	Value
c	4
b	1
f	1
dl	0
loc	15
rs	9.4285
cc	2
eloc	11
nc	2
nop	5

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

    /**
     * @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',
            '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($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] 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 // true
''   == null  // true
'ab' == false // false
'ab' == null  // false

// It is often better to use strict comparison
'' === false // false
'' === null  // 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 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			$cellFormat = strtoupper($this->getCellFormat($cellFormat));
292			$position = array_search($cellFormat, $this->cellFormats, $strict = true);
293			if ($position === false) {
294			$position = count($this->cellFormats);
295			$this->cellFormats[] = $this->escapeCellFormat($cellFormat);
296			$this->cellTypes[] = $this->determineCellType($cellFormat);
297			}
298
299			return $position;
300			}
301
302			/**
303			* @link https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformats(v=office.15).aspx
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			'rub' => '#,##0.00 [$₽-419];[Red]-#,##0.00 [$₽-419]',
320			'NN' => 'DDD',
321			'NNN' => 'DDDD',
322			'NNNN' => 'DDDD", "',
323			];
324
325			if (array_key_exists($cellFormat, $formatArray)) {
326			return $formatArray[$cellFormat];
327			}
328			return $cellFormat;
329			}
330
331			/**
332			* @param string $sheetName
333			* @param array $headerTypes
334			* @param bool $suppressRow
335			*/
336			public function writeSheetHeader($sheetName, array $headerTypes, $suppressRow = false)
337			{
338			if (empty($sheetName) \|\| empty($headerTypes) \|\| !empty($this->sheets[$sheetName])) {
339			return;
340			}
341			$this->initializeSheet($sheetName);
342			/** @var Sheet $sheet */
343			$sheet = &$this->sheets[$sheetName];
344			$sheet->setColumns([]);
345			foreach ($headerTypes as $val) {
346			$sheet->setColumn($this->addCellFormat($val));
347			}
348			if (!$suppressRow) {
349			$this->writeRowHeader($sheet, array_keys($headerTypes));
350			$sheet->increaseRowCount();
351			}
352			$this->currentSheet = $sheetName;
353			}
354
355			/**
356			* @param Sheet $sheet
357			* @param array $headerRow
358			*/
359			private function writeRowHeader(Sheet $sheet, $headerRow)
360			{
361			$sheet->getWriter()->write(
362			'<row collapsed="false" customFormat="false"
363			customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="'.(1).'">'
364			);
365			foreach ($headerRow as $k => $v) {
366			$this->writeCell($sheet->getWriter(), 0, $k, $v, $cell_format_index = '0');
367			}
368			$sheet->getWriter()->write('</row>');
369			}
370
371			/**
372			* @param string $sheetName
373			* @param array $row
374			*/
375			public function writeSheetRow($sheetName, array $row)
376			{
377			if (empty($sheetName) \|\| empty($row)) {
378			return;
379			}
380			$this->initializeSheet($sheetName);
381			/** @var Sheet $sheet */
382			$sheet = &$this->sheets[$sheetName];
383			$columns = $sheet->getColumns();
384			if (empty($columns)) {
385			$sheet->setColumns(array_fill($from = 0, $until = count($row), '0'));
386			}
387			$sheet->getWriter()->write(
388			'<row collapsed="false" customFormat="false" customHeight="false"
389			hidden="false" ht="12.1" outlineLevel="0" r="'.($sheet->getRowCount() + 1).'">'
390			);
391			$column_count = 0;
392			$sheetColumns = $sheet->getColumns();
393			foreach ($row as $k => $v) {
394			$this->writeCell(
395			$sheet->getWriter(),
396			$sheet->getRowCount(),
397			$column_count,
398			$v,
399			$sheetColumns[$column_count]
400			);
401			$column_count++;
402			}
403			$sheet->getWriter()->write('</row>');
404			$sheet->increaseRowCount();
405			$this->currentSheet = $sheetName;
406			}
407
408			/**
409			* @param string $sheetName
410			*/
411			protected function finalizeSheet($sheetName)
412			{
413			if (empty($sheetName) \|\| ($this->sheets[$sheetName] instanceof Sheet &&
414			$this->sheets[$sheetName]->getFinalized()
415			)
416			) {
417			return;
418			}
419
420			/** @var Sheet $sheet */
421			$sheet = &$this->sheets[$sheetName];
422			$sheet->getWriter()->write('</sheetData>');
423			$mergeCells = $sheet->getMergeCells();
424			if (!empty($mergeCells)) {
425			$sheet->getWriter()->write($this->sheetXml->getMergeCells($mergeCells));
426			}
427			$sheet->getWriter()->write($this->sheetXml->getPrintOptions());
428			$sheet->getWriter()->write($this->sheetXml->getPageMargins());
429			$sheet->getWriter()->write($this->sheetXml->getPageSetup());
430			$sheet->getWriter()->write($this->sheetXml->getHeaderFooter());
431			$sheet->getWriter()->write('</worksheet>');
432			$maxCell = ExcelHelper::xlsCell($sheet->getRowCount() - 1, count($sheet->getColumns()) - 1);
433			$maxCellTag = $this->sheetXml->getDimension($maxCell);
434			$paddingLength = $sheet->getMaxCellTagEnd() - $sheet->getMaxCellTagStart() - strlen($maxCellTag);
435			$sheet->getWriter()->fSeek($sheet->getMaxCellTagStart());
436			$sheet->getWriter()->write($maxCellTag.str_repeat(" ", $paddingLength));
437			$sheet->getWriter()->close();
438			$sheet->setFinalized(true);
439			}
440
441			/**
442			* @param string $sheetName
443			* @param int $startCellRow
444			* @param int $startCellColumn
445			* @param int $endCellRow
446			* @param int $endCellColumn
447			*/
448			public function markMergedCell($sheetName, $startCellRow, $startCellColumn, $endCellRow, $endCellColumn)
449			{
450			if (empty($sheetName) \|\| $this->sheets[$sheetName]->getFinalized()) {
451			return;
452			}
453			$this->initializeSheet($sheetName);
454			/** @var Sheet $sheet */
455			$sheet = &$this->sheets[$sheetName];
456			$startCell = ExcelHelper::xlsCell($startCellRow, $startCellColumn);
457			$endCell = ExcelHelper::xlsCell($endCellRow, $endCellColumn);
458			$sheet->setMergeCells($startCell.":".$endCell);
459			}
460
461			/**
462			* @param array $data
463			* @param string $sheetName
464			* @param array $headerTypes
465			*/
466			public function writeSheet(array $data, $sheetName = '', array $headerTypes = [])
467			{
468			$sheetName = empty($sheetName) ? 'Sheet1' : $sheetName;
469			$data = empty($data) ? [['']] : $data;
470			if (!empty($headerTypes)) {
471			$this->writeSheetHeader($sheetName, $headerTypes);
472			}
473			foreach ($data as $i => $row) {
474			$this->writeSheetRow($sheetName, $row);
475			}
476			$this->finalizeSheet($sheetName);
477			}
478
479			/**
480			* @param Writer $file
481			* @param int $rowNumber
482			* @param int $columnNumber
483			* @param mixed $value
484			* @param $cellIndex
485			*/
486			protected function writeCell(Writer $file, $rowNumber, $columnNumber, $value, $cellIndex)
487			{
488			$cellType = $this->cellTypes[$cellIndex];
489			$cellName = ExcelHelper::xlsCell($rowNumber, $columnNumber);
490			$cell = $this->sheetXml->getCell($cellName, $cellIndex, $cellType, $value);
491			if (!$cell) {
			0 ignored issues – show Bug Best Practice introduced 2016-09-02 12:17 UTC by Report Bug Copy Issue Report The expression `$cell` of type `string\|false` is loosely compared to `false`; this is ambiguous if the string can be empty. You might want to explicitly use `=== false` instead. In PHP, under loose comparison (like `==`, or `!=`, or `switch` conditions), values of different types might be equal. For `string` values, the empty string `''` is a special case, in particular the following results might be unexpected: '' == false // true '' == null // true 'ab' == false // false 'ab' == null // false // It is often better to use strict comparison '' === false // false '' === null // false Loading history...
492			$file->write(
493			'<c r="'.$cellName.'" s="'.$cellIndex.'" t="s"><v>'.ExcelHelper::xmlspecialchars(
494			$this->setSharedString($value)
495			).'</v></c>'
496			);
497			} else {
498			$file->write($cell);
499			}
500			}
501
502			/**
503			* @return string
504			*/
505			protected function writeStylesXML()
506			{
507			$temporaryFilename = $this->tempFilename();
508			$file = new Writer($temporaryFilename);
509			$styles = new Styles();
510			$styles->setCellFormats($this->cellFormats);
511			$file->write($styles->buildStylesXML());
512
513			return $temporaryFilename;
514			}
515
516			/**
517			* @param $v
518			*
519			* @return int\|mixed
520			*/
521			protected function setSharedString($v)
522			{
523			if (isset($this->sharedStrings[$v])) {
524			$stringValue = $this->sharedStrings[$v];
525			} else {
526			$stringValue = count($this->sharedStrings);
527			$this->sharedStrings[$v] = $stringValue;
528			}
529			$this->sharedStringCount++;
530
531			return $stringValue;
532			}
533
534			/**
535			* @return string
536			*/
537			protected function writeSharedStringsXML()
538			{
539			$tempFilename = $this->tempFilename();
540			$file = new Writer($tempFilename);
541			$sharedStrings = new SharedStrings($this->sharedStringCount, $this->sharedStrings);
542			$file->write($sharedStrings->buildSharedStringsXML());
543			$file->close();
544
545			return $tempFilename;
546			}
547
548			/**
549			* @param \ZipArchive $zip
550			* @param string $filename
551			*/
552			private function checkAndUnlink(\ZipArchive $zip, $filename)
553			{
554			if (file_exists($filename) && is_writable($filename)) {
555			unlink($filename);
556			}
557			if (empty($this->sheets) \|\| !$zip->open($filename, \ZipArchive::CREATE)) {
558			throw new \RuntimeException(
559			"Error in ".__CLASS__."::".__FUNCTION__.", no worksheets defined or unable to create zip."
560			);
561			}
562			}
563			}
564

ellumilel / php-excel-writer

GitHub Access Token became invalid

Push — master ( a3c45e...b9d52d )

ExcelWriter::writeCell() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like