SRFExcel::readRowData() - Code Metrics - Inspection of "Excel format: Throw error if phpExcel is missing (..." - SemanticMediaWiki/SemanticResultFormats - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#182)

by Stephan

created 2016-10-17 20:45 UTC

SRFExcel::readRowData() A

↳ Parent: SRFExcel

Complexity

Conditions	3
Paths	3

Size

Total Lines	8
Code Lines	5

Duplication

Lines	0
Ratio	0 %

Importance

Changes

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

<?php

namespace SRF;

use ImagePage;
use SMW\FileExportPrinter;
use ParamProcessor\Definition\StringParam;
use SMWQueryResult;
use PHPExcel;
use PHPExcel_IOFactory;
use PHPExcel_Cell_DataType;
use Sanitizer;
use Title;

/**
 * @author Kim Eik
 * @since 1.9
 */
class SRFExcel extends FileExportPrinter {

	const HEADER_ROW_OFFSET = 1;

	/**
	 * @var int
	 */
	protected $rowNum;

	/**
	 * @var int
	 */
	protected $colNum;

	/**
	 * @var \PHPExcel_Worksheet
	 */
	protected $sheet;

	protected $styled = false;

	/**
	 * Some printers do not mainly produce embeddable HTML or Wikitext, but
	 * produce stand-alone files. An example is RSS or iCalendar. This function
	 * returns the mimetype string that this file would have, or FALSE if no
	 * standalone files are produced.
	 *
	 * If this function returns something other than FALSE, then the printer will
	 * not be regarded as a printer that displays in-line results. This is used to
	 * determine if a file output should be generated in Special:Ask.
	 *
	 * @since 1.8
	 *
	 * @param SMWQueryResult $queryResult
	 *
	 * @return string
	 */
	public function getMimeType( SMWQueryResult $queryResult ) {
		return "application/vnd.ms-excel";
	}

	public function getFileName( SMWQueryResult $queryResult ) {

		return $this->params[ 'filename' ] ? $this->params[ 'filename' ] : round( microtime( true ) * 1000 ) . '.xls';
	}

	public function outputAsFile( SMWQueryResult $queryResult, array $params ) {
		if ( $this->isPHPExcelInstalled() ) {
			parent::outputAsFile( $queryResult, $params );
		} else {
			header( 'Cache-Control: no-store, no-cache, must-revalidate' );
			echo $this->getResult( $queryResult, $params, SMW_OUTPUT_FILE );
		}
	}

	/**
	 * @param $definitions \ParamProcessor\ParamDefinition[]
	 *
	 * @return array
	 */
	public function getParamDefinitions( array $definitions ) {
		$params = parent::getParamDefinitions( $definitions );

		$definitions[ 'searchlabel' ]->setDefault( wfMessage( 'srf-excel-link' )->inContentLanguage()->text() );

		$params[ 'templatefile' ] = array(
			'type' => 'string',
			'name' => 'templatefile',
			'default' => '',
			'message' => 'srf-paramdesc-excel-templatefile',
		);

		$params[ 'filename' ] = array(
			'type' => 'string',
			'name' => 'filename',
			'default' => '',
			'message' => 'srf-paramdesc-excel-filename',
		);

		return $params;
	}

	/**
	 * Return serialised results in specified format.
	 */
	protected function getResultText( SMWQueryResult $res, $outputMode ) {

		if ( $outputMode === SMW_OUTPUT_FILE ) {
			return $this->getResultFileContents( $res );
		}

		$this->isHTML = ( $outputMode === SMW_OUTPUT_HTML );
		return $this->getLink( $res, $outputMode )->getText( $outputMode, $this->mLinker );
	}

	/*
	 * Turns the PHPExcel document object into a string
	 */
	/**
	 * @param $document
	 * @return string
	 */
	protected function writeDocumentToString( $document ) {
		$objWriter = PHPExcel_IOFactory::createWriter( $document, 'Excel5' );

		ob_start();
		$objWriter->save('php://output');
		return ob_get_clean();
	}

	/**
	 * Populates the PHPExcel document with the query data
	 *
	 * @param $res SMWQueryResult the query result
	 */
	protected function populateDocumentWithQueryData( $res ) {
		while ( $row = $res->getNext() ) {
			$this->rowNum++;
			$this->colNum = 0;
			$this->readRowData($row);
		}
	}

