ExcelWriter::markMergedCell() - Code Metrics - Inspection of "float type, float with" - ellumilel/php-excel-writer - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( dd6037...170e4d )

by Denis

created 2016-09-05 10:56 UTC

ExcelWriter::markMergedCell() A

↳ Parent: ExcelWriter

Complexity

Conditions	3
Paths	2

Size

Total Lines	12
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	1

Metric	Value
c	1
b	0
f	1
dl	0
loc	12
rs	9.4285
cc	3
eloc	8
nc	2
nop	5

<?php
namespace Ellumilel;

use Ellumilel\DocProps\App;
use Ellumilel\DocProps\Core;
use Ellumilel\Helpers\ExcelHelper;
use Ellumilel\Rels\Relationships;
use Ellumilel\Xl\SharedStrings;
use Ellumilel\Xl\Styles;
use Ellumilel\Xl\Workbook;
use Ellumilel\Xl\Worksheets\SheetXml;

/**
 * Class ExcelWriter
 * @package Ellumilel
 * @author Denis Tikhonov <[email protected]>
 */
class ExcelWriter
{
    /** @var array */
    protected $sheets = [];
    /** @var array */
    protected $sharedStrings = [];
    /** @var int */
    protected $sharedStringCount = 0;
    /** @var array */
    protected $tempFiles = [];
    /** @var array */
    protected $cellFormats = [];
    /** @var array */
    protected $cellTypes = [];
    /** @var string  */
    protected $currentSheet = '';
    /** @var null */
    protected $tmpDir = null;
    /** @var Core */
    protected $core;
    /** @var App */
    protected $app;
    /** @var Workbook */
    protected $workbook;
    /** @var SheetXml */
    protected $sheetXml;

    /**
     * ExcelWriter constructor.
     * @throws \Exception
     */
    public function __construct()
    {
        if (!class_exists('ZipArchive')) {
            throw new \Exception('ZipArchive not found');
        }
        if (!ini_get('date.timezone')) {
            date_default_timezone_set('UTC');
        }
        $this->addCellFormat($cell_format = 'GENERAL');
        $this->core = new Core();
        $this->app = new App();
        $this->workbook = new Workbook();
        $this->sheetXml = new SheetXml();
    }

    /**
     * @param string $author
     */
    public function setAuthor($author)
    {
        $this->core->setAuthor($author);
    }

    public function __destruct()
    {
        if (!empty($this->tempFiles)) {
            foreach ($this->tempFiles as $tempFile) {
                if (file_exists($tempFile)) {
                    unlink($tempFile);
                }
            }
        }
    }

    /**
     * @param $dir
     */
    public function setTmpDir($dir)
    {
        $this->tmpDir = $dir;
    }

    /**
     * Return tmpFileName
     * @return string
     */
    protected function tempFilename()
    {
        $tmpDir = is_null($this->tmpDir) ? sys_get_temp_dir() : $this->tmpDir;
        $filename = tempnam($tmpDir, "excelWriter_");
        $this->tempFiles[] = $filename;

        return $filename;
    }

    public function writeToStdOut()
    {
        $tempFile = $this->tempFilename();
        $this->writeToFile($tempFile);
        readfile($tempFile);
    }

    /**
     * @return string
     */
    public function writeToString()
    {
        $tempFile = $this->tempFilename();
        $this->writeToFile($tempFile);
        $string = file_get_contents($tempFile);

        return $string;
    }

    /**
     * @param string $filename
     */
    public function writeToFile($filename)
    {
        $zip = new \ZipArchive();
        foreach ($this->sheets as $sheetName => $sheet) {
            $this->finalizeSheet($sheetName);
        }
        $this->checkAndUnlink($zip, $filename);
        $this->workbook->setSheet($this->sheets);

        $contentTypes = new ContentTypes(!empty($this->sharedStrings));
        $contentTypes->setSheet($this->sheets);

        $rel = new Relationships(!empty($this->sharedStrings));
        $rel->setSheet($this->sheets);
        $zip->addEmptyDir("docProps/");
        $zip->addFromString("docProps/app.xml", $this->app->buildAppXML());
        $zip->addFromString("docProps/core.xml", $this->core->buildCoreXML());
        $zip->addEmptyDir("_rels/");
        $zip->addFromString("_rels/.rels", $rel->buildRelationshipsXML());
        $zip->addEmptyDir("xl/worksheets/");
        foreach ($this->sheets as $sheet) {
            /** @var Sheet $sheet */
            $zip->addFile($sheet->getFilename(), "xl/worksheets/".$sheet->getXmlName());
        }
        if (!empty($this->sharedStrings)) {
            $zip->addFile(
                $this->writeSharedStringsXML(),
                "xl/sharedStrings.xml"
            );
        }
        $zip->addFromString("xl/workbook.xml", $this->workbook->buildWorkbookXML());
        $zip->addFile(
            $this->writeStylesXML(),
            "xl/styles.xml"
        );
        $zip->addFromString("[Content_Types].xml", $contentTypes->buildContentTypesXML());
        $zip->addEmptyDir("xl/_rels/");
        $zip->addFromString("xl/_rels/workbook.xml.rels", $rel->buildWorkbookRelationshipsXML());
        $zip->close();
    }

