InputOutputExcel::setExcelProperties() - Code Metrics - Inspection of "bug-fix for improper classes mentioned into compos..." - danielgp/io-operations - Measure and Improve Code Quality continuously with Scrutinizer

Test Failed

Push — main ( be988c...61b5d4 )

by Daniel

created 2024-07-22 21:34 UTC

InputOutputExcel::setExcelProperties() A

↳ Parent: InputOutputExcel

Complexity

Conditions	6
Paths	32

Size

Total Lines	15
Code Lines	10

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	6
eloc	10
nc	32
nop	1
dl	0
loc	15
rs	9.2222
c	0
b	0
f	0

<?php

/**
 *
 * The MIT License (MIT)
 *
 * Copyright (c) 2015 - 2024 Daniel Popiniuc
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 *  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 *
 */

namespace danielgp\io_operations;

/**
 * Description of InputOutputExcel
 *
 * @author Daniel Popiniuc
 */
trait InputOutputExcel
{

    private $objPHPExcel;

    /**
     * manages further inputs checks
     *
     * @param array $inFeaturesWorksheets Predefined array of attributes
     * @param array $check Checking messages
     * @return array|string[]
     */
    private function checkInputFeatureContent($inFeaturesWorksheets, $check) {
        $aReturn = [];
        foreach ($inFeaturesWorksheets as $key => $value) {
            if (!array_key_exists('Name', $value)) {
                $aReturn[] = sprintf($check['4'], $key);
            } elseif (!is_string($value['Name'])) {
                $aReturn[] = sprintf($check['4.1'], $key);
            }
            if (!array_key_exists('Content', $value)) {
                $aReturn[] = sprintf($check['5'], $key);
            } elseif (!is_array($value['Content'])) {
                $aReturn[] = sprintf($check['5.1'], $key);
            }
        }
        return $aReturn;
    }

    /**
     * manages the inputs checks
     *
     * @param array $inFeatures Predefined array of attributes
     * @return array|string[]
     */
    private function checkInputFeatures(array $inFeatures) {
        $aReturn = [];
        $check   = $this->internalCheckingErrorMessages();
        if ($inFeatures === []) {
            $aReturn[] = $check['1'];
        }
        if (!array_key_exists('Filename', $inFeatures)) {
            $aReturn[] = $check['2'];
        } elseif (!is_string($inFeatures['Filename'])) {
            $aReturn[] = $check['2.1'];
        }
        if (!array_key_exists('Worksheets', $inFeatures)) {
            $aReturn[] = $check['3'];
        } elseif (!is_array($inFeatures['Worksheets'])) {
            $aReturn[] = $check['3.1'];
        } elseif (array_key_exists('Worksheets', $inFeatures)) {
            $bReturn = $this->checkInputFeatureContent($inFeatures['Worksheets'], $check);
            if ($bReturn !== []) {
                $aReturn = array_merge($aReturn, $bReturn);
            }
        }
        if ($aReturn != []) {
            throw new \PhpOffice\PhpSpreadsheet\Exception(implode(', ', $aReturn));
        }
    }

    public function internalCheckingErrorMessages() {
        return [
            '1'   => 'Check 1: Missing parameters!',
            '2'   => 'Check 2: No filename provided!',
            '2.1' => 'Check 2.1: Provided filename is not a string!',
            '3'   => 'Check 3: No worksheets provided!',
            '3.1' => 'Check 3.1: Provided worksheets is not an array!',
            '4'   => 'Check 4: No Name was provided for the worksheet #%s !',
            '4.1' => 'Check 4.1: The Name provided for the worksheet #%s is not a string!',
            '5'   => 'Check 5: No Content was provided for the worksheet #%s !',
            '5.1' => 'Check 5.1: The Content provided for the worksheet #%s is not an array!',
        ];
    }