	/**
	 * @param SMWQueryResult $res
	 * @return string
	 */
	protected function getResultFileContents( SMWQueryResult $res ) {
		if ( !$this->isPHPExcelInstalled() ) {
			throw new \RuntimeException( wfMessage( 'srf-excel-missing-phpexcel' )->parse() );
		}

		$document = $this->createExcelDocument();
		$this->sheet = $document->getSheet( 0 );

		$this->rowNum = 0;
		//Get headers
		if ( $this->mShowHeaders ) {
			$this->populateDocumentWithHeaders( $res );
			$this->rowNum++;
		}

		//Get data rows
		$this->populateDocumentWithQueryData( $res );

		$document->getActiveSheet()->getDefaultRowDimension()->setRowHeight();

		$result = $this->writeDocumentToString( $document );
		return $result;
	}

	/**
	 * Sets or appends a string value at the given col,row location
	 *
	 * If there already exists a value at a given col,row location, then
	 * convert the cell to a string and append the data value. Creating
	 * a list of comma separated entries.
	 *
	 * @param $object \SMWDataValue the raw data value object
	 */
	protected function setOrAppendStringDataValue( $object ) {
		$type = PHPExcel_Cell_DataType::TYPE_STRING;
		$value = $object->getWikiValue();
		$value = Sanitizer::decodeCharReferences( $value );
		$value = PHPExcel_Cell_DataType::checkString( $value );

		$cell = $this->sheet->getCellByColumnAndRow( $this->colNum, $this->rowNum );
		$existingValue = $cell->getValue();
		if ( $existingValue ) {
			$value = $existingValue . ', ' . $value;
		}
		$cell->setValueExplicit( $value, $type );
	}

	/**
	 * Sets a numeric value at the given col,row location
	 *
	 * @param $object \SMWDataValue the raw data value object
	 */
	protected function setNumberDataValue( $object ) {
		$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
		$value = $object->getDataItem()->getNumber();

		$this->sheet->getCellByColumnAndRow( $this->colNum, $this->rowNum )
			->setValueExplicit( $value, $type );
	}

	/**
	 * Sets a quantity value at the given col,row location
	 *
	 * @param $object \SMWDataValue  the raw data value object
	 */
	protected function setQuantityDataValue( $object ) {
		$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
		$unit = $object->getUnit();
		$value = $object->getNumber();

		$this->sheet->getCellByColumnAndRow( $this->colNum, $this->rowNum )
			->setValueExplicit( $value, $type );

		if ( !$this->styled ) {
			$this->sheet->getStyleByColumnAndRow( $this->colNum, $this->rowNum )
				->getNumberFormat()
				->setFormatCode( '0 "' . $unit . '"' );
		}
	}

	/**
	 * Sets a date/time value at the given col,row location
	 *
	 * @param \SMWTimeValue $object the raw data value object
	 */
	protected function setTimeDataValue ( \SMWTimeValue $object ) {
		$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
		$value = \PHPExcel_Shared_Date::stringToExcel( str_replace( 'T', ' ', $object->getISO8601Date() ) );

		$this->sheet
			->getCellByColumnAndRow( $this->colNum, $this->rowNum )
			->setValueExplicit( $value, $type );

		if ( !$this->styled ) {
			$this->sheet
				->getStyleByColumnAndRow( $this->colNum, $this->rowNum )
				->getNumberFormat()
				->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
		}
	}

	/**
	 * Populates the PHPExcel sheet with the headers from the result query
	 *
	 * @param SMWQueryResult $res The query result
	 */
	protected function populateDocumentWithHeaders( SMWQueryResult $res ) {
		$this->colNum = 0;
		foreach ( $res->getPrintRequests() as $pr ) {
			$header = $pr->getLabel();
			if($this->showLabel($header) ){
				$this->sheet->setCellValueByColumnAndRow( $this->colNum, self::HEADER_ROW_OFFSET, $header )
					->getStyleByColumnAndRow( $this->colNum, self::HEADER_ROW_OFFSET )
					->getFont()
					->setBold( true );
				$this->colNum++;
			}
		}
	}

	/**
	 * Creates a new PHPExcel document and returns it
	 *
	 * @return PHPExcel
	 */
	protected function createExcelDocument() {

		$fileTitle = Title::newFromText( $this->params[ 'templatefile' ], NS_FILE );

		if ( $fileTitle !== null && $fileTitle->exists() ) {

			$filePage = new ImagePage( $fileTitle, $this );

			$virtualFile = $filePage->getDisplayedFile();
			$virtualFilePath =  $virtualFile->getPath();

			$localFile= $virtualFile->getRepo()->getLocalReference( $virtualFilePath );
			$localFilePath = $localFile->getPath();

			$objPHPExcel = PHPExcel_IOFactory::load( $localFilePath );

			$this->styled = true;

		} else {

			$objPHPExcel = new PHPExcel();

		}

		// Set document properties
		$objPHPExcel->getProperties()->setCreator( "SemanticMediaWiki PHPExcel Export" );

		return $objPHPExcel;
	}

