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

Passed

Push — master ( 28bf7f...70b1b7 )

by Denis

created 2016-09-01 14:48 UTC

ExcelWriter::writeToFile() B

↳ Parent: ExcelWriter

Complexity

Conditions	4
Paths	8

Size

Total Lines	41
Code Lines	30

Duplication

Lines	0
Ratio	0 %

Importance

Changes	4
Bugs	3	Features	0

Metric	Value
c	4
b	3
f	0
dl	0
loc	41
rs	8.5806
cc	4
eloc	30
nc	8
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)
    {
        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 \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			switch ($cellFormat) {
316			case 'string':
317			$cellFormat = 'GENERAL';
318			break;
319			case 'integer':
320			$cellFormat = '0';
321			break;
322			case 'date':
323			$cellFormat = 'YYYY-MM-DD';
324			break;
325			case 'datetime':
326			$cellFormat = 'YYYY-MM-DD HH:MM:SS';
327			break;
328			case 'dollar':
329			$cellFormat = '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00';
330			break;
331			case 'money':
332			$cellFormat = '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00';
333			break;
334			case 'euro':
335			$cellFormat = '#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]';
336			break;
337			case 'NN':
338			$cellFormat = 'DDD';
339			break;
340			case 'NNN':
341			$cellFormat = 'DDDD';
342			break;
343			case 'NNNN':
344			$cellFormat = 'DDDD", "';
345			break;
346			}
347
348			return $cellFormat;
349			}
350
351			/**
352			* @param string $sheetName
353			* @param array $headerTypes
354			* @param bool $suppressRow
355			*/
356			public function writeSheetHeader($sheetName, array $headerTypes, $suppressRow = false)
357			{
358			if (empty($sheetName) \|\| empty($headerTypes) \|\| !empty($this->sheets[$sheetName])) {
359			return;
360			}
361			$this->initializeSheet($sheetName);
362			/** @var Sheet $sheet */
363			$sheet = &$this->sheets[$sheetName];
364			$sheet->setColumns([]);
365			foreach ($headerTypes as $val) {
366			$sheet->setColumn($this->addCellFormat($val));
367			}
368			if (!$suppressRow) {
369			$this->writeRowHeader($sheet, array_keys($headerTypes));
370			$sheet->increaseRowCount();
371			}
372			$this->currentSheet = $sheetName;
373			}
374
375			/**
376			* @param Sheet $sheet
377			* @param array $headerRow
378			*/
379			private function writeRowHeader(Sheet $sheet, $headerRow)
380			{
381			$sheet->getWriter()->write(
382			'<row collapsed="false" customFormat="false"
383			customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="'.(1).'">'
384			);
385			foreach ($headerRow as $k => $v) {
386			$this->writeCell($sheet->getWriter(), 0, $k, $v, 0);
387			}
388			$sheet->getWriter()->write('</row>');
389			}
390
391			/**
392			* @param string $sheetName
393			* @param array $row
394			*/
395			public function writeSheetRow($sheetName, array $row)
396			{
397			if (empty($sheetName) \|\| empty($row)) {
398			return;
399			}
400			$this->initializeSheet($sheetName);
401			$columnCount = 0;
402			/** @var Sheet $sheet */
403			$sheet = &$this->sheets[$sheetName];
404			$columns = $sheet->getColumns();
405			if (empty($columns)) {
406			$sheet->setColumns(array_fill($from = 0, $until = count($row), '0'));
407			}
408			$sheet->getWriter()->write(
409			'<row collapsed="false" customFormat="false" customHeight="false"
410			hidden="false" ht="12.1" outlineLevel="0" r="'.($sheet->getRowCount() + 1).'">'
411			);
412
413			$sheetColumns = $sheet->getColumns();
414			foreach ($row as $k => $v) {
415			$this->writeCell($sheet->getWriter(), $sheet->getRowCount(), $columnCount, $v, $sheetColumns[$columnCount]);
416			$columnCount++;
417			}
418			$sheet->getWriter()->write('</row>');
419			$sheet->increaseRowCount();
420			$this->currentSheet = $sheetName;
421			}
422
423			/**
424			* @param string $sheetName
425			*/
426			protected function finalizeSheet($sheetName)
427			{
428			if (empty($sheetName) \|\| $this->sheets[$sheetName]->getFinalized()) {
429			return;
430			}
431			/** @var Sheet $sheet */
432			$sheet = &$this->sheets[$sheetName];
433			$sheet->getWriter()->write('</sheetData>');
434			$mergeCells = $sheet->getMergeCells();
435			if (!empty($mergeCells)) {
436			$sheet->getWriter()->write($this->sheetXml->getMergeCells($mergeCells));
437			}
438			$sheet->getWriter()->write($this->sheetXml->getPrintOptions());
439			$sheet->getWriter()->write($this->sheetXml->getPageMargins());
440			$sheet->getWriter()->write($this->sheetXml->getPageSetup());
441			$sheet->getWriter()->write($this->sheetXml->getHeaderFooter());
442			$sheet->getWriter()->write('</worksheet>');
443			$maxCell = PHPExcelHelper::xlsCell($sheet->getRowCount() - 1, count($sheet->getColumns()) - 1);
444			$maxCellTag = $this->sheetXml->getDimension($maxCell);
445			$paddingLength = $sheet->getMaxCellTagEnd() - $sheet->getMaxCellTagStart() - strlen($maxCellTag);
446			$sheet->getWriter()->fSeek($sheet->getMaxCellTagStart());
447			$sheet->getWriter()->write($maxCellTag.str_repeat(" ", $paddingLength));
448			$sheet->getWriter()->close();
449			$sheet->setFinalized(true);
450			}
451
452			/**
453			* @param string $sheetName
454			* @param int $startCellRow
455			* @param int $startCellColumn
456			* @param int $endCellRow
457			* @param int $endCellColumn
458			*/
459			public function markMergedCell($sheetName, $startCellRow, $startCellColumn, $endCellRow, $endCellColumn)
460			{
461			if (empty($sheetName) \|\| $this->sheets[$sheetName]->getFinalized()) {
462			return;
463			}
464			$this->initializeSheet($sheetName);
465			/** @var Sheet $sheet */
466			$sheet = &$this->sheets[$sheetName];
467			$startCell = PHPExcelHelper::xlsCell($startCellRow, $startCellColumn);
468			$endCell = PHPExcelHelper::xlsCell($endCellRow, $endCellColumn);
469			$sheet->setMergeCells($startCell.":".$endCell);
470			}
471
472			/**
473			* @param array $data
474			* @param string $sheetName
475			* @param array $headerTypes
476			*/
477			public function writeSheet(array $data, $sheetName = '', array $headerTypes = [])
478			{
479			$sheetName = empty($sheetName) ? 'Sheet1' : $sheetName;
480			$data = empty($data) ? [['']] : $data;
481			if (!empty($headerTypes)) {
482			$this->writeSheetHeader($sheetName, $headerTypes);
483			}
484			foreach ($data as $i => $row) {
485			$this->writeSheetRow($sheetName, $row);
486			}
487			$this->finalizeSheet($sheetName);
488			}
489
490			/**
491			* @param Writer $file
492			* @param $rowNumber
493			* @param $columnNumber
494			* @param $value
495			* @param $cellIndex
496			*/
497			protected function writeCell(
498			Writer $file,
499			$rowNumber,
500			$columnNumber,
501			$value,
502			$cellIndex
503			) {
504			$cellType = $this->cellTypes[$cellIndex];
505			$cellName = PHPExcelHelper::xlsCell($rowNumber, $columnNumber);
506			if (!is_scalar($value) \|\| $value === '') {
507			$file->write('<c r="'.$cellName.'" s="'.$cellIndex.'"/>');
508			} elseif (is_string($value) && $value{0} == '=') {
509			$file->write(
510			sprintf(
511			'<c r="%s" s="%s" t="s"><f>%s</f></c>',
512			$cellName,
513			$cellIndex,
514			PHPExcelHelper::xmlspecialchars($value)
515			)
516			);
517			} elseif ($cellType == 'date') {
518			$file->write(
519			sprintf(
520			'<c r="%s" s="%s" t="n"><v>%s</v></c>',
521			$cellName,
522			$cellIndex,
523			PHPExcelHelper::convertDateTime($value)
524			)
525			);
526			} elseif ($cellType == 'datetime') {
527			$file->write(
528			'<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.PHPExcelHelper::convertDateTime($value).'</v></c>'
529			);
530			} elseif ($cellType == 'currency' \|\| $cellType == 'percent' \|\| $cellType == 'numeric') {
531			$file->write(
532			'<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.PHPExcelHelper::xmlspecialchars($value).'</v></c>'
533			);
534			} else {
535			if (!is_string($value)) {
536			$file->write('<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.($value * 1).'</v></c>');
537			} else {
538			if ($value{0} != '0' && $value{0} != '+' && filter_var(
539			$value,
540			FILTER_VALIDATE_INT,
541			['options' => ['max_range' => PHPExcelHelper::EXCEL_MAX_RANGE]]
542			)) {
543			$file->write('<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.($value * 1).'</v></c>');
544			} else {
545			$file->write(
546			'<c r="'.$cellName.'" s="'.$cellIndex.'" t="s"><v>'.PHPExcelHelper::xmlspecialchars(
547			$this->setSharedString($value)
548			).'</v></c>'
549			);
550			}
551			}
552			}
553			}
554
555			/**
556			* @return string
557			*/
558			protected function writeStylesXML()
559			{
560			$temporaryFilename = $this->tempFilename();
561			$file = new Writer($temporaryFilename);
562			$styles = new Styles();
563			$styles->setCellFormats($this->cellFormats);
564			$file->write($styles->buildStylesXML());
565
566			return $temporaryFilename;
567			}
568
569			/**
570			* @param $v
571			*
572			* @return int\|mixed
573			*/
574			private function setSharedString($v)
575			{
576			if (isset($this->sharedStrings[$v])) {
577			$stringValue = $this->sharedStrings[$v];
578			} else {
579			$stringValue = count($this->sharedStrings);
580			$this->sharedStrings[$v] = $stringValue;
581			}
582			$this->sharedStringCount++;
583
584			return $stringValue;
585			}
586
587			/**
588			* @return string
589			*/
590			private function writeSharedStringsXML()
591			{
592			$tempFilename = $this->tempFilename();
593			$file = new Writer($tempFilename);
594			$sharedStrings = new SharedStrings($this->sharedStringCount, $this->sharedStrings);
595			$file->write($sharedStrings->buildSharedStringsXML());
596			$file->close();
597
598			return $tempFilename;
599			}
600
601			/**
602			* @param \ZipArchive $zip
603			* @param string $filename
604			*/
605			private function checkAndUnlink(\ZipArchive $zip, $filename)
606			{
607			if (file_exists($filename) && is_writable($filename)) {
608			unlink($filename);
609			}
610
611			if (empty($this->sheets) \|\| !$zip->open($filename, \ZipArchive::CREATE)) {
612			throw new \RuntimeException(
613			"Error in ".__CLASS__."::".__FUNCTION__.", no worksheets defined or unable to create zip."
614			);
615			}
616			}
617			}
618

ellumilel / php-excel-writer

GitHub Access Token became invalid

Push — master ( 28bf7f...70b1b7 )

ExcelWriter::writeToFile() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like