    /**
     * Generate an Excel file from a given array
     *
     * @param array $inFeatures Predefined array of attributes
     */
    public function setArrayToExcel(array $inFeatures) {
        $this->checkInputFeatures($inFeatures);
        $this->objPHPExcel = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        if (isset($inFeatures['Properties'])) {
            $this->setExcelProperties($inFeatures['Properties']);
        }
        foreach ($inFeatures['Worksheets'] as $key => $wkValue) {
            if ($key > 0) {
                $this->objPHPExcel->createSheet();
            }
            $this->objPHPExcel->setActiveSheetIndex($key);
            $this->objPHPExcel->getActiveSheet()->setTitle($wkValue['Name']);
            foreach ($wkValue['Content'] as $cntValue) {
                $rowIndex = $cntValue['StartingRowIndex'];
                foreach ($cntValue['ContentArray'] as $key2 => $value2) {
                    if ($key2 == 0) {
                        $this->setExcelHeaderCellContent([
                            'StartingColumnIndex' => $cntValue['StartingColumnIndex'],
                            'StartingRowIndex'    => $rowIndex,
                            'RowValues'           => array_keys($value2),
                        ]);
                    }
                    $aRow = [
                        'StartingColumnIndex' => $cntValue['StartingColumnIndex'],
                        'CurrentRowIndex'     => ($rowIndex + 1),
                        'RowValues'           => $value2,
                    ];
                    if (array_key_exists('ContentFormatCode', $cntValue)) {
                        $aRow['ContentFormatCode'] = $cntValue['ContentFormatCode'];
                    }
                    if (array_key_exists('WrapText', $cntValue)) {
                        $aRow['WrapText'] = $cntValue['WrapText'];
                    }
                    if (array_key_exists('ContentFormatting', $cntValue)) {
                        $aRow['ContentFormatting'] = $cntValue['ContentFormatting'];
                    }
                    $this->setExcelRowCellContent($aRow);
                    $rowIndex++;
                }
                $this->setExcelWorksheetPagination();
                $this->setExcelWorksheetUsability([
                    'StartingColumnIndex' => $cntValue['StartingColumnIndex'],
                    'HeaderRowIndex'      => $cntValue['StartingRowIndex'],
                ]);
            }
        }
        $this->objPHPExcel->setActiveSheetIndex(0);
        $inFeatures['Filename'] = filter_var($inFeatures['Filename'], FILTER_SANITIZE_STRING);
        $bolForceSave           = false;
        if (array_key_exists('ForceSave', $inFeatures)) {
            $bolForceSave = $inFeatures['ForceSave'];
        }
        if (array_key_exists('FileFormat', $inFeatures)) {
            switch ($inFeatures['FileFormat']) {
                case 'Excel2007':
                default:
                    $objWriter = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($this->objPHPExcel);
                    break;
                case 'Excel97-2003':
                    $objWriter = new \PhpOffice\PhpSpreadsheet\Writer\Xls($this->objPHPExcel);
                    break;
            }
        } else {
            $objWriter = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($this->objPHPExcel);
        }
        if ($bolForceSave || in_array(PHP_SAPI, ['cli', 'cli-server'])) {
            $strFileNamePath = '';
            if (array_key_exists('FilePath', $inFeatures)) {
                $strFileNamePath = $inFeatures['FilePath'];
            }
            $objWriter->save($strFileNamePath . $inFeatures['Filename']);
        } else {
            $this->setForcedHeadersWhenNotCli($inFeatures['Filename']);
            $objWriter->save('php://output');
            ob_flush();
        }
        unset($this->objPHPExcel);
    }