	/**
	 * Check for the existence of the extra mainlabel.
	 * @param $label
	 * @return bool
	 */
	private function showLabel( $label ) {
		return !(array_key_exists("mainlabel", $this->params) && $label === $this->params[ "mainlabel" ] . '#');
	}

	protected function readFieldValue( $field ) {
		$valueCount = 0;
		while ( ( $object = $field->getNextDataValue() ) !== false ) {
			if( $valueCount === 0 ) {
				$this->setValueAccordingToType($object);
			} else {
				$this->setOrAppendStringDataValue($object);
			}
			$valueCount++;
		}
	}

	/**
	 * Checks the type of the value, and set's it in the sheet accordingly
	 * @param $object
	 */
	protected function setValueAccordingToType( $object ) {
		//NOTE: must check against subclasses before superclasses
		if( $object instanceof \SMWQuantityValue ) {
			$this->setQuantityDataValue($object);
		} else if( $object instanceof \SMWNumberValue ) {
			$this->setNumberDataValue($object);
		} else if ( $object instanceof \SMWTimeValue ) {
			$this->setTimeDataValue( $object );
		} else {
			$this->setOrAppendStringDataValue($object);
		}
	}

	/**
	 * @param $row
	 */
	protected function readRowData( $row ) {
		foreach ( $row as $field ) {
			if( $this->showLabel($field->getPrintRequest()->getLabel()) ) {
				$this->readFieldValue($field);
				$this->colNum++;
			}
		}
	}

	private function isPHPExcelInstalled() {
		return class_exists( "PHPExcel" );
	}

}