    /**
     * @param string $sheetName
     */
    protected function initializeSheet($sheetName)
    {
        if ($this->currentSheet == $sheetName || isset($this->sheets[$sheetName])) {
            return;
        }
        $sheetFilename = $this->tempFilename();
        $sheetXmlName = 'sheet' . (count($this->sheets) + 1).".xml";
        $sheetObj = new Sheet();
        $sheetObj
            ->setFilename($sheetFilename)
            ->setSheetName($sheetName)
            ->setXmlName($sheetXmlName)
            ->setWriter(new Writer($sheetFilename))
        ;
        $this->sheets[$sheetName] = $sheetObj;
        /** @var Sheet $sheet */
        $sheet = &$this->sheets[$sheetName];
        $selectedTab = count($this->sheets) == 1 ? 'true' : 'false';
        $maxCell = ExcelHelper::xlsCell(ExcelHelper::EXCEL_MAX_ROW, ExcelHelper::EXCEL_MAX_COL);
        $sheet->getWriter()->write($this->sheetXml->getXml());
        $sheet->getWriter()->write($this->sheetXml->getWorksheet());
        $sheet->getWriter()->write($this->sheetXml->getSheetPr());
        $sheet->setMaxCellTagStart($sheet->getWriter()->fTell());
        $sheet->getWriter()->write($this->sheetXml->getDimension($maxCell));
        $sheet->setMaxCellTagEnd($sheet->getWriter()->fTell());
        $sheet->getWriter()->write($this->sheetXml->getSheetViews($selectedTab));
        $sheet->getWriter()->write($this->sheetXml->getCools());
        $sheet->getWriter()->write('<sheetData>');
    }

    /**
     * @param $cellFormat
     *
     * @return string
     */
    private function determineCellType($cellFormat)
    {
        $cellFormat = str_replace("[RED]", "", $cellFormat);
        if ($cellFormat == 'GENERAL') {
            return 'string';
        }
        if ($cellFormat == '0') {
            return 'numeric';
        }
        $checkArray = [
            'datetime' => [
                "/[H]{1,2}:[M]{1,2}/",
                "/[M]{1,2}:[S]{1,2}/",
            ],
            'numeric' => [
                "/0/",
            ],
            'date' => [
                "/[YY]{2,4}/",
                "/[D]{1,2}/",
                "/[M]{1,2}/",
            ],
            'currency' => [
                "/$/",
            ],
            'percent' => [
                "/%/",
            ],
        ];
        foreach ($checkArray as $type => $item) {
            foreach ($item as $prMatch) {
                if (preg_match($prMatch, $cellFormat)) {
                    return $type;
                }
            }
        }

        return 'string';
    }

    /**
     * backwards compatibility
     *
     * @param $cellFormat
     *
     * @return int|mixed
     */
    private function addCellFormat($cellFormat)
    {
        $cellFormat = strtoupper($this->getCellFormat($cellFormat));
        $position = array_search($cellFormat, $this->cellFormats, $strict = true);
        if ($position === false) {
            $position = count($this->cellFormats);
            $this->cellFormats[] = ExcelHelper::escapeCellFormat($cellFormat);
            $this->cellTypes[] = $this->determineCellType($cellFormat);
        }

        return $position;
    }

