SheetXml::getFormulaCell() - Code Metrics - ellumilel/php-excel-writer - Measure and Improve Code Quality continuously with Scrutinizer

SheetXml::getFormulaCell() A
last analyzed 2020-06-02 09:18 UTC

↳ Parent: SheetXml

Complexity

Conditions	1
Paths	1

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	9
rs	9.9666
c	0
b	0
f	0
cc	1
nc	1
nop	3

<?php
namespace Ellumilel\Xl\Worksheets;

use Ellumilel\Helpers\ExcelHelper;

/**
 * @link https://msdn.microsoft.com/en-us/library/bb264572(v=office.12).aspx
 *
 * Class SheetXml
 * @package Ellumilel\Xl\Worksheets
 * @author Denis Tikhonov <[email protected]>
 */
class SheetXml
{
    /** @var string */
    private $xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\n";
    /** @var string */
    private $urlOpenXmlFormat = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
    /** @var string */
    private $urlSchemaFormat = 'http://schemas.openxmlformats.org/officeDocument/2006';

    /**
     * @return string
     */
    public function getXml()
    {
        return $this->xml;
    }

    /**
     * @return string
     */
    public function getSheetPr()
    {
        $sPr = '<sheetPr filterMode="false">';
        $sPr .= '<pageSetUpPr fitToPage="false"/>';
        $sPr .= '</sheetPr>';

        return $sPr;
    }

    /**
     * @return string
     */
    public function getWorksheet()
    {
        $wSheet = '<worksheet xmlns="'.$this->urlOpenXmlFormat.'" xmlns:r="'.$this->urlSchemaFormat.'/relationships">';

        return $wSheet;
    }

    /**
     * @param string $selectedTab
     *
     * @return string
     */
    public function getSheetViews($selectedTab)
    {
        $sViews = '<sheetViews>';
        $sViews .= '<sheetView colorId="64" defaultGridColor="true" rightToLeft="false" showFormulas="false"';
        $sViews .= ' showGridLines="true" showOutlineSymbols="true" showRowColHeaders="true" showZeros="true"';
        $sViews .= ' tabSelected="'.$selectedTab.'" topLeftCell="A1" view="normal" windowProtection="false"';
        $sViews .= ' workbookViewId="0" zoomScale="100" zoomScaleNormal="100" zoomScalePageLayoutView="100">';
        $sViews .= '<selection activeCell="A1" activeCellId="0" pane="topLeft" sqref="A1"/>';
        $sViews .= '</sheetView>';
        $sViews .= '</sheetViews>';

        return $sViews;
    }

    /**
     * @todo set colls width
     *
     * @return string
     */
    public function getCools()
    {
        /**
        <cols>
            <col width="11.42578125" max="3" min="1"/>
            <col width="116" max="4" min="4" customWidth="1"/>
            <col width="11.42578125" max="1025" min="5"/>
        </cols>
         */
        $sCols = '<cols>';
        $sCols .= '<col collapsed="false" hidden="false" max="7" min="1" style="0" width="30.5"/>';
        $sCols .= '</cols>';

        return $sCols;
    }

    /**
     * @param string $maxCell
     *
     * @return string
     */
    public function getDimension($maxCell)
    {
        $sCols = '<dimension ref="A1:'.$maxCell.'"/>';

        return $sCols;
    }

    /**
     * @todo refactor
     *
     * @return string
     */
    public function getHeaderFooter()
    {
        $hf = '<headerFooter differentFirst="false" differentOddEven="false">';
        $hf .= '<oddHeader>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12&amp;A</oddHeader>';
        $hf .= '<oddFooter>&amp;C&amp;&quot;Times New Roman,Regular&quot;&amp;12Page &amp;P</oddFooter>';
        $hf .= '</headerFooter>';

        return $hf;
    }

    /**
     * @return string
     */
    public function getPageSetup()
    {
        $ps = '<pageSetup blackAndWhite="false" cellComments="none" copies="1" draft="false" firstPageNumber="1"';
        $ps .= ' fitToHeight="1" fitToWidth="1" horizontalDpi="300" orientation="portrait" pageOrder="downThenOver"';
        $ps .= ' paperSize="1" scale="100" useFirstPageNumber="true" usePrinterDefaults="false" verticalDpi="300"/>';

        return $ps;
    }

