ExcelWriter::determineCellType() - Code Metrics - Inspection of "New text type: 0001 and 111 write text cell type" - ellumilel/php-excel-writer - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( d28d59...eb929a )

by Denis

created 2016-09-13 20:35 UTC

ExcelWriter::determineCellType() C

↳ Parent: ExcelWriter

Complexity

Conditions	7
Paths	7

Size

Total Lines	42
Code Lines	27

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	42
rs	6.7272
c	0
b	0
f	0
cc	7
eloc	27
nc	7
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';
        }
        if ($cellFormat == '@') {
            return '@';
        }
        $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',
            'text' => '@',
            '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			if ($cellFormat == '@') {
240			return '@';
241			}
242			$checkArray = [
243			'datetime' => [
244			"/[H]{1,2}:[M]{1,2}/",
245			"/[M]{1,2}:[S]{1,2}/",
246			],
247			'numeric' => [
248			"/0/",
249			],
250			'date' => [
251			"/[YY]{2,4}/",
252			"/[D]{1,2}/",
253			"/[M]{1,2}/",
254			],
255			'currency' => [
256			"/$/",
257			],
258			'percent' => [
259			"/%/",
260			],
261			];
262			foreach ($checkArray as $type => $item) {
263			foreach ($item as $prMatch) {
264			if (preg_match($prMatch, $cellFormat)) {
265			return $type;
266			}
267			}
268			}
269
270			return 'string';
271			}
272
273			/**
274			* backwards compatibility
275			*
276			* @param $cellFormat
277			*
278			* @return int\|mixed
279			*/
280			private function addCellFormat($cellFormat)
281			{
282			$cellFormat = strtoupper($this->getCellFormat($cellFormat));
283			$position = array_search($cellFormat, $this->cellFormats, $strict = true);
284			if ($position === false) {
285			$position = count($this->cellFormats);
286			$this->cellFormats[] = ExcelHelper::escapeCellFormat($cellFormat);
287			$this->cellTypes[] = $this->determineCellType($cellFormat);
288			}
289
290			return $position;
291			}
292
293			/**
294			* @link https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformats(v=office.15).aspx
295			*
296			* @param string $cellFormat
297			*
298			* @return string
299			*/
300			private function getCellFormat($cellFormat)
301			{
302			$formatArray = [
303			'string' => 'GENERAL',
304			'text' => '@',
305			'integer' => '0',
306			'float_with_sep' => '#,##0.00',
307			'float' => '0.00',
308			'date' => 'YYYY-MM-DD',
309			'datetime' => 'YYYY-MM-DD HH:MM:SS',
310			'dollar' => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',
311			'money' => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',
312			'euro' => '#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]',
313			'rub' => '#,##0.00 [$₽-419];[Red]-#,##0.00 [$₽-419]',
314			'NN' => 'DDD',
315			'NNN' => 'DDDD',
316			'NNNN' => 'DDDD", "',
317			];
318
319			if (array_key_exists($cellFormat, $formatArray)) {
320			return $formatArray[$cellFormat];
321			}
322			return $cellFormat;
323			}
324
325			/**
326			* @param string $sheetName
327			* @param array $headerTypes
328			* @param bool $suppressRow
329			*/
330			public function writeSheetHeader($sheetName, array $headerTypes, $suppressRow = false)
331			{
332			if (empty($sheetName) \|\| empty($headerTypes) \|\| !empty($this->sheets[$sheetName])) {
333			return;
334			}
335			$this->initializeSheet($sheetName);
336			/** @var Sheet $sheet */
337			$sheet = &$this->sheets[$sheetName];
338			$sheet->setColumns([]);
339			foreach ($headerTypes as $val) {
340			$sheet->setColumn($this->addCellFormat($val));
341			}
342			if (!$suppressRow) {
343			$this->writeRowHeader($sheet, array_keys($headerTypes));
344			$sheet->increaseRowCount();
345			}
346			$this->currentSheet = $sheetName;
347			}
348
349			/**
350			* @param Sheet $sheet
351			* @param array $headerRow
352			*/
353			private function writeRowHeader(Sheet $sheet, $headerRow)
354			{
355			$sheet->getWriter()->write(
356			'<row collapsed="false" customFormat="false"
357			customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="'.(1).'">'
358			);
359			foreach ($headerRow as $k => $v) {
360			$this->writeCell($sheet->getWriter(), 0, $k, $v, $cell_format_index = '0');
361			}
362			$sheet->getWriter()->write('</row>');
363			}
364
365			/**
366			* @param string $sheetName
367			* @param array $row
368			*/
369			public function writeSheetRow($sheetName, array $row)
370			{
371			if (empty($sheetName) \|\| empty($row)) {
372			return;
373			}
374			$this->initializeSheet($sheetName);
375			/** @var Sheet $sheet */
376			$sheet = &$this->sheets[$sheetName];
377			$columns = $sheet->getColumns();
378			if (empty($columns)) {
379			$sheet->setColumns(array_fill(0, count($row), '0'));
380			}
381			$sheet->getWriter()->write(
382			'<row collapsed="false" customFormat="false" customHeight="false"
383			hidden="false" ht="12.1" outlineLevel="0" r="'.($sheet->getRowCount() + 1).'">'
384			);
385			$column_count = 0;
386			$sheetColumns = $sheet->getColumns();
387			foreach ($row as $k => $v) {
388			$this->writeCell(
389			$sheet->getWriter(),
390			$sheet->getRowCount(),
391			$column_count,
392			$v,
393			$sheetColumns[$column_count]
394			);
395			$column_count++;
396			}
397			$sheet->getWriter()->write('</row>');
398			$sheet->increaseRowCount();
399			$this->currentSheet = $sheetName;
400			}
401
402			/**
403			* @param string $sheetName
404			*/
405			protected function finalizeSheet($sheetName)
406			{
407			if (empty($sheetName) \|\| ($this->sheets[$sheetName] instanceof Sheet &&
408			$this->sheets[$sheetName]->getFinalized()
409			)
410			) {
411			return;
412			}
413
414			/** @var Sheet $sheet */
415			$sheet = &$this->sheets[$sheetName];
416			$sheet->getWriter()->write('</sheetData>');
417			$mergeCells = $sheet->getMergeCells();
418			if (!empty($mergeCells)) {
419			$sheet->getWriter()->write($this->sheetXml->getMergeCells($mergeCells));
420			}
421			$sheet->getWriter()->write($this->sheetXml->getPrintOptions());
422			$sheet->getWriter()->write($this->sheetXml->getPageMargins());
423			$sheet->getWriter()->write($this->sheetXml->getPageSetup());
424			$sheet->getWriter()->write($this->sheetXml->getHeaderFooter());
425			$sheet->getWriter()->write('</worksheet>');
426			$maxCell = ExcelHelper::xlsCell($sheet->getRowCount() - 1, count($sheet->getColumns()) - 1);
427			$maxCellTag = $this->sheetXml->getDimension($maxCell);
428			$paddingLength = $sheet->getMaxCellTagEnd() - $sheet->getMaxCellTagStart() - strlen($maxCellTag);
429			$sheet->getWriter()->fSeek($sheet->getMaxCellTagStart());
430			$sheet->getWriter()->write($maxCellTag.str_repeat(" ", $paddingLength));
431			$sheet->getWriter()->close();
432			$sheet->setFinalized(true);
433			}
434
435			/**
436			* @param string $sheetName
437			* @param int $startCellRow
438			* @param int $startCellColumn
439			* @param int $endCellRow
440			* @param int $endCellColumn
441			*/
442			public function markMergedCell($sheetName, $startCellRow, $startCellColumn, $endCellRow, $endCellColumn)
443			{
444			if (empty($sheetName) \|\| $this->sheets[$sheetName]->getFinalized()) {
445			return;
446			}
447			$this->initializeSheet($sheetName);
448			/** @var Sheet $sheet */
449			$sheet = &$this->sheets[$sheetName];
450			$startCell = ExcelHelper::xlsCell($startCellRow, $startCellColumn);
451			$endCell = ExcelHelper::xlsCell($endCellRow, $endCellColumn);
452			$sheet->setMergeCells($startCell.":".$endCell);
453			}
454
455			/**
456			* @param array $data
457			* @param string $sheetName
458			* @param array $headerTypes
459			*/
460			public function writeSheet(array $data, $sheetName = '', array $headerTypes = [])
461			{
462			$sheetName = empty($sheetName) ? 'Sheet1' : $sheetName;
463			$data = empty($data) ? [['']] : $data;
464			if (!empty($headerTypes)) {
465			$this->writeSheetHeader($sheetName, $headerTypes);
466			}
467			foreach ($data as $i => $row) {
468			$this->writeSheetRow($sheetName, $row);
469			}
470			$this->finalizeSheet($sheetName);
471			}
472
473			/**
474			* @param Writer $file
475			* @param int $rowNumber
476			* @param int $columnNumber
477			* @param mixed $value
478			* @param $cellIndex
479			*/
480			protected function writeCell(Writer $file, $rowNumber, $columnNumber, $value, $cellIndex)
481			{
482			$cellType = $this->cellTypes[$cellIndex];
483			$cellName = ExcelHelper::xlsCell($rowNumber, $columnNumber);
484			$cell = $this->sheetXml->getCell($cellName, $cellIndex, $cellType, $value);
485			if ($cell === false) {
486			$file->write(
487			'<c r="'.$cellName.'" s="'.$cellIndex.'" t="s"><v>'.ExcelHelper::xmlspecialchars(
488			$this->setSharedString($value)
489			).'</v></c>'
490			);
491			} else {
492			$file->write($cell);
493			}
494			}
495
496			/**
497			* @return string
498			*/
499			protected function writeStylesXML()
500			{
501			$temporaryFilename = $this->tempFilename();
502			$file = new Writer($temporaryFilename);
503			$styles = new Styles();
504			$styles->setCellFormats($this->cellFormats);
505			$file->write($styles->buildStylesXML());
506
507			return $temporaryFilename;
508			}
509
510			/**
511			* @param $v
512			*
513			* @return int\|mixed
514			*/
515			protected function setSharedString($v)
516			{
517			if (isset($this->sharedStrings[$v])) {
518			$stringValue = $this->sharedStrings[$v];
519			} else {
520			$stringValue = count($this->sharedStrings);
521			$this->sharedStrings[$v] = $stringValue;
522			}
523			$this->sharedStringCount++;
524
525			return $stringValue;
526			}
527
528			/**
529			* @return string
530			*/
531			protected function writeSharedStringsXML()
532			{
533			$tempFilename = $this->tempFilename();
534			$file = new Writer($tempFilename);
535			$sharedStrings = new SharedStrings($this->sharedStringCount, $this->sharedStrings);
536			$file->write($sharedStrings->buildSharedStringsXML());
537			$file->close();
538
539			return $tempFilename;
540			}
541
542			/**
543			* @param \ZipArchive $zip
544			* @param string $filename
545			*/
546			private function checkAndUnlink(\ZipArchive $zip, $filename)
547			{
548			if (file_exists($filename) && is_writable($filename)) {
549			unlink($filename);
550			}
551			if (empty($this->sheets) \|\| !$zip->open($filename, \ZipArchive::CREATE)) {
552			throw new \RuntimeException(
553			"Error in ".__CLASS__."::".__FUNCTION__.", no worksheets defined or unable to create zip."
554			);
555			}
556			}
557			}
558

ellumilel / php-excel-writer

GitHub Access Token became invalid

Push — master ( d28d59...eb929a )

ExcelWriter::determineCellType() C

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like