Completed
Push — master ( 0c0c23...2995b2 )
by Jeroen De
16:46
created

formats/spreadsheet/SpreadsheetPrinter.php (2 issues)

Upgrade to new PHP Analysis Engine

These results are based on our legacy PHP analysis, consider migrating to our new PHP analysis engine instead. Learn more

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++ ) {
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
$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
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