ExcelWriter::xlsCell() - Code Metrics - Inspection of "fix - datepart writer" - ellumilel/php-excel-writer - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( c341c3...28bf7f )

by Denis

created 2016-09-01 14:04 UTC

ExcelWriter::xlsCell() A

↳ Parent: Project

Complexity

Conditions	2
Paths	2

Size

Total Lines	9
Code Lines	5

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	1	Features	0

Metric	Value
c	2
b	1
f	0
dl	0
loc	9
rs	9.6666
cc	2
eloc	5
nc	2
nop	2

<?php
namespace Ellumilel;

use Ellumilel\DocProps\App;
use Ellumilel\DocProps\Core;
use Ellumilel\Helpers\PHPExcelHelper;
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 string */
    protected $author ='Unknown Author';
    /** @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->author = $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);
        }
        if (file_exists($filename) && is_writable($filename)) {
            unlink($filename);
        }

        if (empty($this->sheets) || !$zip->open($filename, \ZipArchive::CREATE)) {
            self::log("Error in ".__CLASS__."::".__FUNCTION__.", no worksheets defined or unable to create zip.");
            return;
        }

        $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 = PHPExcelHelper::xlsCell(PHPExcelHelper::EXCEL_MAX_ROW, PHPExcelHelper::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' || preg_match("/0/", $cellFormat)) {
            return 'numeric';
        }
        if (preg_match("/[H]{1,2}:[M]{1,2}/", $cellFormat) || preg_match("/[M]{1,2}:[S]{1,2}/", $cellFormat)) {
            return 'datetime';
        }
        if (preg_match("/[YY]{2,4}/", $cellFormat)
            || preg_match("/[D]{1,2}/", $cellFormat)
            || preg_match("/[M]{1,2}/", $cellFormat)
        ) {
            return 'date';
        }
        if (preg_match("/$/", $cellFormat)) {
            return 'currency';
        }
        if (preg_match("/%/", $cellFormat)) {
            return 'percent';
        }

        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 string $cellFormat
     *
     * @return int|mixed
     */
    private function addCellFormat($cellFormat)
    {
        $cellFormat = strtoupper($this->getCellFormat($cellFormat));
        $position = array_search($cellFormat, $this->cellFormats, $strict = true);
        if ($position === false) {
            $position = count($this->cellFormats);
            $this->cellFormats[] = $this->escapeCellFormat($cellFormat);
            $this->cellTypes[] = $this->determineCellType($cellFormat);
        }

        return $position;
    }

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

        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, 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);
        $columnCount = 0;
        /** @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).'">'
        );

        $sheetColumns = $sheet->getColumns();
        foreach ($row as $k => $v) {
            $this->writeCell($sheet->getWriter(), $sheet->getRowCount(), $columnCount, $v, $sheetColumns[$columnCount]);
            $columnCount++;
        }
        $sheet->getWriter()->write('</row>');
        $sheet->increaseRowCount();
        $this->currentSheet = $sheetName;
    }

    /**
     * @param string $sheetName
     */
    protected function finalizeSheet($sheetName)
    {
        if (empty($sheetName) || $this->sheets[$sheetName]->getFinalized()) {
            return;
        }
        /** @var Sheet $sheet */
        $sheet = &$this->sheets[$sheetName];
        $sheet->getWriter()->write('</sheetData>');
        $mergeCells = $sheet->getMergeCells();
        if (!empty($mergeCells)) {
            $sheet->getWriter()->write($this->sheetXml->getMergeCells($mergeCells));
        }
        $sheet->getWriter()->write($this->sheetXml->getPrintOptions());
        $sheet->getWriter()->write($this->sheetXml->getPageMargins());
        $sheet->getWriter()->write($this->sheetXml->getPageSetup());
        $sheet->getWriter()->write($this->sheetXml->getHeaderFooter());
        $sheet->getWriter()->write('</worksheet>');
        $maxCell = PHPExcelHelper::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 = PHPExcelHelper::xlsCell($startCellRow, $startCellColumn);
        $endCell = PHPExcelHelper::xlsCell($endCellRow, $endCellColumn);
        $sheet->setMergeCells($startCell.":".$endCell);
    }

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

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

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

        return $temporaryFilename;
    }

    /**
     * @param $v
     *
     * @return int|mixed
     */
    private 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
     */
    private function writeSharedStringsXML()
    {
        $tempFilename = $this->tempFilename();
        $file = new Writer($tempFilename);
        $sharedStrings = new SharedStrings($this->sharedStringCount, $this->sharedStrings);
        $file->write($sharedStrings->buildSharedStringsXML());
        $file->close();

        return $tempFilename;
    }

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