    /**
     * Outputs the header cells
     *
     * @param array $inputs
     */
    private function setExcelHeaderCellContent(array $inputs) {
        $columnCounter = $inputs['StartingColumnIndex'];
        foreach ($inputs['RowValues'] as $value2) {
            $crtCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnCounter);
            $this->objPHPExcel
                ->getActiveSheet()
                ->getColumnDimension($crtCol)
                ->setAutoSize(true);
            $this->objPHPExcel
                ->getActiveSheet()
                ->setCellValue($crtCol . $inputs['StartingRowIndex'], $value2);
            $this->objPHPExcel
                ->getActiveSheet()
                ->getStyle($crtCol . $inputs['StartingRowIndex'])
                ->getFill()
                ->applyFromArray([
                    'type'       => 'solid',
                    'startcolor' => ['rgb' => 'CCCCCC'],
                    'endcolor'   => ['rgb' => 'CCCCCC'],
            ]);
            $this->objPHPExcel
                ->getActiveSheet()
                ->getStyle($crtCol . $inputs['StartingRowIndex'])
                ->applyFromArray([
                    'font' => [
                        'bold'  => true,
                        'color' => ['rgb' => '000000'],
                    ]
            ]);
            $columnCounter++;
        }
        $this->objPHPExcel
            ->getActiveSheet()
            ->calculateColumnWidths();
    }

    /**
     * sets the Properties for the Excel file
     *
     * @param array $inProperties
     */
    private function setExcelProperties(array $inProperties) {
        if (array_key_exists('Creator', $inProperties)) {
            $this->objPHPExcel->getProperties()->setCreator($inProperties['Creator']);
        }
        if (array_key_exists('LastModifiedBy', $inProperties)) {
            $this->objPHPExcel->getProperties()->setLastModifiedBy($inProperties['LastModifiedBy']);
        }
        if (array_key_exists('description', $inProperties)) {
            $this->objPHPExcel->getProperties()->setDescription($inProperties['description']);
        }
        if (array_key_exists('subject', $inProperties)) {
            $this->objPHPExcel->getProperties()->setSubject($inProperties['subject']);
        }
        if (array_key_exists('title', $inProperties)) {
            $this->objPHPExcel->getProperties()->setTitle($inProperties['title']);
        }
    }

    /**
     * Outputs the content cells
     *
     * @param array $inputs
     */
    private function setExcelRowCellContent(array $inputs) {
        $clsDate       = new \PhpOffice\PhpSpreadsheet\Shared\Date();
        $columnCounter = $inputs['StartingColumnIndex'];
        $strRegExpr    = '/^(\d{1,4}[ \.\/\-][A-Z]{3,9}([ \.\/\-]\d{1,4})?|[A-Z]{3,9}[ \.\/\-]\d{1,4}'
            . '([ \.\/\-]\d{1,4})?|\d{1,4}[ \.\/\-]\d{1,4}([ \.\/\-]\d{1,4})?)( \d{1,2}:\d{1,2}'
            . '(:\d{1,2})?)?$/iu';
        $strDateFormat = \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2;

        foreach ($inputs['RowValues'] as $key2 => $value2) {
            $crCol       = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnCounter);
            $this->objPHPExcel
                ->getActiveSheet()
                ->getColumnDimension($crCol)
                ->setAutoSize(true);
            $crtCellAddr = $crCol . $inputs['CurrentRowIndex'];
            $cntLen      = strlen($value2);
            if (($value2 == '') || ($value2 == '00:00:00') || ($value2 == '0')) {
                $value2 = '';

            } elseif (in_array($cntLen, [7, 8, 9]) && (($cntLen - strlen(str_replace(':', '', $value2))) == 2)) {
                $this->objPHPExcel
                    ->getActiveSheet()
                    ->SetCellValue($crtCellAddr, ($this->setLocalTime2Seconds($value2) / 60 / 60 / 24));
                $this->objPHPExcel
                    ->getActiveSheet()
                    ->getStyle($crtCellAddr)
                    ->getNumberFormat()
                    ->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME);
            } elseif (preg_match($strRegExpr, $value2) && !is_integer($value2) && !is_numeric($value2)) {
                $this->objPHPExcel
                    ->getActiveSheet()
                    ->SetCellValue($crtCellAddr, $clsDate->stringToExcel($value2));
                if (!array_key_exists($key2, $inputs['ContentFormatCode'])) {
                    $inputs['ContentFormatCode'][$key2] = $strDateFormat;
                }
                $this->objPHPExcel
                    ->getActiveSheet()
                    ->getStyle($crtCellAddr)
                    ->getNumberFormat()
                    ->setFormatCode($inputs['ContentFormatCode'][$key2]);
            } else {
                if (array_key_exists('WrapText', $inputs) && in_array($key2, $inputs['WrapText'])) {
                    $this->objPHPExcel
                        ->getActiveSheet()
                        ->getStyle($crtCellAddress)

                        ->getAlignment()
                        ->setWrapText(true);
                }
                if (array_key_exists('ContentFormatting', $inputs) && array_key_exists($key2, $inputs['ContentFormatting'])) {
                    $this->objPHPExcel
                        ->getActiveSheet()
                        ->setCellValueExplicit($crtCellAddr, strip_tags($value2), $inputs['ContentFormatting'][$key2]);
                } else {
                    $this->objPHPExcel
                        ->getActiveSheet()
                        ->setCellValue($crtCellAddr, strip_tags($value2));
                }
            }
            $columnCounter += 1;
        }
    }

    /**
     * sets the Pagination
     *
     */
    private function setExcelWorksheetPagination() {
        $this->objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation('portrait');
        $this->objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(9); //coresponding to A4
        $margin = 0.7 / 2.54; // margin is set in inches (0.7cm)
        $this->objPHPExcel->getActiveSheet()->getPageMargins()->setHeader($margin);
        $this->objPHPExcel->getActiveSheet()->getPageMargins()->setTop($margin * 2);
        $this->objPHPExcel->getActiveSheet()->getPageMargins()->setBottom($margin);
        $this->objPHPExcel->getActiveSheet()->getPageMargins()->setLeft($margin);
        $this->objPHPExcel->getActiveSheet()->getPageMargins()->setRight($margin);
        // add header content
        $this->objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&F&RPage &P / &N');
        $this->objPHPExcel->getActiveSheet()->setPrintGridlines(true); // activate printing of gridlines
    }

    /**
     * Sets a few usability features
     *
     * @param array $inputs
     */
    private function setExcelWorksheetUsability($inputs) {
        // repeat coloumn headings for every new page...
        $this->objPHPExcel
            ->getActiveSheet()
            ->getPageSetup()
            ->setRowsToRepeatAtTopByStartAndEnd(1, $inputs['HeaderRowIndex']);
        // activate AutoFilter
        $autoFilterArea = implode('', [
            \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($inputs['StartingColumnIndex']),
            $inputs['HeaderRowIndex'],
            ':',
            $this->objPHPExcel->getActiveSheet()->getHighestDataColumn(),
            $this->objPHPExcel->getActiveSheet()->getHighestDataRow(),
        ]);
        $this->objPHPExcel
            ->getActiveSheet()
            ->setAutoFilter($autoFilterArea);
        // freeze 1st top row
        $this->objPHPExcel
            ->getActiveSheet()
            ->freezePane('A' . ($inputs['HeaderRowIndex'] + 1));
    }

    private function setForcedHeadersWhenNotCli($strFileName) {
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Pragma: private');
        header('Cache-control: private, must-revalidate');
        header('Content-Disposition: attachment;filename="' . $strFileName . '"');
        header('Cache-Control: max-age=0');
    }

    /**
     * Converts the time string given into native Excel format (number)
     *
     * @param string $intRsqlTime
     * @return string
     */
    private function setLocalTime2Seconds($intRsqlTime) {
        $sign = '';
        if (is_null($intRsqlTime) || ($intRsqlTime == '')) {
            $intRsqlTime = '00:00:00';
        } elseif (substr($intRsqlTime, 0, 1) == '-') {
            //extract negative sign and keep it separatly until ending
            $intRsqlTime = substr($intRsqlTime, 1, strlen($intRsqlTime) - 1);
            $sign        = '-';
        }
        $resultParts = [
            'seconds' => substr($intRsqlTime, -2),
            'minutes' => substr($intRsqlTime, -5, 2) * 60,
            'hours'   => substr($intRsqlTime, 0, strlen($intRsqlTime) - 6) * 60 * 60,
        ];
        return $sign . implode('', array_values($resultParts));
    }
}


