ExcelWriter::getCellFormat() - Code Metrics - Inspection of "fix - determine cell type" - ellumilel/php-excel-writer - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 70b1b7...44810d )

by Denis

created 2016-09-01 15:02 UTC

ExcelWriter::getCellFormat() A

↳ Parent: ExcelWriter

Complexity

Conditions	2
Paths	2

Size

Total Lines	22
Code Lines	16

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	2	Features	0

Metric	Value
c	2
b	2
f	0
dl	0
loc	22
rs	9.2
cc	2
eloc	16
nc	2
nop	1

<?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);
        }
        $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 = 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') {
            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 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)
    {
        $format = [
            'GENERAL' => 'string',
            '0' => 'integer',
            'YYYY-MM-DD' => 'date',
            'YYYY-MM-DD HH:MM:SS' => 'datetime',
            '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00' => 'dollar',
            '#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]' => 'euro',
            'DDD' => 'NN',
            'DDDD' => 'NNN',
            'DDDD", "' => 'NNNN',
        ];

        if ($cellFormat == 'money') {
            $cellFormat = '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00';
        } else {
            $cellFormat = array_search($cellFormat, $format);
        }

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

ellumilel / php-excel-writer

GitHub Access Token became invalid

Push — master ( 70b1b7...44810d )

ExcelWriter::getCellFormat() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like