    /**
     * @link https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformats(v=office.15).aspx
     *
     * @param string $cellFormat
     *
     * @return string
     */
    private function getCellFormat($cellFormat)
    {
        $formatArray = [
            'string' => 'GENERAL',
            'integer' => '0',
            'float_with_sep' => '#,##0.00',
            'float' => '0.00',
            'date' => 'YYYY-MM-DD',
            'datetime' => 'YYYY-MM-DD HH:MM:SS',
            'dollar' => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',
            'money' => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',
            'euro' => '#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]',
            'rub' => '#,##0.00 [$₽-419];[Red]-#,##0.00 [$₽-419]',
            'NN' => 'DDD',
            'NNN' => 'DDDD',
            'NNNN' => 'DDDD", "',
        ];

        if (array_key_exists($cellFormat, $formatArray)) {
            return $formatArray[$cellFormat];
        }
        return $cellFormat;
    }

    /**
     * @param string $sheetName
     * @param array $headerTypes
     * @param bool $suppressRow
     */
    public function writeSheetHeader($sheetName, array $headerTypes, $suppressRow = false)
    {
        if (empty($sheetName) || empty($headerTypes) || !empty($this->sheets[$sheetName])) {
            return;
        }
        $this->initializeSheet($sheetName);
        /** @var Sheet $sheet */
        $sheet = &$this->sheets[$sheetName];
        $sheet->setColumns([]);
        foreach ($headerTypes as $val) {
            $sheet->setColumn($this->addCellFormat($val));
        }
        if (!$suppressRow) {
            $this->writeRowHeader($sheet, array_keys($headerTypes));
            $sheet->increaseRowCount();
        }
        $this->currentSheet = $sheetName;
    }

    /**
     * @param Sheet $sheet
     * @param array $headerRow
     */
    private function writeRowHeader(Sheet $sheet, $headerRow)
    {
        $sheet->getWriter()->write(
            '<row collapsed="false" customFormat="false" 
                customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="'.(1).'">'
        );
        foreach ($headerRow as $k => $v) {
            $this->writeCell($sheet->getWriter(), 0, $k, $v, $cell_format_index = '0');
        }
        $sheet->getWriter()->write('</row>');
    }

    /**
     * @param string $sheetName
     * @param array $row
     */
    public function writeSheetRow($sheetName, array $row)
    {
        if (empty($sheetName) || empty($row)) {
            return;
        }
        $this->initializeSheet($sheetName);
        /** @var Sheet $sheet */
        $sheet = &$this->sheets[$sheetName];
        $columns = $sheet->getColumns();
        if (empty($columns)) {
            $sheet->setColumns(array_fill(0, count($row), '0'));
        }
        $sheet->getWriter()->write(
            '<row collapsed="false" customFormat="false" customHeight="false" 
            hidden="false" ht="12.1" outlineLevel="0" r="'.($sheet->getRowCount() + 1).'">'
        );
        $column_count = 0;
        $sheetColumns = $sheet->getColumns();
        foreach ($row as $k => $v) {
            $this->writeCell(
                $sheet->getWriter(),
                $sheet->getRowCount(),
                $column_count,
                $v,
                $sheetColumns[$column_count]
            );
            $column_count++;
        }
        $sheet->getWriter()->write('</row>');
        $sheet->increaseRowCount();
        $this->currentSheet = $sheetName;
    }

    /**
     * @param string $sheetName
     */
    protected function finalizeSheet($sheetName)
    {
        if (empty($sheetName) || ($this->sheets[$sheetName] instanceof Sheet &&
                $this->sheets[$sheetName]->getFinalized()
            )
        ) {
            return;
        }

        /** @var Sheet $sheet */
        $sheet = &$this->sheets[$sheetName];
        $sheet->getWriter()->write('</sheetData>');
        $mergeCells = $sheet->getMergeCells();
        if (!empty($mergeCells)) {
            $sheet->getWriter()->write($this->sheetXml->getMergeCells($mergeCells));
        }
        $sheet->getWriter()->write($this->sheetXml->getPrintOptions());
        $sheet->getWriter()->write($this->sheetXml->getPageMargins());
        $sheet->getWriter()->write($this->sheetXml->getPageSetup());
        $sheet->getWriter()->write($this->sheetXml->getHeaderFooter());
        $sheet->getWriter()->write('</worksheet>');
        $maxCell = ExcelHelper::xlsCell($sheet->getRowCount() - 1, count($sheet->getColumns()) - 1);
        $maxCellTag = $this->sheetXml->getDimension($maxCell);
        $paddingLength = $sheet->getMaxCellTagEnd() - $sheet->getMaxCellTagStart() - strlen($maxCellTag);
        $sheet->getWriter()->fSeek($sheet->getMaxCellTagStart());
        $sheet->getWriter()->write($maxCellTag.str_repeat(" ", $paddingLength));
        $sheet->getWriter()->close();
        $sheet->setFinalized(true);
    }

