Completed
Push — master ( 981814...5ed881 )
by Jeroen De
236:00 queued 133:09
created

SpreadsheetPrinter::populateWorksheet()   A

Complexity

Conditions 3
Paths 4

Size

Total Lines 17

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 0
CRAP Score 12

Importance

Changes 0
Metric Value
dl 0
loc 17
ccs 0
cts 9
cp 0
rs 9.7
c 0
b 0
f 0
cc 3
nc 4
nop 2
crap 12
1
<?php
2
3
namespace SRF;
4
5
use ImagePage;
6
use PhpOffice\PhpSpreadsheet\Calculation\DateTime;
7
use PhpOffice\PhpSpreadsheet\Cell\Cell;
8
use PhpOffice\PhpSpreadsheet\Cell\DataType;
9
use PhpOffice\PhpSpreadsheet\IOFactory;
10
use PhpOffice\PhpSpreadsheet\Spreadsheet;
11
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
12
use PhpOffice\PhpSpreadsheet\Worksheet\Row;
13
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
14
use Sanitizer;
15
use SMW\FileExportPrinter;
16
use SMW\Query\ExportPrinter;
17
use SMWDataValue;
18
use SMWQueryResult;
19
use SMWResultArray;
20
use Title;
21
22
/**
23
 * @author Kim Eik
24
 * @since 1.9
25
 */