1			<?php
2
3			namespace SRF;
4
5			use ImagePage;
6			use SMW\FileExportPrinter;
7			use ParamProcessor\Definition\StringParam;
8			use SMWQueryResult;
9			use PHPExcel;
10			use PHPExcel_IOFactory;
11			use PHPExcel_Cell_DataType;
12			use Sanitizer;
13			use Title;
14
15			/**
16			* @author Kim Eik
17			* @since 1.9
18			*/
19			class SRFExcel extends FileExportPrinter {
20
21			const HEADER_ROW_OFFSET = 1;
22
23			/**
24			* @var int
25			*/
26			protected $rowNum;
27
28			/**
29			* @var int
30			*/
31			protected $colNum;
32
33			/**
34			* @var \PHPExcel_Worksheet
35			*/
36			protected $sheet;
37
38			protected $styled = false;
39
40			/**
41			* Some printers do not mainly produce embeddable HTML or Wikitext, but
42			* produce stand-alone files. An example is RSS or iCalendar. This function
43			* returns the mimetype string that this file would have, or FALSE if no
44			* standalone files are produced.
45			*
46			* If this function returns something other than FALSE, then the printer will
47			* not be regarded as a printer that displays in-line results. This is used to
48			* determine if a file output should be generated in Special:Ask.
49			*
50			* @since 1.8
51			*
52			* @param SMWQueryResult $queryResult
53			*
54			* @return string
55			*/
56			public function getMimeType( SMWQueryResult $queryResult ) {
57			return "application/vnd.ms-excel";
58			}
59
60			public function getFileName( SMWQueryResult $queryResult ) {
61
62			return $this->params[ 'filename' ] ? $this->params[ 'filename' ] : round( microtime( true ) * 1000 ) . '.xls';
63			}
64
65			public function outputAsFile( SMWQueryResult $queryResult, array $params ) {
66			if ( $this->isPHPExcelInstalled() ) {
67			parent::outputAsFile( $queryResult, $params );
68			} else {
69			header( 'Cache-Control: no-store, no-cache, must-revalidate' );
70			echo $this->getResult( $queryResult, $params, SMW_OUTPUT_FILE );
71			}
72			}
73
74			/**
75			* @param $definitions \ParamProcessor\ParamDefinition[]
76			*
77			* @return array
78			*/
79			public function getParamDefinitions( array $definitions ) {
80			$params = parent::getParamDefinitions( $definitions );
81
82			$definitions[ 'searchlabel' ]->setDefault( wfMessage( 'srf-excel-link' )->inContentLanguage()->text() );
83
84			$params[ 'templatefile' ] = array(
85			'type' => 'string',
86			'name' => 'templatefile',
87			'default' => '',
88			'message' => 'srf-paramdesc-excel-templatefile',
89			);
90
91			$params[ 'filename' ] = array(
92			'type' => 'string',
93			'name' => 'filename',
94			'default' => '',
95			'message' => 'srf-paramdesc-excel-filename',
96			);
97
98			return $params;
99			}
100
101			/**
102			* Return serialised results in specified format.
103			*/
104			protected function getResultText( SMWQueryResult $res, $outputMode ) {
105
106			if ( $outputMode === SMW_OUTPUT_FILE ) {
107			return $this->getResultFileContents( $res );
108			}
109
110			$this->isHTML = ( $outputMode === SMW_OUTPUT_HTML );
111			return $this->getLink( $res, $outputMode )->getText( $outputMode, $this->mLinker );
112			}
113
114			/*
115			* Turns the PHPExcel document object into a string
116			*/
117			/**
118			* @param $document
119			* @return string
120			*/
121			protected function writeDocumentToString( $document ) {
122			$objWriter = PHPExcel_IOFactory::createWriter( $document, 'Excel5' );
123
124			ob_start();
125			$objWriter->save('php://output');
126			return ob_get_clean();
127			}
128
129			/**
130			* Populates the PHPExcel document with the query data
131			*
132			* @param $res SMWQueryResult the query result
133			*/
134			protected function populateDocumentWithQueryData( $res ) {
135			while ( $row = $res->getNext() ) {
136			$this->rowNum++;
137			$this->colNum = 0;
138			$this->readRowData($row);
139			}
140			}
141
142			/**
143			* @param SMWQueryResult $res
144			* @return string
145			*/
146			protected function getResultFileContents( SMWQueryResult $res ) {
147			if ( !$this->isPHPExcelInstalled() ) {
148			throw new \RuntimeException( wfMessage( 'srf-excel-missing-phpexcel' )->parse() );
149			}
150
151			$document = $this->createExcelDocument();
152			$this->sheet = $document->getSheet( 0 );
153
154			$this->rowNum = 0;
155			//Get headers
156			if ( $this->mShowHeaders ) {
157			$this->populateDocumentWithHeaders( $res );
158			$this->rowNum++;
159			}
160
161			//Get data rows
162			$this->populateDocumentWithQueryData( $res );
163
164			$document->getActiveSheet()->getDefaultRowDimension()->setRowHeight();
165
166			$result = $this->writeDocumentToString( $document );
167			return $result;
168			}
169
170			/**
171			* Sets or appends a string value at the given col,row location
172			*
173			* If there already exists a value at a given col,row location, then
174			* convert the cell to a string and append the data value. Creating
175			* a list of comma separated entries.
176			*
177			* @param $object \SMWDataValue the raw data value object
178			*/
179			protected function setOrAppendStringDataValue( $object ) {
180			$type = PHPExcel_Cell_DataType::TYPE_STRING;
181			$value = $object->getWikiValue();
182			$value = Sanitizer::decodeCharReferences( $value );
183			$value = PHPExcel_Cell_DataType::checkString( $value );
184
185			$cell = $this->sheet->getCellByColumnAndRow( $this->colNum, $this->rowNum );
186			$existingValue = $cell->getValue();
187			if ( $existingValue ) {
188			$value = $existingValue . ', ' . $value;
189			}
190			$cell->setValueExplicit( $value, $type );
191			}
192
193			/**
194			* Sets a numeric value at the given col,row location
195			*
196			* @param $object \SMWDataValue the raw data value object
197			*/
198			protected function setNumberDataValue( $object ) {
199			$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
200			$value = $object->getDataItem()->getNumber();
201
202			$this->sheet->getCellByColumnAndRow( $this->colNum, $this->rowNum )
203			->setValueExplicit( $value, $type );
204			}
205
206			/**
207			* Sets a quantity value at the given col,row location
208			*
209			* @param $object \SMWDataValue the raw data value object
210			*/
211			protected function setQuantityDataValue( $object ) {
212			$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
213			$unit = $object->getUnit();
214			$value = $object->getNumber();
215
216			$this->sheet->getCellByColumnAndRow( $this->colNum, $this->rowNum )
217			->setValueExplicit( $value, $type );
218
219			if ( !$this->styled ) {
220			$this->sheet->getStyleByColumnAndRow( $this->colNum, $this->rowNum )
221			->getNumberFormat()
222			->setFormatCode( '0 "' . $unit . '"' );
223			}
224			}
225
226			/**
227			* Sets a date/time value at the given col,row location
228			*
229			* @param \SMWTimeValue $object the raw data value object
230			*/
231			protected function setTimeDataValue ( \SMWTimeValue $object ) {
232			$type = PHPExcel_Cell_DataType::TYPE_NUMERIC;
233			$value = \PHPExcel_Shared_Date::stringToExcel( str_replace( 'T', ' ', $object->getISO8601Date() ) );
234
235			$this->sheet
236			->getCellByColumnAndRow( $this->colNum, $this->rowNum )
237			->setValueExplicit( $value, $type );
238
239			if ( !$this->styled ) {
240			$this->sheet
241			->getStyleByColumnAndRow( $this->colNum, $this->rowNum )
242			->getNumberFormat()
243			->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_DATE_DDMMYYYY);
244			}
245			}
246
247			/**
248			* Populates the PHPExcel sheet with the headers from the result query
249			*
250			* @param SMWQueryResult $res The query result
251			*/
252			protected function populateDocumentWithHeaders( SMWQueryResult $res ) {
253			$this->colNum = 0;
254			foreach ( $res->getPrintRequests() as $pr ) {
255			$header = $pr->getLabel();
256			if($this->showLabel($header) ){
257			$this->sheet->setCellValueByColumnAndRow( $this->colNum, self::HEADER_ROW_OFFSET, $header )
258			->getStyleByColumnAndRow( $this->colNum, self::HEADER_ROW_OFFSET )
259			->getFont()
260			->setBold( true );
261			$this->colNum++;
262			}
263			}
264			}
265
266			/**
267			* Creates a new PHPExcel document and returns it
268			*
269			* @return PHPExcel
270			*/
271			protected function createExcelDocument() {
272
273			$fileTitle = Title::newFromText( $this->params[ 'templatefile' ], NS_FILE );
274
275			if ( $fileTitle !== null && $fileTitle->exists() ) {
276
277			$filePage = new ImagePage( $fileTitle, $this );
278
279			$virtualFile = $filePage->getDisplayedFile();
280			$virtualFilePath = $virtualFile->getPath();
281
282			$localFile= $virtualFile->getRepo()->getLocalReference( $virtualFilePath );
283			$localFilePath = $localFile->getPath();
284
285			$objPHPExcel = PHPExcel_IOFactory::load( $localFilePath );
286
287			$this->styled = true;
288
289			} else {
290
291			$objPHPExcel = new PHPExcel();
292
293			}
294
295			// Set document properties
296			$objPHPExcel->getProperties()->setCreator( "SemanticMediaWiki PHPExcel Export" );
297
298			return $objPHPExcel;
299			}
300
301			/**
302			* Check for the existence of the extra mainlabel.
303			* @param $label
304			* @return bool
305			*/
306			private function showLabel( $label ) {
307			return !(array_key_exists("mainlabel", $this->params) && $label === $this->params[ "mainlabel" ] . '#');
308			}
309
310			protected function readFieldValue( $field ) {
311			$valueCount = 0;
312			while ( ( $object = $field->getNextDataValue() ) !== false ) {
313			if( $valueCount === 0 ) {
314			$this->setValueAccordingToType($object);
315			} else {
316			$this->setOrAppendStringDataValue($object);
317			}
318			$valueCount++;
319			}
320			}
321
322			/**
323			* Checks the type of the value, and set's it in the sheet accordingly
324			* @param $object
325			*/
326			protected function setValueAccordingToType( $object ) {
327			//NOTE: must check against subclasses before superclasses
328			if( $object instanceof \SMWQuantityValue ) {
329			$this->setQuantityDataValue($object);
330			} else if( $object instanceof \SMWNumberValue ) {
331			$this->setNumberDataValue($object);
332			} else if ( $object instanceof \SMWTimeValue ) {
333			$this->setTimeDataValue( $object );
334			} else {
335			$this->setOrAppendStringDataValue($object);
336			}
337			}
338
339			/**
340			* @param $row
341			*/
342			protected function readRowData( $row ) {
343			foreach ( $row as $field ) {
344			if( $this->showLabel($field->getPrintRequest()->getLabel()) ) {
345			$this->readFieldValue($field);
346			$this->colNum++;
347			}
348			}
349			}
350
351			private function isPHPExcelInstalled() {
352			return class_exists( "PHPExcel" );
353			}
354
355			}
356
357

SemanticMediaWiki / SemanticResultFormats

Pull Request — master (#182)

SRFExcel::readRowData() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like