    /**
     * @param string $sheetName
     * @param int $startCellRow
     * @param int $startCellColumn
     * @param int $endCellRow
     * @param int $endCellColumn
     */
    public function markMergedCell($sheetName, $startCellRow, $startCellColumn, $endCellRow, $endCellColumn)
    {
        if (empty($sheetName) || $this->sheets[$sheetName]->getFinalized()) {
            return;
        }
        $this->initializeSheet($sheetName);
        /** @var Sheet $sheet */
        $sheet = &$this->sheets[$sheetName];
        $startCell = ExcelHelper::xlsCell($startCellRow, $startCellColumn);
        $endCell = ExcelHelper::xlsCell($endCellRow, $endCellColumn);
        $sheet->setMergeCells($startCell.":".$endCell);
    }

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

    /**
     * @param Writer $file
     * @param int $rowNumber
     * @param int $columnNumber
     * @param mixed $value
     * @param $cellIndex
     */
    protected function writeCell(Writer $file, $rowNumber, $columnNumber, $value, $cellIndex)
    {
        $cellType = $this->cellTypes[$cellIndex];
        $cellName = ExcelHelper::xlsCell($rowNumber, $columnNumber);
        $cell = $this->sheetXml->getCell($cellName, $cellIndex, $cellType, $value);
        if ($cell === false) {
            $file->write(
                '<c r="'.$cellName.'" s="'.$cellIndex.'" t="s"><v>'.ExcelHelper::xmlspecialchars(
                    $this->setSharedString($value)
                ).'</v></c>'
            );
        } else {
            $file->write($cell);
        }
    }

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

        return $temporaryFilename;
    }

    /**
     * @param $v
     *
     * @return int|mixed
     */
    protected function setSharedString($v)
    {
        if (isset($this->sharedStrings[$v])) {
            $stringValue = $this->sharedStrings[$v];
        } else {
            $stringValue = count($this->sharedStrings);
            $this->sharedStrings[$v] = $stringValue;
        }
        $this->sharedStringCount++;

        return $stringValue;
    }

    /**
     * @return string
     */
    protected function writeSharedStringsXML()
    {
        $tempFilename = $this->tempFilename();
        $file = new Writer($tempFilename);
        $sharedStrings = new SharedStrings($this->sharedStringCount, $this->sharedStrings);
        $file->write($sharedStrings->buildSharedStringsXML());
        $file->close();

        return $tempFilename;
    }

    /**
     * @param \ZipArchive $zip
     * @param string $filename
     */
    private function checkAndUnlink(\ZipArchive $zip, $filename)
    {
        if (file_exists($filename) && is_writable($filename)) {
            unlink($filename);
        }
        if (empty($this->sheets) || !$zip->open($filename, \ZipArchive::CREATE)) {
            throw new \RuntimeException(
                "Error in ".__CLASS__."::".__FUNCTION__.", no worksheets defined or unable to create zip."
            );
        }
    }
}