    /**
     * @return string
     */
    public function getPageMargins()
    {
        return '<pageMargins left="0.5" right="0.5" top="1.0" bottom="1.0" header="0.5" footer="0.5"/>';
    }

    /**
     * @return string
     */
    public function getPrintOptions()
    {
        return '<printOptions headings="false" gridLines="false" gridLinesSet="true" horizontalCentered="false"
                verticalCentered="false"/>';
    }

    /**
     * @param array $mergeCells
     *
     * @return string
     */
    public function getMergeCells(array $mergeCells)
    {
        $mc = '<mergeCells>';
        foreach ($mergeCells as $range) {
            $mc .= '<mergeCell ref="'.$range.'"/>';
        }
        $mc .= '</mergeCells>';

        return $mc;
    }

    /**
     * @param $cellName
     * @param $cellIndex
     * @param $cellType
     * @param $value
     *
     * @return bool|string
     */
    public function getCell($cellName, $cellIndex, $cellType, $value)
    {
        if ($cellType == '@') {
            return false;
        }

        if (!is_scalar($value) || $value === '') {
            return '<c r="'.$cellName.'" s="'.$cellIndex.'"/>';
        }

        if (is_string($value) && $value[0] == '=') {
            return $this->getFormulaCell($cellName, $cellIndex, $value);
        }

        if ($cellType == 'date' || $cellType == 'datetime') {
            return $this->getDateCell($cellName, $cellIndex, $value);
        } elseif ($cellType == 'currency' || $cellType == 'percent' || $cellType == 'numeric') {
            return $this->getCurrencyCell($cellName, $cellIndex, $value);
        }

        return $this->checkIntCell($cellName, $cellIndex, $value);
    }

    /**
     * @param $cellName
     * @param $cellIndex
     * @param $value
     *
     * @return bool|string
     */
    private function checkIntCell($cellName, $cellIndex, $value)
    {
        if (!is_string($value)) {
            return $this->getIntCell($cellName, $cellIndex, $value);
        } else {
            if ($value[0] != '0' &&
                $value[0] != '+' &&
                filter_var(
                    $value,
                    FILTER_VALIDATE_INT,
                    ['options' => ['max_range' => ExcelHelper::EXCEL_MAX_RANGE]]
                )
            ) {
                return $this->getIntCell($cellName, $cellIndex, $value);
            } else {
                return false;
            }
        }
    }

    /**
     * @param $cellName
     * @param $cellIndex
     * @param $value
     *
     * @return string
     */
    private function getDateCell($cellName, $cellIndex, $value)
    {
        return sprintf(
            '<c r="%s" s="%s" t="n"><v>%s</v></c>',
            $cellName,
            $cellIndex,
            ExcelHelper::convertDateTime($value)
        );
    }

    /**
     * @param $cellName
     * @param $cellIndex
     * @param $value
     *
     * @return string
     */
    private function getCurrencyCell($cellName, $cellIndex, $value)
    {
        return sprintf(
            '<c r="%s" s="%s" t="n"><v>%s</v></c>',
            $cellName,
            $cellIndex,
            ExcelHelper::xmlspecialchars($value)
        );
    }

    /**
     * @param $cellName
     * @param $cellIndex
     * @param $value
     *
     * @return string
     */
    private function getIntCell($cellName, $cellIndex, $value)
    {
        return '<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.intval($value).'</v></c>';
    }

    /**
     * @param $cellName
     * @param $cellIndex
     * @param $value
     *
     * @return string
     */
    private function getFormulaCell($cellName, $cellIndex, $value)
    {
        return sprintf(
            '<c r="%s" s="%s" t="s"><f>%s</f></c>',
            $cellName,
            $cellIndex,
            ExcelHelper::xmlspecialchars($value)
        );
    }
}