1			<?php
2			namespace Ellumilel;
3
4			use Ellumilel\DocProps\App;
5			use Ellumilel\DocProps\Core;
6			use Ellumilel\Helpers\PHPExcelHelper;
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 string */
21			protected $author ='Unknown Author';
22			/** @var array */
23			protected $sheets = [];
24			/** @var array */
25			protected $sharedStrings = [];
26			/** @var int */
27			protected $sharedStringCount = 0;
28			/** @var array */
29			protected $tempFiles = [];
30			/** @var array */
31			protected $cellFormats = [];
32			/** @var array */
33			protected $cellTypes = [];
34			/** @var string */
35			protected $currentSheet = '';
36			/** @var null */
37			protected $tmpDir = 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->author = $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			* Return tmpFileName
95			* @return string
96			*/
97			protected function tempFilename()
98			{
99			$tmpDir = is_null($this->tmpDir) ? sys_get_temp_dir() : $this->tmpDir;
100			$filename = tempnam($tmpDir, "excelWriter_");
101			$this->tempFiles[] = $filename;
102
103			return $filename;
104			}
105
106			public function writeToStdOut()
107			{
108			$tempFile = $this->tempFilename();
109			$this->writeToFile($tempFile);
110			readfile($tempFile);
111			}
112
113			/**
114			* @return string
115			*/
116			public function writeToString()
117			{
118			$tempFile = $this->tempFilename();
119			$this->writeToFile($tempFile);
120			$string = file_get_contents($tempFile);
121
122			return $string;
123			}
124
125			/**
126			* @param string $filename
127			*/
128			public function writeToFile($filename)
129			{
130			$zip = new \ZipArchive();
131			foreach ($this->sheets as $sheetName => $sheet) {
132			$this->finalizeSheet($sheetName);
133			}
134			if (file_exists($filename) && is_writable($filename)) {
135			unlink($filename);
136			}
137
138			if (empty($this->sheets) \|\| !$zip->open($filename, \ZipArchive::CREATE)) {
139			self::log("Error in ".__CLASS__."::".__FUNCTION__.", no worksheets defined or unable to create zip.");
140			return;
141			}
142
143			$this->workbook->setSheet($this->sheets);
144
145			$contentTypes = new ContentTypes(!empty($this->sharedStrings));
146			$contentTypes->setSheet($this->sheets);
147
148			$rel = new Relationships(!empty($this->sharedStrings));
149			$rel->setSheet($this->sheets);
150
151			$zip->addEmptyDir("docProps/");
152			$zip->addFromString("docProps/app.xml", $this->app->buildAppXML());
153			$zip->addFromString("docProps/core.xml", $this->core->buildCoreXML());
154			$zip->addEmptyDir("_rels/");
155			$zip->addFromString("_rels/.rels", $rel->buildRelationshipsXML());
156			$zip->addEmptyDir("xl/worksheets/");
157			foreach ($this->sheets as $sheet) {
158			/** @var Sheet $sheet */
159			$zip->addFile($sheet->getFilename(), "xl/worksheets/".$sheet->getXmlName());
160			}
161			if (!empty($this->sharedStrings)) {
162			$zip->addFile(
163			$this->writeSharedStringsXML(),
164			"xl/sharedStrings.xml"
165			);
166			}
167			$zip->addFromString("xl/workbook.xml", $this->workbook->buildWorkbookXML());
168			$zip->addFile(
169			$this->writeStylesXML(),
170			"xl/styles.xml"
171			);
172			$zip->addFromString("[Content_Types].xml", $contentTypes->buildContentTypesXML());
173			$zip->addEmptyDir("xl/_rels/");
174			$zip->addFromString("xl/_rels/workbook.xml.rels", $rel->buildWorkbookRelationshipsXML());
175			$zip->close();
176			}
177
178			/**
179			* @param string $sheetName
180			*/
181			protected function initializeSheet($sheetName)
182			{
183			if ($this->currentSheet == $sheetName \|\| isset($this->sheets[$sheetName])) {
184			return;
185			}
186			$sheetFilename = $this->tempFilename();
187			$sheetXmlName = 'sheet' . (count($this->sheets) + 1).".xml";
188			$sheetObj = new Sheet();
189			$sheetObj
190			->setFilename($sheetFilename)
191			->setSheetName($sheetName)
192			->setXmlName($sheetXmlName)
193			->setWriter(new Writer($sheetFilename))
194			;
195			$this->sheets[$sheetName] = $sheetObj;
196			/** @var Sheet $sheet */
197			$sheet = &$this->sheets[$sheetName];
198			$selectedTab = count($this->sheets) == 1 ? 'true' : 'false';
199			$maxCell = PHPExcelHelper::xlsCell(PHPExcelHelper::EXCEL_MAX_ROW, PHPExcelHelper::EXCEL_MAX_COL);
200
201			$sheet->getWriter()->write($this->sheetXml->getXml());
202			$sheet->getWriter()->write($this->sheetXml->getWorksheet());
203			$sheet->getWriter()->write($this->sheetXml->getSheetPr());
204
205			$sheet->setMaxCellTagStart($sheet->getWriter()->fTell());
206			$sheet->getWriter()->write($this->sheetXml->getDimension($maxCell));
207			$sheet->setMaxCellTagEnd($sheet->getWriter()->fTell());
208			$sheet->getWriter()->write($this->sheetXml->getSheetViews($selectedTab));
209			$sheet->getWriter()->write($this->sheetXml->getCools());
210			$sheet->getWriter()->write('<sheetData>');
211			}
212
213			/**
214			* @param $cellFormat
215			*
216			* @return string
217			*/
218			private function determineCellType($cellFormat)
219			{
220			$cellFormat = str_replace("[RED]", "", $cellFormat);
221			if ($cellFormat == 'GENERAL') {
222			return 'string';
223			}
224			if ($cellFormat == '0' \|\| preg_match("/0/", $cellFormat)) {
225			return 'numeric';
226			}
227			if (preg_match("/[H]{1,2}:[M]{1,2}/", $cellFormat) \|\| preg_match("/[M]{1,2}:[S]{1,2}/", $cellFormat)) {
228			return 'datetime';
229			}
230			if (preg_match("/[YY]{2,4}/", $cellFormat)
231			\|\| preg_match("/[D]{1,2}/", $cellFormat)
232			\|\| preg_match("/[M]{1,2}/", $cellFormat)
233			) {
234			return 'date';
235			}
236			if (preg_match("/$/", $cellFormat)) {
237			return 'currency';
238			}
239			if (preg_match("/%/", $cellFormat)) {
240			return 'percent';
241			}
242
243			return 'string';
244			}
245
246			/**
247			* @todo check escaping
248			*
249			* @param $cellFormat
250			*
251			* @return string
252			*/
253			private function escapeCellFormat($cellFormat)
254			{
255			$ignoreUntil = '';
256			$escaped = '';
257			for ($i = 0, $ix = strlen($cellFormat); $i < $ix; $i++) {
258			$c = $cellFormat[$i];
259			if ($ignoreUntil == '' && $c == '[') {
260			$ignoreUntil = ']';
261			} else {
262			if ($ignoreUntil == '' && $c == '"') {
263			$ignoreUntil = '"';
264			} else {
265			if ($ignoreUntil == $c) {
266			$ignoreUntil = '';
267			}
268			}
269			}
270			if ($ignoreUntil == '' &&
271			($c == ' ' \|\| $c == '-' \|\| $c == '(' \|\| $c == ')') &&
272			($i == 0 \|\| $cellFormat[$i - 1] != '_')
273			) {
274			$escaped .= "\\".$c;
275			} else {
276			$escaped .= $c;
277			}
278			}
279
280			return $escaped;
281			}
282
283			/**
284			* backwards compatibility
285			*
286			* @param string $cellFormat
287			*
288			* @return int\|mixed
289			*/
290			private function addCellFormat($cellFormat)
291			{
292			$cellFormat = strtoupper($this->getCellFormat($cellFormat));
293			$position = array_search($cellFormat, $this->cellFormats, $strict = true);
294			if ($position === false) {
295			$position = count($this->cellFormats);
296			$this->cellFormats[] = $this->escapeCellFormat($cellFormat);
297			$this->cellTypes[] = $this->determineCellType($cellFormat);
298			}
299
300			return $position;
301			}
302
303			/**
304			* @param string $cellFormat
305			*
306			* @return string
307			*/
308			private function getCellFormat($cellFormat)
309			{
310			switch ($cellFormat) {
311			case 'string':
312			$cellFormat = 'GENERAL';
313			break;
314			case 'integer':
315			$cellFormat = '0';
316			break;
317			case 'date':
318			$cellFormat = 'YYYY-MM-DD';
319			break;
320			case 'datetime':
321			$cellFormat = 'YYYY-MM-DD HH:MM:SS';
322			break;
323			case 'dollar':
324			$cellFormat = '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00';
325			break;
326			case 'money':
327			$cellFormat = '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00';
328			break;
329			case 'euro':
330			$cellFormat = '#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]';
331			break;
332			case 'NN':
333			$cellFormat = 'DDD';
334			break;
335			case 'NNN':
336			$cellFormat = 'DDDD';
337			break;
338			case 'NNNN':
339			$cellFormat = 'DDDD", "';
340			break;
341			}
342
343			return $cellFormat;
344			}
345
346			/**
347			* @param string $sheetName
348			* @param array $headerTypes
349			* @param bool $suppressRow
350			*/
351			public function writeSheetHeader($sheetName, array $headerTypes, $suppressRow = false)
352			{
353			if (empty($sheetName) \|\| empty($headerTypes) \|\| !empty($this->sheets[$sheetName])) {
354			return;
355			}
356			$this->initializeSheet($sheetName);
357			/** @var Sheet $sheet */
358			$sheet = &$this->sheets[$sheetName];
359			$sheet->setColumns([]);
360			foreach ($headerTypes as $val) {
361			$sheet->setColumn($this->addCellFormat($val));
362			}
363			if (!$suppressRow) {
364			$this->writeRowHeader($sheet, array_keys($headerTypes));
365			$sheet->increaseRowCount();
366			}
367			$this->currentSheet = $sheetName;
368			}
369
370			/**
371			* @param Sheet $sheet
372			* @param array $headerRow
373			*/
374			private function writeRowHeader(Sheet $sheet, $headerRow)
375			{
376			$sheet->getWriter()->write(
377			'<row collapsed="false" customFormat="false"
378			customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="'.(1).'">'
379			);
380			foreach ($headerRow as $k => $v) {
381			$this->writeCell($sheet->getWriter(), 0, $k, $v, 0);
382			}
383			$sheet->getWriter()->write('</row>');
384			}
385
386			/**
387			* @param string $sheetName
388			* @param array $row
389			*/
390			public function writeSheetRow($sheetName, array $row)
391			{
392			if (empty($sheetName) \|\| empty($row)) {
393			return;
394			}
395			$this->initializeSheet($sheetName);
396			$columnCount = 0;
397			/** @var Sheet $sheet */
398			$sheet = &$this->sheets[$sheetName];
399			$columns = $sheet->getColumns();
400			if (empty($columns)) {
401			$sheet->setColumns(array_fill($from = 0, $until = count($row), '0'));
402			}
403			$sheet->getWriter()->write(
404			'<row collapsed="false" customFormat="false" customHeight="false"
405			hidden="false" ht="12.1" outlineLevel="0" r="'.($sheet->getRowCount() + 1).'">'
406			);
407
408			$sheetColumns = $sheet->getColumns();
409			foreach ($row as $k => $v) {
410			$this->writeCell($sheet->getWriter(), $sheet->getRowCount(), $columnCount, $v, $sheetColumns[$columnCount]);
411			$columnCount++;
412			}
413			$sheet->getWriter()->write('</row>');
414			$sheet->increaseRowCount();
415			$this->currentSheet = $sheetName;
416			}
417
418			/**
419			* @param string $sheetName
420			*/
421			protected function finalizeSheet($sheetName)
422			{
423			if (empty($sheetName) \|\| $this->sheets[$sheetName]->getFinalized()) {
424			return;
425			}
426			/** @var Sheet $sheet */
427			$sheet = &$this->sheets[$sheetName];
428			$sheet->getWriter()->write('</sheetData>');
429			$mergeCells = $sheet->getMergeCells();
430			if (!empty($mergeCells)) {
431			$sheet->getWriter()->write($this->sheetXml->getMergeCells($mergeCells));
432			}
433			$sheet->getWriter()->write($this->sheetXml->getPrintOptions());
434			$sheet->getWriter()->write($this->sheetXml->getPageMargins());
435			$sheet->getWriter()->write($this->sheetXml->getPageSetup());
436			$sheet->getWriter()->write($this->sheetXml->getHeaderFooter());
437			$sheet->getWriter()->write('</worksheet>');
438			$maxCell = PHPExcelHelper::xlsCell($sheet->getRowCount() - 1, count($sheet->getColumns()) - 1);
439			$maxCellTag = $this->sheetXml->getDimension($maxCell);
440			$paddingLength = $sheet->getMaxCellTagEnd() - $sheet->getMaxCellTagStart() - strlen($maxCellTag);
441			$sheet->getWriter()->fSeek($sheet->getMaxCellTagStart());
442			$sheet->getWriter()->write($maxCellTag.str_repeat(" ", $paddingLength));
443			$sheet->getWriter()->close();
444			$sheet->setFinalized(true);
445			}
446
447			/**
448			* @param string $sheetName
449			* @param int $startCellRow
450			* @param int $startCellColumn
451			* @param int $endCellRow
452			* @param int $endCellColumn
453			*/
454			public function markMergedCell($sheetName, $startCellRow, $startCellColumn, $endCellRow, $endCellColumn)
455			{
456			if (empty($sheetName) \|\| $this->sheets[$sheetName]->getFinalized()) {
457			return;
458			}
459			$this->initializeSheet($sheetName);
460			/** @var Sheet $sheet */
461			$sheet = &$this->sheets[$sheetName];
462			$startCell = PHPExcelHelper::xlsCell($startCellRow, $startCellColumn);
463			$endCell = PHPExcelHelper::xlsCell($endCellRow, $endCellColumn);
464			$sheet->setMergeCells($startCell.":".$endCell);
465			}
466
467			/**
468			* @param array $data
469			* @param string $sheetName
470			* @param array $headerTypes
471			*/
472			public function writeSheet(array $data, $sheetName = '', array $headerTypes = [])
473			{
474			$sheetName = empty($sheetName) ? 'Sheet1' : $sheetName;
475			$data = empty($data) ? [['']] : $data;
476			if (!empty($headerTypes)) {
477			$this->writeSheetHeader($sheetName, $headerTypes);
478			}
479			foreach ($data as $i => $row) {
480			$this->writeSheetRow($sheetName, $row);
481			}
482			$this->finalizeSheet($sheetName);
483			}
484
485			/**
486			* @param Writer $file
487			* @param $rowNumber
488			* @param $columnNumber
489			* @param $value
490			* @param $cellIndex
491			*/
492			protected function writeCell(
493			Writer $file,
494			$rowNumber,
495			$columnNumber,
496			$value,
497			$cellIndex
498			) {
499			$cellType = $this->cellTypes[$cellIndex];
500			$cellName = PHPExcelHelper::xlsCell($rowNumber, $columnNumber);
501			if (!is_scalar($value) \|\| $value === '') {
502			$file->write('<c r="'.$cellName.'" s="'.$cellIndex.'"/>');
503			} elseif (is_string($value) && $value{0} == '=') {
504			$file->write(
505			sprintf(
506			'<c r="%s" s="%s" t="s"><f>%s</f></c>',
507			$cellName,
508			$cellIndex,
509			PHPExcelHelper::xmlspecialchars($value)
510			)
511			);
512			} elseif ($cellType == 'date') {
513			$file->write(
514			sprintf(
515			'<c r="%s" s="%s" t="n"><v>%s</v></c>',
516			$cellName,
517			$cellIndex,
518			PHPExcelHelper::convertDateTime($value)
519			)
520			);
521			} elseif ($cellType == 'datetime') {
522			$file->write(
523			'<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.PHPExcelHelper::convertDateTime($value).'</v></c>'
524			);
525			} elseif ($cellType == 'currency' \|\| $cellType == 'percent' \|\| $cellType == 'numeric') {
526			$file->write(
527			'<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.PHPExcelHelper::xmlspecialchars($value).'</v></c>'
528			);
529			} else {
530			if (!is_string($value)) {
531			$file->write('<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.($value * 1).'</v></c>');
532			} else {
533			if ($value{0} != '0' && $value{0} != '+' && filter_var(
534			$value,
535			FILTER_VALIDATE_INT,
536			['options' => ['max_range' => PHPExcelHelper::EXCEL_MAX_RANGE]]
537			)) {
538			$file->write('<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.($value * 1).'</v></c>');
539			} else {
540			$file->write(
541			'<c r="'.$cellName.'" s="'.$cellIndex.'" t="s"><v>'.PHPExcelHelper::xmlspecialchars(
542			$this->setSharedString($value)
543			).'</v></c>'
544			);
545			}
546			}
547			}
548			}
549
550			/**
551			* @return string
552			*/
553			protected function writeStylesXML()
554			{
555			$temporaryFilename = $this->tempFilename();
556			$file = new Writer($temporaryFilename);
557			$styles = new Styles();
558			$styles->setCellFormats($this->cellFormats);
559			$file->write($styles->buildStylesXML());
560
561			return $temporaryFilename;
562			}
563
564			/**
565			* @param $v
566			*
567			* @return int\|mixed
568			*/
569			private function setSharedString($v)
570			{
571			if (isset($this->sharedStrings[$v])) {
572			$stringValue = $this->sharedStrings[$v];
573			} else {
574			$stringValue = count($this->sharedStrings);
575			$this->sharedStrings[$v] = $stringValue;
576			}
577			$this->sharedStringCount++;
578
579			return $stringValue;
580			}
581
582			/**
583			* @return string
584			*/
585			private function writeSharedStringsXML()
586			{
587			$tempFilename = $this->tempFilename();
588			$file = new Writer($tempFilename);
589			$sharedStrings = new SharedStrings($this->sharedStringCount, $this->sharedStrings);
590			$file->write($sharedStrings->buildSharedStringsXML());
591			$file->close();
592
593			return $tempFilename;
594			}
595
596			/**
597			* @param $string
598			*/
599			public static function log($string)
600			{
601			file_put_contents(
602			"php://stderr",
603			date("Y-m-d H:i:s:").rtrim(is_array($string) ? json_encode($string) : $string)."\n"
604			);
605			}
606			}
607

ellumilel / php-excel-writer

GitHub Access Token became invalid

Push — master ( c341c3...28bf7f )

ExcelWriter::xlsCell() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like