1			<?php
2			namespace Ellumilel;
3
4			use Ellumilel\DocProps\App;
5			use Ellumilel\DocProps\Core;
6			use Ellumilel\Helpers\ExcelHelper;
7			use Ellumilel\Rels\Relationships;
8			use Ellumilel\Xl\SharedStrings;
9			use Ellumilel\Xl\Styles;
10			use Ellumilel\Xl\Workbook;
11			use Ellumilel\Xl\Worksheets\SheetXml;
12
13			/**
14			* Class ExcelWriter
15			* @package Ellumilel
16			* @author Denis Tikhonov <[email protected]>
17			*/
18			class ExcelWriter
19			{
20			/** @var array */
21			protected $sheets = [];
22			/** @var array */
23			protected $sharedStrings = [];
24			/** @var int */
25			protected $sharedStringCount = 0;
26			/** @var array */
27			protected $tempFiles = [];
28			/** @var array */
29			protected $cellFormats = [];
30			/** @var array */
31			protected $cellTypes = [];
32			/** @var string */
33			protected $currentSheet = '';
34			/** @var null */
35			protected $tmpDir = null;
36			/** @var Core */
37			protected $core;
38			/** @var App */
39			protected $app;
40			/** @var Workbook */
41			protected $workbook;
42			/** @var SheetXml */
43			protected $sheetXml;
44
45			/**
46			* ExcelWriter constructor.
47			* @throws \Exception
48			*/
49			public function __construct()
50			{
51			if (!class_exists('ZipArchive')) {
52			throw new \Exception('ZipArchive not found');
53			}
54			if (!ini_get('date.timezone')) {
55			date_default_timezone_set('UTC');
56			}
57			$this->addCellFormat($cell_format = 'GENERAL');
58			$this->core = new Core();
59			$this->app = new App();
60			$this->workbook = new Workbook();
61			$this->sheetXml = new SheetXml();
62			}
63
64			/**
65			* @param string $author
66			*/
67			public function setAuthor($author)
68			{
69			$this->core->setAuthor($author);
70			}
71
72			public function __destruct()
73			{
74			if (!empty($this->tempFiles)) {
75			foreach ($this->tempFiles as $tempFile) {
76			if (file_exists($tempFile)) {
77			unlink($tempFile);
78			}
79			}
80			}
81			}
82
83			/**
84			* @param $dir
85			*/
86			public function setTmpDir($dir)
87			{
88			$this->tmpDir = $dir;
89			}
90
91			/**
92			* Return tmpFileName
93			* @return string
94			*/
95			protected function tempFilename()
96			{
97			$tmpDir = is_null($this->tmpDir) ? sys_get_temp_dir() : $this->tmpDir;
98			$filename = tempnam($tmpDir, "excelWriter_");
99			$this->tempFiles[] = $filename;
100
101			return $filename;
102			}
103
104			public function writeToStdOut()
105			{
106			$tempFile = $this->tempFilename();
107			$this->writeToFile($tempFile);
108			readfile($tempFile);
109			}
110
111			/**
112			* @return string
113			*/
114			public function writeToString()
115			{
116			$tempFile = $this->tempFilename();
117			$this->writeToFile($tempFile);
118			$string = file_get_contents($tempFile);
119
120			return $string;
121			}
122
123			/**
124			* @param string $filename
125			*/
126			public function writeToFile($filename)
127			{
128			$zip = new \ZipArchive();
129			foreach ($this->sheets as $sheetName => $sheet) {
130			$this->finalizeSheet($sheetName);
131			}
132			$this->checkAndUnlink($zip, $filename);
133			$this->workbook->setSheet($this->sheets);
134
135			$contentTypes = new ContentTypes(!empty($this->sharedStrings));
136			$contentTypes->setSheet($this->sheets);
137
138			$rel = new Relationships(!empty($this->sharedStrings));
139			$rel->setSheet($this->sheets);
140			$zip->addEmptyDir("docProps/");
141			$zip->addFromString("docProps/app.xml", $this->app->buildAppXML());
142			$zip->addFromString("docProps/core.xml", $this->core->buildCoreXML());
143			$zip->addEmptyDir("_rels/");
144			$zip->addFromString("_rels/.rels", $rel->buildRelationshipsXML());
145			$zip->addEmptyDir("xl/worksheets/");
146			foreach ($this->sheets as $sheet) {
147			/** @var Sheet $sheet */
148			$zip->addFile($sheet->getFilename(), "xl/worksheets/".$sheet->getXmlName());
149			}
150			if (!empty($this->sharedStrings)) {
151			$zip->addFile(
152			$this->writeSharedStringsXML(),
153			"xl/sharedStrings.xml"
154			);
155			}
156			$zip->addFromString("xl/workbook.xml", $this->workbook->buildWorkbookXML());
157			$zip->addFile(
158			$this->writeStylesXML(),
159			"xl/styles.xml"
160			);
161			$zip->addFromString("[Content_Types].xml", $contentTypes->buildContentTypesXML());
162			$zip->addEmptyDir("xl/_rels/");
163			$zip->addFromString("xl/_rels/workbook.xml.rels", $rel->buildWorkbookRelationshipsXML());
164			$zip->close();
165			}
166
167			/**
168			* @param string $sheetName
169			*/
170			protected function initializeSheet($sheetName)
171			{
172			if ($this->currentSheet == $sheetName \|\| isset($this->sheets[$sheetName])) {
173			return;
174			}
175			$sheetFilename = $this->tempFilename();
176			$sheetXmlName = 'sheet' . (count($this->sheets) + 1).".xml";
177			$sheetObj = new Sheet();
178			$sheetObj
179			->setFilename($sheetFilename)
180			->setSheetName($sheetName)
181			->setXmlName($sheetXmlName)
182			->setWriter(new Writer($sheetFilename))
183			;
184			$this->sheets[$sheetName] = $sheetObj;
185			/** @var Sheet $sheet */
186			$sheet = &$this->sheets[$sheetName];
187			$selectedTab = count($this->sheets) == 1 ? 'true' : 'false';
188			$maxCell = ExcelHelper::xlsCell(ExcelHelper::EXCEL_MAX_ROW, ExcelHelper::EXCEL_MAX_COL);
189			$sheet->getWriter()->write($this->sheetXml->getXml());
190			$sheet->getWriter()->write($this->sheetXml->getWorksheet());
191			$sheet->getWriter()->write($this->sheetXml->getSheetPr());
192			$sheet->setMaxCellTagStart($sheet->getWriter()->fTell());
193			$sheet->getWriter()->write($this->sheetXml->getDimension($maxCell));
194			$sheet->setMaxCellTagEnd($sheet->getWriter()->fTell());
195			$sheet->getWriter()->write($this->sheetXml->getSheetViews($selectedTab));
196			$sheet->getWriter()->write($this->sheetXml->getCools());
197			$sheet->getWriter()->write('<sheetData>');
198			}
199
200			/**
201			* @param $cellFormat
202			*
203			* @return string
204			*/
205			private function determineCellType($cellFormat)
206			{
207			$cellFormat = str_replace("[RED]", "", $cellFormat);
208			if ($cellFormat == 'GENERAL') {
209			return 'string';
210			}
211			if ($cellFormat == '0') {
212			return 'numeric';
213			}
214			$checkArray = [
215			'datetime' => [
216			"/[H]{1,2}:[M]{1,2}/",
217			"/[M]{1,2}:[S]{1,2}/",
218			],
219			'numeric' => [
220			"/0/",
221			],
222			'date' => [
223			"/[YY]{2,4}/",
224			"/[D]{1,2}/",
225			"/[M]{1,2}/",
226			],
227			'currency' => [
228			"/$/",
229			],
230			'percent' => [
231			"/%/",
232			],
233			];
234			foreach ($checkArray as $type => $item) {
235			foreach ($item as $prMatch) {
236			if (preg_match($prMatch, $cellFormat)) {
237			return $type;
238			}
239			}
240			}
241
242			return 'string';
243			}
244
245			/**
246			* backwards compatibility
247			*
248			* @param $cellFormat
249			*
250			* @return int\|mixed
251			*/
252			private function addCellFormat($cellFormat)
253			{
254			$cellFormat = strtoupper($this->getCellFormat($cellFormat));
255			$position = array_search($cellFormat, $this->cellFormats, $strict = true);
256			if ($position === false) {
257			$position = count($this->cellFormats);
258			$this->cellFormats[] = ExcelHelper::escapeCellFormat($cellFormat);
259			$this->cellTypes[] = $this->determineCellType($cellFormat);
260			}
261
262			return $position;
263			}
264
265			/**
266			* @link https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.numberingformats(v=office.15).aspx
267			*
268			* @param string $cellFormat
269			*
270			* @return string
271			*/
272			private function getCellFormat($cellFormat)
273			{
274			$formatArray = [
275			'string' => 'GENERAL',
276			'integer' => '0',
277			'float_with_sep' => '#,##0.00',
278			'float' => '0.00',
279			'date' => 'YYYY-MM-DD',
280			'datetime' => 'YYYY-MM-DD HH:MM:SS',
281			'dollar' => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',
282			'money' => '[$$-1009]#,##0.00;[RED]-[$$-1009]#,##0.00',
283			'euro' => '#,##0.00 [$€-407];[RED]-#,##0.00 [$€-407]',
284			'rub' => '#,##0.00 [$₽-419];[Red]-#,##0.00 [$₽-419]',
285			'NN' => 'DDD',
286			'NNN' => 'DDDD',
287			'NNNN' => 'DDDD", "',
288			];
289
290			if (array_key_exists($cellFormat, $formatArray)) {
291			return $formatArray[$cellFormat];
292			}
293			return $cellFormat;
294			}
295
296			/**
297			* @param string $sheetName
298			* @param array $headerTypes
299			* @param bool $suppressRow
300			*/
301			public function writeSheetHeader($sheetName, array $headerTypes, $suppressRow = false)
302			{
303			if (empty($sheetName) \|\| empty($headerTypes) \|\| !empty($this->sheets[$sheetName])) {
304			return;
305			}
306			$this->initializeSheet($sheetName);
307			/** @var Sheet $sheet */
308			$sheet = &$this->sheets[$sheetName];
309			$sheet->setColumns([]);
310			foreach ($headerTypes as $val) {
311			$sheet->setColumn($this->addCellFormat($val));
312			}
313			if (!$suppressRow) {
314			$this->writeRowHeader($sheet, array_keys($headerTypes));
315			$sheet->increaseRowCount();
316			}
317			$this->currentSheet = $sheetName;
318			}
319
320			/**
321			* @param Sheet $sheet
322			* @param array $headerRow
323			*/
324			private function writeRowHeader(Sheet $sheet, $headerRow)
325			{
326			$sheet->getWriter()->write(
327			'<row collapsed="false" customFormat="false"
328			customHeight="false" hidden="false" ht="12.1" outlineLevel="0" r="'.(1).'">'
329			);
330			foreach ($headerRow as $k => $v) {
331			$this->writeCell($sheet->getWriter(), 0, $k, $v, $cell_format_index = '0');
332			}
333			$sheet->getWriter()->write('</row>');
334			}
335
336			/**
337			* @param string $sheetName
338			* @param array $row
339			*/
340			public function writeSheetRow($sheetName, array $row)
341			{
342			if (empty($sheetName) \|\| empty($row)) {
343			return;
344			}
345			$this->initializeSheet($sheetName);
346			/** @var Sheet $sheet */
347			$sheet = &$this->sheets[$sheetName];
348			$columns = $sheet->getColumns();
349			if (empty($columns)) {
350			$sheet->setColumns(array_fill(0, count($row), '0'));
351			}
352			$sheet->getWriter()->write(
353			'<row collapsed="false" customFormat="false" customHeight="false"
354			hidden="false" ht="12.1" outlineLevel="0" r="'.($sheet->getRowCount() + 1).'">'
355			);
356			$column_count = 0;
357			$sheetColumns = $sheet->getColumns();
358			foreach ($row as $k => $v) {
359			$this->writeCell(
360			$sheet->getWriter(),
361			$sheet->getRowCount(),
362			$column_count,
363			$v,
364			$sheetColumns[$column_count]
365			);
366			$column_count++;
367			}
368			$sheet->getWriter()->write('</row>');
369			$sheet->increaseRowCount();
370			$this->currentSheet = $sheetName;
371			}
372
373			/**
374			* @param string $sheetName
375			*/
376			protected function finalizeSheet($sheetName)
377			{
378			if (empty($sheetName) \|\| ($this->sheets[$sheetName] instanceof Sheet &&
379			$this->sheets[$sheetName]->getFinalized()
380			)
381			) {
382			return;
383			}
384
385			/** @var Sheet $sheet */
386			$sheet = &$this->sheets[$sheetName];
387			$sheet->getWriter()->write('</sheetData>');
388			$mergeCells = $sheet->getMergeCells();
389			if (!empty($mergeCells)) {
390			$sheet->getWriter()->write($this->sheetXml->getMergeCells($mergeCells));
391			}
392			$sheet->getWriter()->write($this->sheetXml->getPrintOptions());
393			$sheet->getWriter()->write($this->sheetXml->getPageMargins());
394			$sheet->getWriter()->write($this->sheetXml->getPageSetup());
395			$sheet->getWriter()->write($this->sheetXml->getHeaderFooter());
396			$sheet->getWriter()->write('</worksheet>');
397			$maxCell = ExcelHelper::xlsCell($sheet->getRowCount() - 1, count($sheet->getColumns()) - 1);
398			$maxCellTag = $this->sheetXml->getDimension($maxCell);
399			$paddingLength = $sheet->getMaxCellTagEnd() - $sheet->getMaxCellTagStart() - strlen($maxCellTag);
400			$sheet->getWriter()->fSeek($sheet->getMaxCellTagStart());
401			$sheet->getWriter()->write($maxCellTag.str_repeat(" ", $paddingLength));
402			$sheet->getWriter()->close();
403			$sheet->setFinalized(true);
404			}
405
406			/**
407			* @param string $sheetName
408			* @param int $startCellRow
409			* @param int $startCellColumn
410			* @param int $endCellRow
411			* @param int $endCellColumn
412			*/
413			public function markMergedCell($sheetName, $startCellRow, $startCellColumn, $endCellRow, $endCellColumn)
414			{
415			if (empty($sheetName) \|\| $this->sheets[$sheetName]->getFinalized()) {
416			return;
417			}
418			$this->initializeSheet($sheetName);
419			/** @var Sheet $sheet */
420			$sheet = &$this->sheets[$sheetName];
421			$startCell = ExcelHelper::xlsCell($startCellRow, $startCellColumn);
422			$endCell = ExcelHelper::xlsCell($endCellRow, $endCellColumn);
423			$sheet->setMergeCells($startCell.":".$endCell);
424			}
425
426			/**
427			* @param array $data
428			* @param string $sheetName
429			* @param array $headerTypes
430			*/
431			public function writeSheet(array $data, $sheetName = '', array $headerTypes = [])
432			{
433			$sheetName = empty($sheetName) ? 'Sheet1' : $sheetName;
434			$data = empty($data) ? [['']] : $data;
435			if (!empty($headerTypes)) {
436			$this->writeSheetHeader($sheetName, $headerTypes);
437			}
438			foreach ($data as $i => $row) {
439			$this->writeSheetRow($sheetName, $row);
440			}
441			$this->finalizeSheet($sheetName);
442			}
443
444			/**
445			* @param Writer $file
446			* @param int $rowNumber
447			* @param int $columnNumber
448			* @param mixed $value
449			* @param $cellIndex
450			*/
451			protected function writeCell(Writer $file, $rowNumber, $columnNumber, $value, $cellIndex)
452			{
453			$cellType = $this->cellTypes[$cellIndex];
454			$cellName = ExcelHelper::xlsCell($rowNumber, $columnNumber);
455			$cell = $this->sheetXml->getCell($cellName, $cellIndex, $cellType, $value);
456			if ($cell === false) {
457			$file->write(
458			'<c r="'.$cellName.'" s="'.$cellIndex.'" t="s"><v>'.ExcelHelper::xmlspecialchars(
459			$this->setSharedString($value)
460			).'</v></c>'
461			);
462			} else {
463			$file->write($cell);
464			}
465			}
466
467			/**
468			* @return string
469			*/
470			protected function writeStylesXML()
471			{
472			$temporaryFilename = $this->tempFilename();
473			$file = new Writer($temporaryFilename);
474			$styles = new Styles();
475			$styles->setCellFormats($this->cellFormats);
476			$file->write($styles->buildStylesXML());
477
478			return $temporaryFilename;
479			}
480
481			/**
482			* @param $v
483			*
484			* @return int\|mixed
485			*/
486			protected function setSharedString($v)
487			{
488			if (isset($this->sharedStrings[$v])) {
489			$stringValue = $this->sharedStrings[$v];
490			} else {
491			$stringValue = count($this->sharedStrings);
492			$this->sharedStrings[$v] = $stringValue;
493			}
494			$this->sharedStringCount++;
495
496			return $stringValue;
497			}
498
499			/**
500			* @return string
501			*/
502			protected function writeSharedStringsXML()
503			{
504			$tempFilename = $this->tempFilename();
505			$file = new Writer($tempFilename);
506			$sharedStrings = new SharedStrings($this->sharedStringCount, $this->sharedStrings);
507			$file->write($sharedStrings->buildSharedStringsXML());
508			$file->close();
509
510			return $tempFilename;
511			}
512
513			/**
514			* @param \ZipArchive $zip
515			* @param string $filename
516			*/
517			private function checkAndUnlink(\ZipArchive $zip, $filename)
518			{
519			if (file_exists($filename) && is_writable($filename)) {
520			unlink($filename);
521			}
522			if (empty($this->sheets) \|\| !$zip->open($filename, \ZipArchive::CREATE)) {
523			throw new \RuntimeException(
524			"Error in ".__CLASS__."::".__FUNCTION__.", no worksheets defined or unable to create zip."
525			);
526			}
527			}
528			}
529

ellumilel / php-excel-writer

GitHub Access Token became invalid

Push — master ( dd6037...170e4d )

ExcelWriter::markMergedCell() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like