1			<?php
2
3			/**
4			*
5			* The MIT License (MIT)
6			*
7			* Copyright (c) 2015 - 2024 Daniel Popiniuc
8			*
9			* Permission is hereby granted, free of charge, to any person obtaining a copy
10			* of this software and associated documentation files (the "Software"), to deal
11			* in the Software without restriction, including without limitation the rights
12			* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
13			* copies of the Software, and to permit persons to whom the Software is
14			* furnished to do so, subject to the following conditions:
15			*
16			* The above copyright notice and this permission notice shall be included in all
17			* copies or substantial portions of the Software.
18			*
19			* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
20			* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
21			* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
22			* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
23			* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
24			* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
25			* SOFTWARE.
26			*
27			*/
28
29			namespace danielgp\io_operations;
30
31			/**
32			* Description of InputOutputExcel
33			*
34			* @author Daniel Popiniuc
35			*/
36			trait InputOutputExcel
37			{
38
39			private $objPHPExcel;
40
41			/**
42			* manages further inputs checks
43			*
44			* @param array $inFeaturesWorksheets Predefined array of attributes
45			* @param array $check Checking messages
46			* @return array\|string[]
47			*/
48			private function checkInputFeatureContent($inFeaturesWorksheets, $check) {
49			$aReturn = [];
50			foreach ($inFeaturesWorksheets as $key => $value) {
51			if (!array_key_exists('Name', $value)) {
52			$aReturn[] = sprintf($check['4'], $key);
53			} elseif (!is_string($value['Name'])) {
54			$aReturn[] = sprintf($check['4.1'], $key);
55			}
56			if (!array_key_exists('Content', $value)) {
57			$aReturn[] = sprintf($check['5'], $key);
58			} elseif (!is_array($value['Content'])) {
59			$aReturn[] = sprintf($check['5.1'], $key);
60			}
61			}
62			return $aReturn;
63			}
64
65			/**
66			* manages the inputs checks
67			*
68			* @param array $inFeatures Predefined array of attributes
69			* @return array\|string[]
70			*/
71			private function checkInputFeatures(array $inFeatures) {
72			$aReturn = [];
73			$check = $this->internalCheckingErrorMessages();
74			if ($inFeatures === []) {
75			$aReturn[] = $check['1'];
76			}
77			if (!array_key_exists('Filename', $inFeatures)) {
78			$aReturn[] = $check['2'];
79			} elseif (!is_string($inFeatures['Filename'])) {
80			$aReturn[] = $check['2.1'];
81			}
82			if (!array_key_exists('Worksheets', $inFeatures)) {
83			$aReturn[] = $check['3'];
84			} elseif (!is_array($inFeatures['Worksheets'])) {
85			$aReturn[] = $check['3.1'];
86			} elseif (array_key_exists('Worksheets', $inFeatures)) {
87			$bReturn = $this->checkInputFeatureContent($inFeatures['Worksheets'], $check);
88			if ($bReturn !== []) {
89			$aReturn = array_merge($aReturn, $bReturn);
90			}
91			}
92			if ($aReturn != []) {
93			throw new \PhpOffice\PhpSpreadsheet\Exception(implode(', ', $aReturn));
94			}
95			}
96
97			public function internalCheckingErrorMessages() {
98			return [
99			'1' => 'Check 1: Missing parameters!',
100			'2' => 'Check 2: No filename provided!',
101			'2.1' => 'Check 2.1: Provided filename is not a string!',
102			'3' => 'Check 3: No worksheets provided!',
103			'3.1' => 'Check 3.1: Provided worksheets is not an array!',
104			'4' => 'Check 4: No Name was provided for the worksheet #%s !',
105			'4.1' => 'Check 4.1: The Name provided for the worksheet #%s is not a string!',
106			'5' => 'Check 5: No Content was provided for the worksheet #%s !',
107			'5.1' => 'Check 5.1: The Content provided for the worksheet #%s is not an array!',
108			];
109			}
110
111			/**
112			* Generate an Excel file from a given array
113			*
114			* @param array $inFeatures Predefined array of attributes
115			*/
116			public function setArrayToExcel(array $inFeatures) {
117			$this->checkInputFeatures($inFeatures);
118			$this->objPHPExcel = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
119			if (isset($inFeatures['Properties'])) {
120			$this->setExcelProperties($inFeatures['Properties']);
121			}
122			foreach ($inFeatures['Worksheets'] as $key => $wkValue) {
123			if ($key > 0) {
124			$this->objPHPExcel->createSheet();
125			}
126			$this->objPHPExcel->setActiveSheetIndex($key);
127			$this->objPHPExcel->getActiveSheet()->setTitle($wkValue['Name']);
128			foreach ($wkValue['Content'] as $cntValue) {
129			$rowIndex = $cntValue['StartingRowIndex'];
130			foreach ($cntValue['ContentArray'] as $key2 => $value2) {
131			if ($key2 == 0) {
132			$this->setExcelHeaderCellContent([
133			'StartingColumnIndex' => $cntValue['StartingColumnIndex'],
134			'StartingRowIndex' => $rowIndex,
135			'RowValues' => array_keys($value2),
136			]);
137			}
138			$aRow = [
139			'StartingColumnIndex' => $cntValue['StartingColumnIndex'],
140			'CurrentRowIndex' => ($rowIndex + 1),
141			'RowValues' => $value2,
142			];
143			if (array_key_exists('ContentFormatCode', $cntValue)) {
144			$aRow['ContentFormatCode'] = $cntValue['ContentFormatCode'];
145			}
146			if (array_key_exists('WrapText', $cntValue)) {
147			$aRow['WrapText'] = $cntValue['WrapText'];
148			}
149			if (array_key_exists('ContentFormatting', $cntValue)) {
150			$aRow['ContentFormatting'] = $cntValue['ContentFormatting'];
151			}
152			$this->setExcelRowCellContent($aRow);
153			$rowIndex++;
154			}
155			$this->setExcelWorksheetPagination();
156			$this->setExcelWorksheetUsability([
157			'StartingColumnIndex' => $cntValue['StartingColumnIndex'],
158			'HeaderRowIndex' => $cntValue['StartingRowIndex'],
159			]);
160			}
161			}
162			$this->objPHPExcel->setActiveSheetIndex(0);
163			$inFeatures['Filename'] = filter_var($inFeatures['Filename'], FILTER_SANITIZE_STRING);
164			$bolForceSave = false;
165			if (array_key_exists('ForceSave', $inFeatures)) {
166			$bolForceSave = $inFeatures['ForceSave'];
167			}
168			if (array_key_exists('FileFormat', $inFeatures)) {
169			switch ($inFeatures['FileFormat']) {
170			case 'Excel2007':
171			default:
172			$objWriter = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($this->objPHPExcel);
173			break;
174			case 'Excel97-2003':
175			$objWriter = new \PhpOffice\PhpSpreadsheet\Writer\Xls($this->objPHPExcel);
176			break;
177			}
178			} else {
179			$objWriter = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($this->objPHPExcel);
180			}
181			if ($bolForceSave \|\| in_array(PHP_SAPI, ['cli', 'cli-server'])) {
182			$strFileNamePath = '';
183			if (array_key_exists('FilePath', $inFeatures)) {
184			$strFileNamePath = $inFeatures['FilePath'];
185			}
186			$objWriter->save($strFileNamePath . $inFeatures['Filename']);
187			} else {
188			$this->setForcedHeadersWhenNotCli($inFeatures['Filename']);
189			$objWriter->save('php://output');
190			ob_flush();
191			}
192			unset($this->objPHPExcel);
193			}
194
195			/**
196			* Outputs the header cells
197			*
198			* @param array $inputs
199			*/
200			private function setExcelHeaderCellContent(array $inputs) {
201			$columnCounter = $inputs['StartingColumnIndex'];
202			foreach ($inputs['RowValues'] as $value2) {
203			$crtCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnCounter);
204			$this->objPHPExcel
205			->getActiveSheet()
206			->getColumnDimension($crtCol)
207			->setAutoSize(true);
208			$this->objPHPExcel
209			->getActiveSheet()
210			->setCellValue($crtCol . $inputs['StartingRowIndex'], $value2);
211			$this->objPHPExcel
212			->getActiveSheet()
213			->getStyle($crtCol . $inputs['StartingRowIndex'])
214			->getFill()
215			->applyFromArray([
216			'type' => 'solid',
217			'startcolor' => ['rgb' => 'CCCCCC'],
218			'endcolor' => ['rgb' => 'CCCCCC'],
219			]);
220			$this->objPHPExcel
221			->getActiveSheet()
222			->getStyle($crtCol . $inputs['StartingRowIndex'])
223			->applyFromArray([
224			'font' => [
225			'bold' => true,
226			'color' => ['rgb' => '000000'],
227			]
228			]);
229			$columnCounter++;
230			}
231			$this->objPHPExcel
232			->getActiveSheet()
233			->calculateColumnWidths();
234			}
235
236			/**
237			* sets the Properties for the Excel file
238			*
239			* @param array $inProperties
240			*/
241			private function setExcelProperties(array $inProperties) {
242			if (array_key_exists('Creator', $inProperties)) {
243			$this->objPHPExcel->getProperties()->setCreator($inProperties['Creator']);
244			}
245			if (array_key_exists('LastModifiedBy', $inProperties)) {
246			$this->objPHPExcel->getProperties()->setLastModifiedBy($inProperties['LastModifiedBy']);
247			}
248			if (array_key_exists('description', $inProperties)) {
249			$this->objPHPExcel->getProperties()->setDescription($inProperties['description']);
250			}
251			if (array_key_exists('subject', $inProperties)) {
252			$this->objPHPExcel->getProperties()->setSubject($inProperties['subject']);
253			}
254			if (array_key_exists('title', $inProperties)) {
255			$this->objPHPExcel->getProperties()->setTitle($inProperties['title']);
256			}
257			}
258
259			/**
260			* Outputs the content cells
261			*
262			* @param array $inputs
263			*/
264			private function setExcelRowCellContent(array $inputs) {
265			$clsDate = new \PhpOffice\PhpSpreadsheet\Shared\Date();
266			$columnCounter = $inputs['StartingColumnIndex'];
267			$strRegExpr = '/^(\d{1,4}[ \.\/\-][A-Z]{3,9}([ \.\/\-]\d{1,4})?\|[A-Z]{3,9}[ \.\/\-]\d{1,4}'
268			. '([ \.\/\-]\d{1,4})?\|\d{1,4}[ \.\/\-]\d{1,4}([ \.\/\-]\d{1,4})?)( \d{1,2}:\d{1,2}'
269			. '(:\d{1,2})?)?$/iu';
270			$strDateFormat = \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2;
			0 ignored issues – show Bug introduced 2024-07-22 21:37 UTC by Report Bug Copy Issue Report The constant `PhpOffice\PhpSpreadsheet...::FORMAT_DATE_YYYYMMDD2` was not found. Maybe you did not declare it correctly or list all dependencies? Loading history...
271			foreach ($inputs['RowValues'] as $key2 => $value2) {
272			$crCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnCounter);
273			$this->objPHPExcel
274			->getActiveSheet()
275			->getColumnDimension($crCol)
276			->setAutoSize(true);
277			$crtCellAddr = $crCol . $inputs['CurrentRowIndex'];
278			$cntLen = strlen($value2);
279			if (($value2 == '') \|\| ($value2 == '00:00:00') \|\| ($value2 == '0')) {
280			$value2 = '';
			0 ignored issues – show Unused Code introduced 2024-07-22 21:37 UTC by Report Bug Copy Issue Report The assignment to `$value2` is dead and can be removed. Loading history...
281			} elseif (in_array($cntLen, [7, 8, 9]) && (($cntLen - strlen(str_replace(':', '', $value2))) == 2)) {
282			$this->objPHPExcel
283			->getActiveSheet()
284			->SetCellValue($crtCellAddr, ($this->setLocalTime2Seconds($value2) / 60 / 60 / 24));
285			$this->objPHPExcel
286			->getActiveSheet()
287			->getStyle($crtCellAddr)
288			->getNumberFormat()
289			->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_DATETIME);
290			} elseif (preg_match($strRegExpr, $value2) && !is_integer($value2) && !is_numeric($value2)) {
291			$this->objPHPExcel
292			->getActiveSheet()
293			->SetCellValue($crtCellAddr, $clsDate->stringToExcel($value2));
294			if (!array_key_exists($key2, $inputs['ContentFormatCode'])) {
295			$inputs['ContentFormatCode'][$key2] = $strDateFormat;
296			}
297			$this->objPHPExcel
298			->getActiveSheet()
299			->getStyle($crtCellAddr)
300			->getNumberFormat()
301			->setFormatCode($inputs['ContentFormatCode'][$key2]);
302			} else {
303			if (array_key_exists('WrapText', $inputs) && in_array($key2, $inputs['WrapText'])) {
304			$this->objPHPExcel
305			->getActiveSheet()
306			->getStyle($crtCellAddress)
			0 ignored issues – show Comprehensibility Best Practice introduced 2024-07-22 21:37 UTC by Report Bug Copy Issue Report The variable `$crtCellAddress` does not exist. Did you maybe mean `$crtCellAddr`? Loading history...
307			->getAlignment()
308			->setWrapText(true);
309			}
310			if (array_key_exists('ContentFormatting', $inputs) && array_key_exists($key2, $inputs['ContentFormatting'])) {
311			$this->objPHPExcel
312			->getActiveSheet()
313			->setCellValueExplicit($crtCellAddr, strip_tags($value2), $inputs['ContentFormatting'][$key2]);
314			} else {
315			$this->objPHPExcel
316			->getActiveSheet()
317			->setCellValue($crtCellAddr, strip_tags($value2));
318			}
319			}
320			$columnCounter += 1;
321			}
322			}
323
324			/**
325			* sets the Pagination
326			*
327			*/
328			private function setExcelWorksheetPagination() {
329			$this->objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation('portrait');
330			$this->objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(9); //coresponding to A4
331			$margin = 0.7 / 2.54; // margin is set in inches (0.7cm)
332			$this->objPHPExcel->getActiveSheet()->getPageMargins()->setHeader($margin);
333			$this->objPHPExcel->getActiveSheet()->getPageMargins()->setTop($margin * 2);
334			$this->objPHPExcel->getActiveSheet()->getPageMargins()->setBottom($margin);
335			$this->objPHPExcel->getActiveSheet()->getPageMargins()->setLeft($margin);
336			$this->objPHPExcel->getActiveSheet()->getPageMargins()->setRight($margin);
337			// add header content
338			$this->objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&F&RPage &P / &N');
339			$this->objPHPExcel->getActiveSheet()->setPrintGridlines(true); // activate printing of gridlines
340			}
341
342			/**
343			* Sets a few usability features
344			*
345			* @param array $inputs
346			*/
347			private function setExcelWorksheetUsability($inputs) {
348			// repeat coloumn headings for every new page...
349			$this->objPHPExcel
350			->getActiveSheet()
351			->getPageSetup()
352			->setRowsToRepeatAtTopByStartAndEnd(1, $inputs['HeaderRowIndex']);
353			// activate AutoFilter
354			$autoFilterArea = implode('', [
355			\PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($inputs['StartingColumnIndex']),
356			$inputs['HeaderRowIndex'],
357			':',
358			$this->objPHPExcel->getActiveSheet()->getHighestDataColumn(),
359			$this->objPHPExcel->getActiveSheet()->getHighestDataRow(),
360			]);
361			$this->objPHPExcel
362			->getActiveSheet()
363			->setAutoFilter($autoFilterArea);
364			// freeze 1st top row
365			$this->objPHPExcel
366			->getActiveSheet()
367			->freezePane('A' . ($inputs['HeaderRowIndex'] + 1));
368			}
369
370			private function setForcedHeadersWhenNotCli($strFileName) {
371			header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
372			header('Pragma: private');
373			header('Cache-control: private, must-revalidate');
374			header('Content-Disposition: attachment;filename="' . $strFileName . '"');
375			header('Cache-Control: max-age=0');
376			}
377
378			/**
379			* Converts the time string given into native Excel format (number)
380			*
381			* @param string $intRsqlTime
382			* @return string
383			*/
384			private function setLocalTime2Seconds($intRsqlTime) {
385			$sign = '';
386			if (is_null($intRsqlTime) \|\| ($intRsqlTime == '')) {
387			$intRsqlTime = '00:00:00';
388			} elseif (substr($intRsqlTime, 0, 1) == '-') {
389			//extract negative sign and keep it separatly until ending
390			$intRsqlTime = substr($intRsqlTime, 1, strlen($intRsqlTime) - 1);
391			$sign = '-';
392			}
393			$resultParts = [
394			'seconds' => substr($intRsqlTime, -2),
395			'minutes' => substr($intRsqlTime, -5, 2) * 60,
396			'hours' => substr($intRsqlTime, 0, strlen($intRsqlTime) - 6) * 60 * 60,
397			];
398			return $sign . implode('', array_values($resultParts));
399			}
400			}
401

danielgp / io-operations

Push — main ( be988c...61b5d4 )

InputOutputExcel::setExcelProperties() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like