1			<?php
2			namespace Ellumilel\Xl\Worksheets;
3
4			use Ellumilel\Helpers\ExcelHelper;
5
6			/**
7			* @link https://msdn.microsoft.com/en-us/library/bb264572(v=office.12).aspx
8			*
9			* Class SheetXml
10			* @package Ellumilel\Xl\Worksheets
11			* @author Denis Tikhonov <[email protected]>
12			*/
13			class SheetXml
14			{
15			/** @var string */
16			private $xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\n";
17			/** @var string */
18			private $urlOpenXmlFormat = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
19			/** @var string */
20			private $urlSchemaFormat = 'http://schemas.openxmlformats.org/officeDocument/2006';
21
22			/**
23			* @return string
24			*/
25			public function getXml()
26			{
27			return $this->xml;
28			}
29
30			/**
31			* @return string
32			*/
33			public function getSheetPr()
34			{
35			$sPr = '<sheetPr filterMode="false">';
36			$sPr .= '<pageSetUpPr fitToPage="false"/>';
37			$sPr .= '</sheetPr>';
38
39			return $sPr;
40			}
41
42			/**
43			* @return string
44			*/
45			public function getWorksheet()
46			{
47			$wSheet = '<worksheet xmlns="'.$this->urlOpenXmlFormat.'" xmlns:r="'.$this->urlSchemaFormat.'/relationships">';
48
49			return $wSheet;
50			}
51
52			/**
53			* @param string $selectedTab
54			*
55			* @return string
56			*/
57			public function getSheetViews($selectedTab)
58			{
59			$sViews = '<sheetViews>';
60			$sViews .= '<sheetView colorId="64" defaultGridColor="true" rightToLeft="false" showFormulas="false"';
61			$sViews .= ' showGridLines="true" showOutlineSymbols="true" showRowColHeaders="true" showZeros="true"';
62			$sViews .= ' tabSelected="'.$selectedTab.'" topLeftCell="A1" view="normal" windowProtection="false"';
63			$sViews .= ' workbookViewId="0" zoomScale="100" zoomScaleNormal="100" zoomScalePageLayoutView="100">';
64			$sViews .= '<selection activeCell="A1" activeCellId="0" pane="topLeft" sqref="A1"/>';
65			$sViews .= '</sheetView>';
66			$sViews .= '</sheetViews>';
67
68			return $sViews;
69			}
70
71			/**
72			* @todo set colls width
73			*
74			* @return string
75			*/
76			public function getCools()
77			{
78			/**
79			<cols>
80			<col width="11.42578125" max="3" min="1"/>
81			<col width="116" max="4" min="4" customWidth="1"/>
82			<col width="11.42578125" max="1025" min="5"/>
83			</cols>
84			*/
85			$sCols = '<cols>';
86			$sCols .= '<col collapsed="false" hidden="false" max="7" min="1" style="0" width="30.5"/>';
87			$sCols .= '</cols>';
88
89			return $sCols;
90			}
91
92			/**
93			* @param string $maxCell
94			*
95			* @return string
96			*/
97			public function getDimension($maxCell)
98			{
99			$sCols = '<dimension ref="A1:'.$maxCell.'"/>';
100
101			return $sCols;
102			}
103
104			/**
105			* @todo refactor
106			*
107			* @return string
108			*/
109			public function getHeaderFooter()
110			{
111			$hf = '<headerFooter differentFirst="false" differentOddEven="false">';
112			$hf .= '<oddHeader>&C&"Times New Roman,Regular"&12&A</oddHeader>';
113			$hf .= '<oddFooter>&C&"Times New Roman,Regular"&12Page &P</oddFooter>';
114			$hf .= '</headerFooter>';
115
116			return $hf;
117			}
118
119			/**
120			* @return string
121			*/
122			public function getPageSetup()
123			{
124			$ps = '<pageSetup blackAndWhite="false" cellComments="none" copies="1" draft="false" firstPageNumber="1"';
125			$ps .= ' fitToHeight="1" fitToWidth="1" horizontalDpi="300" orientation="portrait" pageOrder="downThenOver"';
126			$ps .= ' paperSize="1" scale="100" useFirstPageNumber="true" usePrinterDefaults="false" verticalDpi="300"/>';
127
128			return $ps;
129			}
130
131			/**
132			* @return string
133			*/
134			public function getPageMargins()
135			{
136			return '<pageMargins left="0.5" right="0.5" top="1.0" bottom="1.0" header="0.5" footer="0.5"/>';
137			}
138
139			/**
140			* @return string
141			*/
142			public function getPrintOptions()
143			{
144			return '<printOptions headings="false" gridLines="false" gridLinesSet="true" horizontalCentered="false"
145			verticalCentered="false"/>';
146			}
147
148			/**
149			* @param array $mergeCells
150			*
151			* @return string
152			*/
153			public function getMergeCells(array $mergeCells)
154			{
155			$mc = '<mergeCells>';
156			foreach ($mergeCells as $range) {
157			$mc .= '<mergeCell ref="'.$range.'"/>';
158			}
159			$mc .= '</mergeCells>';
160
161			return $mc;
162			}
163
164			/**
165			* @param $cellName
166			* @param $cellIndex
167			* @param $cellType
168			* @param $value
169			*
170			* @return bool\|string
171			*/
172			public function getCell($cellName, $cellIndex, $cellType, $value)
173			{
174			if ($cellType == '@') {
175			return false;
176			}
177
178			if (!is_scalar($value) \|\| $value === '') {
179			return '<c r="'.$cellName.'" s="'.$cellIndex.'"/>';
180			}
181
182			if (is_string($value) && $value[0] == '=') {
183			return $this->getFormulaCell($cellName, $cellIndex, $value);
184			}
185
186			if ($cellType == 'date' \|\| $cellType == 'datetime') {
187			return $this->getDateCell($cellName, $cellIndex, $value);
188			} elseif ($cellType == 'currency' \|\| $cellType == 'percent' \|\| $cellType == 'numeric') {
189			return $this->getCurrencyCell($cellName, $cellIndex, $value);
190			}
191
192			return $this->checkIntCell($cellName, $cellIndex, $value);
193			}
194
195			/**
196			* @param $cellName
197			* @param $cellIndex
198			* @param $value
199			*
200			* @return bool\|string
201			*/
202			private function checkIntCell($cellName, $cellIndex, $value)
203			{
204			if (!is_string($value)) {
205			return $this->getIntCell($cellName, $cellIndex, $value);
206			} else {
207			if ($value[0] != '0' &&
208			$value[0] != '+' &&
209			filter_var(
210			$value,
211			FILTER_VALIDATE_INT,
212			['options' => ['max_range' => ExcelHelper::EXCEL_MAX_RANGE]]
213			)
214			) {
215			return $this->getIntCell($cellName, $cellIndex, $value);
216			} else {
217			return false;
218			}
219			}
220			}
221
222			/**
223			* @param $cellName
224			* @param $cellIndex
225			* @param $value
226			*
227			* @return string
228			*/
229			private function getDateCell($cellName, $cellIndex, $value)
230			{
231			return sprintf(
232			'<c r="%s" s="%s" t="n"><v>%s</v></c>',
233			$cellName,
234			$cellIndex,
235			ExcelHelper::convertDateTime($value)
236			);
237			}
238
239			/**
240			* @param $cellName
241			* @param $cellIndex
242			* @param $value
243			*
244			* @return string
245			*/
246			private function getCurrencyCell($cellName, $cellIndex, $value)
247			{
248			return sprintf(
249			'<c r="%s" s="%s" t="n"><v>%s</v></c>',
250			$cellName,
251			$cellIndex,
252			ExcelHelper::xmlspecialchars($value)
253			);
254			}
255
256			/**
257			* @param $cellName
258			* @param $cellIndex
259			* @param $value
260			*
261			* @return string
262			*/
263			private function getIntCell($cellName, $cellIndex, $value)
264			{
265			return '<c r="'.$cellName.'" s="'.$cellIndex.'" t="n"><v>'.intval($value).'</v></c>';
266			}
267
268			/**
269			* @param $cellName
270			* @param $cellIndex
271			* @param $value
272			*
273			* @return string
274			*/
275			private function getFormulaCell($cellName, $cellIndex, $value)
276			{
277			return sprintf(
278			'<c r="%s" s="%s" t="s"><f>%s</f></c>',
279			$cellName,
280			$cellIndex,
281			ExcelHelper::xmlspecialchars($value)
282			);
283			}
284			}
285

ellumilel / php-excel-writer

GitHub Access Token became invalid

SheetXml::getFormulaCell() A last analyzed 2020-06-02 09:18 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

SheetXml::getFormulaCell() A
last analyzed 2020-06-02 09:18 UTC