26
class SpreadsheetPrinter extends FileExportPrinter {
27
28
	const HEADER_ROW_OFFSET = 1;
29
30
	protected $fileFormats = [
31
		'xlsx' => [
32
			'writer' => 'Xlsx',
33
			'mimetype' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
34
			'extension' => '.xlsx',
35
		],
36
		'xls' => [
37
			'writer' => 'Xls',
38
			'mimetype' => 'aapplication/vnd.ms-excel',
39
			'extension' => '.xls',
40
		],
41
		'ods' => [
42
			'writer' => 'Ods',
43
			'mimetype' => 'application/vnd.oasis.opendocument.spreadsheet',
44
			'extension' => '.ods',
45
		],
46
		'csv' => [
47
			'writer' => 'Csv',
48
			'mimetype' => 'text/csv',
49
			'extension' => '.csv',
50
		],
51
	];
52
53
	protected $styled = false;
54
	protected $fileFormat;
55
56
	/**
57
	 * @see ExportPrinter::getMimeType()
58
	 *
59
	 * @since 1.8
60
	 *
61
	 * @param SMWQueryResult $queryResult
62
	 *
63
	 * @return string
64
	 */
65
	public function getMimeType( SMWQueryResult $queryResult ) {
66
		return $this->fileFormat[ 'mimetype' ];
67
	}
68
69
	/**
70
	 * @see ExportPrinter::getFileName
71
	 *
72
	 * @param SMWQueryResult $queryResult
73
	 *
74
	 * @return string
75
	 */
76
	public function getFileName( SMWQueryResult $queryResult ) {
77
		return ( $this->params[ 'filename' ] ?: base_convert( uniqid(), 16, 36 ) ) . $this->fileFormat[ 'extension' ];
78
	}
79
80
	/**
81
	 * @see ExportPrinter::outputAsFile
82
	 *
83
	 * @param SMWQueryResult $queryResult
84
	 * @param array $params
85
	 */
86
	public function outputAsFile( SMWQueryResult $queryResult, array $params ) {
87
88
		if ( array_key_exists( 'fileformat', $params) && array_key_exists( $params[ 'fileformat' ]->getValue(), $this->fileFormats )) {
89
			$this->fileFormat = $this->fileFormats[ $params[ 'fileformat' ]->getValue() ];
90
		} else {
91
			$this->fileFormat = $this->fileFormats[ 'xlsx' ];
92
		}
93
94
		parent::outputAsFile( $queryResult, $params );
95
	}
96
97
	/**
98
	 * @param $definitions \ParamProcessor\ParamDefinition[]
99
	 *
100
	 * @return array
101
	 */
102
	public function getParamDefinitions( array $definitions ) {
103
		$params = parent::getParamDefinitions( $definitions );
104
105
		$definitions[ 'searchlabel' ]->setDefault( wfMessage( 'srf-spreadsheet-link' )->inContentLanguage()->text() );
106
107
		$params[ 'templatefile' ] = [
108
			'type'    => 'string',
109
			'name'    => 'templatefile',
110
			'default' => '',
111
			'message' => 'srf-paramdesc-spreadsheet-templatefile',
112
		];
113
114
		$params[ 'filename' ] = [
115
			'type'    => 'string',
116
			'name'    => 'filename',
117
			'default' => '',
118
			'message' => 'srf-paramdesc-spreadsheet-filename',
119
		];
120
121
		$params[ 'fileformat' ] = [
122
			'type'    => 'string',
123
			'name'    => 'fileformat',
124
			'default' => 'xlsx',
125
			'tolower' => true,
126
			'message' => 'srf-paramdesc-spreadsheet-fileformat',
127
		];
128
129
		return $params;
130
	}
131
132
	/*
133
	 * Turns the PhpSpreadsheet document object into a string
134
	 */
135
136
	/**
137
	 * Return serialised results in specified format.
138
	 *
139
	 * @throws \PhpOffice\PhpSpreadsheet\Exception
140
	 * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
141
	 */
142 1
	protected function getResultText( SMWQueryResult $queryResult, $outputMode ) {
143
144 1
		if ( $outputMode === SMW_OUTPUT_FILE ) {
145
			return $this->getResultFileContents( $queryResult );
146
		}
147
148 1
		$this->isHTML = ( $outputMode === SMW_OUTPUT_HTML );
149 1
		return $this->getLink( $queryResult, $outputMode )->getText( $outputMode, $this->mLinker );
150
	}
151
152
	/**
153
	 * @param SMWQueryResult $queryResult
154
	 *
155
	 * @return string
156
	 * @throws \PhpOffice\PhpSpreadsheet\Exception
157
	 * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
158
	 */
159
	protected function getResultFileContents( SMWQueryResult $queryResult ) {
160
161
		$spreadsheet = $this->createSpreadsheet();
162
		$worksheet = $spreadsheet->getSheet( 0 );
163
164
		$this->populateWorksheet( $worksheet, $queryResult );
165
166
		//$spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight();
167
168
		for ( $i = 0; $i < count( $queryResult->getPrintRequests() ); $i++ ) {
0 ignored issues
show
Performance Best Practice introduced by Stephan
It seems like you are calling the size function count() as part of the test condition. You might want to compute the size beforehand, and not on each iteration.

If the size of the collection does not change during the iteration, it is generally a good practice to compute it beforehand, and not on each iteration:

for ($i=0; $i<count($array); $i++) { // calls count() on each iteration
}

// Better
for ($i=0, $c=count($array); $i<$c; $i++) { // calls count() just once
}
Loading history...
Performance Best Practice introduced by Stephan
Consider avoiding function calls on each iteration of the for loop.

If you have a function call in the test part of a for loop, this function is executed on each iteration. Often such a function, can be moved to the initialization part and be cached.

// count() is called on each iteration
for ($i=0; $i < count($collection); $i++) { }

// count() is only called once
for ($i=0, $c=count($collection); $i<$c; $i++) { }
Loading history...
169
			$worksheet->getColumnDimensionByColumn( $i )->setAutoSize( true );
170
		}
171
172
		$result = $this->getStringFromSpreadsheet( $spreadsheet );
173
		return $result;
174
	}
175
176
	/**
177
	 * Creates a new PhpSpreadsheet document and returns it
178
	 *
179
	 * @return Spreadsheet
180
	 * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
181
	 */
182
	protected function createSpreadsheet() {
183
184
		$fileTitle = Title::newFromText( $this->params[ 'templatefile' ], NS_FILE );
185
186
		if ( $fileTitle !== null && $fileTitle->exists() ) {
187
188
			$spreadsheet = $this->createSpreadsheetFromTemplate( $fileTitle );
189
190
			$this->styled = true;
191
192
		} else {
193
194
			$spreadsheet = new Spreadsheet();
195
196
		}
197
198
		// Set document properties
199
		$spreadsheet->getProperties()->setCreator( 'SemanticMediaWiki Spreadsheet Export' );
200
201
		return $spreadsheet;
202
	}
203
204
	/**
205
	 * @param $fileTitle
206
	 *
207
	 * @return Spreadsheet
208
	 * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
209
	 */
210
	private function createSpreadsheetFromTemplate( $fileTitle ) {
211
		$filePage = new ImagePage( $fileTitle, $this );
212
213
		$virtualFile = $filePage->getDisplayedFile();
214
		$virtualFilePath = $virtualFile->getPath();
215
216
		$localFile = $virtualFile->getRepo()->getLocalReference( $virtualFilePath );
217
		$localFilePath = $localFile->getPath();
218
219
		return IOFactory::load( $localFilePath );
220
	}
221
222
	/**
223
	 * @param SMWQueryResult $queryResult
224
	 * @param $worksheet
225
	 *
226
	 * @throws \PhpOffice\PhpSpreadsheet\Exception
227
	 */
228
	protected function populateWorksheet( Worksheet $worksheet, SMWQueryResult $queryResult ) {
229
230
		$rowIterator = $worksheet->getRowIterator( self::HEADER_ROW_OFFSET );
231
232
		//Get headers
233
		if ( $this->mShowHeaders ) {
234
			$this->populateHeaderRow( $rowIterator->current(), $queryResult );
235
			$rowIterator->next();
236
		}
237
238
		while ( $resultRow = $queryResult->getNext() ) {
239
240
			//Get data rows
241
			$this->populateRow( $rowIterator->current(), $resultRow );
242
			$rowIterator->next();
243
		}
244
	}
245
246
	/**
247
	 * Populates the PhpSpreadsheet sheet with the headers from the result query
248
	 *
249
	 * @param Row $row
250
	 * @param SMWQueryResult $queryResult The query result
251
	 *
252
	 * @return Row
253
	 * @throws \PhpOffice\PhpSpreadsheet\Exception
254
	 */
255
	protected function populateHeaderRow( Row $row, SMWQueryResult $queryResult ) {
256
257
		$printRequests = $queryResult->getPrintRequests();
258
		$cellIterator = $row->getCellIterator();
259
260
		foreach ( $printRequests as $printRequest ) {
261
262
			$cell = $cellIterator->current();
263
264
			$cell->setValue( $printRequest->getLabel() );
265
266
			$cell->getStyle()
267
				->getFont()
268
				->setBold( true );
269
270
			$cellIterator->next();
271
272
		}
273
274
		return $row;
275
	}
276
277
	/**
278
	 * Populates the PhpSpreadsheet document with the query data
279
	 *
280
	 * @param Row $row
281
	 * @param $resultRow
282
	 *
283
	 * @throws \PhpOffice\PhpSpreadsheet\Exception
284
	 */
285
	protected function populateRow( Row $row, $resultRow ) {
286
287
		$cellIterator = $row->getCellIterator();
288
289
		foreach ( $resultRow as $resultField ) {
290
291
			$this->populateCell( $cellIterator->current(), $resultField );
292
			$cellIterator->next();
293
		}
294
	}
295
296
	/**
297
	 * @param Cell $cell
298
	 * @param SMWResultArray $field
299
	 *
300
	 * @throws \PhpOffice\PhpSpreadsheet\Exception
301
	 */
302
	protected function populateCell( Cell $cell, SMWResultArray $field ) {
303
304
		$dataItems = $field->getContent();
305
306
		if ( $dataItems === false ) {
307
			return;
308
		}
309
310
		if ( count( $dataItems ) > 1 ) {
311
312
			while ( $value = $field->getNextText( SMW_OUTPUT_FILE ) ) {
313
				$values[] = $value;
0 ignored issues
show
Coding Style Comprehensibility introduced by Stephan
$values was never initialized. Although not strictly required by PHP, it is generally a good practice to add $values = array(); before regardless.

Adding an explicit array definition is generally preferable to implicit array definition as it guarantees a stable state of the code.

Let’s take a look at an example:

foreach ($collection as $item) {
    $myArray['foo'] = $item->getFoo();

    if ($item->hasBar()) {
        $myArray['bar'] = $item->getBar();
    }

    // do something with $myArray
}

As you can see in this example, the array $myArray is initialized the first time when the foreach loop is entered. You can also see that the value of the bar key is only written conditionally; thus, its value might result from a previous iteration.

This might or might not be intended. To make your intention clear, your code more readible and to avoid accidental bugs, we recommend to add an explicit initialization $myArray = array() either outside or inside the foreach loop.

Loading history...
314
			}
315
316
			$cell->setValueExplicit( join( ', ', $values ), DataType::TYPE_STRING );
0 ignored issues
show
Bug introduced by Stephan
The variable $values does not seem to be defined for all execution paths leading up to this point.

If you define a variable conditionally, it can happen that it is not defined for all execution paths.

Let’s take a look at an example:

function myFunction($a) {
    switch ($a) {
        case 'foo':
            $x = 1;
            break;

        case 'bar':
            $x = 2;
            break;
    }

    // $x is potentially undefined here.
    echo $x;
}

In the above example, the variable $x is defined if you pass “foo” or “bar” as argument for $a. However, since the switch statement has no default case statement, if you pass any other value, the variable $x would be undefined.

Available Fixes

  1. Check for existence of the variable explicitly:

    function myFunction($a) {
        switch ($a) {
            case 'foo':
                $x = 1;
                break;
    
            case 'bar':
                $x = 2;
                break;
        }
    
        if (isset($x)) { // Make sure it's always set.
            echo $x;
        }
    }
    
  2. Define a default value for the variable:

    function myFunction($a) {
        $x = ''; // Set a default which gets overridden for certain paths.
        switch ($a) {
            case 'foo':
                $x = 1;
                break;
    
            case 'bar':
                $x = 2;
                break;
        }
    
        echo $x;
    }
    
  3. Add a value for the missing path:

    function myFunction($a) {
        switch ($a) {
            case 'foo':
                $x = 1;
                break;
    
            case 'bar':
                $x = 2;
                break;
    
            // We add support for the missing case.
            default:
                $x = '';
                break;
        }
    
        echo $x;
    }
    
Loading history...
317
318
		} else {
319
320
			$nextDataValue = $field->getNextDataValue();
321
322
			if ( $nextDataValue !== false ) {
323
				$this->populateCellAccordingToType( $cell, $nextDataValue );
324
			}
325
326
		}
327
	}
328
329
	/**
330
	 * Checks the type of the value, and set's it in the sheet accordingly
331
	 *
332
	 * @param Cell $cell
333
	 * @param SMWDataValue $value
334
	 *
335
	 * @throws \PhpOffice\PhpSpreadsheet\Exception
336
	 */
337
	protected function populateCellAccordingToType( Cell $cell, SMWDataValue $value ) {
338
339
		//NOTE: must check against subclasses before superclasses
340
		if ( $value instanceof \SMWQuantityValue ) {
341
342
			$this->setQuantityDataValue( $cell, $value );
343
344
		} elseif ( $value instanceof \SMWNumberValue ) {
345
346
			$this->setNumberDataValue( $cell, $value );
347
348
		} elseif ( $value instanceof \SMWTimeValue ) {
349
350
			$this->setTimeDataValue( $cell, $value );
351
352
		} else {
353
354
			$this->setStringDataValue( $cell, $value );
355
356
		}
357
358
	}
359
360
	/**
361
	 * Sets a quantity value at the given col,row location
362
	 *
363
	 * @param Cell $cell
364
	 * @param \SMWQuantityValue $value SMWDataValue  the raw data value object
365
	 *
366
	 * @throws \PhpOffice\PhpSpreadsheet\Exception
367
	 */
368
	protected function setQuantityDataValue( Cell $cell, \SMWQuantityValue $value ) {
369
370
		$type = DataType::TYPE_NUMERIC;
371
		$unit = $value->getUnit();
372
		$number = $value->getNumber();
373
374
		$cell->setValueExplicit( $number, $type );
375
376
		if ( !$this->styled ) {
377
			$cell->getStyle()
378
				->getNumberFormat()
379
				->setFormatCode( '0 "' . $unit . '"' );
380
		}
381
	}
382
383
	/**
384
	 * Sets a numeric value at the given col,row location
385
	 *
386
	 * @param Cell $cell
387
	 * @param \SMWNumberValue $value SMWDataValue the raw data value object
388
	 *
389
	 * @throws \PhpOffice\PhpSpreadsheet\Exception
390
	 */
391
	protected function setNumberDataValue( Cell $cell, \SMWNumberValue $value ) {
392
393
		$type = DataType::TYPE_NUMERIC;
394
		$number = $value->getNumber();
395
396
		$cell->setValueExplicit( $number, $type );
397
	}
398
399
	/**
400
	 * Sets a date/time value at the given col,row location
401
	 *
402
	 * @param Cell $cell
403
	 * @param \SMWTimeValue $value the raw data value object
404
	 *
405
	 * @throws \PhpOffice\PhpSpreadsheet\Exception
406
	 */
407
	protected function setTimeDataValue( Cell $cell, \SMWTimeValue $value ) {
408
409
		$type = DataType::TYPE_NUMERIC;
410
		$number = DateTime::DATEVALUE( str_replace( 'T', ' ', $value->getISO8601Date() ) );
411
412
		$cell->setValueExplicit( $number, $type );
413
414
		if ( !$this->styled ) {
415
			$cell->getStyle()
416
				->getNumberFormat()
417
				->setFormatCode( NumberFormat::FORMAT_DATE_DDMMYYYY );
418
		}
419
	}
420
421
	/**
422
	 * Sets or appends a string value at the given col,row location
423
	 *
424
	 * If there already exists a value at a given col,row location, then
425
	 * convert the cell to a string and append the data value. Creating
426
	 * a list of comma separated entries.
427
	 *
428
	 * @param Cell $cell
429
	 * @param $value SMWDataValue the raw data value object
430
	 *
431
	 * @throws \PhpOffice\PhpSpreadsheet\Exception
432
	 */
433
	protected function setStringDataValue( Cell $cell, SMWDataValue $value ) {
434
435
		$type = DataType::TYPE_STRING;
436
		$text = $value->getWikiValue();
437
		$text = Sanitizer::decodeCharReferences( $text );
438
		$text = DataType::checkString( $text );
439
440
		$cell->setValueExplicit( $text, $type );
441
	}
442
443
	/**
444
	 * @param Spreadsheet $spreadsheet
445
	 *
446
	 * @return string
447
	 * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
448
	 */
449
	protected function getStringFromSpreadsheet( Spreadsheet $spreadsheet ) {
450
451
		$writer = IOFactory::createWriter( $spreadsheet, $this->fileFormat[ 'writer' ] );
452
453
		ob_start();
454
		$writer->save( 'php://output' );
455
		return ob_get_clean();
456
	}
457
458
}
459
460